Environment:

Oracle Version: 19.3.0
OS Version: Oracle Linux Server 8

Primary Server

DB Unique Name=pearl
DB Name=pearl
Hostname=192.168.31.201

Standby Server

DB Unique Name=muthu
DB Name=muthu
Hostname=192.168.31.202

Add listener Entery primary Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pearl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )

)

LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

Add listener Entery Standby Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = muthu)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = muthu)
    )

)

LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.202)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

Enable TNS Entry For Both Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PEARL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pearl)
    )
)

MUTHU =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.202)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = muthu)
    )
)

Note: add the pluggable database entry also

Check TND ping for Both Side:
tnsping pearl 4
tnsping muthu 4

Step 1: (prim)Enable archivelog mode and Force logging

select log_mode from v$database;

(OR)

archive log list
set linesize 250
select name,force_logging,flashback_on,log_mode from v$database;

How to enable Archiver and set the location:
shut immediate
startup mount
alter database archivelog;

alter database force logging;
alter database open;

(prim)Enable the FRA
alter system set db_recovery_file_dest_size=15G scope=both;
!mkdir -p /u02/oradata/fra/
alter system set db_recovery_file_dest='/u02/oradata/fra/' scope=both;

Flashback should be enabled in primary database:

ALTER DATABASE FLASHBACK ON;

select name,open_mode,force_logging,flashback_on,log_mode from v$database;

NAME OPEN_MODE FORCE_LOGGING FLASHBACK_ON LOG_MODE
--------- -------------------- --------------------------------------- ------------------ ------------
PEARL READ WRITE YES YES ARCHIVELOG

Step 2: (prim)Add the Standby Redo Log files

set linesize 250
col MEMBER format a80
select group#,member,type,status from v$logfile;

alter database add standby logfile group 4 '/u02/oradata/PEARL/redo04.log' size 150m;
alter database add standby logfile group 5 '/u02/oradata/PEARL/redo05.log' size 150m;
alter database add standby logfile group 6 '/u02/oradata/PEARL/redo06.log' size 150m;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step 3: Configure the dataguard parameters primary side

ALTER SYSTEM SET log_archive_config='dg_config=(pearl,muthu)' SCOPE=both;
ALTER SYSTEM SET fal_server='muthu' SCOPE=both;
ALTER SYSTEM SET fal_client='pearl' SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;

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

ALTER SYSTEM SET log_archive_dest_2='service=muthu async valid_for=(online_logfiles,primary_role) db_unique_name=muthu' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;

alter system set log_archive_max_processes=30 scope=both;
alter system set remote_login_passwordfile=exclusive scope=spfile;

##Optional
alter system set db_file_name_convert='/u02/oradata/PEARL','/u02/oradata/MUTHU/' scope=spfile;
alter system set log_file_name_convert='/u02/oradata/PEARL','/u02/oradata/MUTHU/' scope=spfile;

Step 4: restart the Primary database and verify the parameters:

shut immediate
startup

Verify the parameter Values:
col name for a30
col value for a85
set lin 300 pagesize 300

select name, value from v$parameter
where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1′,’log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes',
'fal_server','fal_client','db_file_name_convert','log_file_name_convert','standby_file_management');

Step 5:(prim) create password file and send to standby

cd $ORACLE_HOME/dbs
orapwd file=orapwpearl password=admin123 entries=20 format=12
scp orapwpearl oracle@192.168.31.202:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmuthu

Step 6:(stand) Create the required directory on the standby side

check primary server data and log file locations:

col name format a80
select name from v$datafile;
show parameter audit;
show parameter db_recovery_file_dest;

create the directory like:

mkdir -p /u02/oradata/MUTHU/
mkdir -p /u02/oradata/MUTHU/pdbseed/
mkdir -p /u02/oradata/MUTHU/pdb1/
mkdir -p /u02/oradata/MUTHU/pdb2/
mkdir -p /u01/app/oracle/admin/muthu/adump
mkdir -p /u02/oradata/fra/

Step 7: create pfile for standby

[oracle@localhost dbs]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/dbs/initmuthu.ora
db_name=pearl
enable_pluggable_database=true

Step 8: start the standby database use pfile with nomount stage

export ORACLE_SID=muthu

sqlplus / as sysdba

startup pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initmuthu.ora' nomount;

Step 9: connect RMAN from standby (both password need to same)

rman target sys/admin123@pearl auxiliary sys/admin123@muthu

Step 10: Build the standby use rman

RMAN> duplicate target database for standby from active database nofilenamecheck;

(OR)

duplicate target database for standby from active database;

(OR)

Recommended:

RMAN>
run
{
allocate channel c1 type disk;
allocate auxiliary channel c3 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'pearl','muthu'
set db_name='pearl'
set db_unique_name='muthu'
set audit_file_dest='/u01/app/oracle/admin/muthu/adump'
set diagnostic_dest='/u01/app/oracle/admin/muthu/adump'
set db_file_name_convert='/u02/oradata/PEARL/','/u02/oradata/MUTHU/'
set log_file_name_convert='/u02/oradata/PEARL/','/u02/oradata/MUTHU/'
set control_files='/u02/oradata/MUTHU/control01.ctl'
set log_archive_max_processes='5'
set fal_client='muthu'
set fal_server='pearl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(pearl,muthu)'
set compatible='19.0.0.0.0'
nofilenamecheck;
}

Step 11: start the MRP process in standby.

select name,database_role,open_mode from v$database;

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

alter database open;

alter database recover managed standby database disconnect from session;

Step 12: check MRP process

select sequence#,process,status from v$managed_standby;

Step 13: verify the standby status

select name,open_mode,database_role from v$database;

select name, value from v$dataguard_stats where name in ('transport lag', 'apply lag');

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PEARL READ ONLY WITH APPLY PHYSICAL STANDBY

Step 14: log applying status in standby

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Step 15: Check the differences redo log sequences(Both Sides)

SELECT ARCH.THREAD# AS Thread, ARCH.SEQUENCE# AS "Last Sequence Received", APPL.SEQUENCE# AS "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS Difference FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#, SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Enable flashback on standby database:

alter database recover managed standby database cancel;

alter database flashback on;

alter database recover managed standby database disconnect from session;

select sequence#,process,status from v$managed_standby;

select role, thread#, sequence#, action from v$dataguard_process;

select name,force_logging,flashback_on,log_mode from v$database;

NAME FORCE_LOGGING FLASHBACK_ON LOG_MODE
--------- --------------------------------------- ------------------ ------------
PEARL YES YES ARCHIVELOG

select status,instance_name,database_role,protection_mode from v$database,v$instance;

select max(sequence#) from v$archived_log;

Configure Archive deletion policy:

We must set this policy in order to prevent accidental deletion of archive logs on primary database

On Primary:

rman target /
configure archivelog deletion policy to applied on all standby;

create some tables and insert the data primary side check standby

Primary Database:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> create user shri identified by shri123;

User created.

SQL> grant connect,create session,resource,unlimited tablespace to shri;

Grant succeeded.

SQL> create table shri.emp(id number(3),name varchar2(50));

Table created.

SQL> insert into shri.emp values(1,'muthu');

1 row created.

SQL> insert into shri.emp values(2,'siva');

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter system switch logfile;

System altered.

Standby Database:

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> select * from shri.emp;

        ID NAME
---------- --------------------------------------------------
         1 muthu
         2 siva

Search This Blog

DBA Master

Dataguard Setup in Oracle 19C Container Database