Mallik034: June 2023

Tuesday, June 20, 2023

PDB save state?

Automate Pluggable Database opening during Container instance startup

1) List the PDBs and respective Modes

sqlplus / as sysdba

SQL> show pdbs

2) Check the Saved State for all PDB’s from DBA view

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

3) Check the Saved State for all PDB’s from CDB view

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

4) Change Save State of PDB by usig below statement

alter pluggable database PDB1 save state;

5) To discard any saved state of a pluggable database using below statement

alter pluggable database PDB1 discard state;

logs:

=====

[root@oracleprod ~]# su - oracle

Last login: Wed Jun 21 01:20:29 +08 2023

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ ps -ef|grep smon

oracle 17433 16853 0 01:45 pts/0 00:00:00 grep --color=auto smon

oracle 29594 1 0 Jun19 ? 00:00:02 ora_smon_PRODCDB

[oracle@oracleprod ~]$ . oraenv

ORACLE_SID = [PRODCDB] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ env |grep ORA

ORACLE_SID=PRODCDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:00 2023

Version 19.19.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.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 PDB2 READ WRITE NO

6 ORAODB1 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:47:57 2023

Version 19.19.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$ ps -ef|grep smon

oracle 18270 16853 0 01:48 pts/0 00:00:00 grep --color=auto smon

[oracle@oracleprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 21 01:48:53 2023

Version 19.19.0.0.0

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3707763824 bytes

Fixed Size 9170032 bytes

Variable Size 1912602624 bytes

Database Buffers 1778384896 bytes

Redo Buffers 7606272 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 MOUNTED

4 PDB2 MOUNTED

6 ORAODB1 MOUNTED

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 MOUNTED

6 ORAODB1 MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

6 ORAODB1 READ WRITE NO

SQL> alter pluggable database ORAODB1 close;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

6 ORAODB1 MOUNTED

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

no rows selected

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

no rows selected

SQL> alter pluggable database PDB1 save state;

Pluggable database altered.

SQL> col CON_NAME for a20;

SQL> col INSTANCE_NAME for a20;

SQL> col state for a20;

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME INSTANCE_NAME STATE

-------------------- -------------------- --------------------

PDB1 PRODCDB OPEN

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME STATE

-------------------- --------------------

PDB1 OPEN

SQL> alter pluggable database PDB2 save state;

Pluggable database altered.

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME STATE

-------------------- --------------------

PDB1 OPEN

PDB2 OPEN

SQL> alter pluggable database ORAODB1 save state;

Pluggable database altered.

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME STATE

-------------------- --------------------

PDB1 OPEN

PDB2 OPEN

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3707763824 bytes

Fixed Size 9170032 bytes

Variable Size 1912602624 bytes

Database Buffers 1778384896 bytes

Redo Buffers 7606272 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

6 ORAODB1 MOUNTED

SQL> col name for a20;

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

NAME STATE

-------------------- --------------------

PDB1 OPEN

PDB2 OPEN

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME INSTANCE_NAME STATE

-------------------- -------------------- --------------------

PDB1 PRODCDB OPEN

PDB2 PRODCDB OPEN

SQL> alter pluggable database PDB1 discard state;

Pluggable database altered.

SQL> col CON_NAME for a20;

SQL> col INSTANCE_NAME for a20;

SQL> col state for a20;

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME INSTANCE_NAME STATE

-------------------- -------------------- --------------------

PDB2 PRODCDB OPEN

SQL> alter pluggable database PDB2 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

no rows selected

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

[oracle@oracleprod ~]$

Regards,

Mallikarjun Ramadurg

WhatsApp: +91 9880616848

gmail: mallikarjun.ramadurg@gmail.com

Saturday, June 10, 2023

RMAN cloning or RMAN standby build is very easy - RMAN insights

What are the various methods to build standby database?

Option 1: Build standby database using Restore & Recover method from primary database RMAN backup

Option 2: Build standby database using RMAN duplicate method from primary database RMAN backup

Option 3: Build standby database using RMAN active database duplicate method from primary database without backup

Option 4: Build standby database using Primary Service name without backup - 12c New Feature

What are the various methods to build or clone primary/production database to lower environment?

Option 5: Clone/Build database using Restore & Recover methode from primary database RMAN backup

Option 6: Clone/Build database using RMAN duplicate method from primary database RMAN backup

Option 7: Clone/Build database using RMAN active database duplicate method from primary database without backup

#################################################################

#|||||||||||||||||||||||||Standby Build|||||||||||||||||||||||||#

#################################################################

Option 1: Build standby database using Restore & Recover method from primary database RMAN backup

=================================================================================================

Step 1 - Verify the primary and standby environment details

Primary:

========

[oracle@oracledb archivelog]$ ps -ef|grep smon

oracle 22431 1 0 23:09 ? 00:00:00 ora_smon_ORCL

oracle 24508 19837 0 23:14 pts/1 00:00:00 grep --color=auto smon

[oracle@oracledb archivelog]$

[oracle@oracledb archivelog]$ hostname -f

oracledb.localdomain.com

[oracle@oracledb archivelog]$

Standby:

========

[oracle@oraclesb onlinelog]$ ps -ef|grep smon

oracle 7608 6665 0 23:16 pts/1 00:00:00 grep --color=auto smon

[oracle@oraclesb onlinelog]$

[oracle@oraclesb onlinelog]$ hostname -f

oraclesb.localdomain.com

[oracle@oraclesb onlinelog]$

Primary:

========

[root@oracledb ~]# su - oracle

Last login: Fri Jun 9 23:51:40 IST 2023 on pts/1

[oracle@oracledb ~]$ . oraenv

ORACLE_SID = [oracle] ? ORCL

The Oracle base has been set to /u01/app/oracle

[oracle@oracledb ~]$

[oracle@oracledb admin]$ env |grep ORA

ORACLE_SID=ORCL

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oracledb admin]$

