使用rman创建physical standby过程手记

测试说明：在本机使用rman创建physical standby;
primary db: matx
standby db: mat_standby
OS : HPUX 11.11
ORACLE: 9207

首先确认控制文件的maxlogfiles参数值，该值默认为5，由于在standby需要创建standby redo log,如果该参数过小，会导致ORA-01185: logfile group number 6 is invalid错误，因此，如果该参数过小，需要在开始之前重建controlfile. 建议值: maxlogfiles>主库redo log group数*2+1

------------------------------------------------------
  重建主库的控制文件
  --如果maxlogfiles参数足够大，该步骤可以略过。
------------------------------------------------------
(1)导出重建脚本
SQL> alter database backup controlfile to trace;
在udump目录下找到新生成的trace文件,找到控制文件创建脚本，注意取"Set #1. NORESETLOGS case"的，而非“Set #2. RESETLOGS case”。

(2)在主库重建控制文件
SQL> shutdown immediate
SQL> STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE "matx" NORESETLOGS FORCE LOGGING ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 15
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/u08/oradata/matx/redo01.log'  SIZE 100M,
  GROUP 2 '/u08/oradata/matx/redo02.log'  SIZE 100M,
  GROUP 3 '/u08/oradata/matx/redo03.log'  SIZE 100M,
  GROUP 4 '/u08/oradata/matx/redo04.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/u08/oradata/matx/system01.dbf',
  '/u08/oradata/matx/undotbs01.dbf',
  '/u08/oradata/matx/indx01.dbf',
  '/u08/oradata/matx/tools01.dbf',
  '/u08/oradata/matx/users01.dbf',
  '/u08/oradata/matx/mat_data.dbf',
  '/u08/oradata/matx/mat_idx.dbf',
  '/u08/oradata/matx/mat_data1.dbf',
  '/u08/oradata/matx/sys_bak.dbf',
  '/u08/oradata/matx/ek_data.dbf',
  '/u08/oradata/matx/ek_idx.dbf'
CHARACTER SET ZHS16GBK
;

** 注意noresetlogs和MAXLOGFILES的设置

SQL> shutdown immediate;
SQL> startup

(3)确认REDO LOG的SEQUENCE没有被RESET：

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

    GROUP#  SEQUENCE# STATUS
---------- ---------- ------------------------------------------------
         1        146 INACTIVE
         2        148 INACTIVE
         3        147 INACTIVE
         4        149 CURRENT

------------------------------------------------------
  在standby所在服务器创建新实例
------------------------------------------------------
(1) 定义standby实例名
export ORACLE_SID=mat_standby
(2) 如果希望实例在服务器重启后自动启动，要将该实例名添加到/etc/oratab文件中
vi /etc/oratab
   #add sid:oracle_home:auto_start
   mat_standby:/u01/oracle:Y
(3) 创建密码文件
orapwd file=/u01/oracle/dbs/pwdamtstandby.ora password=oracle entries=5
(4) 修改listener配置，使listener监控到新实例
vi listener.ora
   LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
    )
    )
   )
   SID_LIST_LISTENER =
       (SID_DESC =
         (GLOBAL_DBNAME = matx)
         (ORACLE_HOME = /u01/oracle)
         (SID_NAME = matx)
       )
       (SID_DESC =
         (ORACLE_HOME = /u01/oracle)
         (SID_NAME = mat_standby)   ##在本机安装standby,不要设置globa_dbname,只要sid就可以
       )
     )
(5) 修改tnsnames配置
vi tnsnames.ora
   matx =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = matx)
       )
     )
   mat_standby =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SID = mat_standby)
       )
     )
注：本次测试是在同台server上进行，如果standby和primary不在同一主机，则以上步骤(4)(5)略有调整  

------------------------------------------------------
  在主库生成创建standby用的全备
