ORACLE DATABASE PROBLEM AND SOLUTIONS: April 2021

Friday, April 30, 2021

How to Configure TDE in Oracle 19c

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

In this Article i will discuss how we can configure TDE in oracle 19c standalone database running on Linux

STEP 1: Create pfile from spfile in below location.

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

SQL> show parameter spfile;

NAME TYPE VALUE

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

spfile string /backup/19.3.0/dbhome_1/dbs/spfileorcl.ora

SQL>

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

File created.

SQL>

STEP 2: Configure the Keystore Location and Type

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

We are going to configure Keystore location and type by setting WALLET_ROOT and TDE_CONFIGURATION parameters in pfile or spfile.

* from 18c onwards we have to configure WALLET_ROOT,TDE_CONFIGURATION parameter and need bounce the database, SQLNET.ORA no longer needed

If necessary, create a wallet directory. Typically, wallet directory is located in $ORACLE_BASE/admin/db_unique_name/wallet. Ideally wallet directory should be empty.

mkdir -p /backup/app/oracle/admin/orcl/wallet

pre-output

SQL> show parameter WALLET_ROOT

NAME TYPE VALUE

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

wallet_root string

SQL>

SQL> show parameter TDE_CONFIGURATION

NAME TYPE VALUE

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

tde_configuration string

SQL>

alter system set WALLET_ROOT="/backup/app/oracle/admin/orcl/wallet" scope=spfile;

bounce the database otherwise you will get below error

{

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-46693: The WALLET_ROOT location is missing or invalid.

SQL>

}

STEP 3: Restart the Database

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

[oracle@localhost orcl]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 28 21:47:14 2021

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes

Variable Size 822083584 bytes

Database Buffers 369098752 bytes

Redo Buffers 7880704 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter WALLET_ROOT

NAME TYPE VALUE

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

wallet_root string /backup/app/oracle/admin/orcl/wallet

SQL>

SQL> !

[oracle@localhost orcl]$ cd /backup/app/oracle/admin/orcl/wallet

[oracle@localhost wallet]$ ls -lrt

total 0

[oracle@localhost wallet]$

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;

System altered.

SQL>

SQL> show parameter TDE_CONFIGURATION

NAME TYPE VALUE

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

tde_configuration string KEYSTORE_CONFIGURATION=FILE

SQL>

SQL> show parameter wallet_root

NAME TYPE VALUE

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

wallet_root string /backup/app/oracle/admin/orcl/wallet

SQL> show parameter tde_configuration

NAME TYPE VALUE

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

tde_configuration string KEYSTORE_CONFIGURATION=FILE

SQL>

Step 4: Create software Keystore

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

administer key management create keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;

SQL>

SQL> administer key management create keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;

keystore altered.

SQL>

SQL> !

[oracle@localhost wallet]$ ls -lrt

total 4

-rw------- 1 oracle oinstall 2555 Apr 28 21:57 ewallet.p12

[oracle@localhost wallet]$

STEP 5: Check the status of the wallet

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

set lines 200

column WRL_PARAMETER format a40

select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;

administer key management create auto_login keystore from keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;

SQL>

SQL> administer key management create auto_login keystore from keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;

keystore altered.

SQL>

SQL> administer key management create auto_login keystore from keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID

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

FILE /backup/app/oracle/admin/orcl/wallet/tde OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE NONE UNDEFINED 0

SQL>

STEP 6: Open the software Keystore

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

administer key management set keystore open force keystore identified by welcome123;

SQL>

SQL> administer key management set keystore open force keystore identified by welcome123;

keystore altered.

STEP 7: Set the Keystore TDE Encryption Master Key

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

administer key management set key FORCE KEYSTORE identified by welcome123 with backup;

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS CON_ID

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

FILE /backup/app/oracle/admin/orcl/wallet/tde OPEN_NO_MASTER_KEY 0

SQL> administer key management set key FORCE KEYSTORE identified by welcome123 with backup;

keystore altered.

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS CON_ID

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

FILE /backup/app/oracle/admin/orcl/wallet/tde OPEN 0

SQL>

[oracle@localhost tde]$ ls -lrt

total 12

-rw------- 1 oracle oinstall 2555 Apr 28 22:38 ewallet_2021042814383622.p12

-rw------- 1 oracle oinstall 3995 Apr 28 22:38 ewallet.p12

-rw------- 1 oracle oinstall 4040 Apr 28 22:38 cwallet.sso

[oracle@localhost tde]$

[oracle@localhost tde]$ pwd

/backup/app/oracle/admin/orcl/wallet/tde

[oracle@localhost tde]$

SQL>

SQL> alter tablespace users encryption online encrypt;

