Sreenu Oracle DBA

Wednesday, 26 September 2018

How to handle Oracle File_Recovery_Area(FRA)

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it’s usage, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

Check and confirm FRA location with below parameter

SQL>Select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
------ --------- -------------
ORADB1 READ WRITE PRIMARY

SQL> Show parameter db_recovery_file_dest;

NAME TYPE VALUE
-------------------------- ----------- ------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 120G

Check FRA usage like Total, used and free space with below select statement

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

TOTAL_GB USED_GB
-------- -------
120 88.8515625

Check the fra usage in percentage with below select statement

SQL> Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 74.043

Check the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

Select * from v$recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 73.53 73.53 282 0
BACKUP PIECE .51 .51 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0

8 rows selected.

or

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 73.53 73.53 282 0
BACKUP PIECE .51 .51 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0

8 rows selected.

Check Size, usage, Reclaimable space used
SQL>SELECT ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,ROUND((A.SPACE_RECLAIMABLE/1024/1024/1024), 2) AS FLASH_RECLAIMABLE_GB,SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM V$RECOVERY_FILE_DEST A, V$FLASH_RECOVERY_AREA_USAGE B GROUP BY SPACE_LIMIT, SPACE_USED , SPACE_RECLAIMABLE ;

FLASH_IN_GB FLASH_USED_IN_GB FLASH_RECLAIMABLE_GB PERCENT_OF_SPACE_USED
----------- ---------------- -------------------- ---------------------
120 88.85 88.85 74.04

As per above output FRA usage 74.04%

We have thre scenarios to fix FRA issue.

1. We can increase FRA space if we have sufficient free disk space :

Check FRA disk free space with below select statement

SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

NAME TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
DATA 1000 303.550781
FRA 200 109.339844
REDO 60 47.4726563

or