[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 23:54:08 2023

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> set pages 1000 lines 1000

SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FORCE_LOGGING,DB_UNIQUE_NAME from v$database;

DBID NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING DB_UNIQUE_NAME

---------- --------- ------------ -------------------- -------------------- ---------------- --------------------------------------- ------------------------------

1607315920 ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY YES ORCL

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 36266

Next log sequence to archive 36268

Current log sequence 36268

SQL> select FORCE_LOGGING,log_mode from v$database;

FORCE_LOGGING LOG_MODE

--------------------------------------- ------------

YES ARCHIVELOG

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

[oracle@oracledb ~]$

Step 2. Verify the Primary and Standby listener.ora and tnsnames.ora files

Primary:

========

cd $ORACLE_HOME/network/admin

cat listener.ora

cat tnsnames.ora

lsnrctl status LISTENER_ORCL

lsnrctl start LISTENER_ORCL

Standby:

========

. oraenv

ORCLSB

/u01/app/oracle/product/19.0.0.0/dbhome_1

cd $ORACLE_HOME/network/admin

cat listener.ora

cat tnsnames.ora

lsnrctl status LISTENER_ORCLSB

lsnrctl start LISTENER_ORCLSB

Primary:

========

[oracle@oracledb ~]$ cd $ORACLE_HOME/network/admin

[oracle@oracledb admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oracledb admin]$

[oracle@oracledb admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

)

ADR_BASE_LISTENER_ORCL = /u01/app/oracle

[oracle@oracledb admin]$

[oracle@oracledb admin]$ cat tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

ORCLSB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCLSB)

)

[oracle@oracledb admin]$

[oracle@oracledb admin]$ lsnrctl status LISTENER_ORCL

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 23:55:33

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.localdomain.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER_ORCL

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 09-JUN-2023 20:52:34

Uptime 0 days 3 hr. 2 min. 59 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oracledb/listener_orcl/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledb.localdomain.com)(PORT=1522)))

Services Summary...

Service "ORCL" has 1 instance(s).

Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCLXDB" has 1 instance(s).

Instance "ORCL", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@oracledb admin]$

Standby:

========

[root@oraclesb ~]# su - oracle

Last login: Fri Jun 9 23:51:13 IST 2023 on pts/2

[oracle@oraclesb ~]$ . oraenv

ORACLE_SID = [oracle] ? ORCLSB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclesb ~]$

[oracle@oraclesb ~]$ env |grep ORA

ORACLE_SID=ORCLSB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclesb ~]$

[oracle@oraclesb ~]$ cd $ORACLE_HOME/network/admin

[oracle@oraclesb admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_ORCLSB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

)

ADR_BASE_LISTENER_ORCLSB = /u01/app/oracle

SID_LIST_LISTENER_ORCLSB =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORCLSB)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)

(SID_NAME = ORCLSB)

)

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCLSB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLSB)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ lsnrctl status LISTENER_ORCLSB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 23:58:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclesb.localdomain.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER_ORCLSB

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 09-JUN-2023 20:56:43

Uptime 0 days 3 hr. 1 min. 51 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oraclesb/listener_orclsb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclesb.localdomain.com)(PORT=1522)))

Services Summary...

Service "ORCLSB" has 1 instance(s).

Instance "ORCLSB", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@oraclesb admin]$

Primary:

========

tnsping ORCL

tnsping ORCLSB

Standby:

========

tnsping ORCL

tnsping ORCLSB

Primary:

========

[oracle@oracledb backup]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:06:46

Used parameter files:

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (0 msec)

[oracle@oracledb backup]$ tnsping ORCLSB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:06:49

Used parameter files:

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ORCLSB)))

OK (0 msec)

[oracle@oracledb backup]$

Standby:

========

[oracle@oraclesb dbs]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:07:05

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

OK (10 msec)

[oracle@oraclesb dbs]$ tnsping ORCLSB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:07:09

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLSB)))

OK (0 msec)

[oracle@oraclesb dbs]$

Step 3. Copy Primary database password file to standby server

Primary:

========

cd $ORACLE_HOME/dbs

ls -ltr orapwORCL

scp orapwORCL oracle@standbyserver:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwORCLSB

Standby:

========

cd $ORACLE_HOME/dbs

ls -l orapwORCLSB

Primary:

========

[oracle@oracledb admin]$ cd $ORACLE_HOME/dbs

[oracle@oracledb dbs]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

[oracle@oracledb dbs]$ ll orapwORCL

-rw-r-----. 1 oracle oinstall 2048 Aug 2 2021 orapwORCL

[oracle@oracledb dbs]$

[oracle@oracledb dbs]$ scp orapwORCL oracle@10.38.4.118:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwORCLSB

oracle@10.38.4.118's password:

orapwORCL 100% 2048 1.2MB/s 00:00

[oracle@oracledb dbs]$

Standby:

========

[oracle@oraclesb admin]$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/

[oracle@oraclesb dbs]$ ls -ltr orapwORCLSB

-rw-r-----. 1 oracle oinstall 2048 Jun 10 00:00 orapwORCLSB

[oracle@oraclesb dbs]$

Steps 4. Take a backup from primary database including standby controlfile backup and once backups are completed copy themt to standby server.

