This post lists the steps for setting up an Oracle data guard on 12.2 with Oracle restart. Both primary and standby uses ASM for data file storage. The primary database is a CDB with two PDBs. The primary and standby Oracle restart has been setup with role separation. There's a similar post for
setting up data guard on 11.2 with Oracle restart.
Below are few of the new features introduced in 12.2.0.1, some of which is relevant to setting up DG with CDB and PDB.
As of Oracle Database 12c Release 2 (12.2.0.1) password file changes done on a primary database are automatically propagated to standby databases. The only exception to this is far sync instances.
A new INSTANCES [ ALL | integer] clause is available on the SQL ALTER RECOVER MANAGED STANDBY DATABASE command which enables you to control the number of instances on a physical standby that Redo Apply uses.
You can now detect lost writes and also inconsistencies between a primary database and physical standby databases by using the new PL/SQL procedure, DBMS_DBCOMP.DBCOMP.
The new ENABLED_PDBS_ON_STANDBY initialization parameter enables you to specify a subset of pluggable databases (PDBs) for replication on a physical standby of a multitenant container database (CDB). In releases prior to Oracle Database 12c Release 2 (12.2.0.1), you had to specify either all PDBs or none.
As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby connected, without disruption, during the switchover/failover. To enable this feature, set the STANDBY_DB_PRESERVE_STATES initialization parameter in your init.ora file before the standby instance is started.
This parameter applies to physical standby databases only. The allowed values are:
• NONE — No sessions on the standby are retained during a switchover/failover. This is the default value.
• ALL — User sessions are retained during switchover/failover.
• SESSION — User sessions are retained during switchover/failover.
You can now use the Oracle Diagnostic Pack with an Oracle Active Data Guard standby database that is open read-only.The table below list the relevant names used in the DG setup.
| Item |
On Primary |
On Standby |
|---|
| Host name | city7 | city7s |
| Database Name | prodcdb | stbycdb |
| Diskgroup Names | DATA
FRA | DATA
FRA |
| TNS Entry Name | PRODCDBTNS | STBYTNS |
1. Create the relevant *dump directories in the standby server
cd $ORACLE_BASE/admin
mkdir stbycdb
cd stbycdb
mkdir adump dpdump hdump pfile
2.If not done already, put the primary database into archive log mode.
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
3. Enable force logging on the primary database
alter database force logging;
4. Create standby log files for each thread on primary db. These should be same size as the online redo log. There should be at least one more redo log group per thread than the online redo log groups.
alter database add standby logfile thread 1 size 209715200;
5. Add static listener entries on standby and primary $GI_HOME's listener.ora file. This must be done as grid user. On primary add the following static listener entry to listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prodcdb)
(SID_NAME = prodcdb)
(ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
)
)On standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stbycdb)
(SID_NAME = stbycdb)
(ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
)
)stop and start the listener and verify service for static listener entry is shown (below only standby entry is shown).
srvctl stop listener
srvctl start listener
lsnrctl status
Service "stbycdb" has 1 instance(s).
Instance "stbycdb", status UNKNOWN, has 1 handler(s) for this service...
6. As oracle user create tns entries in $ORACLE_HOME/network/admin/tnsnames.ora. The listeners on standby and primary runs on
non-default port of 1581. On primary server's oracle home create a TNS entry pointing to the standby instance
STBYTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = city7s)(PORT = 1581))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbycdb)
)
)On standby's tnsnames.ora file add an entry pointing to the primary instance
PRODCDBTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = city7)(PORT = 1581))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodcdb)
)
)7. Create a backup pfile from the primary spfile
create pfile='/home/oracle/pfile.ora' from spfile;
8. Modify the initialization parameters on the primary db. Remote archive dest state is set to defer, enable it before running the standby duplication. LGWR with ASYNC and NOAFFIRM is used as the redo transport this should be changed to suite the desired protection mode.
alter system set log_archive_config='dg_config=(prodcdb,stbycdb)' scope=both ;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prodcdb' scope=both;
alter system set log_archive_dest_2='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb' scope=both;
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set fal_server='STBYTNS' scope=both;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/stbycdb/','/prodcdb/' scope=spfile;
alter system set log_file_name_convert='/stbycdb/','/prodcdb/' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;
Stop and start the database for changes to take effect. The initialization parameter enabled_PDBs_on_standby which is related to PDBs is left at the default value. Which means all PDBs on primary will be created on all the standbys.
SQL> show parameter enabled_PDBs_on_standby
NAME TYPE VALUE
------------------------ ----------- ------
enabled_PDBs_on_standby string *
This only sets the minimum parameters needed for data guard setup. Optionally other data guard and performance related parameters could be set at this time or once the data guard setup is completed. One of the common parameters in this category is the lost write protection. In this setup, this parameter was set once the data guard setup was complete.
alter system set db_lost_write_protect='typical' scope=both;
9. Copy password to standby and rename with the instance name
scp $ORACLE_HOME/dbs/orapwprodcdb city7s:$ORACLE_HOME/dbs/orapwstbycdb
10. Create pfile on standby with db_name parameter only and start the standby instance in nomount mode.
export ORACLE_SID=stbycdb
cat initstbycdb.ora
db_name=stbycdb
SQL> startup nomount;
11. Enable the remote log archive destination on primary (this was set to defer when settign the initialization parameters on step 7).
alter system set log_archive_dest_state_2='enable' SCOPE=both sid='*';
12. Unlike
previous post, this DG creation does not use active data database option to create the standby DB. (At the end of the post, commands to create standby DB using active data base option is shown). Instead it uses backups from primary to create the standby database (similar to
duplicating with backups). Below are the necessary RMAN commands to create the required backup files for standby duplication.
backup spfile format '/home/oracle/backup/spbackup.bkp';
backup current controlfile for standby format '/home/oracle/backup/stdbycontro.ctl';
backup database format '/home/oracle/backup/dbbackup%U' plus archivelog format '/home/oracle/backup/archbackup%U' delete all input;
switch few log files and backup the archive logs
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
backup archivelog all format '/home/oracle/backup/archbkp%U' delete all input;
13. Copy backup files to standby host. Backups are on a different location on standby than primary.
cd /home/oracle/backup
scp * city7s:/home/oracle/backup_dir
14. Login to standby server and connect to the standby instance using an auxiliary connection and run
duplicate database for standby command. Since the command is run from standby omit the
target keyword (duplicating while connected to primary is shown at the end of the post). Since ASM disk group names are same on both primary and standby lot of the db_*_dest* parameters could be kept same as primary. If not these would require file name conversions with disk groups prefixed as shown in
previous post.rman auxiliary sys/prodcdb
duplicate database for standby
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
BACKUP LOCATION '/home/oracle/backup_dir' dorecover nofilenamecheck;
Following output shows the full duplication process.
rman auxiliary sys/prodcdbdb
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Feb 16 14:23:14 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: STBYCDB (not mounted)
RMAN> duplicate database for standby
2> spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
3> 4> set db_name='prodcdb'
5> set db_unique_name='stbycdb'
6> set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
7> 8> set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
9> 10> 11> set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
12> 13> BACKUP LOCATION '/home/oracle/backup_dir' dorecover nofilenamecheck;
Starting Duplicate Db at 16-FEB-18
contents of Memory Script:
{
restore clone spfile to '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora' from
'/home/oracle/backup_dir/spbackup.bkp';
sql clone "alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora''";
}
executing Memory Script
Starting restore at 16-FEB-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup_dir/spbackup.bkp
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-18
sql statement: alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/opt/app/oracle/admin/stbycdb/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/STBYCDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBYCDB/CONTROLFILE/current.256.965841019'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stbycdbXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_name =
''prodcdb'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stbycdb'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/prodcdb/'', ''/stbycdb/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/prodcdb/'', ''/stbycdb/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''PRODCDBTNS'' comment=
'''' scope=spfile";
sql clone "alter system reset local_listener scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/opt/app/oracle/admin/stbycdb/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/STBYCDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBYCDB/CONTROLFILE/current.256.965841019'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stbycdbXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''prodcdb'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stbycdb'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/prodcdb/'', ''/stbycdb/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/prodcdb/'', ''/stbycdb/'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_server = ''PRODCDBTNS'' comment= '''' scope=spfile
sql statement: alter system reset local_listener scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1191182336 bytes
Fixed Size 8792104 bytes
Variable Size 452986840 bytes
Database Buffers 721420288 bytes
Redo Buffers 7983104 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/STBYCDB/CONTROLFILE/current.263.968250233'', ''+FRA/STBYCDB/CONTROLFILE/current.263.968250233'' comment=
''Set by RMAN'' scope=spfile";
restore clone standby controlfile from '/home/oracle/backup_dir/stdbycontro.ctl';
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/STBYCDB/CONTROLFILE/current.263.968250233'', ''+FRA/STBYCDB/CONTROLFILE/current.263.968250233'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 16-FEB-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/STBYCDB/CONTROLFILE/current.262.968250233
output file name=+FRA/STBYCDB/CONTROLFILE/current.264.968250235
Finished restore at 16-FEB-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set until scn 1495617;
set newname for tempfile 1 to
"+DATA";
set newname for tempfile 2 to
"+DATA";
set newname for tempfile 3 to
"+DATA";
set newname for tempfile 4 to
"+DATA";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA";
set newname for datafile 2 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA";
set newname for datafile 5 to
"+DATA";
set newname for datafile 6 to
"+DATA";
set newname for datafile 7 to
"+DATA";
set newname for datafile 11 to
"+DATA";
set newname for datafile 12 to
"+DATA";
set newname for datafile 13 to
"+DATA";
set newname for datafile 14 to
"+DATA";
set newname for datafile 15 to
"+DATA";
set newname for datafile 16 to
"+DATA";
set newname for datafile 17 to
"+DATA";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
renamed tempfile 4 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-FEB-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2isrce37_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2isrce37_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2ksrce3e_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2ksrce3e_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2lsrce3h_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2lsrce3h_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2jsrce3a_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2jsrce3a_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 16-FEB-18
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=968250346 file name=+DATA/STBYCDB/DATAFILE/system.261.968250253
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=968250346 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.271.968250291
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=968250346 file name=+DATA/STBYCDB/DATAFILE/sysaux.267.968250255
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=968250346 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.270.968250291
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=968250347 file name=+DATA/STBYCDB/DATAFILE/undotbs1.268.968250255
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=968250347 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.272.968250291
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=968250347 file name=+DATA/STBYCDB/DATAFILE/users.269.968250255
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/system.274.968250315
datafile 12 switched to datafile copy
input datafile copy RECID=23 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/sysaux.273.968250315
datafile 13 switched to datafile copy
input datafile copy RECID=24 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/undotbs1.275.968250315
datafile 14 switched to datafile copy
input datafile copy RECID=25 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/system.277.968250331
datafile 15 switched to datafile copy
input datafile copy RECID=26 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/sysaux.276.968250331
datafile 16 switched to datafile copy
input datafile copy RECID=27 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/undotbs1.278.968250331
datafile 17 switched to datafile copy
input datafile copy RECID=28 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/users.279.968250331
contents of Memory Script:
{
set until scn 1495617;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 16-FEB-18
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbackup2hsrce34_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbackup2hsrce34_1_1 tag=TAG20180216T122820
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_42.265.968250369 thread=1 sequence=0
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_42.265.968250369 RECID=1 STAMP=968250369
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=43
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbackup2msrce3l_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbackup2msrce3l_1_1 tag=TAG20180216T122837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_43.265.968250371 thread=1 sequence=43
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_43.265.968250371 RECID=2 STAMP=968250371
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=44
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=45
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=46
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=47
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbkp2osrce4b_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbkp2osrce4b_1_1 tag=TAG20180216T122859
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_44.265.968250373 thread=1 sequence=44
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_44.265.968250373 RECID=4 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_45.266.968250373 thread=1 sequence=45
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_45.266.968250373 RECID=5 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_46.269.968250373 thread=1 sequence=46
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_46.269.968250373 RECID=3 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_47.268.968250373 thread=1 sequence=47
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_47.268.968250373 RECID=7 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_48.267.968250373 thread=1 sequence=48
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_48.267.968250373 RECID=6 STAMP=968250373
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-FEB-18
Finished Duplicate Db at 16-FEB-1815. Once the duplication for standby is completed start the log apply process on standby. Monitor the standby instance's alert log for MRP starting and query the archived log view for log apply progress.
alter database recover managed standby database using current logfile disconnect;
select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
273 YES
274 YES
275 YES
276 YES
277 YES
278 YES
279 YES
280 IN-MEMORY16. Final step is to bring the database under the control of oracle restart. To do this add the database to the oracle restart configuration with srvctl add database. This must be run as oracle user not as grid user. Running the command as grid user would fail as oracle home is owned by a different user
[grid@city7s ~]$ srvctl add database -db stbycdb -oraclehome /opt/app/oracle/product/12.2.0/dbhome_1 -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"
PRCD-1025 : Failed to create database stbycdb
PRKH-1014 : Current user "grid" is not the oracle owner user "oracle" of oracle home "/opt/app/oracle/product/12.2.0/dbhome_1"
As oracle user run
$GI_HOME/bin/srvctl add database -db stbycdb -oraclehome $ORACLE_HOME -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"
Verify the database configuration
srvctl config database -db stbycdb
Database unique name: stbycdb
Database name:
Oracle home: /opt/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: /opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group:
OSOPER group:
Database instance: stbycdb
At this stage the oracle binary in standby oracle home will not have the correct ownership with regard to when role separation is used.
[oracle@city7s bin]$ cd $ORACLE_HOME/bin
[oracle@city7s bin]$ ls -l oracle
-rwsr-s--x. 1 oracle oinstall 408115488 Jan 19 16:27 oracle
This could lead to the issue highlighted
here. In fact if the standby database is to be started without correcting the permission it will fail as shown below
srvctl start database -db stbycdb
PRCR-1079 : Failed to start resource ora.stbycdb.db
CRS-5017: The resource action "ora.stbycdb.db start" encountered the following error:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1001 (oinstall), current egid = 1005 (asmadmin)
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/diag/crs/city7s/crs/trace/ohasd_oraagent_grid.trc".
CRS-2674: Start of 'ora.stbycdb.db' on 'city7s' failed
Interestingly after the failure first time the permission get applied automatically to the oracle binary (without any manual intervention). So, if attempted the standby database start will succeed second time around.
This conclude the setting up of DG with CDB.
Standby DB creation with target database
In this case the location to which backup files are copied to in step 13 above must match the backup file location on primary. (i.e. if on primary backup files are on /home/oracle/backup then on standby also it must be on /home/oracle/backup).
While in primary server, connect to both target and standby (with an auxiliary connection) and run the duplicate command
rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stbytns
run {
duplicate target database for standby
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
dorecover nofilenamecheck;
}Standby DB creation with active database
With active database option there's no need to create any backups. Connect to both target and standby and run the duplicate command with active database.
rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stbytns
duplicate target database for standby from active database
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb';
If the active data guard license is available then standby database could be open for read only access. To do this cancel the managed recovery, open the CDB root in read only mode and then the PDBs.
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter pluggable database all open read only;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBAPP1 READ ONLY NO
4 PDBAPP2 READ ONLY NO
Related Posts
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
11gR2 Standalone Data Guard (with ASM and Role Separation)
Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync
Enable Active Dataguard on 11gR2 RAC Standby
