Oracle 11G R2利用RMAN搭建DataGuard環境

Oracle 11G R2利用RMAN搭建DataGuard環境

環境：

角色	機器名	作業系統	IP	備忘
主庫	db1	CentOS 5.11 x86_64	192.168.2.241	安裝Oracle，建立資料庫
備庫	db2	CentOS 5.11 x86_64	192.168.2.242	只安裝Oracle

準備工作:
在db1的/etc/hosts裡增加
127.0.0.1      db1
192.168.2.242    db2
在db2的/etc/hosts裡增加
127.0.0.1      db2
192.168.2.241    db1

目錄

1. 主庫開啟歸檔及強制歸檔
2. 建立3組standby redolog
3. 修改參數檔案
4. 修改監聽檔案
5. RMAN備份主庫
6. 複製檔案至備庫
7. 恢複參數檔案(db2)
8. 修改備庫參數檔案(db2)
9. 準備RMAN恢複工作(db2)
10. 產生備庫參數檔案(db2)
11. 恢複資料庫(db2)
12. 啟動備庫(db2)

1.主庫開啟歸檔及強制歸檔(db1)

檢查Oracle是否開啟歸檔
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival            Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    6
Current log sequence          8
#可以看到Automatic archival            Disabled說明未開啟歸檔
開啟歸檔(開啟歸檔需要先關閉Oracle,然後將資料庫啟動至mount狀態才能修改)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;        #開啟歸檔

SQL> alter database force logging;    #開啟強制歸檔也可以在資料庫open狀態下開啟

SQL> alter database open;                  #開啟資料庫

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival            Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    6
Next log sequence to archive  8
Current log sequence          8

2.建立多組standby redo log，最少需要多一組，standby redo log是使用Real Time Apply的必要條件
SQL> select group#,member from v$logfile;
    GROUP#        MEMBER
--------------------------------------------------------------------------------
        3                /opt/oracle/oradata/tpy100/redo03.log
        2                /opt/oracle/oradata/tpy100/redo02.log
        1                /opt/oracle/oradata/tpy100/redo01.log
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby04.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby05.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby06.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby07.log') size 50m;
SQL> select group#,member from v$logfile;
    GROUP#        MEMBER
--------------------------------------------------------------------------------
        3                /opt/oracle/oradata/tpy100/redo03.log
        2                /opt/oracle/oradata/tpy100/redo02.log
        1                /opt/oracle/oradata/tpy100/redo01.log
        4                /opt/oracle/oradata/tpy100/standby04.log
        5                /opt/oracle/oradata/tpy100/standby05.log
        6                /opt/oracle/oradata/tpy100/standby06.log

        7                /opt/oracle/oradata/tpy100/standby07.log

3.修改參數檔案
修改參數檔案前，我們先進行備份
SQL> create pfile='/tmp/tpy100.pfile' from spfile;
在修改前我們需要查看下備份的參數檔案，根據具體環境更改下面語句
SQL> alter system set db_unique_name=db1 scope=spfile;
SQL> alter system set log_archive_config='dg_config=(db1,db2)' scope=both;
SQL> alter system set log_archive_dest_1= 'location=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db1' scope=both;
報錯：
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME db1 is not in the Data Guard Configuration

可能會遇上如下報錯資訊，這個時候需要重啟下資料庫
SQL> shutdown immediate;
SQL> startup
SQL> alter system set log_archive_dest_1= 'location=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db1' scope=both;
SQL> alter system set log_archive_dest_2= 'service=db2 async  valid_for=(online_logfiles,primary_role)  db_unique_name=db2' scope=both;
SQL> alter system set log_archive_dest_state_1=enable scope=both;
SQL> alter system set log_archive_dest_state_2=enable scope=both;
SQL> alter system set standby_file_management=auto scope=both;
SQL> alter system set fal_server=db2 scope=both;
SQL> alter system set fal_client=db1 scope=both;
SQL> alter system set db_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' scope=spfile;
SQL> alter system set log_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' scope=spfile;

4.修改監聽檔案
[oracle@db1 ~]$ vim /opt/oracle/product/11.2.0/network/admin/tnsnames.ora
在後面增加
db1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db1)
    )
  )

db2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )

5.RMAN備份主庫
建立備份存放目錄

