rman duplicate搭建第二個 dg

1、備庫網路設定
cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 lrwltestdb  //刪除lrwltestdb
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.21   lrwldb
192.168.0.28   lrwldbdg


2、 設定備庫force_logging及資料庫歸檔；
[oracle@lrwltestdb ~]$ sqlplus / as sysdba 
SQL> startup mount;
SQL> show parameter instance_name; 
SQL> alter database force logging;
SQL> select force_logging from v$database;
SQL> select name from v$archived_log;
SQL> alter database archivelog; 
SQL> archive log list;
SQL> alter database open;
SQL> alter system archive log current;  //使其產生歸檔目錄，即/u01/app/oracle/flash_recovery_area/LRWLDBDG/archivelog


3、建立standby庫密碼檔案並scp到備庫；
主庫中操作
SQL> exit
[oracle@lrwltestdb ~]$cd $ORACLE_HOME/dbs
[oracle@lrwltestdb dbs]$ orapwd file=orapwLRWLDB password=oracle force=y
[oracle@lrwltestdb dbs]$ ll
[oracle@lrwltestdb dbs]$ scp orapwLRWLDB 192.168.0.28:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwLRWLDBDG


備庫中檢查確認
[oracle@lrwltestdbst ~]$cd $ORACLE_HOME/dbs
[oracle@lrwltestdbst dbs]$ll


4、建立主庫引數檔案並做調整
主庫中操作
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(LRWLDB,LRWLDBST,LRWLDBDG)';
SQL> create pfile from spfile;
SQL> exit


[oracle@lrwltestdb dbs]$ cd $ORACLE_HOME/dbs
[oracle@lrwltestdb dbs]$ scp initLRWLDB.ora 192.168.0.28:$ORACLE_HOME/dbs/initLRWLDBDG.ora


5、主庫修改引數後，第一備庫也需要修改log_archive_config引數
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(LRWLDBST,LRWLDB,LRWLDBDG)';


6、建立備庫引數檔案並作調整
[oracle@lrwltestdbst dbs]$ ll
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/oradata/LRWLDBDG
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/admin/LRWLDBDG/adump
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area/LRWLDBDG/
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area/LRWLDBDG/archivelog/
[oracle@lrwltestdbst dbs]$ vi initLRWLDB.ora  //新增如下內容
*.audit_file_dest='/u01/app/oracle/admin/LRWLDBDG/adump'   //LRWLDB修改為LRWLDBDG
*.control_files='/u01/app/oracle/oradata/LRWLDBDG/control01.ctl','/u01/app/oracle/flash_recovery_area/LRWLDBDG/control02.ctl'  //LRWLDB修改為LRWLDBDG
*.log_archive_config='dg_config=(LRWLDBDG,LRWLDB,LRWLDBST)';
DB_UNIQUE_NAME=LRWLDBDG
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/LRWLDBDG/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LRWLDBDG'
LOG_ARCHIVE_DEST_4='SERVICE=LRWLDB LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE DB_UNIQUE_NAME=LRWLDB'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/LRWLDB','/u01/app/oracle/oradata/LRWLDBDG'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/flash_recovery_area/LRWLDB/archivelog','/u01/app/oracle/flash_recovery_area/LRWLDBDG/archivelog'
STANDBY_FILE_MANAGEMENT=AUTO


7、建立備庫相應的目錄和檔案並複製主庫資料庫檔案到備庫上
備庫中操作
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/diag/rdbms/lrwldb/LRWLDBDG/trace
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/diag/rdbms/lrwldb/LRWLDBDG/cdump
[oracle@lrwltestdbst dbs]$ mkdir -p /u01/app/oracle/diag/rdbms/lrwldb/LRWLDBDG/alert
SQL> create spfile from pfile;


8、建立監聽程式並測試
主庫中操作
[oracle@lrwltestdb dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@lrwltestdb admin]$ vi tnsnames.ora              --新增
 
LRWLDBDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.28)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LRWLDBDG)
      (INSTANCE_NAME = LRWLDBDG)
    )
  )


