RMAN Block Recovery ,Skip Corrupted Block

 

Create table under tablespace test2 and check the block# for the first few rows.

SQL> create table oratab.objects2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from oratab.objects2) where rownum < 7;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
131
132
133
134
135

SQL> select count(*) from oratab.objects2;

COUNT(*)
———-
18520

No corrupted blocks in the tablespace.

SQL> select * from v$database_block_corruption;

no rows selected

Manually corrupt one of the block containing data.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.0526e-05 s, 162 MB/s

Flush buffer cache as it may have all blocks currently in memory.

SQL> alter system flush buffer_cache;

System altered.

Query the table, it will through error message with the block# of corrupted block.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

The corrupted block details will be reflected in v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 133 1 0 ALL ZERO

Recover block using RMAN

RMAN> blockrecover datafile 7 block 133;

Starting recover at 21-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Corrupt multiple blocks now.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=134 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6443e-05 s, 145 MB/s
$ dd of=/u02/oradata/test201.dbf bs=8192 seek=135 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2078e-05 s, 157 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 134)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

Use RMAN validate to check the corrupted block list.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE TEST2;

Starting validate at 21-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u02/oradata/test201.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 FAILED 0 12410 12800 30691648
File Name: /u02/oradata/test201.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 247
Index 0 0
Other 2 143

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 134 2 0 ALL ZERO
When there are multiple corrupted blocks, use blockrecover corruption list to recover them.

RMAN> blockrecover corruption list;

Starting recover at 21-APR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Skipping Corrupted Block using event ‘10231’  or DBMS_REPAIR if it can’t be recovered.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9682e-05 s, 165 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 131 1 0 ALL ZERO

SQL> ALTER SESSION SET EVENTS
‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’; 2

Session altered.

SQL> create table oratab.objects2_recover tablespace test2 as select * from oratab.objects;

Table created.

SQL> SQL> select count(*) from oratab.objects2_recover;

COUNT(*)
———-
18518 — 2 rows decreased.

Corrupted block can be skipped using DBMS_REPAIR package as well

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME SKIP_CORRUPT
—————————— ——–
OBJECTS2 ENABLED

Skip corrupt flag can be cleared after exporting the table or using CTAS

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,SKIP_CORRUPT from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME                               SKIP_CORRUPT
—————————— ——–
OBJECTS2                                             DISABLED

 

ref:Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 (Doc ID 33405.1)

 

ORA-1017 and ORA-16047 in Dataguard

Issue:

Primary not able to connect to standby database, no RFS process started on standby database.

Alert log on primary has ORA 1017 errors and trace file has ORA-16047

ORA-16047: DGID mismatch between destination setting and target database

Troubleshooting:

Recreated the password file on primary and copied to standby database, but still primary LNS not able to communicate to RFS on standby

select process, status from v$managed_standby;  — RFS not started on DR database

I am able to connect to standby database using sqlplus with the recreated password file.

Solution:

LOG_ARCHIVE_CONFIG parameter is missing from standby database , though password file is fine , primary not able to authenticate the DR DB unique_name as the LOG_ARCHIVE_CONFIG is missing.

alter system set log_archive_config=’DG_CONFIG=(primary,DR)’ scope=both

Above parameter setting resolved the issue.

 

 

 

 

 

Convert Non-CDB to PDB

1) Shut down the non CDB database and startup in read only mode

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

SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 905969768 bytes
Database Buffers 155189248 bytes
Redo Buffers 9650176 bytes
Database mounted.
Database opened.

2) Describe the Non-CDB using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’);
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

3) Shutdown the non-CDB database

SHUTDOWN IMMEDIATE;SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Need to check the compatibility of Non-CDB to be converted to Pluggable DB So, connect to the container DB (CDB) and run the following

SQL>
SET SERVEROUTPUT ON
SQL> SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
3 pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’,
4 pdb_name => ‘TESTPDB2’)
5 WHEN TRUE THEN ‘YES’
6 ELSE ‘NO’
END;
7 8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(compatible);
10 END;
/ 11
YES

PL/SQL procedure successfully completed.

5) Create Pluggable database from the XML file, here we are using the same datafiles that were there earlier for the Non CDB DB.

SQL> CREATE PLUGGABLE DATABASE TESTPDB2 using ‘/apps/oracle/home/TEST12c.xml’ nocopy tempfile reuse;

Pluggable database created.

6) Check the status of the created PDB

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE
———- —————————— ———-
2 PDB$SEED READ ONLY
3 TESTPDB READ WRITE
4 TESTPDB2 MOUNTED
SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NEW
SQL> alter session set container=TESTPDB2;