Tablespace altered.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC CON_ID

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

1 SYSAUX YES NO YES 0

0 SYSTEM YES NO YES 0

2 UNDOTBS1 YES NO YES 0

4 USERS YES NO YES 0

3 TEMP NO NO YES 0

6 TEST YES NO YES 0

6 rows selected.

SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;

TABLESPACE_NAME STATUS ENC

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

SYSTEM ONLINE NO

SYSAUX ONLINE NO

UNDOTBS1 ONLINE NO

TEMP ONLINE NO

USERS ONLINE YES

TEST ONLINE NO

6 rows selected.

SQL>

SQL> select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;

NAME TS# ENCRYPT ENC KEY_VERSION STATUS

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

USERS 4 AES128 YES 1 NORMAL

SQL>

ANURAG PANDEY

I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert . Database Administration and Implementation. Provide technical guidance and work with global team. Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible

Thursday, April 29, 2021

Oracle 12c -Step by Step configuration Oracle Dataguard Using RMAN (Active Duplicate)

12c - Step by Step configuration Oracle Dataguard Using RMAN (active database)

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

Source DB Name : india

Source IP : 10.0.1.12

Targte DB Name : india_stby

Targte IP : 10.0.1.37

Ensure the primary database is in Archivelog mode and Force logging enabled.

Enable Archivelog

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

[oracle@localhost ~]$ !sq

sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 29 12:13:52 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL> select name ,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE

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

INDIA READ WRITE NOARCHIVELOG

SQL>

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 998244352 bytes

Fixed Size 8628064 bytes

Variable Size 356518048 bytes

Database Buffers 629145600 bytes

Redo Buffers 3952640 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name ,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE

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

INDIA READ WRITE ARCHIVELOG

SQL>

Enable Force Logging

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

SQL> select force_logging from v$database;

FORCE_LOGGING

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

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

SQL>

Create standby redo logs (redolog +1)

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

check existing redolog size and location details

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

SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,

MEMBERS,STATUS from v$log; 2

GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS

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

1 1 1 200 1 CURRENT

2 1 0 200 1 UNUSED

3 1 0 200 1 UNUSED

SQL>

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

3 ONLINE /u01/app/oracle/oradata/india/redo03.log NO 0

2 ONLINE /u01/app/oracle/oradata/india/redo02.log NO 0

1 ONLINE /u01/app/oracle/oradata/india/redo01.log NO 0

SQL>

alter database add standby logfile group 4 '/u01/app/oracle/oradata/india/standby_redo04.log' size 200M;

alter database add standby logfile group 5 '/u01/app/oracle/oradata/india/standby_redo05.log' size 200M;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/india/standby_redo06.log' size 200M;

alter database add standby logfile group 7 '/u01/app/oracle/oradata/india/standby_redo07.log' size 200M;

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

GROUP# THREAD# SEQUENCE# ARC STATUS

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

4 0 0 YES UNASSIGNED

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

7 0 0 YES UNASSIGNED

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

3 ONLINE /u01/app/oracle/oradata/india/redo03.log NO 0

2 ONLINE /u01/app/oracle/oradata/india/redo02.log NO 0

1 ONLINE /u01/app/oracle/oradata/india/redo01.log NO 0

4 STANDBY /u01/app/oracle/oradata/india/standby_redo04.log NO 0

5 STANDBY /u01/app/oracle/oradata/india/standby_redo05.log NO 0

6 STANDBY /u01/app/oracle/oradata/india/standby_redo06.log NO 0

7 STANDBY /u01/app/oracle/oradata/india/standby_redo07.log NO 0

7 rows selected.

SQL>

Adding the network entry in primary and standby side(Both servers)

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

tnsnames entry:

INDIA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.12)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = india)

)

INDIA_STBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.37)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = india_stby)

)

Source listener.ora

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

# listener.ora Network Configuration File: /backup/19.3.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.12)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

Target Listner.ora

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

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.37)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

Target listener.ora (create static listner )

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

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

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = india_stby)

(SID_NAME = india_stby)

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

)

Set dg parameter in source database

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

ALTER SYSTEM SET log_archive_config='dg_config=(india,india_stby)' SCOPE=both;

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

ALTER SYSTEM SET fal_server='india_stby' SCOPE=both;

ALTER SYSTEM SET fal_client='india' SCOPE=both;

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

ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby' SCOPE=SPFILE;

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=BOTH;

SQL>

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(india,india_stby)' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2='service=india_stby async valid_for=(online_logfiles,primary_role) db_unique_name=india_stby' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET fal_server='india_stby' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET fal_client='india' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby' SCOPE=SPFILE;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=BOTH;

System altered.

