Sreenu Oracle DBA: April 2018

When trying to backup the SYS.AUD$ table using datapump for my oracle 11g database, this is what i'm getting below

Oradev01:corpqa:/export/corpqa $ expdp directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 15:31:15 2017

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:31:18

Cause:

According to Oracle there is a restriction on dataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.

Solution:

Export the table SYS.AUD$ using the traditional export:

Oradev01: corpqa:/export/corpqa $ exp file=SYS_AUD_table.dmp log=exp_SYS_AUD_table.log tables=AUD$ statistics=none

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 16:24:40 2017

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table AUD$ 504728389 rows exported
Export terminated successfully without warnings.

Reference:

DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Doc ID 553402.1)
How to Export the AUD$ Table (Doc ID 745540.1)

convert physical standby database to snapshot standby database, physical standby database in READ WRITE mode, snapshot standby, standby in READ WRITE mode
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions. Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active dataguard implemented, but a snapshot standby database can be used to perform both READ and WRITE activities. Most importantly, a snapshot standby database keeps receiving the redo data from the primary database but does not apply them. These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode. There by the snapshot standby database provides data protection on primary database.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is a mirrored copy of the primary database). This allows the users to test the application on a standby database which has the primary data before implementing it in the Real production environment. When a physical standby database is converted to a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means that the transactions that were made when the standby database was opened in READ WRITE mode will be flushed out.
The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled. Below are the steps on how to convert a physical standby database to a snapshot standby database and viceversa.
Primary database Details:
Oracle Database version: 11.2.0.3 Enterprise Edition
Primary database: SRPRIM
Details with respect to the primary database:

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ------------- ----------
OPEN srprim PRIMARY READ WRITE

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

THREAD# MAX(SEQUENCE#)
---------- ----------

1 206
Standby database Details:
Oracle database version: 11.2.0.3 Enterprise Edition
Standby database name: SRPS
Details with respect to the physical standby database:

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN srps PHYSICAL STANDBY READ ONLY WITH APPLY

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

THREAD# MAX(SEQUENCE#)
---------- ----------
1 206

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------

NO
You can observe that the standby database is in sync with the primary database. Below outcome shows that the Flash Recovery Area is configured on the physical standby database.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
--------------------------- ----------- -------------
db_recovery_file_dest string +FRA_NEW
db_recovery_file_dest_size big integer 4122M

Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 805306608 bytes
Database Buffers 452984832 bytes
Redo Buffers 8847360 bytes

Database mounted.

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

SQL> alter database convert to snapshot standby;

Database altered.

Step 3: You can now open the snapshot standby database and check its mode.

SQL> alter database open;

Database altered.

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
----------- --------------- ---------------- ----------

OPEN srps SNAPSHOT STANDBY READ WRITE

Small Test on the snapshot standby database:

1. Create a user called “SNAPTEST”

2. Create a table called “TEST” whose owner is “SNAPTEST” and insert some records in it. You can also update some of the records as well.

SQL> create user snaptest identified by oracle;

User created.

SQL> grant connect,resource to snaptest;

Grant succeeded.

SQL> conn snaptest/oracle;

Connected.

SQL> create table test(code number, name char(20));

Table created.

SQL> insert into test values (100,'ARUN');

1 row created.

SQL> insert into test values(200,'SHIVU');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;
CODE NAME
---------- --------------------
100 ARUN
200 SHIVU

SQL> update snaptest.test set code=500 where name='ARUN';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from snaptest.test;
CODE NAME
---------- --------------------
500 ARUN
200 SHIVU

In the mean time, you can also see that the redo data from the primary database is received by the snapshot standby database but would not be applied.
On primary database the latest sequence generated is 208 and that on the standby database, the RFS process is idle for sequence 209.

Primary:

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ------------- ----------
OPEN srprim PRIMARY READ WRITE

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

THREAD# MAX(SEQUENCE#)
---------- --------------
1 208

Standby:

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
----------- --------------- ---------------- ----------
OPEN srps SNAPSHOT STANDBY READ WRITE

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

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 1
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 209
RFS IDLE 0

7 rows selected.

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

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

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 805306608 bytes
Database Buffers 452984832 bytes
Redo Buffers 8847360 bytes
Database mounted.
SQL>

Step 2: Convert the snapshot standby database to physical standby database.

SQL> alter database convert to physical standby;

Database altered.

Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
QL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 805306608 bytes
Database Buffers 452984832 bytes
Redo Buffers 8847360 bytes
Database mounted.
Database opened.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ -------------- ---------------- ---------
OPEN srps PHYSICAL STANDBY READ ONLY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 213

8 rows selected.

Crosscheck whether the physical standby database is in sync with the primary database.

On Primary database:

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ------------- ----------
OPEN srprim PRIMARY READ WRITE

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

THREAD# MAX(SEQUENCE#)
---------- --------------
1 212
On Standby database:

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ -------------- ---------------- ---------
OPEN srps PHYSICAL STANDBY READ ONLY
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 212
You can see below that the transactions that were carried out when the standby database is opened in READ WRITE mode are flushed out after it was converted back to physical standby database mode.

SQL> select * from snaptest.test;
select * from snaptest.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select username,account_status from dba_users where username='SNAPTEST';

no rows selected

############################################### All The Best ###############################################

Sreenu Oracle DBA

Thursday, 26 April 2018

OPATCHAUTO-72046: No Wallet Option Provided

ORA-39166: Object SYS.AUD$ was not found

Thursday, 19 April 2018

Snapshot Standby Database -How To Convert Physical Standby Database To Snapshot Standby Database (READ WRITE Mode)

Small Test on the snapshot standby database:

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

Crosscheck whether the physical standby database is in sync with the primary database.

Wednesday, 18 April 2018

ORA-39358: Export dump file version 12.1.0.2.0 not compatible with target version 11.2.0.4.0