run {

allocate channel ch1 device type disk;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt expired archivelog all;

backup as backupset database format '/u01/backup/Fullback_%T_%U'

plus archivelog format '/u01/backup/Archive_%T_%U';

backup spfile format '/u01/backup/Controlback_%T_%U';

backup current controlfile format '/u01/backup/Controlback_%T_%U';

backup current controlfile for standby format '/u01/backup/Standby_Controlback_%T_%U';

release channel ch1;

}

[oracle@oracledb dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jun 10 00:02:17 2023

Version 19.11.0.0.0

connected to target database: ORCL (DBID=1607315920)

RMAN> run {

allocate channel ch1 device type disk;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt expired archivelog all;

backup as backupset database format '/u01/backup/Fullback_%T_%U'

plus archivelog format '/u01/backup/Archive_%T_%U';

backup spfile format '/u01/backup/Controlback_%T_%U';

backup current controlfile format '/u01/backup/Controlback_%T_%U';

backup current controlfile for standby format '/u01/backup/Standby_Controlback_%T_%U';

release channel ch1;

}

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=271 device type=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20230609_0h1ua27a_19473_1_1 RECID=18238 STAMP=1139083498

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20230609_0i1ua27b_19474_1_1 RECID=18239 STAMP=1139083499

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20230609_0j1ua27d_19475_1_1 RECID=18240 STAMP=1139083501

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20230609_0k1ua27e_19476_1_1 RECID=18241 STAMP=1139083502

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20230609_0l1ua27t_19477_1_1 RECID=18242 STAMP=1139083517

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20230609_0m1ua27u_19478_1_1 RECID=18243 STAMP=1139083518

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_06_09/o1_mf_s_1139083522_l86g5bfp_.bkp RECID=18244 STAMP=1139083522

Crosschecked 7 objects

List of Backup Pieces

BP Key BS Key Pc# Cp# Status Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

18238 18235 1 1 EXPIRED DISK /u01/backup/Archive_20230609_0h1ua27a_19473_1_1

18239 18236 1 1 EXPIRED DISK /u01/backup/Archive_20230609_0i1ua27b_19474_1_1

18240 18237 1 1 EXPIRED DISK /u01/backup/Archive_20230609_0j1ua27d_19475_1_1

18241 18238 1 1 EXPIRED DISK /u01/backup/Fullback_20230609_0k1ua27e_19476_1_1

18242 18239 1 1 EXPIRED DISK /u01/backup/Archive_20230609_0l1ua27t_19477_1_1

18243 18240 1 1 EXPIRED DISK /u01/backup/Controlback_20230609_0m1ua27u_19478_1_1

18244 18241 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_06_09/o1_mf_s_1139083522_l86g5bfp_.bkp

deleted backup piece

backup piece handle=/u01/backup/Archive_20230609_0h1ua27a_19473_1_1 RECID=18238 STAMP=1139083498

deleted backup piece

backup piece handle=/u01/backup/Archive_20230609_0i1ua27b_19474_1_1 RECID=18239 STAMP=1139083499

deleted backup piece

backup piece handle=/u01/backup/Archive_20230609_0j1ua27d_19475_1_1 RECID=18240 STAMP=1139083501

deleted backup piece

backup piece handle=/u01/backup/Fullback_20230609_0k1ua27e_19476_1_1 RECID=18241 STAMP=1139083502

deleted backup piece

backup piece handle=/u01/backup/Archive_20230609_0l1ua27t_19477_1_1 RECID=18242 STAMP=1139083517

deleted backup piece

backup piece handle=/u01/backup/Controlback_20230609_0m1ua27u_19478_1_1 RECID=18243 STAMP=1139083518

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_06_09/o1_mf_s_1139083522_l86g5bfp_.bkp RECID=18244 STAMP=1139083522

Deleted 7 EXPIRED objects

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36245_l86n06v9_.arc RECID=57141 STAMP=1139089502

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36246_l86njhjb_.arc RECID=57143 STAMP=1139090023

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36247_l86njhkd_.arc RECID=57144 STAMP=1139090023

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36248_l86njjl5_.arc RECID=57147 STAMP=1139090024

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36249_l86njjlm_.arc RECID=57148 STAMP=1139090024

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36250_l86njjp0_.arc RECID=57151 STAMP=1139090024

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36251_l86nrjc3_.arc RECID=57153 STAMP=1139090280

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36252_l86ocjnr_.arc RECID=57166 STAMP=1139090889

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36253_l86ocjo2_.arc RECID=57163 STAMP=1139090888

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36254_l86oclz2_.arc RECID=57167 STAMP=1139090891

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36255_l86ocm0q_.arc RECID=57168 STAMP=1139090891

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36256_l86ocmfs_.arc RECID=57171 STAMP=1139090891

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36257_l86owjx4_.arc RECID=57173 STAMP=1139091432

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36258_l86prjwy_.arc RECID=57175 STAMP=1139092328

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36259_l86qnntl_.arc RECID=57177 STAMP=1139093228

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36260_l86rjrj1_.arc RECID=57179 STAMP=1139094128

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36261_l86sdw0z_.arc RECID=57181 STAMP=1139095028

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36262_l86sf8h9_.arc RECID=57183 STAMP=1139095040

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36263_l86t9co4_.arc RECID=57185 STAMP=1139095939

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_09/o1_mf_1_36264_l86v5hof_.arc RECID=57187 STAMP=1139096839

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_10/o1_mf_1_36265_l86w1mgd_.arc RECID=57189 STAMP=1139097739

Crosschecked 21 objects

specification does not match any archived log in the repository

Starting backup at 10-JUN-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=36245 RECID=57141 STAMP=1139089502

input archived log thread=1 sequence=36246 RECID=57143 STAMP=1139090023

input archived log thread=1 sequence=36247 RECID=57144 STAMP=1139090023

input archived log thread=1 sequence=36248 RECID=57147 STAMP=1139090024

input archived log thread=1 sequence=36249 RECID=57148 STAMP=1139090024

input archived log thread=1 sequence=36250 RECID=57151 STAMP=1139090024

input archived log thread=1 sequence=36251 RECID=57153 STAMP=1139090280

input archived log thread=1 sequence=36252 RECID=57166 STAMP=1139090889

input archived log thread=1 sequence=36253 RECID=57163 STAMP=1139090888

input archived log thread=1 sequence=36254 RECID=57167 STAMP=1139090891

input archived log thread=1 sequence=36255 RECID=57168 STAMP=1139090891

input archived log thread=1 sequence=36256 RECID=57171 STAMP=1139090891

input archived log thread=1 sequence=36257 RECID=57173 STAMP=1139091432

input archived log thread=1 sequence=36258 RECID=57175 STAMP=1139092328

input archived log thread=1 sequence=36259 RECID=57177 STAMP=1139093228

input archived log thread=1 sequence=36260 RECID=57179 STAMP=1139094128

input archived log thread=1 sequence=36261 RECID=57181 STAMP=1139095028

input archived log thread=1 sequence=36262 RECID=57183 STAMP=1139095040

input archived log thread=1 sequence=36263 RECID=57185 STAMP=1139095939

input archived log thread=1 sequence=36264 RECID=57187 STAMP=1139096839

input archived log thread=1 sequence=36265 RECID=57189 STAMP=1139097739

input archived log thread=1 sequence=36266 RECID=57190 STAMP=1139097745

channel ch1: starting piece 1 at 10-JUN-23

channel ch1: finished piece 1 at 10-JUN-23

piece handle=/u01/backup/Archive_20230610_121uag4h_19490_1_1 tag=TAG20230610T000225 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-JUN-23

Starting backup at 10-JUN-23

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_k3jps1mq_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_k3jpsjp6_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_k3jprllp_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_k3jpsqnf_.dbf

channel ch1: starting piece 1 at 10-JUN-23

channel ch1: finished piece 1 at 10-JUN-23

piece handle=/u01/backup/Fullback_20230610_131uag4i_19491_1_1 tag=TAG20230610T000226 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:15

Finished backup at 10-JUN-23

Starting backup at 10-JUN-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=36267 RECID=57191 STAMP=1139097762

channel ch1: starting piece 1 at 10-JUN-23

channel ch1: finished piece 1 at 10-JUN-23

piece handle=/u01/backup/Archive_20230610_141uag52_19492_1_1 tag=TAG20230610T000242 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-JUN-23

Starting backup at 10-JUN-23

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ch1: starting piece 1 at 10-JUN-23

channel ch1: finished piece 1 at 10-JUN-23

piece handle=/u01/backup/Controlback_20230610_151uag53_19493_1_1 tag=TAG20230610T000243 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-JUN-23

Starting backup at 10-JUN-23

channel ch1: starting datafile copy

copying current control file

output file name=/u01/backup/Controlback_20230610_cf_D-ORCL_id-1607315920_161uag54 tag=TAG20230610T000244 RECID=1799 STAMP=1139097764

channel ch1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 10-JUN-23

Starting backup at 10-JUN-23

channel ch1: starting datafile copy

copying standby control file

output file name=/u01/backup/Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55 tag=TAG20230610T000245 RECID=1800 STAMP=1139097766

channel ch1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 10-JUN-23

Starting Control File and SPFILE Autobackup at 10-JUN-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_06_10/o1_mf_s_1139097767_l86w2h7j_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-JUN-23

released channel: ch1

RMAN> exit

Recovery Manager complete.

[oracle@oracledb dbs]$

[oracle@oracledb dbs]$ cd /u01/backup/

[oracle@oracledb backup]$ ls -ltrh

total 3.2G

-rw-r-----. 1 oracle oinstall 98M Jun 10 00:02 Archive_20230610_121uag4h_19490_1_1

-rw-r-----. 1 oracle oinstall 3.0G Jun 10 00:02 Fullback_20230610_131uag4i_19491_1_1

-rw-r-----. 1 oracle oinstall 14K Jun 10 00:02 Archive_20230610_141uag52_19492_1_1

-rw-r-----. 1 oracle oinstall 112K Jun 10 00:02 Controlback_20230610_151uag53_19493_1_1

-rw-r-----. 1 oracle oinstall 26M Jun 10 00:02 Controlback_20230610_cf_D-ORCL_id-1607315920_161uag54

-rw-r-----. 1 oracle oinstall 26M Jun 10 00:02 Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55

[oracle@oracledb backup]$

Copy backups to standby server:

[oracle@oracledb backup]$ scp * oracle@10.38.4.118:/u01/backup/.

oracle@10.38.4.118's password:

Archive_20230610_121uag4h_19490_1_1 100% 98MB 97.7MB/s 00:01

Archive_20230610_141uag52_19492_1_1 100% 14KB 8.2MB/s 00:00

Controlback_20230610_151uag53_19493_1_1 100% 112KB 27.7MB/s 00:00

Controlback_20230610_cf_D-ORCL_id-1607315920_161uag54 100% 26MB 101.2MB/s 00:00

Fullback_20230610_131uag4i_19491_1_1 100% 3061MB 121.6MB/s 00:25

Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55 100% 26MB 93.9MB/s 00:00

[oracle@oracledb backup]$

Standby:

========

[oracle@oraclesb backup]$ cd /u01/backup/

[oracle@oraclesb backup]$ ll

total 3287224

-rw-r-----. 1 oracle oinstall 102478848 Jun 10 00:08 Archive_20230610_121uag4h_19490_1_1

-rw-r-----. 1 oracle oinstall 14336 Jun 10 00:08 Archive_20230610_141uag52_19492_1_1

-rw-r-----. 1 oracle oinstall 114688 Jun 10 00:08 Controlback_20230610_151uag53_19493_1_1

-rw-r-----. 1 oracle oinstall 26755072 Jun 10 00:08 Controlback_20230610_cf_D-ORCL_id-1607315920_161uag54

-rw-r-----. 1 oracle oinstall 3209994240 Jun 10 00:08 Fullback_20230610_131uag4i_19491_1_1

-rw-r-----. 1 oracle oinstall 26755072 Jun 10 00:09 Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55

[oracle@oraclesb backup]$

Step 5. Create pfile from Primary and modify pfile according to standby server

Primary:

========

[oracle@oracledb backup]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 20:11:07 2023

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/19.0.0

.0/dbhome_1/dbs/spfileORCL.ora

SQL> create pfile='/tmp/initORCL.ora' from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

[oracle@oracledb backup]$

[oracle@oracledb backup]$ cat /tmp/initORCL.ora

ORCL.__data_transfer_cache_size=0

ORCL.__db_cache_size=1308622848

ORCL.__inmemory_ext_roarea=0

ORCL.__inmemory_ext_rwarea=0

ORCL.__java_pool_size=0

ORCL.__large_pool_size=16777216

ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ORCL.__pga_aggregate_target=1241513984

ORCL.__sga_target=3707764736

ORCL.__shared_io_pool_size=134217728

ORCL.__shared_pool_size=2164260864

ORCL.__streams_pool_size=0

ORCL.__unified_pga_pool_size=0

*.archive_lag_target=900

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'

*.audit_trail='DB'

*.compatible='19.0.0'

*.control_file_record_keep_time=7

*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_jjhgcrh6_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_jjhgcrjw_.ctl'#Restore Controlfile

*.db_16k_cache_size=16777216

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/datafile','/u01/app/oracle/oradata/orclsb/datafile'

*.db_name='ORCL'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=21474836480

*.db_unique_name='ORCL'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

*.fal_client='ORCL'

*.fal_server='ORCLSB'

*.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))'

