参考文章：Data Guard Physical Standby Setup in Oracle Database 11g Release 2

环境准备

	主机名	ip	DB Version	db_name	db_unique_name
主库	orcl	192.168.56.120	11.2.0.4	orcl	orcl
备库	orcl_stby	192.168.56.121	11.2.0.4	orcl	orcl_stby

Notes：

1、db_unique_name主备库不能相同。

2、db_name主备库需保持一致。

3、主备库DB版本需保持一致。

一、Preparing the Primary Database for Standby Database Creation

1.Enable Archiving

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SELECT LOG_MODE FROM V$DATABASE;

2.Enable Forced Logging

ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING FROM V$DATABASE;

3.Create a Backup Copy of the Primary Database Datafiles(RMAN)

##create backup directory
mkdir -p /backup
chown -R oracle:oinstall /backup
chmod 775 /backup

##create rman backup script
su - oracle
echo '#!/bin/sh' >>/backup/rman_backup_forstby.sh
echo 'source ~/.bash_profile' >>/backup/rman_backup_forstby.sh
echo 'backtime=`date +"20%y%m%d%H%M%S"`' >>/backup/rman_backup_forstby.sh
echo "rman target / log=/backup/rman_backup_forstby_\${backtime}.log<<EOF" >>/backup/rman_backup_forstby.sh
echo 'run {' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c1 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c2 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c3 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'allocate channel c4 device type disk;' >>/backup/rman_backup_forstby.sh
echo 'crosscheck backup;' >>/backup/rman_backup_forstby.sh
echo 'crosscheck archivelog all;' >>/backup/rman_backup_forstby.sh
echo 'sql "alter system switch logfile";' >>/backup/rman_backup_forstby.sh
echo 'delete noprompt expired backup;' >>/backup/rman_backup_forstby.sh
echo 'delete noprompt obsolete device type disk;' >>/backup/rman_backup_forstby.sh
echo "backup database format '/backup/FULL_%U.bak';" >>/backup/rman_backup_forstby.sh
echo "backup archivelog all format '/backup/ARC_%U.bak';" >>/backup/rman_backup_forstby.sh
echo 'release channel c1;' >>/backup/rman_backup_forstby.sh
echo 'release channel c2;' >>/backup/rman_backup_forstby.sh
echo 'release channel c3;' >>/backup/rman_backup_forstby.sh
echo 'release channel c4;' >>/backup/rman_backup_forstby.sh
echo '}' >>/backup/rman_backup_forstby.sh
echo 'EOF' >>/backup/rman_backup_forstby.sh

##begin rman backup
chmod +x /backup/rman_backup_forstby.sh
nohup /backup/rman_backup_forstby.sh >/dev/null 2>&1 &

4.Create a Control File for the Standby Database

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby_ctrl.ctl';

5.Create a Parameter File for the Standby Database

CREATE PFILE='/tmp/initstby.ora' FROM SPFILE;

6.Copy Files from the Primary System to the Standby System

a.Copy the Backup datafiles

##Standby create /backup dir
mkdir /backup
chown oracle:oinstall /backup
chmod 775 /backup

##Primary copy backup files
scp *bak /backup/oracle@orcl_stby:/backup

b.Copy the remote login password file

##ORACLE_HOME must be the same dir on primary and standby first

scp $ORACLE_HOME/dbs/orapworcl oracle@orcl_stby:$ORACLE_HOME/dbs

c.Copy the Standby control file

scp stby_ctrl.ctl oracle@orcl_stby:/tmp

d.Copy the Initialization parameter file

scp initstby.ora oracle@orcl_stby:/tmp/

7.Configure listeners for the primary and standby databases

Configure hosts for the primary and standby databases

##Botn Primary and standby Set

vi /etc/hosts

#Add Public IP
192.168.56.120  orcl
192.168.56.121 orcl_stby

##Botn Primary and standby Set listener.ora

vi $TNS_ADMIN/listener.ora

##ADD
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl)
    )
  )


##Then restart listener
lsnrctl stop
lsnrctl start

8.Create Oracle Net service names.

##Botn Primary and standby Set tnsnames.ora

vi $TNS_ADMIN/tnsnames.ora


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_stby)
    )
  )

##Test tnsping and sqlplus
tnsping orcl
tnsping orcl_stby
sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcl_stby as sysdba

9.Add Standby redo log for the Standby Database

set line222
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#;
 
   THREAD#     GROUP# MEMBER                                                       T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
         1          3 /oradata/orcl/redo03.log                                                    120
         1          2 /oradata/orcl/redo02.log                                                    120
         1          1 /oradata/orcl/redo01.log                                                    120
 
--需要注意：
--1.stanby log日志大小与redo log日志保持一致
--2.stanby log数量： 
standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile.
--3.thread要与redo log保持一致，如果是rac，需要增加多个thread对应的standby log
 
ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M,
group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M,
group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M,
group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;

二、Preparing the Standby Database for Standby Database Creation

1.Configure Parameter File for the Standby Database

vi /tmp/initstby.ora

##ADD
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=orcl_stby
*.fal_client='ORCL_STBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)'
*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='/oradata/orcl','/oradata/orcl'
*.db_file_name_convert='/oradata/orcl','/oradata/orcl'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4

2.Create a server parameter file for the standby database

create spfile from pfile='/tmp/initstby.ora';

三、Create Physical Standby Database

1.Start the Physical Standby Database nomount

startup nomount

2.Restore Standby From RMAN Backup

a.Restore Standby Controlfile from RMAN Backup

restore standby controlfile from '/tmp/stby_ctrl.ctl';

b.mount Standby database

alter database mount;

c.Restore standby database

##create rman restore script
su - oracle
echo '#!/bin/sh' >>/backup/rman_restore_forstby.sh
echo 'source ~/.bash_profile' >>/backup/rman_restore_forstby.sh
echo 'backtime=`date +"20%y%m%d%H%M%S"`' >>/backup/rman_restore_forstby.sh
echo "rman target / log=/backup/rman_restore_forstby_\${backtime}.log<<EOF" >>/backup/rman_restore_forstby.sh
echo 'run {' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c1 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c2 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c3 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'allocate channel c4 device type disk;' >>/backup/rman_restore_forstby.sh
echo 'restore database;' >>/backup/rman_restore_forstby.sh
echo 'release channel c1;' >>/backup/rman_restore_forstby.sh
echo 'release channel c2;' >>/backup/rman_restore_forstby.sh
echo 'release channel c3;' >>/backup/rman_restore_forstby.sh
echo 'release channel c4;' >>/backup/rman_restore_forstby.sh
echo '}' >>/backup/rman_restore_forstby.sh
echo 'EOF' >>/backup/rman_restore_forstby.sh

##begin rman backup
chmod +x /backup/rman_restore_forstby.sh
nohup /backup/rman_restore_forstby.sh >/dev/null 2>&1 &

d.check standby database file

select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;

3.Configure Primary database Parameter

--设置DG参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCL_STBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4.Open Standby Database and Start Redo Apply

alter database open;

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

5.check Standby database

set line222
col member for a60

select open_mode,database_role from v$database;

select process,group#,thread#,sequence# from v$managed_standby;

select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;

select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;