“This is the 20th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Environment to prepare

Notes:

1. Db_unique_name The active and standby databases must be different.

2. The db_name must be the same as that of the active and standby databases.

3. The DB versions of the active and standby databases must be consistent.

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;
Copy the code

2.Enable Forced Logging

ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING FROM V$DATABASE;
Copy the code

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 &
Copy the code

4.Create a Control File for the Standby Database

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

5.Create a Parameter File for the Standby Database

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

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
Copy the code

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
Copy the code

c.Copy the Standby control file

scp stby_ctrl.ctl oracle@orcl_stby:/tmp
Copy the code

d.Copy the Initialization parameter file

scp initstby.ora oracle@orcl_stby:/tmp/
Copy the code

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 IP192.168.56.120 former 192.168.56.121 orcl_stbyCopy the code

##Botn Primary and standby Set listener.ora

vi $TNS_ADMIN/listener.ora

##ADDSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = former) (ORACLE_HOME = / u01 / app/oracle/product / 11.2.0 / db) (SID_NAME = orcl) ) )##Then restart listener
lsnrctl stop
lsnrctl start
Copy the code

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
Copy the code

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
 
-- Note:
--1. The size of stanby log is the same as that of redo log
--2. Stanby log quantity:
standby logfile=(1+Logfile group number)*thread=(1+3)*1=4Group, need to add4Set of standby logfile.If the log is rac, add a standby log for each thread
 
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;
Copy the code

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
Copy the code

2.Create a server parameter file for the standby database

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

Create Physical Standby Database

1.Start the Physical Standby Database nomount

startup nomount
Copy the code

2.Restore Standby From RMAN Backup

a.Restore Standby Controlfile from RMAN Backup

restore standby controlfile from '/tmp/stby_ctrl.ctl';
Copy the code

b.mount Standby database

alter database mount;
Copy the code

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 &
Copy the code

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;
Copy the code

3.Configure Primary database Parameter

-- Set the DG parameter
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;
Copy the code

4.Open Standby Database and Start Redo Apply

alter database open;

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

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;
Copy the code