------------------------------------------------------
export ORACLE_SID=matx
(1) 确认primary已经运行在archivelog模式下
SYS@ORA9I> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u09/arch
Oldest online log sequence     140
Next log sequence to archive   143
Current log sequence           143
(2) 将primary调整为强制logging
SYS@ORA9I> alter database force logging;

Database altered.
(3) 生成供standby使用的pfile
create pfile='/u01/oracle/dbs/init_amtstandby.ora' from spfile;
vi /u01/oracle/dbs/init_amtstandby.ora
    ** 需要修改或添加的参数如下
    standby_archive_dest='/u09/oradata/mat_standby/arch'
    standby_file_management=AUTO
    remote_archive_enable=true
    fal_server=matx
    fal_client=mat_standby
    control_files='/u09/oradata/mat_standby/standby.ctl'
    log_archive_dest_1='location=/u09/oradata/mat_standby/arch reopen=60'
    instance_name=mat_standby
    lock_name_space=tttt   
    db_file_name_convert=('/u08/oradata/matx','/u09/oradata/mat_standby')
    log_file_name_convert=('/u08/oradata/matx','/u09/oradata/mat_standby')
** lock_name_space不能超过8位，也不能和现有数据的lock_name_space重复，检查方法：
** 如果主库和备库的dbfile和日志路径一致，则db_file_name_convert和log_file_name_convert不需要设置
    $ cd $ORACLE_HOME/dbs
    $ ll lk*
    -rw-r-----   1 oracle     dba             24 5??19è? 09:20 lkORA9I
    -rw-rw----   1 oracle     dba             24 2007?ê11??19è? lkTEST
    -rw-rw----   1 oracle     dba             24 5??19è? 13:47 lkTESTDB
    -rw-rw----   1 oracle     dba             24 5??31è? 15:53 lkTTT
(4) 生成全备
vi /home/oracle/jenny/fullbackup4standby.sh
   run {
   allocate channel d1 type disk;
   allocate channel d2 type disk;
   allocate channel d3 type disk;
   allocate channel d4 type disk;
   backup incremental level 0 format '/u03/test/fullbk4standby_%d_%s_%p_%T' database
   include current controlfile for standby;
   sql "alter system archive log current";
   release channel d1;
   release channel d2;
   release channel d3;
   release channel d4;
   }
vi /home/oracle/jenny/execbk.sh
   rman nocatalog target / cmdfile='/home/oracle/jenny/fullbackup4standby.sh' log='/home/oracle/jenny/fullbackup4standby.log'
sh /home/oracle/jenny/execbk.sh &
(5) 将步骤(3)(4)产生的文件以及全备期间产生的archive log传到standby
** 备份集在standby的存放路径必须与primary一致

------------------------------------------------------
  通过duplicate db方式创建Standby
------------------------------------------------------
export ORACLE_SID=mat_standby
(1) 以nomount方式打开standby
sqlplus '/ as sysdba'
SQL> startup nomount pfile=/u01/oracle/dbs/init_amtstandby.ora
SQL> exit
(2) duplicate命令创建standby
vi /home/oracle/jenny/createstandby.sh
   run {
   allocate auxiliary channel dup1 type disk;
   allocate auxiliary channel dup2 type disk;
   allocate auxiliary channel dup3 type disk;
   set newname for datafile 1 to '/u09/oradata/mat_standby/system01.dbf';
   set newname for datafile 2 to '/u09/oradata/mat_standby/undotbs01.dbf';
   set newname for datafile 3 to '/u09/oradata/mat_standby/indx01.dbf';
   set newname for datafile 4 to '/u09/oradata/mat_standby/tools01.dbf';
   set newname for datafile 5 to '/u09/oradata/mat_standby/users01.dbf';
   set newname for datafile 6 to '/u09/oradata/mat_standby/mat_data.dbf';
   set newname for datafile 7 to '/u09/oradata/mat_standby/mat_idx.dbf';
   set newname for datafile 8 to '/u09/oradata/mat_standby/mat_data1.dbf';
   set newname for datafile 9 to '/u09/oradata/mat_standby/sys_bak.dbf';
   set newname for datafile 10 to '/u09/oradata/mat_standby/ek_data.dbf ';
   set newname for datafile 11 to '/u09/oradata/mat_standby/ek_idx.dbf';
   duplicate target database for standby dorecover nofilenamecheck;
   release channel dup1;
   release channel dup2;
   release channel dup3;
   }
   ** 因为是在本机测试，所有的文件都需要重命名，如果是在不同服务器的同乡路径下，可以不用set newname