SQL>

create directory on target

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

mkdir -p /u01/app/oracle/oradata/india_stby

mkdir -p /u01/app/oracle/admin/india_stby/adump

connect from targte (standby database)

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

rman target sys/sys@india

connect auxiliary sys/sys@india_stby

[oracle@admin]$

[oracle@admin]$ rman target sys/sys@india

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 29 16:22:14 2021

connected to target database: INDIA (DBID=3248178439)

RMAN> connect auxiliary sys/sys@india_stby

connected to auxiliary database: INDIA (not mounted)

RMAN>

[oracle@localhost ~]$ rman target sys/sys@india

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 29 13:49:04 2021

connected to target database: INDIA (DBID=3248181382)

RMAN>

RMAN> connect auxiliary sys/sys@india_stby

connected to auxiliary database: INDIA (not mounted)

RMAN>

run

{

allocate channel p1 type disk;

allocate channel p2 type disk;

allocate channel p3 type disk;

allocate channel p4 type disk;

allocate auxiliary channel s1 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'india','india_stby'

set db_name='india'

set db_unique_name='india_stby'

set db_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby'

set log_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby'

set log_archive_max_processes='5'

set fal_server='india'

set fal_client='india_stby'

set standby_file_management='AUTO';

}

RMAN>

RMAN> run

{

allocate2> 3> channel p1 type disk;

4> allocate channel p2 type disk;

a5> llocate channel p3 type disk;

6> allocate channel p4 type disk;

alloca7> te auxiliary channel s1 type disk;

8> duplicate target database for standby from active database

spfile9>

para10> meter_value_convert 'india','india_stby'

11> set db_name='india'

s12> et db_unique_name='india_stby'

13> set db_file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby'

set log_14> file_name_convert='/u01/app/oracle/oradata/india','/u01/app/oracle/oradata/india_stby'

15> set log_archive_max_processes='5'

s16> et fal_server='india'

se17> t fal_client='india_stby'

s18> et standby_file_management='AUTO';

}19>

using target database control file instead of recovery catalog

allocated channel: p1

channel p1: SID=40 device type=DISK

allocated channel: p2

channel p2: SID=272 device type=DISK

allocated channel: p3

channel p3: SID=41 device type=DISK

allocated channel: p4

channel p4: SID=273 device type=DISK

allocated channel: s1

channel s1: SID=179 device type=DISK

Starting Duplicate Db at 29-APR-21

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwindia' auxiliary format

'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwindia_stby' targetfile

'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileindia.ora' auxiliary format

'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileindia_stby.ora' ;

sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileindia_stby.ora''";

}

executing Memory Script

Starting backup at 29-APR-21

Finished backup at 29-APR-21

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileindia_stby.ora''

contents of Memory Script:

{

sql clone "alter system set audit_file_dest =

''/u01/app/oracle/admin/india_stby/adump'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/india_stby/control01.ctl'', ''/u01/app/oracle/oradata/india_stby/control02.ctl'' comment=

'''' scope=spfile";

sql clone "alter system set dispatchers =

''(PROTOCOL=TCP) (SERVICE=india_stbyXDB)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_2 =

''service=india_stby_stby async valid_for=(online_logfiles,primary_role) db_unique_name=india_stby'' comment=

'''' scope=spfile";

sql clone "alter system set db_name =

''india'' comment=

'''' scope=spfile";

sql clone "alter system set db_unique_name =

''india_stby'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''/u01/app/oracle/oradata/india'', ''/u01/app/oracle/oradata/india_stby'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/u01/app/oracle/oradata/india'', ''/u01/app/oracle/oradata/india_stby'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_max_processes =

5 comment=

'''' scope=spfile";

sql clone "alter system set fal_server =

''india'' comment=

'''' scope=spfile";

sql clone "alter system set fal_client =

''india_stby'' comment=

'''' scope=spfile";

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/india_stby/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/india_stby/control01.ctl'', ''/u01/app/oracle/oradata/india_stby/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=india_stbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=india_stby_stby async valid_for=(online_logfiles,primary_role) db_unique_name=india_stby'' comment= '''' scope=spfile

sql statement: alter system set db_name = ''india'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''india_stby'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/india'', ''/u01/app/oracle/oradata/india_stby'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/india'', ''/u01/app/oracle/oradata/india_stby'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

sql statement: alter system set fal_server = ''india'' comment= '''' scope=spfile

sql statement: alter system set fal_client = ''india_stby'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1048576000 bytes

Fixed Size 8628640 bytes

Variable Size 369100384 bytes

Database Buffers 662700032 bytes

Redo Buffers 8146944 bytes

allocated channel: s1

channel s1: SID=237 device type=DISK

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/india_stby/control01.ctl';

restore clone primary controlfile to '/u01/app/oracle/oradata/india_stby/control02.ctl' from

'/u01/app/oracle/oradata/india_stby/control01.ctl';

}