[grid@e75lrl2036 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 1024000 310836 0 310836 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 204800 111964 0 111964 0 N FRA/
MOUNTED EXTERN N 512 512 4096 4194304 61440 48612 0 48612 0 N REDO/

As per above output we have free space approx 109GB, So we are good to increase FRA with below syntax

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=140G scope=both;

System altered.

Now check FRA Usage after increasing.

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

TOTAL_GB USED_GB
---------- ----------
140 88.8515625

SQL> Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 62.043

2. Change the FRA to a new location, new archives will be created to this new location:

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/backup/archive' scope =both;

System Altered.

Now check FRA location after changing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/archive
db_recovery_file_dest_size big integer 120G

3. Deleting Old archive logfiles to reclaim FRA space.

Check backup of database+archivelogs backup atleast for one week with below select statement, If all backups are successfull and valid backups then we can remove old archive logfiles to reclaim space.

SQL>
col output_bytes_display form a15
col input_bytes_display form a15
col STATUS form a25
col time_taken_display form a10
col INPUT_BYTES_PER_SEC_DISPLAY form a15
col OUTPUT_BYTES_PER_SEC_DISPLAY form a17
col input_rate_per_sec form a20
col output_rate_per_sec form a20
select session_key,
input_type,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
input_bytes_display,
output_bytes_display,
--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,
time_taken_display
from v$rman_backup_job_details
order by session_key asc;

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME INPUT_BYTES_DIS OUTPUT_BYTES_DI OUTPUT_RATE_PER_SEC TIME_TAKEN
----------- ------------- ------------------------- ---------------- ---------------- --------------- --------------- -------------------- ----------
9 ARCHIVELOG COMPLETED 2020-02-16 05:03 2020-02-16 05:05 322.98M 324.00M 2.63M 00:02:03
12 DB INCR COMPLETED 2020-02-16 05:44 2020-02-16 11:35 157.76G 88.56G 4.31M 05:50:37
14 ARCHIVELOG COMPLETED 2020-02-16 06:04 2020-02-16 06:05 1.27G 1.27G 14.18M 00:01:32
17 ARCHIVELOG COMPLETED 2020-02-16 07:03 2020-02-16 07:05 1.94G 1.94G 19.33M 00:01:43
84 ARCHIVELOG COMPLETED 2020-02-16 17:02 2020-02-16 17:04 2.41G 2.42G 24.25M 00:01:42
87 DB INCR COMPLETED 2020-02-16 17:12 2020-02-16 17:14 0.00K 0.00K 0.00K 00:02:05
144 ARCHIVELOG COMPLETED 2020-02-17 11:04 2020-02-17 11:06 4.81G 4.81G 34.43M 00:02:23
186 ARCHIVELOG COMPLETED 2020-02-17 12:03 2020-02-17 12:05 5.36G 5.37G 48.62M 00:01:53
244 ARCHIVELOG COMPLETED 2020-02-18 06:06 2020-02-18 06:40 11.90G 11.90G 5.97M 00:34:01
246 DB INCR COMPLETED 2020-02-18 06:15 0.00K 0.00K
249 ARCHIVELOG COMPLETED 2020-02-18 07:04 2020-02-18 07:15 11.91G 11.91G 17.40M 00:11:41
252 ARCHIVELOG COMPLETED 2020-02-18 08:03 2020-02-18 08:09 12.00G 12.00G 38.28M 00:05:21
343 ARCHIVELOG COMPLETED 2020-02-19 02:03 2020-02-19 02:54 21.56G 21.56G 7.20M 00:51:08
345 DB INCR COMPLETED 2020-02-19 02:40 2020-02-19 02:42 0.00K 0.00K 0.00K 00:02:06
432 ARCHIVELOG COMPLETED 2020-02-19 22:04 2020-02-19 22:09 8.58G 8.58G 26.88M 00:05:27
435 ARCHIVELOG COMPLETED 2020-02-19 23:05 2020-02-19 23:06 8.67G 8.68G 116.89M 00:01:16
441 ARCHIVELOG COMPLETED 2020-02-20 01:05 2020-02-20 01:09 9.16G 9.16G 39.92M 00:03:55
444 DB INCR COMPLETED 2020-02-20 02:02 2020-02-20 02:17 704.54G 6.25G 6.97M 00:15:18
446 ARCHIVELOG COMPLETED 2020-02-20 02:04 2020-02-20 02:12 9.26G 9.26G 20.57M 00:07:41
450 ARCHIVELOG COMPLETED 2020-02-20 02:20 2020-02-20 02:21 9.26G 9.26G 135.51M 00:01:10
531 ARCHIVELOG COMPLETED 2020-02-21 00:04 2020-02-21 00:05 19.00G 19.01G 173.78M 00:01:52
537 DB INCR COMPLETED 2020-02-21 02:01 2020-02-21 02:12 704.54G 5.64G 8.51M 00:11:19
539 ARCHIVELOG COMPLETED 2020-02-21 02:05 2020-02-21 02:07 19.31G 19.31G 125.16M 00:02:38
543 ARCHIVELOG COMPLETED 2020-02-21 02:16 2020-02-21 02:17 19.31G 19.31G 184.83M 00:01:47
3376 ARCHIVELOG COMPLETED 2020-02-22 01:04 2020-02-22 01:08 56.15G 56.16G 246.80M 00:03:53
3379 DB INCR COMPLETED 2020-02-22 02:01 2020-02-22 02:22 574.54G 352.57G 274.55M 00:21:55
3381 ARCHIVELOG COMPLETED 2020-02-22 02:03 2020-02-22 02:11 56.26G 56.26G 118.54M 00:08:06
3385 ARCHIVELOG COMPLETED 2020-02-22 02:24 2020-02-22 02:28 56.26G 56.26G 249.42M 00:03:51
4123 ARCHIVELOG COMPLETED 2020-02-23 23:04 2020-02-22 23:12 59.87G 59.88G 126.94M 00:08:03
4156 DB INCR COMPLETED 2020-02-23 00:04 2020-02-23 00:33 59.89G 59.89G 34.73M 00:29:26
4159 ARCHIVELOG COMPLETED 2020-02-23 01:04 2020-02-23 01:13 59.93G 59.94G 121.53M 00:08:25
4163 ARCHIVELOG COMPLETED 2020-02-23 23:04 2020-02-22 23:12 59.87G 59.88G 126.94M 00:08:03
4193 ARCHIVELOG COMPLETED 2020-02-24 02:58 2020-02-24 03:03 65.98G 65.98G 226.74M 00:04:58
4195 DB INCR COMPLETED 2020-02-24 03:02 2020-02-24 03:34 574.54G 4.95G 2.67M 00:31:41
4199 ARCHIVELOG COMPLETED 2020-02-24 03:36 2020-02-24 03:42 66.07G 66.07G 214.78M 00:05:15
4202 ARCHIVELOG COMPLETED 2020-02-24 04:08 2020-02-24 04:11 66.07G 66.08G 393.38M 00:02:52
4299 ARCHIVELOG COMPLETED 2020-02-25 01:04 2020-02-25 01:08 76.69G 76.69G 409.02M 00:03:12
4302 DB INCR COMPLETED 2020-02-25 02:02 2020-02-25 02:10 574.54G 5.95G 12.36M 00:08:13
4304 ARCHIVELOG COMPLETED 2020-02-25 02:04 2020-02-25 02:09 76.74G 76.74G 242.54M 00:05:24
4308 ARCHIVELOG COMPLETED 2020-02-25 02:11 2020-02-25 02:15 76.74G 76.74G 370.68M 00:03:32
4404 ARCHIVELOG COMPLETED 2020-02-26 01:06 2020-02-26 01:17 87.23G 87.24G 131.18M 00:11:21
4407 DB INCR COMPLETED 2020-02-26 02:01 2020-02-26 02:12 574.54G 5.93G 9.49M 00:10:40
4409 ARCHIVELOG COMPLETED 2020-02-26 02:05 2020-02-26 02:14 87.24G 87.24G 160.39M 00:09:17
4413 ARCHIVELOG COMPLETED 2020-02-26 02:15 2020-02-26 02:21 87.24G 87.25G 230.26M 00:06:28

As per above output backup is successfull, Connect rman prompt and remove old archive log files

RMAN>delete noprompt archivelog until time 'sysdate-7' backed up 1 times to device type disk;

or

Manually delete from ASM command prompt

Note:Not recomended approach since by deleting manually it won't release space we need to run (CROSSCHECK ARCHIVELOG ALL command from RMAN to release space)

ASMCMD> pwd
+FRA/INSISCLN/ARCHIVELOG
ASMCMD> ls
2020_02_19/
2020_02_20/
2020_02_21/
2020_02_22/
2020_02_23/
2020_02_24/
2020_02_25/
2020_02_26/
ASMCMD>cd 2020_02_19/
ASMCMD>rm *.arc
ASMCMD> ..
ASMCMD> ls
2020_02_20/
2020_02_21/
2020_02_22/
2020_02_23/
2020_02_24/
2020_02_25/
2020_02_26/

After removing old archive logfile with retention of 7 days check current FRA usage.

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

TOTAL_GB USED_GB
---------- ----------
120 73.8515625

Check the fra usage in percentage with below select statement

SQL> Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID FRA_PCT_USED
----- ----------- ---------- ----------------- --------------- ---------- ----------
+FRA 1.2885E+11 9.5404E+10 9.5404E+10 285 0 62.043

Thursday, 26 April 2018

OPATCHAUTO-72046: No Wallet Option Provided

PROBLEM:

I was applying proactive bundle patch on oracle 12c RAC database. And during execution of opatchauto apply command on rdbms binary, got below error.

$ORACLE_HOME/OPatch/opatchauto apply /softdump/OCTBP2017/26635880 -oh /oracle/app/oracle/product/12.1.0.2/dbhome_1

OPatchauto session is initiated at Wed Nov 8 11:48:21 2017
OPATCHAUTO-72046: No wallet option provided.
OPATCHAUTO-72046: Wallet option is not provided which is required during patching.
OPATCHAUTO-72046: Please provide a wallet option.

SOLUTION:

opatchauto command always needs to be executed by root user. Use of any other user will throw this error.

So login with root and rerun the command.

$ sudo su - root
Oracle Corporation SunOS 5.11 11.3 March 2017
You have new mail.

-- Go the $ORACLE_HOME/OPatch location .

root:~# pwd
/root
root:~# cd /oracle/app/oracle/product/12.1.0.2/dbhome_1/OPatch

root:# ./opatchauto apply /softdump/OCTBP2017/26635880 -oh /oracle/app/oracle/product/12.1.0.2/dbhome_1

OPatchauto session is initiated at Wed Nov 8 11:49:09 2017

System initialization log file is /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2017-11-08_11-49-13AM.log.

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

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)

Thursday, 19 April 2018

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

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

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

PROBLEM:
While importing dump file, got error as

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

SOLUTION:
1. Check the compatible parameter of both source and target database.

SOURCE DB:
SQL>show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ---------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE

TARGET DB:

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- --------
compatible string 11.2.0.4.0

We can see the source compatible parameter is higher version(12.1.0.2) and target is lower(11.2.0.4). A dump file generated from database with higher compatible parameter can’t be imported to a database with lower compatible value. Either both should be same or target db compatible can be higher than the source compatible.

To fix it, While taking export use VERSION=11.2 parameter

expdp dumpfile=test.dmp logfile=test.log directory=EXPDIR full=y version=11.2

Now try to import this, It will succeed without any issues.

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