[oracle@db1 ~]$ mkdir -p /opt/oracle/dbackup
執行備份
rman>run{
allocate channel c1 type disk;
backup format '/opt/oracle/dbackup/tpy100_%T_%s_%p' database;
sql 'alter system archive log current';
backup format '/opt/oracle/dbackup/archive_log_%T_%s_%p' archivelog all;
backup spfile format '/opt/oracle/dbackup/spfile_%u_%T.bak';
release channel c1;
}
rman>copy current controlfile for standby to '/opt/oracle/dbackup/standby.ctl';

6.複製檔案至備庫
[oracle@db1 ~]$ scp -r /opt/oracle/dbackup/ db2:/opt/oracle
[oracle@db1 ~]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ scp -r orapwtpy100 db2:$ORACLE_HOME/dbs
[oracle@db1 dbs]$ cd $ORACLE_HOME/network/admin
[oracle@db1 admin]$ scp -r listener.ora tnsnames.ora db2:$ORACLE_HOME/network/admin

7.恢複參數檔案(db2)
RMAN> set dbid 2926260986
RMAN> startup nomount;
#這裡會報錯不用理會即可
RMAN> restore spfile to pfile '/tmp/tpy100.pfile' from '/opt/oracle/dbackup/spfile_04quaekm_20160219.bak';
#我們將參數檔案恢複至/tmp/tpy100.pfile,因為這個是主庫的參數檔案，備庫略有不同
8.修改備庫參數檔案(db2)
[oracle@db2 ~]$ vim /tmp/tpy100.pfile
#將裡面的DB1 db1變成相應的DB2 db2,將DB1變成db1
tpy100.__db_cache_size=322961408
tpy100.__java_pool_size=4194304
tpy100.__large_pool_size=4194304
tpy100.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
tpy100.__pga_aggregate_target=339738624
tpy100.__sga_target=503316480
tpy100.__shared_io_pool_size=0
tpy100.__shared_pool_size=159383552
tpy100.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/tpy100/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/tpy100/control01.ctl','/opt/oracle/flash_recovery_area/tpy100/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area'
*.db_name='tpy100'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='DB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tpy100XDB)'
*.fal_client='DB2'
*.fal_server='DB1'
*.log_archive_config='dg_config=(db2,db1)'
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db2'
*.log_archive_dest_2='service=db1 async  valid_for=(online_logfiles,primary_role)  db_unique_name=db1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area'
*.memory_target=843055104
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

9.準備RMAN恢複工作(db2)
建立相應的目錄，複製備庫控制檔案到相應的位置
[oracle@db2 ~]$ mkdir -p /opt/oracle/admin/tpy100/adump
[oracle@db2 ~]$ mkdir -p /opt/oracle/oradata/tpy100
[oracle@db2 ~]$ mkdir -p /opt/oracle/flash_recovery_area/tpy100
[oracle@db2 ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/tpy100/control01.ctl
[oracle@db2 ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/flash_recovery_area/tpy100/control02.ctl
[oracle@db2 ~]$ lsnrctl start
#啟動監聽

10.產生備庫參數檔案(db2)

SQL> shutdown immediate;
SQL> startup nomount pfile='/tmp/tpy100.pfile'
SQL> create spfile from pfile='/tmp/tpy100.pfile';
SQL> alter database mount;

11.恢複資料庫(db2)

RMAN> restore database;

12.啟動備庫(db2)

SQL > alter database open read only;
#在這裡啟動的時候如果出現
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/tpy100/system01.dbf'
先使用shutdown immediate關閉後再重新啟動

SQL > alter database recover managed standby database using current logfile disconnect from session;

如果需要重啟備庫,則需要按如下命令進行重啟
SQL> startup;

SQL> alter database recover managed standby database using current logfile disconnect from session;
注意：剛重啟完你會發現主庫的資料還沒過來，但是過段時間就過來了，在生產環境中我們需要快速處理這個問題，以便減少宕機時間。
關閉完備庫後，在主庫查看
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME                    STATUS                        ERROR
------------------------------ --------
LOG_ARCHIVE_DEST_1          VALID
LOG_ARCHIVE_DEST_2      ERROR          ORA-03113: end-of-file on  communication channel
可以看到LOG_ARCHIVE_DEST_2是錯誤的，這個是因為沒有串連到備庫的歸檔路徑，預設情況下Dataguard會每300秒自動連接，這邊為了快讀處理
解決辦法：在主庫執行
SQL> alter system set log_archive_dest_state_2= enable;
再查詢，如果依然是如此則需要檢查備庫的網路及監聽

測試有如下語句：
SQL> select sequence#,applied from v$archived_log;
SQL> select process,status from v$managed_standby;
SQL> select sequence# from v$log_history;

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法