11gR2 ADG实验搭建

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (文档 ID 1075908.1)

 

 

主库：10.20.30.11/12         rac          db_name:lc db_unique_name:lc

备库：10.20.30.31              单机        db_unique_name:lcstd

 

1、主库准备

 

a、开启归档和force logging

 

SQL> ALTER DATABASE FORCE LOGGING;

 

select log_mode,force_logging from v$database;

 

b、创建standby redologs

(日志组数+1)*instance数目，方便以后切换

 

alter database add standby logfile '<name>' size <size>;

 

c、主库修改参数

 

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(lc,lcstd)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lc' sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=lcstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lcstd';

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

alter system set FAL_SERVER=lcstd;

alter system set FAL_CLIENT=lc;

alter system set DB_FILE_NAME_CONVERT='/oracle/app/oracle/datafile','+DATA/lc/datafile' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/oracle/app/oracle/datafile','+FLA/lc/onlinelog' scope=spfile;

 

 

 

2、SQL NET

 

 

a、主备+备库的tnsnames.ora

 

lcstd =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.31)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = lcstd)

    )

  )

 

lc =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.11)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = lc)

    )

  )  

 

 

b、备库监听listener.ora（有grid管理的话，会自动注册，就不需要做这一步了）

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.31)(PORT = 1521))

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = lcstd )

      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

      (GLOBAL_DBNAME = lcstd)

    )

  )

 

lsnrctl start

 

3、创建备库

 

 

a、拷贝密码文件

 Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby. 

The best practice for this is to copy the passwordfile as suggested. 

The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

 

scp /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwlc1 11std:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwlcstd

 

b、建立initlcstd.ora

cat /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initlcstd.ora

DB_NAME=lc

DB_UNIQUE_NAME=lcstd

DB_BLOCK_SIZE=8192

 

c、创建ADR_HOMES

mkdir -p /oracle/app/oracle/admin/lcstd/{a,b,c,dp,u}dump

 

d、启动备库到nomount

startup nomount pfile=$ORACLE_HOME/dbs/initlcstd.ora

 

e、sys校验登录

sqlplus /nolog

SQL> connect sys/oracle@lcstd AS SYSDBA

SQL> connect sys/oracle@lc AS SYSDBA

 

 

f、主库rman生成辅助库

 

rman target sys/oracle@lc auxiliary sys/oracle@lcstd

 

run {

allocate channel prmy1 type disk;

allocate auxiliary channel stby type disk;

 

duplicate target database for standby from active database nofilenamecheck

spfile

  parameter_value_convert 'lc','lcstd'

  set db_unique_name='lcstd'

  set cluster_database='false'

  set REMOTE_LISTENER=''

  set local_listener=''

  set db_cache_size='300M'

  set shared_pool_size='200M'

  set sga_max_size='600M'

  set control_files='/oracle/app/oracle/datafile/control01.ctl'

  set log_archive_max_processes='4'

  set fal_client='lcstd'

  set fal_server='lc'

  set standby_file_management='AUTO'

  set log_archive_config='dg_config=(lc,lcstd)'

  set db_recovery_file_dest='/oracle/app/oracle/fla'

  set db_create_file_dest='/oracle/app/oracle/datafile'

  set log_archive_dest_2='service=lc ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=lc'

  set DB_FILE_NAME_CONVERT='+DATA/lc/datafile' ,'/oracle/app/oracle/datafile'

  set LOG_FILE_NAME_CONVERT='+FLA/lc/onlinelog' ,'/oracle/app/oracle/datafile'

;

}

 

遇到的问题：创建了备库的spfile后，启动会报密码错误

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

最终排查，多半是复制粘贴产生listener.ora出现了问题！！！不显示的符号可能造成了问题

用以前的方式是添加UR=A ，使用更改备库spfile+主库执行duplicate

经过验证，这种方法做出来的备库的参数很容易出问题，还是手工改生成好的pfile启动，好调整一点！

 

4. Start managed recovery

SQL> alter database recover managed standby database disconnect from session;

5. Open standby database in Read Only (active dataguard)

先cancel取消，然后open，继续应用，才能成为adg

 

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> alter database recover managed standby database disconnect;

 

 

select open_mode,database_role,log_mode,flashback_on,force_logging from v$database; 

 

 

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;