Linux 9i下Dataguard配置

發布: 2008-10-21 10:05 | 作者: 網絡轉載 | 來源: 網絡轉載 | 查看: 155次 | 進入軟件測試論壇討論

9.主輔數據庫的切換（停止主數據庫,啟用備用數據庫）
修改primary的參數文件initoracle.ora(先做好備份)
增加
*.standby_archive_dest='/home/oracle/oradata/standbyarch'
*.fal_server='standby'
*.fal_client='primary'
*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")
*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archivelog","/home/oracle/oradata/archivelog")
*.STANDBY_FILE_MANAGEMENT='AUTO'
刪除  log_archive_dest_2參數

修改standby的參數文件initoracle.ora(先做好備份)
增加
*.log_archive_dest_2='service=primary mandatory reopen=60'
*.log_archive_dest_state_2='ENABLE'
刪除
*.STANDBY_ARCHIVE_DEST=/home/oracle/oradata/archivelog
*.fal_server='standby'
*.fal_client='primary'
*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")
*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archivelog","/home/oracle/oradata/archivelog")
*.STANDBY_FILE_MANAGEMENT=AUTO

在primary主機上執行
SQL> alter database commit to switchover to physical standby with session shutdown ;
Database altered.
察看primary主機上的后臺日志
…………………………….

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
以備用模式（standby）啟用主數據
SQL> create spfile from pfile;
SQL> startup nomount;
SQL>show parameter standby_file_management;
SQL> alter database mount standby database;
Database altered.

打開備用數據庫（在standby主機上執行）
[oracle@standby oracle]$ sqlplus "/ as sysdba"

SQL> alter database commit to switchover to primary with session shutdown ;
Database altered.
SQL> shutdown immediate;
     ORA-01507: database not mounted
ORACLE instance shut down
SQL>create spfile from pfile;
SQL> startup;
        ……
Database mounted.
Database opened.
SQL> select SEQUENCE#,GROUP#,STATUS from v$log;
SQL> select sequence#,group#,status from v$log;

SEQUENCE#     GROUP# STATUS
---------- ---------- --------------------------------
        72          1 INACTIVE
        73          2 INACTIVE
        74          3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select sequence#,group#,status from v$log;

SEQUENCE#     GROUP# STATUS
---------- ---------- --------------------------------
        75          1 CURRENT
        73          2 INACTIVE
        74          3 ACTIVE

在primary主機上執行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

在primary主機上觀察日志應用情況
[oracle@primary bdump]$ tail -f alert_oracle.log
Starting datafile 2 recovery in thread 1 sequence 93
Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf'
Starting datafile 3 recovery in thread 1 sequence 93
Datafile 3: '/opt/oracle/oradata/primary/users01.dbf'
……………………………………………….
Media recover

10.現在可做一個測試，在standby主機上進行數據修改（standby主機現在做primary）

SQL> create table t as select * from dba_users;
Table created.
SQL> alter system switch logfile;
System altered.

在從庫上(primary主機上)以read only打開數據庫，執行查詢
SQL> select username from t;
select username from t
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> select username from t;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYS
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

常見問題

至此，主副的配置已基本完成，可做以下實驗來驗證配置的準確性。
1．在主庫上新建一個表，看修改時候能馬上傳到副庫上
2．在主庫上新建一個表空間，新增加一個datafile,看修改時候能馬上傳到副庫上（應該是只要在副庫上執行恢復模式，就能在副庫上看到新建的表空間
3．在主庫上新建一個臨時表空間，rename datafile 均不能應用到副庫上.
4．應當實時察看standby庫的alert文件，就能清晰明了地知道主副更新的情況。
5．關于啟動關閉順序
啟動的時候，先從庫的listener,再啟動從庫,再啟動主庫的listener,再主庫
關閉的時候，先關閉主庫，再啟動從庫。
6. 8i副數據庫切換為主的話，將無法再切為副。主數據庫也是！也就是說，只能完成一次切換,這叫failover！9i可實現主副數據庫任意切換，這叫switchover
------------------------------------------------------------------------------------------------------------------
第六點我理解得有些問題,看了版主Rollingpig在itpub上的結論,覺得他說的應該是對的,現將他的陳述列在下面!
Rollingpig
我給個結論吧。在9i 的dataguad環境中：
1。Switch Over 必須是Primary 正常，并且是必須Primary 主動先Switch 成 standby. 然后standby 才能switch 成primary.
2。如果需要作成primary出問題，standby 能接管的話，必須作 failover ,而不是Switch Over
-------------------------------------------------------------------------------------------------------------------

7. 察看主機當前的運行狀態：
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY   MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY
8.查看主數據庫日志是否全部傳送到副數據庫，可查看v$archive_gap,更簡單的方法是查看各自主機的日志歸檔目錄的日志序號即可。

11．常用的一些方法：
可在副數據庫上運行一些腳本，確保主數據庫上的更新能及時在副數據庫上得到實現�？蓪⒁韵履_本放在crontab表中。
oracle@standby $cat refresh （刷新腳本）
ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/product/9.2.0
export ORACLE_SID ORACLE_HOME
DATE=`date '+%Y%m%d'`
touch /home/oracle/refresh_$DATE.log
$ORACLE_HOME/bin/sqlplus /nolog << EOF
spool /home/oracle/refresh_$DATE.log
connect sys/abc123 as sysdba
shutdown immediate;
quit
EOF
$ORACLE_HOME/bin/sqlplus /nolog << EOF2
spool /home/oracle/refresh_2_$DATE.log
connect sys/abc123  as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/initoracle.ora;
alter database mount standby database;
alter database set standby database to maximize performance;
alter database recover managed standby database disconnect from session;
spool off
EOF2
oracle@standby $cat readonly （更新腳本）
#!/bin/sh
ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/product/9.2.0
export ORACLE_SID ORACLE_HOME
$ORACLE_HOME/bin/sqlplus /nolog << EOF
spool /home/oracle/refresh-read.log
connect sys/abc123 as sysdba
rem change from recover mode to read-only
alter database recover managed standby database cancel;
alter database open read only ;
spool off
EOF

switchover過程：

12.把數據庫切換回到主節點
先將standby此時的initoracle.ora恢復為以前是standby時的參數。
在主節點（standby主機上）
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
statORACLE instance shut down.
SQL> startup nomount  pfile=/home/oracle/product/9.2.0/dbs/initoracle.ora;
;
ORACLE instance started.
Total System Global Area  135337420 bytes
Fixed Size                   452044 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

在備用節點（在primary主機上）
先將primary此時的initoracle.ora恢復為以前是primary時的參數。
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup  pfile=/home/oracle/product/9.2.0/dbs/initoracle.ora;
ORACLE instance started.
Total System Global Area  135337420 bytes
Fixed Size                   452044 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.