vi /home/oracle/jenny/execcreate.sh
   rman target sys/easydba@matx auxiliary / cmdfile='/home/oracle/jenny/createstandby.sh' log='/home/oracle/jenny/createstandby.log'
sh execcreate.sh &
执行过程中报错：
   RMAN-03002: failure of Duplicate Db command at 11/16/2007 10:23:12
   RMAN-05507: standby controlfile checkpoint (23456133) is more recent than duplication point in time (23412363)
google了一下，这个错误是说控制文件教新，也就是说相较数据文件而言，控制文件是新的，这是由于控制文件的持续更新的原因。只要在执行Duplicate之前，在源数据库执行一次LOG ARCHIVE动作，增加一下检查点。
在主库执行：alter system archive log current;

sh execcreate.sh &
执行成功

(4) 创建standby的spfile
create spfile from pfile=/u01/oracle/dbs/init_amtstandby.ora

(5) 创建Standby redo log
默认情况下，dataguard是maximum performance模式下运行的，这种模式下只有在主库commit的事务才会被同步到standby，可能会造成数据丢失，但是这种方式对主库的影响最小，不会造成standby影响primary的情况。
如果dataguard是运行在maximum available或者maximum protection模式下，则必须要创建standby redo log。所有从主库同步到standby的archive log会先写入standby_redo_log, apply完成后再写入archive log.
查看数据库运行在何种模式下：
SYS@ORA9I> select name,database_role,PROTECTION_MODE from v$database;
NAME                        DATABASE_ROLE        PROTECTION_MODE
--------------------------- -------------------- ------------------------------
matx                      PRIMARY              MAXIMUM PERFORMANCE
更改模式：
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
如果必要，创建standby redo log,比primary日志多一组:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u09/oradata/mat_standby/standby_redo05.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u09/oradata/mat_standby/standby_redo06.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u09/oradata/mat_standby/standby_redo07.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u09/oradata/mat_standby/standby_redo08.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u09/oradata/mat_standby/standby_redo09.log') SIZE 100M;

 

------------------------------------------------------
  启动standby的日志恢复功能
------------------------------------------------------
(1) @standby: 启动日志恢复功能
export ORACLE_SID=mat_standby
sqlplus '/ as sysdba'
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
(2) @primary: 配置log_archive_dest_2到Standby
export ORACLE_SID=matx
alter system set log_archive_dest_2='service=mat_standby reopen=60 max_failure=10 optional lgwr async=20480 noaffirm' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
** 注：此处采用lgwr的异步方式传递日志
(3) @standby: 查看当前已归档日志情况
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
       151 05-JUN-08       05-JUN-08
       152 05-JUN-08       05-JUN-08
       153 05-JUN-08       05-JUN-08

(4) @primary: 切换主库日志
ALTER SYSTEM ARCHIVE LOG CURRENT;
or
alter system switch logfile;

(5) @standby: 验证日志是否接收
SYS@ORA9I> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
       151 05-JUN-08       05-JUN-08
       152 05-JUN-08       05-JUN-08
       153 05-JUN-08       05-JUN-08
       154 05-JUN-08       05-JUN-08

(6) @standby: 验证日志已经apply

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# APPLIED
---------- ---------
       151 YES
       152 YES
       153 YES
       154 YES

(7) @standby：在日志恢复和只读之间切换

日志恢复->只读
alter database recover managed standby database cancel;
alter database open read only;
只读->日志恢复
alter database recover managed standby database disconnect from session;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-332616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-332616/