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

標籤：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. 複製檔案至備庫
   

   
   

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.修改監聽檔案

[[email protected] ~]$ 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備份主庫

建立備份存放目錄

[[email protected] ~]$ 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.複製檔案至備庫

[[email protected] ~]$ scp -r /opt/oracle/dbackup/ db2:/opt/oracle

[[email protected] ~]$ cd $ORACLE_HOME/dbs

[[email protected] dbs]$ scp -r orapwtpy100 db2:$ORACLE_HOME/dbs

[[email protected] dbs]$ cd $ORACLE_HOME/network/admin

[[email protected] 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,因為這個是主庫的參數檔案，備庫略有不同

[[email protected] ~]$ 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‘

建立相應的目錄，以便啟動oracle

[[email protected] ~]$ mkdir -p /opt/oracle/admin/tpy100/adump
[[email protected] ~]$ mkdir -p /opt/oracle/oradata/tpy100
[[email protected] ~]$ mkdir -p /opt/oracle/flash_recovery_area/tpy100

[[email protected] ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/tpy100/control01.ctl
[[email protected] ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/flash_recovery_area/tpy100/control02.ctl

SQL> shutdown immediate;

SQL> startup nomount pfile=‘/tmp/tpy100.pfile‘

SQL> create spfile from pfile=‘/tmp/tpy100.pfile‘;

SQL> alter database mount;

RMAN> restore database;

SQL > alter database open read only;

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

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

SQL> startup mount;
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‘

則是備庫的監聽未開啟,使用lsnrctl start,啟動監聽,再開啟資料庫就不會報錯了.

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

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

本文出自 “楓林晚” 部落格，請務必保留此出處http://fengwan.blog.51cto.com/508652/1743295

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