executing Memory Script

Starting backup at 29-APR-21

channel p1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_india.f tag=TAG20210429T162341

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

Finished backup at 29-APR-21

Starting restore at 29-APR-21

channel s1: copied control file copy

Finished restore at 29-APR-21

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/india_stby/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/india_stby/system01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/india_stby/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/india_stby/undotbs01.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/india_stby/users01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/india_stby/system01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/india_stby/sysaux01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/india_stby/undotbs01.dbf" datafile

7 auxiliary format

"/u01/app/oracle/oradata/india_stby/users01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/india_stby/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 29-APR-21

channel p1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/india/system01.dbf

channel p2: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/india/sysaux01.dbf

channel p3: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/india/undotbs01.dbf

channel p4: starting datafile copy

input datafile file number=00007 name=/u01/app/oracle/oradata/india/users01.dbf

output file name=/u01/app/oracle/oradata/india_stby/users01.dbf tag=TAG20210429T162348

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

output file name=/u01/app/oracle/oradata/india_stby/undotbs01.dbf tag=TAG20210429T162348

channel p3: datafile copy complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/oradata/india_stby/system01.dbf tag=TAG20210429T162348

channel p1: datafile copy complete, elapsed time: 00:00:25

output file name=/u01/app/oracle/oradata/india_stby/sysaux01.dbf tag=TAG20210429T162348

channel p2: datafile copy complete, elapsed time: 00:00:25

Finished backup at 29-APR-21

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1071159854 file name=/u01/app/oracle/oradata/india_stby/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1071159854 file name=/u01/app/oracle/oradata/india_stby/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1071159854 file name=/u01/app/oracle/oradata/india_stby/undotbs01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1071159854 file name=/u01/app/oracle/oradata/india_stby/users01.dbf

Finished Duplicate Db at 29-APR-21

released channel: p1

released channel: p2

released channel: p3

released channel: p4

released channel: s1

RMAN>

check RFS process should be running

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

SQL> select * from v$managed_standby;

PROCESS PID STATUS CLIENT_P CLIENT_PID CLIENT_DBID GROUP#

RESETLOG_ID

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

THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS CON_ID

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

ARCH 1897 CONNECTED ARCH 1897 3248178439 N/A

0 0 0 0 0 0 0 0

DGRD 1899 ALLOCATED N/A N/A N/A N/A

0 0 0 0 0 0 0 0

DGRD 1901 ALLOCATED N/A N/A N/A N/A

0 0 0 0 0 0 0 0

ARCH 1903 CONNECTED ARCH 1903 3248178439 N/A

0 0 0 0 0 0 0 0

ARCH 1905 CONNECTED ARCH 1905 3248178439 N/A

0 0 0 0 0 0 0 0

ARCH 1907 CLOSING ARCH 1907 3248178439 5

1071141577

1 5 1 409 0 0 0 0

ARCH 1909 CLOSING ARCH 1909 3248178439 4

1071141577

1 6 2048 152 0 0 0 0

RFS 2093 IDLE LGWR 6204 3248178439 1

1071141577

1 7 1 1 0 0 0 0

RFS 2095 IDLE UNKNOWN 6197 3248178439 N/A

0 0 0 0 0 0 0 0

RFS 2099 IDLE Archival 6191 3248178439 N/A

0 0 0 0 0 0 0 0

RFS 2101 IDLE UNKNOWN 6199 3248178439 N/A

0 0 0 0 0 0 0 0

RFS 2103 IDLE UNKNOWN 6201 3248178439 N/A

0 0 0 0 0 0 0 0

12 rows selected.

SQL>

Enable MRP process at targte db side

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

alter database recover managed standby database using current logfile disconnect from session;

to check log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "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;

SQL>

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "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;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 7 1 6

SQL>

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

Thread Last Sequence Received Last Sequence Applied Difference

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

1 7 7 0

SQL>

select dest_id id,database_mode db_mode,recovery_mode,

protection_mode,standby_logfile_count "SRLs",

standby_logfile_active ACTIVE,

archived_seq#

from v$archive_dest_status

where dest_id =2;

SQL> select dest_id id,database_mode db_mode,recovery_mode,

protection_mode,standby_logfile_count "SRLs",

standby_logfile_active ACTIVE,

archived_seq#

from v$archive_dest_status

where dest_id =2; 2 3 4 5 6

ID DB_MODE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#

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

2 MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 4 1 7

SQL>

ANURAG PANDEY