A new feature in Oracle 21c is the ability to create a Data Guard Far Sync Instance via Data Guard Broker.
The DGMGRL command CREATE FAR_SYNC automates the entire process of creating the Far Sync database instance for a Data Guard Configuration.
It internally runs the RMAN DUPLICATE TARGET DATABASE FOR FARSYNC command and creates the Far Sync instance with the appropriate parameters.
An example of this new feature is shown here using an Oracle 21c OCI hosted environment.
###########################################################################################################
Environment
###########################################################################################################
Primary Database DB_UNIQUE_NAME:db21c_syd1tp
Standby Database DB_UNIQUE_NAME:db21c_syd1ts
Far Sync Instance DB_UNIQUE_NAME:db21c_syd1fs
###########################################################################################################
Add static entry for Far Sync Instance in listener.ora in Grid Infrastructure home
###########################################################################################################
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db21c_syd1fs.ad1.cmuvnc.oraclevcn.com)
(ORACLE_HOME = /u01/app/oracle/product/21.0.0.0/dbhome_1)
(SID_NAME = db21cfs)
(ENVS = "TNS_ADMIN=/u01/app/oracle/homes/OraDB21000_home1/network/admin")
)
)
###########################################################################################################
Add entry in tnsnames.ora for Far Sync instance
###########################################################################################################
DB21C_SYD1TS =
(DESCRIPTION =
(SDU = 65535)
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db21c_syd1ts.ad1.cmuvnc.oraclevcn.com)
(UR = A)
)
)
DB21C_SYD1TP =
(DESCRIPTION =
(SDU = 65535)
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db21c_syd1tp.ad1.cmuvnc.oraclevcn.com)
)
)
DB21C_SYD1FS =
(DESCRIPTION =
(SDU = 65535)
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db21c_syd1fs.ad1.cmuvnc.oraclevcn.com)
)
)
###########################################################################################################
Edit sqlnet.ora file with Wallet location
###########################################################################################################
[oracle@db21c admin]$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/db21c_syd1tp)))
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/db21c_syd1tp)))
SQLNET.WALLET_OVERRIDE=true
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)
#########################################################################################################################
Add entry in SEPS (Secure External Password Store) for Far Sync Instance SYS user
#########################################################################################################################
[oracle@db21c admin]$ mkstore -wrl /opt/oracle/dcs/commonstore/wallets/tde/db21c_syd1tp -createCredential db21c_syd1fs SYS
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@db21c admin]$ mkstore -wrl /opt/oracle/dcs/commonstore/wallets/tde/db21c_syd1tp -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
3: DB21C_SYD1TS sys
2: db21c_syd1fs sys
1: db21c_syd1tp sys
###########################################################################################################
Start Far Sync Instance in NOMOUNT mode
###########################################################################################################
[oracle@db21c OraDB21000_home1]$ export ORACLE_SID=db21cfs
[oracle@db21c OraDB21000_home1]$ sqlplus sys as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Jan 8 07:20:56 2021
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 356514328 bytes
Fixed Size 9685528 bytes
Variable Size 234881024 bytes
Database Buffers 109051904 bytes
Redo Buffers 2895872 bytes
SQL> quit
Disconnected from Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
###########################################################################################################
Create Far Sync Instance via Data Guard Broker
CREATE FAR_SYNC 'db21c_syd1fs'
AS CONNECT IDENTIFIER IS 'DB21C_SYD1FS'
SPFILE
SET DB_RECOVERY_FILE_DEST='+RECO'
SET DB_RECOVERY_FILE_DEST_SIZE ='20G'
SET LOG_FILE_NAME_CONVERT = 'DB21C_SYD1TP','DB21C_SYD1FS';
##########################################################################################################
[oracle@db21c OraDB21000_home1]$ export ORACLE_SID=db21c
[oracle@db21c OraDB21000_home1]$ dgmgrl -debug
$DG_ADMIM is not set
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Fri Jan 8 07:25:31 2021
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
[W000 2021-01-08T07:25:34.326+00:00] Connecting to database.
[W000 2021-01-08T07:25:34.326+00:00] Attempting to attach to
[W000 2021-01-08T07:25:34.685+00:00] Attempting logon as SYSDG
[W000 2021-01-08T07:25:34.825+00:00] Successfully logged on as SYSDG
[W000 2021-01-08T07:25:34.825+00:00] Executing query [select sys_context('USERENV','CON_ID') from dual].
[W000 2021-01-08T07:25:34.825+00:00] Query result is '1'
[W000 2021-01-08T07:25:34.826+00:00] Executing query [select value from v$parameter where name = 'db_unique_name'].
[W000 2021-01-08T07:25:34.831+00:00] Query result is 'db21c_syd1tp'
Connected to "db21c_syd1tp"
[W000 2021-01-08T07:25:34.832+00:00] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 2021-01-08T07:25:34.833+00:00] Oracle database version is '21.1.0.0.0'
Connected as SYSDG.
DGMGRL> CREATE FAR_SYNC 'db21c_syd1fs'
AS CONNECT IDENTIFIER IS 'DB21C_SYD1FS'
SPFILE
SET DB_RECOVERY_FILE_DEST='+RECO'
SET DB_RECOVERY_FILE_DEST_SIZE ='20G'
SET LOG_FILE_NAME_CONVERT = 'DB21C_SYD1TP','DB21C_SYD1FS';> > > > >
Creating far sync instance "db21c_syd1fs".
[W000 2021-01-08T07:25:42.725+00:00] Connect identifier for primary = [db21c_syd1tp]
[W000 2021-01-08T07:25:42.726+00:00] Connecting to database using db21c_syd1tp.
[W000 2021-01-08T07:25:42.726+00:00] Attempting to attach to db21c_syd1tp
[W000 2021-01-08T07:25:43.101+00:00] Attempting logon as SYSDG
[W000 2021-01-08T07:25:43.312+00:00] Attempting to attach to db21c_syd1tp
[W000 2021-01-08T07:25:43.820+00:00] Attempting logon as SYSDG
[W000 2021-01-08T07:25:45.080+00:00] Attempting to attach to db21c_syd1tp
[W000 2021-01-08T07:25:45.513+00:00] Attempting logon as SYSDBA
[W000 2021-01-08T07:25:45.730+00:00] Successfully logged on as SYSDBA
[W000 2021-01-08T07:25:45.730+00:00] Executing query [select sys_context('USERENV','CON_ID') from dual].
[W000 2021-01-08T07:25:45.731+00:00] Query result is '1'
[W000 2021-01-08T07:25:45.731+00:00] Executing query [select value from v$parameter where name = 'db_unique_name'].
[W000 2021-01-08T07:25:45.739+00:00] Query result is 'db21c_syd1tp'
Connected to "db21c_syd1tp"
[W000 2021-01-08T07:25:45.740+00:00] Connect identifier for auxiliary = [DB21C_SYD1FS]
[W000 2021-01-08T07:25:45.741+00:00] Connecting to database using DB21C_SYD1FS.
[W000 2021-01-08T07:25:45.741+00:00] Attempting to attach to DB21C_SYD1FS
[W000 2021-01-08T07:25:46.237+00:00] Attempting logon as SYSDG
[W000 2021-01-08T07:25:46.479+00:00] Attempting to attach to DB21C_SYD1FS
[W000 2021-01-08T07:25:47.001+00:00] Attempting logon as SYSDG
[W000 2021-01-08T07:25:48.256+00:00] Attempting to attach to DB21C_SYD1FS
[W000 2021-01-08T07:25:48.614+00:00] Attempting logon as SYSDBA
[W000 2021-01-08T07:25:48.799+00:00] Successfully logged on as SYSDBA
[W000 2021-01-08T07:25:48.799+00:00] Executing query [select sys_context('USERENV','CON_ID') from dual].
[W000 2021-01-08T07:25:48.802+00:00] Query result is '1'
[W000 2021-01-08T07:25:48.802+00:00] Executing query [select value from v$parameter where name = 'db_unique_name'].
[W000 2021-01-08T07:25:48.815+00:00] Query result is 'db21cfs'
Connected to "db21cfs"
[W000 2021-01-08T07:25:48.815+00:00] Creating a temporary file for RMAN script
[W000 2021-01-08T07:25:48.815+00:00] The temporary file "/tmp/ora_tfil0zj7pr" created
[W000 2021-01-08T07:25:48.815+00:00] Executing query [select sum( nvl2 ( value, 1, 0) ) from v$parameter where name = 'spfile'].
[W000 2021-01-08T07:25:48.823+00:00] Query result is '0'
[W000 2021-01-08T07:25:48.823+00:00] The auxiliary instance was started with pfile.
[W000 2021-01-08T07:25:48.823+00:00] Executing query [select value from v$parameter where name = 'db_name'].
[W000 2021-01-08T07:25:48.828+00:00] Query result is 'db21c'
[W000 2021-01-08T07:25:48.828+00:00] Executing query [SELECT DISTINCT NAME FROM V$SPPARAMETER WHERE UPPER(NAME) LIKE 'LOG_ARCHIVE_DEST_%' AND UPPER(NAME) NOT LIKE 'LOG_ARCHIVE_DEST_STATE_%' AND VALUE IS NOT NULL].
[W000 2021-01-08T07:25:48.861+00:00] Query result is 'log_archive_dest_2'
[W000 2021-01-08T07:25:48.862+00:00] Temporary log file is /tmp/ora_tfil6wcGGN
[W000 2021-01-08T07:25:48.862+00:00] Creating a far sync instance by using Oracle wallet
[W000 2021-01-08T07:25:48.862+00:00] Executing rman
[W000 2021-01-08T07:27:55.983+00:00] Returned value is 0. 472880 msecs remained
[W000 2021-01-08T07:27:55.983+00:00] Output of command:
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38>
[W000 2021-01-08T07:27:55.983+00:00] RMAN output follows
Recovery Manager: Release 21.0.0.0.0 - Production on Fri Jan 8 07:25:48 2021
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET *
2> CONNECT AUXILIARY *
3> RUN {
4> DUPLICATE TARGET DATABASE FOR FARSYNC
5> FROM ACTIVE DATABASE
6> SPFILE
7> SET DB_NAME 'db21c'
8> RESET log_archive_dest_2
9> RESET CONTROL_FILES
10> RESET CLUSTER_DATABASE
11> RESET DB_RECOVERY_FILE_DEST
12> RESET DB_RECOVERY_FILE_DEST_SIZE
13> RESET DB_FILE_NAME_CONVERT
14> RESET LOG_ARCHIVE_CONFIG
15> RESET SGA_MAX_SIZE
16> RESET DB_CACHE_SIZE
17> RESET DB_2K_CACHE_SIZE
18> RESET DB_4K_CACHE_SIZE
19> RESET DB_8K_CACHE_SIZE
20> RESET DB_16K_CACHE_SIZE
21> RESET DB_32K_CACHE_SIZE
22> RESET DB_KEEP_CACHE_SIZE
23> RESET DB_RECYCLE_CACHE_SIZE
24> RESET LOG_BUFFER
25> RESET SHARED_POOL_SIZE
26> RESET LARGE_POOL_SIZE
27> RESET JAVA_POOL_SIZE
28> RESET STREAMS_POOL_SIZE
29> SET DB_UNIQUE_NAME 'db21c_syd1fs'
30> SET SGA_TARGET '340M'
31> SET CPU_COUNT '1'
32>
33> SET DB_RECOVERY_FILE_DEST='+RECO'
34> SET DB_RECOVERY_FILE_DEST_SIZE ='20G'
35> SET LOG_FILE_NAME_CONVERT = 'DB21C_SYD1TP','DB21C_SYD1FS'
36> NOFILENAMECHECK
37> ;}
38>
connected to target database: DB21C (DBID=219159300)
connected to auxiliary database: DB21C (not mounted)
Starting Duplicate Db at 08-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=65 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=66 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.2
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/dbs/orapwdb21cfs' ;
restore clone from service 'db21c_syd1tp' spfile to
'/u01/app/oracle/dbs/spfiledb21cfs.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/dbs/spfiledb21cfs.ora''";
}
executing Memory Script
Starting backup at 08-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 device type=DISK
ignoring encryption for proxy or image copies
Finished backup at 08-JAN-21
Starting restore at 08-JAN-21
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db21c_syd1tp
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/dbs/spfiledb21cfs.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 08-JAN-21
sql statement: alter system set spfile= ''/u01/app/oracle/dbs/spfiledb21cfs.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''db21c'' comment=
'''' scope=spfile";
sql clone "alter system reset log_archive_dest_2 scope=spfile";
sql clone "alter system reset CONTROL_FILES scope=spfile";
sql clone "alter system reset db_recovery_file_dest scope=spfile";
sql clone "alter system reset DB_RECOVERY_FILE_DEST_SIZE scope=spfile";
sql clone "alter system reset LOG_ARCHIVE_CONFIG scope=spfile";
sql clone "alter system reset SGA_MAX_SIZE scope=spfile";
sql clone "alter system reset LOG_BUFFER scope=spfile";
sql clone "alter system set db_unique_name =
''db21c_syd1fs'' comment=
'''' scope=spfile";
sql clone "alter system set SGA_TARGET =
340M comment=
'''' scope=spfile";
sql clone "alter system set CPU_COUNT =
1 comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''+RECO'' comment=
'''' scope=spfile";
sql clone "alter system set DB_RECOVERY_FILE_DEST_SIZE =
20G comment=
'''' scope=spfile";
sql clone "alter system set LOG_FILE_NAME_CONVERT =
''DB21C_SYD1TP'', ''DB21C_SYD1FS'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''db21c'' comment= '''' scope=spfile
sql statement: alter system reset log_archive_dest_2 scope=spfile
sql statement: alter system reset CONTROL_FILES scope=spfile
sql statement: alter system reset db_recovery_file_dest scope=spfile
sql statement: alter system reset DB_RECOVERY_FILE_DEST_SIZE scope=spfile
sql statement: alter system reset LOG_ARCHIVE_CONFIG scope=spfile
sql statement: alter system reset SGA_MAX_SIZE scope=spfile
sql statement: alter system reset LOG_BUFFER scope=spfile
sql statement: alter system set db_unique_name = ''db21c_syd1fs'' comment= '''' scope=spfile
sql statement: alter system set SGA_TARGET = 340M comment= '''' scope=spfile
sql statement: alter system set CPU_COUNT = 1 comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+RECO'' comment= '''' scope=spfile
sql statement: alter system set DB_RECOVERY_FILE_DEST_SIZE = 20G comment= '''' scope=spfile
sql statement: alter system set LOG_FILE_NAME_CONVERT = ''DB21C_SYD1TP'', ''DB21C_SYD1FS'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 356514328 bytes
Fixed Size 9685528 bytes
Variable Size 234881024 bytes
Database Buffers 109051904 bytes
Redo Buffers 2895872 bytes
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
sql clone "alter system set control_files =
''+RECO/DB21C_SYD1FS/CONTROLFILE/current.451.1061278023'' comment=
''Set by RMAN'' scope=spfile";
restore clone from service 'db21c_syd1tp' farsync controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''+RECO/DB21C_SYD1FS/CONTROLFILE/current.451.1061278023'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 08-JAN-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=64 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=65 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.2
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db21c_syd1tp
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+RECO/DB21C_SYD1FS/CONTROLFILE/current.455.1061278033
Finished restore at 08-JAN-21
contents of Memory Script:
{
sql clone 'alter database mount';
}
executing Memory Script
sql statement: alter database mount
contents of Memory Script:
{
sql 'alter system archive log current';
}
executing Memory Script
sql statement: alter system archive log current
Oracle error from auxiliary database: ORA-00359: logfile group 1 does not exist
RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-00359: logfile group 2 does not exist
RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-00359: logfile group 3 does not exist
RMAN-05535: warning: All redo log files were not defined properly.
Finished Duplicate Db at 08-JAN-21
Recovery Manager complete.
far sync instance "db21c_syd1fs" created
[W000 2021-01-08T07:27:55.984+00:00]