*.log_archive_config='dg_config=(ORCL,ORCLSB)'

*.log_archive_dest_1='location=use_db_recovery_file_dest'

*.log_archive_dest_2='service=ORCLSB async valid_for=(online_logfiles,primary_role) db_unique_name=ORCLSB'

*.log_archive_dest_3=''

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/onlinelog','/u01/app/oracle/oradata/orclsb/onlinelog','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog','/u01/app/oracle/fast_recovery_area/orclsb/onlinelog'

*.open_cursors=300

*.pga_aggregate_target=1174M

*.processes=320

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3536M

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

[oracle@oracledb backup]$

Standby:

========

[oracle@oraclesb backup]$ vi /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initORCLSB.ora

[oracle@oraclesb backup]$ cat /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initORCLSB.ora

*.db_name=ORCL

*.db_unique_name=ORCLSB

*.audit_file_dest='/u01/app/oracle/admin/ORCLSB/adump'

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_create_online_log_dest_1='/u01/app/oracle/oradata'

*.db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area'

*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLSB/'

*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLSB/','/u01/app/oracle/fast_recovery_area/ORCL/','/u01/app/oracle/fast_recovery_area/ORCLSB/'

*.control_files='/u01/app/oracle/oradata/ORCLSB/controlfile/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLSB/controlfile/control02.ctl'