Session altered.

SQL> show con_name;

CON_NAME
——————————
TESTPDB2

7) Execute the script to update metadata

SQL> !echo $ORACLE_HOME
/apps/oracle/product/12.1.0

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;

8) Open the new PDB database

SQL> alter session set container=TESTPDB2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TESTPDB READ WRITE
TESTPDB2 READ WRITE

3 rows selected.

SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NORMAL

3 rows selected.

RMAN Convert database from Linux to Windows

Check Endian format for the source and target OS versions:

SQL> select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ————————————————–
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
5 HP Tru64 UNIX Little
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
13 Linux x86 64-bit Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little

12 rows selected.

SQL> select platform_name from v$database;

PLATFORM_NAME
————————————————————-
Linux x86 64-bit

Preparing to Convert database:

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
begin
3 4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and
retry.

PL/SQL procedure successfully completed.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> set serveroutput on
SQL> declare
db_ready boolean;
2 3 begin
/* db_ready is ignored, but with SERVEROUTPUT set to ON any
4 5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,
7 dbms_tdb.skip_none);
8 end;
9 /

PL/SQL procedure successfully completed.
Using DBMS_TDB .CHECK_EXTERNAL to Identify External Objects:
SQL> declare
external boolean;
begin
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := dbms_tdb.check_external;
end; 2 3 4 5 6 7 8
9 /
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.EXPDP

PL/SQL procedure successfully completed.

RMAN convert from Source platform:

RMAN> CONVERT DATABASE NEW DATABASE ‘PDMTMP2’
2> transport script ‘/ofaroot/MISC/transportscript’
3> to platform ‘Microsoft Windows IA (32-bit)’
db_file_name_convert ‘/oradata/’ ‘/ofaroot/MISC/’
;4> 5>

Starting conversion at source at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/oradata/users01.dbf
converted datafile=/ofaroot/MISC/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/oradata/system.dbf
converted datafile=/ofaroot/MISC/system.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oradata/sysaux.dbf
converted datafile=/ofaroot/MISC/sysaux.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/oradata/undotbs1.dbf
converted datafile=/ofaroot/MISC/undotbs1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Edit init.ora file /apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /ofaroot/MISC/transportscript on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
Transport script output:

[oracle@VM1]$ cat transportscript
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
CREATE CONTROLFILE REUSE SET DATABASE “PDMTMP2” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-778_T-1_A-847714242_03qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-779_T-1_A-847714242_04qpgua4’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/apps/oracle/product/11.2.0.3/dbs/arch_D-PDMTMP2_id-2935319874_S-777_T-1_A-847714242_05qpgua4’ SIZE 100M BLOCKSIZE 512
DATAFILE
‘/ofaroot/MISC/system.dbf’,
‘/ofaroot/MISC/sysaux.dbf’,
‘/ofaroot/MISC/undotbs1.dbf’,
‘/ofaroot/MISC/users01.dbf’
CHARACTER SET AL32UTF8
;

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/apps/oracle/product/11.2.0.3/dbs/data_D-PDMTMP2_I-2935319874_TS-TEMP_FNO-1_06qpgua4′
SIZE 524288000 AUTOEXTEND OFF;
— End of tempfile additions.

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE=’/apps/oracle/product/11.2.0.3/dbs/init_00qpgua4_1_0.ora’
— The following step will recompile all PL/SQL modules.
— It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
RMAN convert on TARGET Platform:

RMAN> CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT ‘/ofaroot/MISC/convertscript.rman’
2> 3> TRANSPORT SCRIPT ‘/ofaroot/MISC/transportscripttarget.sql’
4> new database ‘PDMTMP1’
5> FORMAT ‘/ofaroot/MISC/%U’;

Starting conversion at source at 23-DEC-15
using channel ORA_DISK_1

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.EXPDP found in the database
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00004 name=/oradata/users01.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00001 name=/oradata/system.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00002 name=/oradata/sysaux.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile file number=00003 name=/oradata/undotbs1.dbf
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
Edit init.ora file /ofaroot/MISC/init_00qpgv1f_1_0.ora. This PFILE will be used to create the database on the target platform
Run RMAN script /ofaroot/MISC/convertscript.rman on target platform to convert datafiles
Run SQL script /ofaroot/MISC/transportscripttarget.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-DEC-15
[oracle@VM1]$ cat convertscript.rman
STARTUP NOMOUNT PFILE = ‘/ofaroot/MISC/init_00qpgv1f_1_0.ora’;
RUN {
CONVERT
FROM PLATFORM ‘Linux x86 64-bit’
PARALLELISM 1
DATAFILE ‘/oradata/users01.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-USERS_FNO-4_05qpgv1f’
DATAFILE ‘/oradata/system.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSTEM_FNO-1_06qpgv1f’
DATAFILE ‘/oradata/sysaux.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-SYSAUX_FNO-2_07qpgv1f’
DATAFILE ‘/oradata/undotbs1.dbf’ FORMAT ‘/ofaroot/MISC/data_D-PDMTMP_I-2935319874_TS-UNDOTBS1_FNO-3_08qpgv1f’
; }

Ref: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm#CHDEICED

 

ORA- 28040: No matching Authentication protocol after 12c Upgrade

Issue:

Web application was not working, when connecting through ODBC application team is getting error ORA-28040 .

What Was changed:

Oracle database upgraded from oracle 11.2.0.2 to 12.1.0.2

Resolution/Workaround:

SQLNET.ALLOWED_LOGON_VERSION default is changed to 11 from 8 in earlier versions.  We need to modify the parameter to value 9 or less. SQLNET.ALLOWED_LOGON_VERSION is depreciated in 12c, SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT replaced this parameter.

Ref: https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2016

Snapshot Standby database Oracle 11g

Snapshot standby database allows to open the database in read/write mode and continues to ship the redo from primary which can later be applied after reverting the database to Physical standby.

Application has a test case which needs to be tested in production, so converted the database to snapshot standby database and reverted back to physical standby after they finished their testing.

Check if Flash back enabled for the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
NO

Set the DB_RECOVERY_FILE_DEST and FLASH recovery area. Default retention(DB_FLASHBACK_RETENTION_TARGET) time is 1 day, so set the retention period and size as required.

SQL> alter system set db_recovery_file_dest=’/u03/ofaroot/PROD_FLASHBACK/’ scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=50G scope=both;

System altered.

Mount the Instance:
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
SQL> alter database mount standby database;

Database altered.

Check if parameters are modified: 

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u03/ofaroot/PROD_FLAS
HBACK
db_recovery_file_dest_size big integer 50G

Enable Flashback:

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Stop the MRP Process and start the DB in mount mode

SQL> alter databAse recover managed standby database cancel;

Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.
SQL> select name from v$database;

NAME
———
PROD

Convert the database to Snapshot standby:

SQL> alter database convert to snapshot standby;

Database altered.

Alertlog Entries:
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED 12:41:17
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 7195028071
Resetting resetlogs activation ID 3952627246 (0xeb984e2e)

Snapshot database creates a guaranteed restore point , so make sure sufficient space is maintained in flash recovery area.
Open the database:

SQL> alter database open;

Database altered.

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

INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————- —————- ——————–
PROD SNAPSHOT STANDBY READ WRITE

Reverting the database to physical standby:

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.

SQL> alter database convert to physical standby;
Database altered.

Alertlog Entries:

alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (PROD)
krsv_proc_kill: Killing 3 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped

Convert to Physical standby statement will flashback to the guaranteed restore point and drops the guaranteed restore point as they do not age out.

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.

Start the MRP:

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

Database altered.

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

INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————- —————- ——————–
PROD PHYSICAL STANDBY MOUNTED

ORA-39126 – During Schema Export

Issue:

Schema export form system user failed with below errors.

Estimate in progress using BLOCKS method…
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPW$WORKER”, line 9001

—– PL/SQL Call Stack —–
object line object
handle number name
4069d2ac0 20462 package body SYS.KUPW$WORKER
4069d2ac0 9028 package body SYS.KUPW$WORKER
4069d2ac0 6814 package body SYS.KUPW$WORKER
4069d2ac0 13688 package body SYS.KUPW$WORKER
4069d2ac0 2654 package body SYS.KUPW$WORKER
4069d2ac0 9697 package body SYS.KUPW$WORKER
4069d2ac0 1775 package body SYS.KUPW$WORKER
4053c83e8 2 anonymous block

Solution:

There was a DUAL table created under SYSTEM user having multiple rows.

SQL> select table_name,owner from dba_Tables where table_name=’DUAL’;

TABLE_NAME OWNER
—————————— ——————————
DUAL SYS
DUAL SYSTEM

SQL> select * from dual;

D

X
X
X
X

Dropped the custom dual table created under system and the export worked fine.

drop table system.dual;