建立Local Physical Standby Oracle9i standby 資料庫筆記

建立Local Physical Standby  Oracle9i standby 資料庫筆記

說明:已配置一個遠端的Standby,現在再配置一個Local Standby

規劃：
1 資料檔案，日誌檔案轉換路徑：/opt/oracle/oradata/mydb --&gt /opt/oracle/oradata/standby

2 關閉Primary Instance 複製資料檔案到既定目的地,不複製日誌檔案
shutdown immediate
cp /opt/oracle/oradata/mydb/*.dbf  /opt/oracle/oradata/standby/

3 為Standby 資料庫建立控制檔案
SQL> Startup
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/opt/oracle/oradata/standby/control01.ctl';

NOTE：先停庫，將data files複製到standby，然後啟動主庫，建立standby的control files。(有什麼不一樣?)

4 編輯　Standby引數檔案　
Primary: create pfile='/home/oracle/pfilestandby.ora' from spfile;

再更改，新增一些引數．
#備庫instance_name=standby　與主庫mydb可以不一樣,但db_name必須一樣  注意幾個引數

*.control_files='/opt/oracle/oradata/standby/control01.ctl','/opt/oracle/oradata/standby/control02.ctl','/opt/oracle/oradata/standby/control03.ctl'
*.db_name='mydb'
*.instance_name='standby'
lock_name_space=standby
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/opt/oracle/standbyarchive'
db_file_name_convert=('/opt/oracle/oradata/mydb', '/opt/oracle/oradata/standby')
log_file_name_convert=('/opt/oracle/oradata/standby', '/opt/oracle/oradata/standby')
log_archive_dest_1=('LOCATION=/opt/oracle/standbyarchive')

 

5 生成密碼檔案，啟動Standby

密碼檔案　
cp $ORACLE_HOME/dbs/orapwmydb  $ORACLE_HOME/dbs/orapwstandby

export ORACLE_SID=standby

sqlplus /nolog
SQL> connect / as sysdba
SQL> STARTUP NOMOUNT pfile='/home/oracle/pfilestandby.ora';
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

這裡最好建立Standby Redo Log
alter database add standby logfile group 4 ('/opt/oracle/oradata/standby/redo04.log') size 10M;
alter database add standby logfile group 5 ('/opt/oracle/oradata/standby/redo05.log') size 10M;
alter database add standby logfile group 6 ('/opt/oracle/oradata/standby/redo06.log') size 10M;
alter database add standby logfile group 7 ('/opt/oracle/oradata/standby/redo07.log') size 10M;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

6 在Primary上啟用到物理Standby資料庫的歸檔
localstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.230.17.34)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = standby)
    )
  )
 
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=localstandby LGWR' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

說明用LOG_ARCHIVE_DEST_3的原因是LOG_ARCHIVE_DEST_2已被用來配置了一個Remote Physical Standby Database.

7 測試　

啟動遠端歸檔
Primary SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

在Primary 查詢V$ARCHIVED_LOG,驗證是否生成歸檔
(其實也可以直接到相關目錄下檢視Log是否建立):

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

查詢從Primary接收到的所有歸檔(Standby 端)
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;

驗證是否新的歸檔Redo日誌已經被應用
SQL> SELECT  sequence#, applied FROM v$archived_log ORDER BY sequence#;

OK.表明我們還是成功的。暫時告一段落。