*.db_recovery_file_dest_size=8931M

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

*.archive_lag_target=900

*.audit_trail='DB'

*.compatible='19.0.0'

*.control_file_record_keep_time=7

*.db_16k_cache_size=16777216

*.db_block_size=8192

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLSBXDB)'

*.fal_client='ORCLSB'

*.fal_server='ORCL'

*.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))'

*.log_archive_config='dg_config=(ORCLSB,ORCL)'

*.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'

*.log_archive_dest_3=''

*.log_archive_dest_state_2='ENABLE'

*.open_cursors=300

*.pga_aggregate_target=1174M

*.processes=320

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3536M

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

[oracle@oraclesb backup]$

Step 6. Set the standby sync parameters at Primary and create standby redologs:

Primary:

========

ALTER SYSTEM SET db_unique_name='ORCL' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_config='dg_config=(ORCL,ORCLSB)' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ORCL' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_2='service=ORCLSB async valid_for=(online_logfiles,primary_role) db_unique_name=ORCLSB' SCOPE=both;

ALTER SYSTEM SET fal_server='ORCLSB' SCOPE=SPFILE;

ALTER SYSTEM SET fal_client='ORCL' SCOPE=SPFILE;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;

Optionanl:

ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/ORCL/datafile','/u01/app/oracle/oradata/ORCLSB/datafile' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/ORCL/onlinelog','/u01/app/oracle/oradata/ORCLSB/onlinelog','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog','/u01/app/oracle/fast_recovery_area/ORCLSB/onlinelog' SCOPE=SPFILE;

show parameter db_name

show parameter instance_name

show parameter db_unique_name

show parameter log_archive_config

show parameter log_archive_dest_1

show parameter log_archive_dest_2

show parameter fal

show parameter standby_file_management

show parameter db_file_name_convert

show parameter log_file_name_convert

Verify the logfiles and size of the logfiles:

=============================================

set pages 1000

set lines 1000

col INSTANCE for a10

col member for a75

SELECT t.INSTANCE, l.THREAD#, l.GROUP#, l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

Add Standby redologs:

=====================

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/ORCL/onlinelog/standby_redo01.log','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/standby_redo01_1.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/ORCL/onlinelog/standby_redo02.log','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/standby_redo02_2.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/ORCL/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/standby_redo03_3.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/ORCL/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/standby_redo04_4.log') SIZE 200M;

Verify the standby redologs:

============================

set pages 1000

set lines 1000

col DBID for a10

select * from v$standby_log;

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string ORCL

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string ORCL

SQL> show parameter instance_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

instance_name string ORCL

SQL> show parameter log_archive_config

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_config string dg_config=(ORCL,ORCLSB)

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1 string location=use_db_recovery_file_

dest

log_archive_dest_10 string

log_archive_dest_11 string

log_archive_dest_12 string

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

log_archive_dest_17 string

log_archive_dest_18 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_19 string

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=ORCLSB async valid_for

=(online_logfiles,primary_role

) db_unique_name=ORCLSB

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

log_archive_dest_27 string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_28 string

log_archive_dest_29 string

SQL> show parameter fal

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_client string ORCL

fal_server string ORCLSB

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

SQL> show parameter db_file_name_convert

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string /u01/app/oracle/oradata/ORCL/d

atafile, /u01/app/oracle/orada

ta/orclsb/datafile

pdb_file_name_convert string

SQL> show parameter log_file_name_convert

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_file_name_convert string /u01/app/oracle/oradata/ORCL/o

nlinelog, /u01/app/oracle/orad

ata/orclsb/onlinelog, /u01/app

/oracle/fast_recovery_area/ORC

L/onlinelog, /u01/app/oracle/f

ast_recovery_area/orclsb/onlin

elog

SQL> set pages 1000 lines 1000

SQL> set pages 1000

set lines 1000

col INSTANCE for a10

col member for a75

SELECT t.INSTANCE, l.THREAD#, l.GROUP#, l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

INSTANCE THREAD# GROUP# SEQUENCE# BYTES ARC STATUS MEMBER

---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------------------------------------------

ORCL 1 1 36269 209715200 YES ACTIVE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_k90p82vd_.log

ORCL 1 1 36269 209715200 YES ACTIVE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_k90p8306_.log

ORCL 1 2 36270 209715200 NO CURRENT /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_k90p84vy_.log

ORCL 1 2 36270 209715200 NO CURRENT /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_k90p850w_.log

ORCL 1 3 36268 209715200 YES INACTIVE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_k90p8704_.log

ORCL 1 3 36268 209715200 YES INACTIVE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_k90p8749_.log

6 rows selected.

SQL> set pages 1000

set lines 1000

col DBID for a10

select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------

4 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

5 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

6 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

7 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

SQL>

Step 7. Build standby database using Restore & Recover method from RMAN backup

Standby:

========

[oracle@oraclesb dbs]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

[oracle@oraclesb dbs]$

[oracle@oraclesb dbs]$ . oraenv

ORACLE_SID = [ORCLSB] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclesb dbs]$

[oracle@oraclesb dbs]$ env |grep ORA

ORACLE_SID=ORCLSB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclesb backup]$ cat /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initORCLSB.ora

*.db_name=ORCL

*.db_unique_name=ORCLSB

*.audit_file_dest='/u01/app/oracle/admin/ORCLSB/adump'

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_create_online_log_dest_1='/u01/app/oracle/oradata'

*.db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area'

*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLSB/'

*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLSB/','/u01/app/oracle/fast_recovery_area/ORCL/','/u01/app/oracle/fast_recovery_area/ORCLSB/'

*.control_files='/u01/app/oracle/oradata/ORCLSB/controlfile/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLSB/controlfile/control02.ctl'

*.db_recovery_file_dest_size=8931M

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

*.archive_lag_target=900

*.audit_trail='DB'

*.compatible='19.0.0'

*.control_file_record_keep_time=7

*.db_16k_cache_size=16777216

*.db_block_size=8192

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLSBXDB)'

*.fal_client='ORCLSB'

*.fal_server='ORCL'

*.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))'

*.log_archive_config='dg_config=(ORCLSB,ORCL)'

*.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'

*.log_archive_dest_3=''

*.log_archive_dest_state_2='ENABLE'

*.open_cursors=300

*.pga_aggregate_target=1174M

*.processes=320

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3536M

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

[oracle@oraclesb backup]$

[oracle@oraclesb ORCLSB]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 10 00:13:28 2023

Version 19.11.0.0.0

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initORCLSB.ora';

ORACLE instance started.

Total System Global Area 3707763112 bytes

Fixed Size 8903080 bytes

Variable Size 704643072 bytes

Database Buffers 2986344448 bytes

Redo Buffers 7872512 bytes

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

[oracle@oraclesb ORCLSB]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jun 10 00:13:52 2023

Version 19.11.0.0.0

connected to target database: ORCL (not mounted)

RMAN> restore standby controlfile from '/u01/backup/Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55';

Starting restore at 10-JUN-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=262 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/ORCLSB/controlfile/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCLSB/controlfile/control02.ctl

Finished restore at 10-JUN-23

RMAN> alter database mount standby database;

released channel: ORA_DISK_1

Statement processed

RMAN> catalog start with '/u01/backup';

Starting implicit crosscheck backup at 10-JUN-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 10-JUN-23

Starting implicit crosscheck copy at 10-JUN-23

using channel ORA_DISK_1

Crosschecked 14 objects

Finished implicit crosscheck copy at 10-JUN-23

searching for all files in the recovery area

cataloging files...

no files cataloged

searching for all files that match the pattern /u01/backup

List of Files Unknown to the Database

=====================================

File Name: /u01/backup/Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/backup/Standby_Controlback_20230610_cf_D-ORCL_id-1607315920_171uag55

RMAN> restore database;

