ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

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

DGMGRL>

DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;

Configuration "india" created with primary database "india"

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

DGMGRL>

Steps to resolve issue in 12c

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

DGMGRL> disable configuration;

DGMGRL> remove configuration;

Disable log_archive_dest_2 on both Primary/standby

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

alter system set log_archive_dest_2='' scope=both;

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service=india_stby async valid

                                                 _for=(online_logfiles,primary_

                                                 role) db_unique_name=india_stb

                                                 y

SQL> alter system set log_archive_dest_2='' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

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

log_archive_dest_28                  string

log_archive_dest_29                  string

SQL>

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service=india_stby_stby async

                                                 valid_for=(online_logfiles,pri

                                                 mary_role) db_unique_name=indi

                                                 a_stby

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

NAME                                 TYPE        VALUE

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

log_archive_dest_27                  string

log_archive_dest_28                  string

log_archive_dest_29                  string

SQL>

SQL> alter system set log_archive_dest_2='' scope=both;

System altered.

SQL>  show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

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

log_archive_dest_28                  string

log_archive_dest_29                  string

SQL>

Disable/Enable Data Guard broker on both Primary/standby

SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL>

SQL>

[oracle@ip-10-0-1-12 ~]$ dgmgrl sys/sys@india

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat May 8 12:45:37 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL>  create configuration 'india' as primary database is 'india' connect identifier is india;

Configuration "india" created with primary database "india"

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL>

DGMGRL>  create configuration 'india' as primary database is 'india' connect identifier is india;

Configuration "india" created with primary database "india"

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;

Database "india_stby" added

DGMGRL>

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

Enbale LOG_ARCHIVE_DEST_2 and check log shipping is working fine 

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

Primary

*************

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

Standby

************

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

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 51 seconds ago)

DGMGRL>

DGMGRL> show database india InconsistentProperties

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>

DGMGRL>

DGMGRL> show database india_stby InconsistentProperties

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>

DGMGRL>

DGMGRL> show database india

Database - india

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    india

Database Status:

SUCCESS

DGMGRL> show database india_stby

Database - india_stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 1.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    india_stby

Database Status:

SUCCESS

DGMGRL>

DGMGRL> show database verbose india

Database - india

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    india

  Properties:

    DGConnectIdentifier             = 'india'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '30'

    TransportLagThreshold           = '30'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    ApplyInstances                  = '0'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DataGuardSyncLatency            = '0'

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

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

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    PreferredObserverHosts          = ''

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_DGMGRL)(INSTANCE_NAME=india)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

  Log file locations:

    Alert log               : /u01/app/oracle/diag/rdbms/india/india/trace/alert_india.log

    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/india/india/trace/drcindia.log

Database Status:

SUCCESS

DGMGRL>

DGMGRL> show database verbose india_stby

Database - india_stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 1.00 KByte/s

  Active Apply Rate:  64.00 KByte/s

  Maximum Apply Rate: 64.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    india_stby

  Properties:

    DGConnectIdentifier             = 'india_stby'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    RedoRoutes                      = ''

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyLagThreshold               = '30'

    TransportLagThreshold           = '30'

    TransportDisconnectedThreshold  = '30'

    ApplyParallel                   = 'AUTO'

    ApplyInstances                  = '0'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '5'

    LogArchiveMinSucceedDest        = '1'

    DataGuardSyncLatency            = '0'

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

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

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    PreferredObserverHosts          = ''

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_stby_DGMGRL)(INSTANCE_NAME=india_stby)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

  Log file locations:

    Alert log               : /u01/app/oracle/diag/rdbms/india_stby/india_stby/trace/alert_india_stby.log

    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/india_stby/india_stby/trace/drcindia_stby.log

Database Status:

SUCCESS

DGMGRL>

Convert Physical Standby To Snapshot Standby Database

 

Convert Physical Standby To Snapshot Standby Database 

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

High Level Steps to converting physical standby database to snapshot standby database  

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

• No gap 
• enable flashback standby side
• shutdown and start in mount standby database 
• convert to snapshot standby

Steps to  converting back a snapshot standby database to physical standby database

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

• Shut down the snapshot standby database and open it in Mount mode.
• Convert the snapshot standby database to physical standby database
• Bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

 

