Data Guard Physical Standby Setup in Oracle Database 18c(18.0.0.0.0)
Posted by Mir Sayeed Hassan on July 29th, 2019
Data Guard Physical Standby Setup in Oracle Database 18c
SQL> select instance_name,status,version from V$instance; INSTANCE_NAME STATUS VERSION ---------- ------------ ----------------- ora18c OPEN 18.0.0.0.0
PRIMARY DATABASE CONFIGURATION
Check the archivelog status
[oracle@oracle18prim ~]$ !sq sqlplus / as sysdba SQL> SELECT log_mode FROM v$database; LOG_MODE ----------- ARCHIVELOG
In case if the db is in noarchivelog mode, fallow the below steps.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
Enabled forced logging
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
Check the redologs files exisit in database
SQL> select member from V$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_3_g7wokwvw_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_3_g7wokz80_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_2_g7wokwt1_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_2_g7wokz81_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_1_g7wokwrp_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_1_g7wokx6f_.log 6 rows selected.
SQL> show parameter db_create_online NAME TYPE VALUE ------------ ----------- ------------------------------ db_create_online_log_dest_1 string db_create_online_log_dest_2 string
Configure as OMF
SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/'; System altered.
SQL> alter system set db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area/'; System altered.
SQL> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u01/app/oracle/oradata db_create_online_log_dest_1 string /u01/app/oracle/oradata/ db_create_online_log_dest_2 string /u01/app/oracle/fast_recovery_area/
Add the standby logfile into database
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 50M; Database altered. SQL> / Database altered. SQL> / Database altered. SQL> / Database altered.
Verify the added standby logfile by using database level
SQL> select member from V$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_3_g7wokwvw_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_3_g7wokz80_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_2_g7wokwt1_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_2_g7wokz81_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_1_g7wokwrp_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_1_g7wokx6f_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_4_gltxr6v3_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_4_gltxr6x8_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_5_gltxxx58_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_5_gltxxx7q_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_6_gltxxygy_.log MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_6_gltxxykj_.log /u01/app/oracle/oradata/ORA18C/onlinelog/o1_mf_7_gltxxz9l_.log /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/o1_mf_7_gltxxzdy_.log 14 rows selected.
Verify the added standby logfile by using database level physical level
[oracle@oracle18prim ~]$ cd /u01/app/oracle/oradata/ORA18C/onlinelog/ [oracle@oracle18prim onlinelog]$ ls o1_mf_1_g7wokwrp_.log o1_mf_2_g7wokwt1_.log o1_mf_3_g7wokwvw_.log o1_mf_4_gltxr6v3_.log o1_mf_5_gltxxx58_.log o1_mf_6_gltxxygy_.log o1_mf_7_gltxxz9l_.log
[oracle@oracle18prim onlinelog]$ cd /u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/ [oracle@oracle18prim onlinelog]$ ls o1_mf_1_g7wokx6f_.log o1_mf_2_g7wokz81_.log o1_mf_3_g7wokz80_.log o1_mf_4_gltxr6x8_.log o1_mf_5_gltxxx7q_.log o1_mf_6_gltxxykj_.log o1_mf_7_gltxxzdy_.log
Check DB_NAME and DB_UNIQUE_NAME in database & set both to “ora18c” on the primary database.
SQL> show parameter db_name NAME TYPE VALUE ------------------------------- ----------- ------------------------------ db_name string ora18c
SQL> show parameter db_unique_name NAME TYPE VALUE ---------------------------------------- ----------- ------------------------------ db_unique_name string ora18c
Set the STANDBY_FILE_MANAGEMENT parameter to AUTO
SQL> show parameter STANDBY_FILE_MANAGEMENT NAME TYPE VALUE ------------------------------------ ----------- ------- standby_file_management string MANUAL
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered.
Set the network configuration for both primary and standby databases as shown below
On Primary DATABASE
TNSNAMES.ORA
[oracle@oracle18prim ~]$ cd /u01/ora18c_soft/network/admin/
[oracle@oracle18prim admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/ora18c_soft/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORA18C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora18c) ) ) PDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb1) ) ) PDB_AVAND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb_avand) ) ) #ADD THE STANDBY DB ORA18CSTBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18cstby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora18c) ) )
LISTENER.ORA
[oracle@oracle18prim admin]$ vi listener.ora # listener.ora Network Configuration File: /u01/ora18c_soft/network/admin/listener.ora # Generated by Oracle configuration tools. #PRIMARY DB LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora18c_DGMGRL) (ORACLE_HOME = /u01/ora18c_soft/) (SID_NAME = ora18c) ) ) ADR_BASE_LISTENER = /u01/app/oracle :wq!
ON STANDBY DATABASE
TNSMAMES.ORA
[oracle@oracle18cstd ~]$ cd $ORACLE_HOME/network/admin
[oracle@oracle18cstd admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/ora18c_soft/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORA18C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora18c) ) ) #STANDBY DB ORA18C_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18cstby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ora18c) ) ) #TEST PDB PDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb1) ) ) #PRE-PRODUCTION PDB PDB_AVAND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18c_db)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb_avand) ) ) :wq!
LISTENER.ORA
[oracle@oracle18cstd admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/ora18c_soft/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18cstby)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora18c_stby_DGMGRL) (ORACLE_HOME = /u01/ora18c_soft/) (SID_NAME = ora18c) ) ) ADR_BASE_LISTENER = /u01/app/oracle :wq!
Reload/stop/start the listener.ora on both servers.
$lsnrctl reload/stop
$lsnrctl start
Configure the hostname on both server
PRIMARY DB
[root@oracle18prim ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 192.168.120.131 oracle18prim 192.168.120.136 oracle18stby
STANDBY DB
[root@ oracle18cstby ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 192.168.120.131 oracle18prim 192.168.120.136 oracle18stby
[root@ oracle18cstby ~]# hostname oracle18cstby [oracle@oracle18cstby ~]$ hostname oracle18cstby
Set the BASH PROFILE On STANDBY DB
[oracle@oracle18cstby ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export PATH # Oracle Settings export TMP=/tmp export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; ORACLE_SID=ora18c_stby; export ORACLE_SID ORACLE_UNQNAME=ora18c_stby; export ORACLE_UNQNAME ORACLE_HOSTNAME=oracle18stby; export ORACLE_HOSTNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/ora18c_db_home; export ORACLE_HOME export PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin:$HOME/.local/bin:$HOME/bin alias cdob='cd $ORACLE_BASE' alias cdoh='cd $ORACLE_HOME' alias tns='cd $ORACLE_HOME/network/admin' alias envo='env | grep ORACLE' umask 022 if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -u 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi #envo :wq
Run the Bash Profile
[oracle@oracle18stby ~]$ . .bash_profile
Verify
[oracle@oracle18stby ~]$ echo $ORACLE_SID ora18c_stby
[oracle@oracle18stby ~]$ echo $ORACLE_UNQNAME ora18c_stby
[oracle@oracle18stby ~]$ echo $ORACLE_BASE /u01/app/oracle
[oracle@oracle18stby ~]$ echo $ORACLE_HOME /u01/ora18c_db_home
Create directories on Standby DB
[oracle@oracle18stby ~]$ mkdir -p /u01/app/oracle/oradata/ORA18C_STBY/ [oracle@oracle18stby ~]$ mkdir -p /u01/app/oracle/admin/ORA18C_STBY/adump/ [oracle@oracle18stby ~]$ mkdir - /u01/app/oracle/fast_recovery_area/ORA18C_STBY/
Copy the ORAPWD File from Primary DB to Standby DB
[oracle@oracle18prim dbs]$ scp orapwora18c oracle@192.168.120.136:/u01/ora18c_db_home/dbs/ oracle@192.168.120.136's password: orapwora18c 100% 6144 3.5MB/s 00:00
[oracle@oracle18prim dbs]$ ll orapwora18c -rw-r-----. 1 oracle oinstall 6144 Jul 16 04:08 orapwora18c
Verify
[oracle@oracle18stby ~]$ cd /u01/ora18c_db_home/dbs/
[oracle@oracle18stby dbs]$ mv orapwora18c orapwora18c_stby [oracle@oracle18stby dbs]$ ll orapwora18c-stby -rw-r----- 1 oracle oinstall 6144 Jul 16 04:23 orapwora18c-stby
Set the INIT Parameter in dbs location (Here I have given the assign directory & db_file_name_convert
[oracle@oracle18stby ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle18stby dbs]$ vi initora18c_stby.ora ora18c_stby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment *.audit_file_dest='/u01/app/oracle/admin/ORA18C_STBY/adump/' *.audit_trail='db' *.compatible='18.0.0' *.control_files='/u01/app/oracle/oradata/ORA18C_STBY/controlfile/o1_mf_gjyy5fv9_.ctl','/u01/app/oracle/fast_recovery_area/ORA18C_STBY/controlfile/o1_mf_gjyy5fw7_.ctl' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata/' #*.db_domain='localdomain' *.db_name='ora18c' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=12918m *.db_unique_name='ora18c_stby' #is standby *.db_file_name_convert='/u01/app/oracle/oradata/ORA18C/datafile/','/u01/app/oracle/oradata/ORA18C_STBY/datafile/','/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/','/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/','/u01/app/oracle/oradata/ORA18C/8ACC527FC25A7B2FE0538378A8C0927F/datafile/','/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/' *.log_file_name_convert='/u01/app/oracle/oradata/ORA18C/onlinelog/','/u01/app/oracle/oradata/ORA18C_STBY/onlinelog/','/u01/app/oracle/fast_recovery_area/ORA18C/onlinelog/','/u01/app/oracle/fast_recovery_area/ORA18C_STBY/onlinelog/' *.diagnostic_dest='/u01/app/oracle' *.enable_pluggable_database=true *.open_cursors=300 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' :wq!
Startup DB with NOMOUNT
[oracle@oracle18stby]$ !sq SQL> startup nomount pfile=’$ORACLE_HOME/dbs/initora18c_stby.ora'; ORACLE instance started. Total System Global Area 243266936 bytes Fixed Size 8656248 bytes Variable Size 180355072 bytes Database Buffers 50331648 bytes Redo Buffers 3923968 bytes SQL> exit
Check the SYS Password by connecting both server
On STANDBY DB
Connecting to Primary DB
[oracle@oracle18stby ~]$ sqlplus sys/"ora18ctest$"@ora18c as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 07:13:26 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 09:11:40 -04:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select status from v$instance; STATUS ------ OPEN
Connecting to Standby DB
[oracle@oracle18stby ~]$ sqlplus sys/"ora18ctest$"@ora18c_stby as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 07:14:34 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 09:11:40 -04:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select status from V$instance; STATUS ------- STARTED
Similarly check from the Primary DB
[oracle@oracle18prim ~]$ sqlplus sys/"ora18ctest$"@ora18c_stby as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 07:03:52 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Mon Jul 29 2019 07:14:34 -04:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select status from v$instance; STATUS ------- STARTED
[oracle@oracle18prim ~]$ sqlplus sys/"ora18ctest$"@ora18c as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 07:04:02 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Mon Jul 29 2019 07:00:40 -04:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select status from v$instance; STATUS ------- OPEN
Now create the standby database using DUPLICATE by using RMAN
Connect to RMAN by using both the TARGET and AUXILIARY instances using below command.
[oracle@oracle18stby ~]$ rman TARGET sys/"ora18ctest$"@ora18c AUXILIARY sys/"ora18ctest$"@ora18c_stby
Recovery Manager: Release 18.0.0.0.0 - Production on Sun Jul 28 08:26:15 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA18C (DBID=624054314)
connected to auxiliary database: ORA18C (not mounted)
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER;
Starting Duplicate Db at 28-JUL-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/ora18c_db_home/dbs/orapwora18c_stby' ;
}
executing Memory Script
Starting backup at 28-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK
Finished backup at 28-JUL-19
contents of Memory Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/ORA18C_STBY/controlfile/o1_mf_gjyy5fv9_.ctl'', ''/u01/app/oracle/fast_recovery_area/ORA18C_STBY/controlfile/o1_mf_gjyy5fw7_.ctl'' comment=
''Set by RMAN'' scope=spfile";
restore clone from service 'ora18c' standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/ORA18C_STBY/controlfile/o1_mf_gjyy5fv9_.ctl'', ''/u01/app/oracle/fast_recovery_area/ORA18C_STBY/controlfile/o1_mf_gjyy5fw7_.ctl'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 28-JUL-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORA18C_STBY/controlfile/o1_mf_gjyy5fv9_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/controlfile/o1_mf_gjyy5fw7_.ctl
Finished restore at 28-JUL-19
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_temp_g7wol9v4_.tmp";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/temp012019-03-05_06-02-46-915-AM.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_temp_g8sx02ho_.dbf";
set newname for tempfile 4 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_temp_ghptpjb6_.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_system_g7wofq7v_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_sysaux_g7wohgf4_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_undotbs1_g7wojkpd_.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_system_g7wosthw_.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_sysaux_g7wosthp_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_users_g7wojltf_.dbf";
set newname for datafile 8 to
"/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_undotbs1_g7wosthz_.dbf";
set newname for datafile 14 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g8sx02h4_.dbf";
set newname for datafile 15 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g8sx02hl_.dbf";
set newname for datafile 16 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g8sx02hn_.dbf";
set newname for datafile 17 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g8sx02hp_.dbf";
set newname for datafile 18 to
"/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sxpxts_.dbf";
set newname for datafile 19 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_system_ghptpj95_.dbf";
set newname for datafile 20 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_sysaux_ghptpjb4_.dbf";
set newname for datafile 21 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_undotbs1_ghptpjb5_.dbf";
set newname for datafile 22 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_users_ghptq11g_.dbf";
set newname for datafile 24 to
"/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/avand_tbs.dbf";
restore
from nonsparse from service
'ora18c' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_temp_g7wol9v4_.tmp in control file
renamed tempfile 2 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/temp012019-03-05_06-02-46-915-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_temp_g8sx02ho_.dbf in control file
renamed tempfile 4 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_temp_ghptpjb6_.dbf 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 28-JUL-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_system_g7wofq7v_.dbf
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: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_sysaux_g7wohgf4_.dbf
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: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_undotbs1_g7wojkpd_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_system_g7wosthw_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_sysaux_g7wosthp_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_users_g7wojltf_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_undotbs1_g7wosthz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g8sx02h4_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g8sx02hl_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g8sx02hn_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g8sx02hp_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00018 to /u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sxpxts_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_system_ghptpj95_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_sysaux_ghptpjb4_.dbf
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: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_undotbs1_ghptpjb5_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_users_ghptq11g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/avand_tbs.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 28-JUL-19
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'ora18c'
archivelog from scn 15252946;
switch clone datafile all;
}
executing Memory Script
Starting restore at 28-JUL-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=403
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service ora18c
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=404
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-JUL-19
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_system_gmv545og_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_sysaux_gmv54nwn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_undotbs1_gmv55g1s_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357D033BA6826A8E0538378A8C07E85/datafile/o1_mf_system_gmv55k7j_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357D033BA6826A8E0538378A8C07E85/datafile/o1_mf_sysaux_gmv55ndy_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/datafile/o1_mf_users_gmv55qkc_.dbf
atafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357D033BA6826A8E0538378A8C07E85/datafile/o1_mf_undotbs1_gmv55rrs_.dbf
atafile 14 switched to datafile copy
input datafile copy RECID=11 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_gmv55t00_.dbf
atafile 15 switched to datafile copy
input datafile copy RECID=12 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_gmv5614x_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=13 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_gmv568fs_.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=14 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_gmv56cft_.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=15 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_gmv56doy_.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=16 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_system_gmv56fwb_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=17 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_sysaux_gmv56o1w_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=18 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_undotbs1_gmv574dq_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=19 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/o1_mf_users_gmv577gp_.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=20 STAMP=1014798513 file name=/u01/app/oracle/oradata/ORA18C_STBY/8ACC527FC25A7B2FE0538378A8C0927F/datafile/avand_tbs.dbf
contents of Memory Script:
{
set until scn 15253964;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 28-JUL-19
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 403 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_403_gmv57g9j_.arc
archived log for thread 1 with sequence 404 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_404_gmv57hys_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_403_gmv57g9j_.arc thread=1 sequence=403
archived log file name=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_404_gmv57hys_.arc thread=1 sequence=404
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JUL-19
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_403_gmv57g9j_.arc RECID=1 STAMP=1014798510
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/2019_07_28/o1_mf_1_404_gmv57hys_.arc RECID=2 STAMP=1014798511
Deleted 2 objects
Finished Duplicate Db at 28-JUL-19
Check the SYNC of Database from Primary & Standby DB
ON Primary DB
SQL> alter system set log_archive_config='DG_CONFIG=(ora18c,ora18c_stby)' scope=BOTH; System altered.
SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(ora18c,ora18c_stby)
SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY, valid_for=(ALL_LOGFILES,ALL_ROLES)
SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="ora18c_stby", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="ora18c_stby" net_timeout=30, valid_for=(online_logfile,all_roles)
SQL> alter system set log_archive_dest_state_2='ENABLE' scope=BOTH; System altered.
SQL> alter system set log_archive_dest_state_1='ENABLE' scope=BOTH; System altered.
SQL> alter system set fal_client='ora18c' scope=BOTH; System altered.
SQL> alter system set fal_server='ora18c_stby' scope=BOTH; System altered.
On Standby DB
SQL> alter system set fal_client='ora18c_stby'; System altered.
SQL> alter system set fal_server='ora18c'; System altered.
SQL> alter system set standby_file_management='AUTO'; System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(ora18c,ora18c_stby)'; System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORA18C_STBY/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora18c_stby'; System altered.
SQL> alter system set log_archive_dest_2='service=ora18c ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ora18c'; System altered.
Check the status of MRP, RFS Process
PRIMARY DB
[oracle@oracle18prim ~]$ sqlplus / as sysdba SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF ---------- ---------- ----------- --------- ---------- 1 456 456 29-JUL-19 0
STANDBY DB
[oracle@oracle18stby ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 07:48:34 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 446 1 2 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 448 1 1 ARCH CLOSING 1 430 1 1 ARCH CLOSING 1 432 1 1 MRP0 WAIT_FOR_LOG 1 457 0 0 RFS IDLE 1 0 0 0 RFS IDLE 1 457 13833 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- RFS IDLE 0 0 0 0 12 rows selected. SQL> THREAD# MAX(SEQUENCE#) ----------------------------------- 0 456
Check the DBS, PDBS LISTENER & TNSNAMES Status from Both DB
PRIMARY DB
[oracle@oracle18prim ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 08:21:41 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB_AVAND READ WRITE NO
[oracle@oracle18prim ~]$ lsnrctl status LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:37:02 Copyright (c) 1991, 2018, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle18prim)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 21-JUL-2019 08:08:18 Uptime 7 days 23 hr. 28 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/ora18c_soft/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle18prim/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle18prim)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle18prim)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora18c/xdb_wallet))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle18prim)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora18c/xdb_wallet))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle18prim)(PORT=5505))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora18c/xdb_wallet))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle18prim)(PORT=5511))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "8357ef6595df3749e0538378a8c0e2c5" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "8acc527fc25a7b2fe0538378a8c0927f" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "ora18c" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "ora18cXDB" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "ora18c_CFG" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "ora18c_DGB" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... Service "pdb_avand" has 1 instance(s). Instance "ora18c", status READY, has 1 handler(s) for this service... The command completed successfully
[oracle@oracle18prim ~]$ tnsping ora18c TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:37:16 Copyright (c) 1997, 2018, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18prim)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora18c) (UR = A))) OK (0 msec)
[oracle@oracle18prim ~]$ tnsping ora18c_stby TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:37:19 Copyright (c) 1997, 2018, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18stby)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora18c_stby) (UR = A))) OK (0 msec)
STANDBY DB
[oracle@oracle18stby ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 29 08:36:08 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 PDB1 MOUNTED 4 PDB_AVAND MOUNTED
[oracle@oracle18stby ~]$ lsnrctl status LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:49:52 Copyright (c) 1991, 2018, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle18stby)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 28-JUL-2019 07:27:00 Uptime 1 days 0 hr. 22 min. 52 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/ora18c_db_home/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle18stby/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle18stby)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s). Instance "ora18c_stby", status READY, has 1 handler(s) for this service... Service "ora18c_CFG" has 1 instance(s). Instance "ora18c_stby", status READY, has 1 handler(s) for this service... Service "ora18c_stby" has 1 instance(s). Instance "ora18c_stby", status READY, has 1 handler(s) for this service... Service "ora18c_stby_DGB" has 1 instance(s). Instance "ora18c_stby", status READY, has 1 handler(s) for this service... The command completed successfully
[oracle@oracle18stby ~]$ tnsping ora18c TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:50:52 Copyright (c) 1997, 2018, Oracle. All rights reserved. Used parameter files: /u01/ora18c_db_home/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18prim)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora18c) (UR = A))) OK (0 msec)
[oracle@oracle18stby ~]$ tnsping ora18c_stby TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 29-JUL-2019 07:50:57 Copyright (c) 1997, 2018, Oracle. All rights reserved. Used parameter files: /u01/ora18c_db_home/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle18stby)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora18c_stby) (UR = A))) OK (0 msec)
========Hence Tested & Verified Primary & Standby DB are sync & configured========