Starting restore at 09-JUN-23

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLSB/datafile/o1_mf_system_k3jprllp_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLSB/datafile/o1_mf_sysaux_k3jps1mq_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLSB/datafile/o1_mf_undotbs1_k3jpsjp6_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLSB/datafile/o1_mf_users_k3jpsqnf_.dbf

channel ORA_DISK_1: reading from backup piece /u01/backup/Fullback_20230609_0k1ua27e_19476_1_1

channel ORA_DISK_1: piece handle=/u01/backup/Fullback_20230609_0k1ua27e_19476_1_1 tag=TAG20230609T200502

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 09-JUN-23

RMAN> exit

Recovery Manager complete.

[oracle@oraclesb dbs]$

Step 8. Enable log shipping from primary database and start mrp process on standby also verify the sync status of standby database

Primary:

========

alter system set log_archive_dest_state_2=DEFER scope=both;

alter system set log_archive_dest_state_2=enable scope=both;

show parameter log_archive_dest_state_2

archive log list

alter system switch logfile;

archive log list

SQL> alter system set log_archive_dest_state_2=DEFER scope=both;

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string ENABLE

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

log_archive_dest_state_29 string enable

SQL>

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 36268

Next log sequence to archive 36270

Current log sequence 36270

SQL> alter system switch logfile;

alter system switch logfile;

System altered.

SQL>

System altered.

SQL>

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 36271

Next log sequence to archive 36273

Current log sequence 36273

SQL>

Standby:

========

SELECT name,database_role FROM v$database;

set pages 1000 lines 1000

select DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FORCE_LOGGING,DB_UNIQUE_NAME from v$database;

show parameter fal

create spfile from pfile;

show parameter standby_file_management

set lines 1000

col member for a80

col INSTANCE for a10

SELECT t.INSTANCE, l.THREAD#, l.GROUP#, l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

shut immediate;

startup nomount;

alter database mount standby database;

--- To cancel mrp

alter database recover managed standby database cancel;

--- start mrp process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session; --- arc/mrp apply

alter database recover managed standby database using current logfile disconnect from session; --- real time apply

SELECT sequence#, first_time, next_time, applied FROM v$archived_log where APPLIED='NO';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>=36268;

[oracle@oraclesb ORCLSB]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 10 00:26:21 2023

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> SELECT name,database_role FROM v$database;

NAME DATABASE_ROLE

--------- ----------------

ORCL PHYSICAL STANDBY

SQL> show parameter fal

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_client string ORCLSB

fal_server string ORCL

SQL> create spfile from pfile;

File created.

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

SQL> set lines 1000

SQL> col member for a80

col INSTANCE for a10

SELECT t.INSTANCE, l.THREAD#, l.GROUP#, l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD#

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

INSTANCE THREAD# GROUP# SEQUENCE# BYTES ARC STATUS MEMBER

---------- ---------- ---------- ---------- ---------- --- ---------------- --------------------------------------------------------------------------------

ORCLSB 1 1 36266 209715200 YES INACTIVE /u01/app/oracle/oradata/ORCLSB/onlinelog/o1_mf_1_k90p82vd_.log

ORCLSB 1 1 36266 209715200 YES INACTIVE /u01/app/oracle/fast_recovery_area/ORCLSB/onlinelog/o1_mf_1_k90p8306_.log

ORCLSB 1 2 36267 209715200 YES ACTIVE /u01/app/oracle/oradata/ORCLSB/onlinelog/o1_mf_2_k90p84vy_.log

ORCLSB 1 2 36267 209715200 YES ACTIVE /u01/app/oracle/fast_recovery_area/ORCLSB/onlinelog/o1_mf_2_k90p850w_.log

ORCLSB 1 3 36268 209715200 NO CURRENT /u01/app/oracle/oradata/ORCLSB/onlinelog/o1_mf_3_k90p8704_.log

ORCLSB 1 3 36268 209715200 NO CURRENT /u01/app/oracle/fast_recovery_area/ORCLSB/onlinelog/o1_mf_3_k90p8749_.log

6 rows selected.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3707763112 bytes

Fixed Size 8903080 bytes

Variable Size 704643072 bytes

Database Buffers 2986344448 bytes

Redo Buffers 7872512 bytes

SQL> alter database mount standby database;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

Database altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where APPLIED='NO';

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- --------- --------- ---------

36268 10-JUN-23 10-JUN-23 NO

36269 10-JUN-23 10-JUN-23 NO

36271 10-JUN-23 10-JUN-23 NO

36270 10-JUN-23 10-JUN-23 NO

36272 10-JUN-23 10-JUN-23 NO

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>=36268;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- --------- --------- ---------

36268 10-JUN-23 10-JUN-23 YES

36269 10-JUN-23 10-JUN-23 NO

36271 10-JUN-23 10-JUN-23 NO

36270 10-JUN-23 10-JUN-23 NO

36272 10-JUN-23 10-JUN-23 NO

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

Database altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>=36268;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- --------- --------- ---------

36268 10-JUN-23 10-JUN-23 YES

36269 10-JUN-23 10-JUN-23 YES

36271 10-JUN-23 10-JUN-23 YES

36270 10-JUN-23 10-JUN-23 YES

36272 10-JUN-23 10-JUN-23 YES

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>=36268;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- --------- --------- ---------

36268 10-JUN-23 10-JUN-23 YES

36269 10-JUN-23 10-JUN-23 YES

36271 10-JUN-23 10-JUN-23 YES

36270 10-JUN-23 10-JUN-23 YES

36272 10-JUN-23 10-JUN-23 YES

36273 10-JUN-23 10-JUN-23 IN-MEMORY

6 rows selected.

SQL>