Primary database Details

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

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

Oldest online log sequence     17

Next log sequence to archive   19

Current log sequence           19

SQL>

SQL>

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         india            PRIMARY          READ WRITE

SQL>

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             18

SQL>

Standby database Details:

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

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     PHYSICAL STANDBY READ ONLY WITH APPLY

check gap status ( make sure no gap )

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                     18                    18          0

SQL>

SQL>

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             18

SQL>

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL>

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

SQL>

SQL>

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled.

SQL> alter system set db_recovery_file_dest_size=2;

System altered.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 2

SQL>

SQL> select name from V$RECOVERY_FILE_DEST;

NAME

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

SQL> show parameter reco

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 2

db_unrecoverable_scn_tracking        boolean     TRUE

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

SQL>

SQL>

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;

System altered.

SQL> select name from V$RECOVERY_FILE_DEST;

NAME

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

/u01/app/oracle/fra

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL>

Cancel recovery process

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

SQL>

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     PHYSICAL STANDBY READ ONLY

SQL>

shutdown standby database and startup mount 

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

SQL>

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup mount

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

Database mounted.

SQL>

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> alter database flashback on ;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38708: not enough space for first flashback database log file

SQL>

* we have to increase recovery size 

SQL> alter system set db_recovery_file_dest_size=3g;

System altered.

SQL> alter database flashback on ;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

Now convert it to snapshot standby

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

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database convert to snapshot standby ;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     SNAPSHOT STANDBY READ WRITE

SQL>

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

NAME                                                                                                                             GUA

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

SNAPSHOT_STANDBY_REQUIRED_05/08/2021 11:17:56                                                                                    YES

SQL>

for Testing create table in this snapshot standby

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

SQL> create table hr.test_snap (id number(5),name varchar2(50));

Table created.

SQL>

SQL> insert into hr.test_snap values (1,'ANURAG');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.test_snap;

        ID NAME

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

         1 ANURAG

SQL>

SQL>

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>

SQL>

SQL> alter system switch logfile;

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     1

Next log sequence to archive   3

Current log sequence           3

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     SNAPSHOT STANDBY READ WRITE

on primary 

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

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

Oldest online log sequence     18

Next log sequence to archive   20

Current log sequence           20

SQL>

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>  select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             21

SQL>

SQL>  select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         india            PRIMARY          READ WRITE

SQL>

Convert it again to physical standby again.

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

Shut down the snapshot standby database and open it in Mount mode.

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

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

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

Database mounted.

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     SNAPSHOT STANDBY MOUNTED

SQL>

Convert the snapshot standby database to physical standby database.

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

SQL>

SQL>

SQL> alter database convert to physical standby;

Database altered.

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     PHYSICAL STANDBY MOUNTED

Bounce the standby database 

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

SQL> alter database open;

Database altered.

SQL> select name,db_unique_name ,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

INDIA     india_stby                     PHYSICAL STANDBY READ ONLY

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

Database altered.

SQL>

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

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

DGRD      ALLOCATED             0

DGRD      ALLOCATED             0

ARCH      CONNECTED             0

ARCH      CLOSING              24

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE                 25

RFS       IDLE                  0

MRP0      APPLYING_LOG         25

10 rows selected.

on primary

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

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             27

SQL>

on standby 

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

PROCESS   STATUS        SEQUENCE#

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

ARCH      CLOSING              26

DGRD      ALLOCATED             0

DGRD      ALLOCATED             0

ARCH      CONNECTED             0

ARCH      CLOSING              27

ARCH      CLOSING              25

ARCH      CONNECTED             0

RFS       IDLE                 28

RFS       IDLE                  0

MRP0      APPLYING_LOG         28

10 rows selected.

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                     27                    27          0

SQL>

Table does not exists which we created during database was snapshot standby 

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

SQL>

SQL>

SQL> select * from hr.test_snap;

select * from hr.test_snap

                 *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

How to Configure TDE in Oracle 19c

 

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>

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>

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

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

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>

SQL> show parameter WALLET_ROOT

NAME                                 TYPE        VALUE

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

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

SQL>

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>

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]$

[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>

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>

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]$

[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>

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>