備庫中操作
[oracle@lrwltestdbst dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@lrwltestdbst dbs]$ vi listener.ora  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.28)(PORT = 1521))  //修改localhost為192.168.0.197
    )
  )


 //修改=LRWLDB為=LRWLDBDG
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=LRWLDBDG)
         (SID_NAME=LRWLDBDG)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
      )
[oracle@lrwltestdbst dbs]$ lsnrctl status
[oracle@lrwltestdbst dbs]$ lsnrctl stop
[oracle@lrwltestdbst dbs]$ lsnrctl start
[oracle@lrwltestdbst dbs]$ vi tnsnames.ora
LRWLDBDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.28)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LRWLDBDG)
      (INSTANCE_NAME = LRWLDBDG)
    )
  )


LRWLDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LRWLDB)
      (INSTANCE_NAME = LRWLDB)
    )
  )


備註：主備庫都要執行以下的操作
[oracle@lrwltestdb admin]$ tnsping LRWLDB
[oracle@lrwltestdb admin]$ tnsping LRWLDBDG




9、在主端生成完全備份，包括歸檔日誌與standby控制檔案 
mkdir /u01/rman     --存放rman檔案
run 
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rman/%F';
ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/u01/rman/%U';
BACKUP DATABASE SKIP INACCESSIBLE FILESPERSET 10 PLUS ARCHIVELOG FILESPERSET 20
DELETE ALL INPUT;
RELEASE CHANNEL CH1;
}
--將完全備份copy 到備機  
scp * 192.168.0.28:/u01/rman/


10、備庫以pfile啟動到nomount狀態 
startup nomount;


11、主庫使用RMAN的Duplicate建立dg，如果資料檔案與重做日誌檔案目錄相同，要新增 nofilenamecheck
rman target /
connect auxiliary sys/oracle@lrwldbdg
RMAN>  run{  
duplicate target database for standby nofilenamecheck dorecover;  
}


12、主庫設定傳輸引數
LOG_ARCHIVE_DEST_4='SERVICE=LRWLDBDG LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LRWLDBDG'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4='ENABLE';
檢視主備庫日誌，等歸檔全部傳輸完畢
備庫open;


13、測試
主庫中操作
SQL> create table tab3(id int); 
SQL> insert into tab3 values(20000);
SQL> commit;
備庫中檢查確認：
SQL> select * from tab3;


主庫中執行：
SQL> insert into tab3 values(166);
SQL> commit;
備庫中檢查確認：
SQL> select * from tab3;


14、主庫和備庫切換演練
主備切換
主切換到備，出現2個備庫，可以任意提升一個為主庫
SQL> select open_mode,database_role,protection_mode,switchover_status from v$database;
SQL> alter database commit to switchover to primary;  //把備切換為主
SQL> alter database commit to switchover to standby;  //把主切換到備


主庫中操作
SQL> show parameter unique;
SQL> select open_mode,database_role,protection_mode,switchover_status from v$database;
SQL> alter database commit to switchover to standby with session shutdown; 
SQL> shutdown immediate
SQL> startup mount;
SQL> select open_mode,database_role,protection_mode,switchover_status from v$database;
SQL> alter database commit to switchover to primary;
SQL> select open_mode,database_role,protection_mode,switchover_status from v$database;
SQL> alter database open;


備庫中操作
SQL> show parameter unique;
SQL> select open_mode,database_role,protection_mode,switchover_status from v$database;
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup

在生產操作這個文件的時候有個問題，第一次duplicate的時候由於溝通有誤，檔案沒有完全歸檔完畢，第二次做的時候就一直報錯，也沒找到原因，且由於生產的文件裡寫了delete input all;刪除了所有的歸檔檔案導致之前的一個dg也沒法應用，後來發現是由於備庫已經有資料檔案導致無法duplicate恢復，於是刪除備庫的資料檔案，重新rman備份並duplicate，最後一看資料傳送過來了，之前搞壞的 那個dg也同理重做了一個，還好沒對生產造成什麼影響。原本估計1-3個小時的工作搞了7個小時。下次得千萬注意。