Option 2: Build standby database using RMAN duplicate method from primary database RMAN backup

==============================================================================================

rman auxiliary /

duplicate target database for standby backup location '/u01/backup' nofilenamecheck;

Step 1 - Verify the primary and standby environment details

Primary:

========

[oracle@oracledb archivelog]$ ps -ef|grep smon

oracle 22431 1 0 23:09 ? 00:00:00 ora_smon_ORCL

oracle 24508 19837 0 23:14 pts/1 00:00:00 grep --color=auto smon

[oracle@oracledb archivelog]$

[oracle@oracledb archivelog]$ hostname -f

oracledb.localdomain.com

[oracle@oracledb archivelog]$

Standby:

========

[oracle@oraclesb onlinelog]$ ps -ef|grep smon

oracle 7608 6665 0 23:16 pts/1 00:00:00 grep --color=auto smon

[oracle@oraclesb onlinelog]$

[oracle@oraclesb onlinelog]$ hostname -f

oraclesb.localdomain.com

[oracle@oraclesb onlinelog]$

Primary:

========

[root@oracledb ~]# su - oracle

Last login: Fri Jun 9 23:51:40 IST 2023 on pts/1

[oracle@oracledb ~]$ . oraenv

ORACLE_SID = [oracle] ? ORCL

The Oracle base has been set to /u01/app/oracle

[oracle@oracledb ~]$

[oracle@oracledb admin]$ env |grep ORA

ORACLE_SID=ORCL

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oracledb admin]$

[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 23:54:08 2023

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> set pages 1000 lines 1000

SQL> select DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FORCE_LOGGING,DB_UNIQUE_NAME from v$database;

DBID NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING DB_UNIQUE_NAME

---------- --------- ------------ -------------------- -------------------- ---------------- --------------------------------------- ------------------------------

1607315920 ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY YES ORCL

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 36266

Next log sequence to archive 36268

Current log sequence 36268

SQL> select FORCE_LOGGING,log_mode from v$database;

FORCE_LOGGING LOG_MODE

--------------------------------------- ------------

YES ARCHIVELOG

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

[oracle@oracledb ~]$

Step 2. Verify the Primary and Standby listener.ora and tnsnames.ora files

Primary:

========

cd $ORACLE_HOME/network/admin

cat listener.ora

cat tnsnames.ora

lsnrctl status LISTENER_ORCL

lsnrctl start LISTENER_ORCL

Standby:

========

. oraenv

ORCLSB

/u01/app/oracle/product/19.0.0.0/dbhome_1

cd $ORACLE_HOME/network/admin

cat listener.ora

cat tnsnames.ora

lsnrctl status LISTENER_ORCLSB

lsnrctl start LISTENER_ORCLSB

Primary:

========

[oracle@oracledb ~]$ cd $ORACLE_HOME/network/admin

[oracle@oracledb admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oracledb admin]$

[oracle@oracledb admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

)

ADR_BASE_LISTENER_ORCL = /u01/app/oracle

[oracle@oracledb admin]$

[oracle@oracledb admin]$ cat tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

ORCLSB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCLSB)

)

[oracle@oracledb admin]$

[oracle@oracledb admin]$ lsnrctl status LISTENER_ORCL

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 23:55:33

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.localdomain.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER_ORCL

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 09-JUN-2023 20:52:34

Uptime 0 days 3 hr. 2 min. 59 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oracledb/listener_orcl/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledb.localdomain.com)(PORT=1522)))

Services Summary...

Service "ORCL" has 1 instance(s).

Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCLXDB" has 1 instance(s).

Instance "ORCL", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@oracledb admin]$

Standby:

========

[root@oraclesb ~]# su - oracle

Last login: Fri Jun 9 23:51:13 IST 2023 on pts/2

[oracle@oraclesb ~]$ . oraenv

ORACLE_SID = [oracle] ? ORCLSB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclesb ~]$

[oracle@oraclesb ~]$ env |grep ORA

ORACLE_SID=ORCLSB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclesb ~]$

[oracle@oraclesb ~]$ cd $ORACLE_HOME/network/admin

[oracle@oraclesb admin]$ pwd

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_ORCLSB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

)

ADR_BASE_LISTENER_ORCLSB = /u01/app/oracle

SID_LIST_LISTENER_ORCLSB =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORCLSB)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)

(SID_NAME = ORCLSB)

)

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCLSB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLSB)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

[oracle@oraclesb admin]$

[oracle@oraclesb admin]$ lsnrctl status LISTENER_ORCLSB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 23:58:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclesb.localdomain.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER_ORCLSB

Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date 09-JUN-2023 20:56:43

Uptime 0 days 3 hr. 1 min. 51 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oraclesb/listener_orclsb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclesb.localdomain.com)(PORT=1522)))

Services Summary...

Service "ORCLSB" has 1 instance(s).

Instance "ORCLSB", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@oraclesb admin]$

Primary:

========

tnsping ORCL

tnsping ORCLSB

Standby:

========

tnsping ORCL

tnsping ORCLSB

Primary:

========

[oracle@oracledb backup]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:06:46

Used parameter files:

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (0 msec)

[oracle@oracledb backup]$ tnsping ORCLSB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:06:49

Used parameter files:

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ORCLSB)))

OK (0 msec)

[oracle@oracledb backup]$

Standby:

========

[oracle@oraclesb dbs]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:07:05

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.localdomain.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

OK (10 msec)

[oracle@oraclesb dbs]$ tnsping ORCLSB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2023 00:07:09

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclesb.localdomain.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLSB)))

OK (0 msec)

[oracle@oraclesb dbs]$

Steps 3. Copy Primary database password file to standby server