Oracle Database Commands and Practical Examples

expdp error fails DETERMINE_FATAL_ERROR with ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

NOTE : In this scenario using rman backup we have restored from linux to window 12.1.0.2.0 version , After than when we apply expdp it throws an error. after finding out one of tempfile was not created, created tempfile and expdp completed.

D:\>expdp \"sys/infodba@SMRPRODB as sysdba\" dumpfile=expdpSMRwindow.dmp directory=expdp_w logfile=expdpSMRwinodow.log FULL=Y EXCLUDE=STATISTICS CONTE
NT=ALL JOB_NAME=FULL_EXPDP

Export: Release 12.1.0.2.0 - Production on Sun Jun 9 17:32:09 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."FULL_EXPDP": "sys/********@SMRPRODB AS SYSDBA" dumpfile=expdpSMRwindow.dmp directory=expdp_w logfile=expdpSMRwinodow.log FULL=Y EXCLU
DE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.986 GB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u04/MasterDB/oradata/SMRPRODB/temp02.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259

----- PL/SQL Call Stack -----
object line object
handle number name
000000268F3EC4A0 27116 package body SYS.KUPW$WORKER
000000268F3EC4A0 11286 package body SYS.KUPW$WORKER
000000268F3EC4A0 13515 package body SYS.KUPW$WORKER
000000268F3EC4A0 3173 package body SYS.KUPW$WORKER
000000268F3EC4A0 12035 package body SYS.KUPW$WORKER
000000268F3EC4A0 2081 package body SYS.KUPW$WORKER
000000269F44FC18 2 anonymous block

In procedure BUILD_OBJECT_STRINGS
In procedure BUILD_SUBNAME_LIST with MARKER:.
In function NEXT_PO_NUMBER
FORALL
FORALL
DBMS_LOB.TRIM
DBMS_LOB.TRIM
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
In procedure DETERMINE_FATAL_ERROR with ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u04/MasterDB/oradata/SMRPRODB/temp02.dbf'
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u04/MasterDB/oradata/SMRPRODB/temp02.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259

----- PL/SQL Call Stack -----
object line object
handle number name
000000268F3EC4A0 27116 package body SYS.KUPW$WORKER
000000268F3EC4A0 11286 package body SYS.KUPW$WORKER
000000268F3EC4A0 13515 package body SYS.KUPW$WORKER
000000268F3EC4A0 3173 package body SYS.KUPW$WORKER
000000268F3EC4A0 12035 package body SYS.KUPW$WORKER
000000268F3EC4A0 2081 package body SYS.KUPW$WORKER
000000269F44FC18 2 anonymous block

In procedure BUILD_OBJECT_STRINGS
In procedure BUILD_SUBNAME_LIST with MARKER:.
In function NEXT_PO_NUMBER
FORALL
FORALL
DBMS_LOB.TRIM
DBMS_LOB.TRIM
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
In procedure DETERMINE_FATAL_ERROR with ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u04/MasterDB/oradata/SMRPRODB/temp02.dbf'
Job "SYS"."FULL_EXPDP" stopped due to fatal error at Sun Jun 9 17:32:20 2019 elapsed 0 00:00:09

D:\>

Planned failover on 12c

STEP#1 Shutdown Primary Database for planned failover on 12c

STEP#2 On Standby Database

Stop MRP Process -

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

converting standby role to the primary role

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

converting standby role to the primary role

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open database in read/Write mode

ALTER DATABASE OPEN;

Now primary database up make primary database as standby database

Startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

######################################################################

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB READ WRITE PRIMARY YES 439121110

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6267E+11 bytes
Fixed Size 7653480 bytes
Variable Size 2.4696E+10 bytes
Database Buffers 1.3744E+11 bytes
Redo Buffers 529215488 bytes
Database mounted.

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
xxxPRODB xxxPRODB xxxPRODB MOUNTED PRIMARY YES 439121110

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB MOUNTED PHYSICAL STANDBY YES 0

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.6267E+11 bytes
Fixed Size 7653480 bytes
Variable Size 2.4696E+10 bytes
Database Buffers 1.3744E+11 bytes
Redo Buffers 529215488 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Move datafile Online on 12c oracle database

Basic Syntax

OMF do not use KEEP option , It will skip.

Tempfile can not move online.

The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.
SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRPATCH7/system01.dbf
/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF
/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf
/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf
/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF
/u01/app/oracle/oradata/SMRPATCH7/users01.dbf
/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF

7 rows selected.

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME

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

/u04/MasterDB/oradata/SMRPATCH7/system01.dbf

/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

/u04/MasterDB/oradata/SMRPATCH7/users01.dbf

/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

7 rows selected.

Alert Log Details :

Tue Jun 11 10:16:46 2019

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF'

Tue Jun 11 10:16:46 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/indx01.DBF (7) to /u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf'

Tue Jun 11 10:24:41 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/users01.dbf (6) to /u04/MasterDB/oradata/SMRPATCH7/users01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/users01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF'

Tue Jun 11 10:25:43 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/idata01.DBF (5) to /u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf'

Tue Jun 11 10:25:53 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf (4) to /u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF'

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF (2) to /u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

Tue Jun 11 10:26:04 2019

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf'

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf (3) to /u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf'

Tue Jun 11 10:26:24 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/system01.dbf (1) to /u04/MasterDB/oradata/SMRPATCH7/system01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/system01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf'

Tue Jun 11 11:05:03 2019

We can not move tempfile online it will through error below .
ORA-01516: nonexistent log file, data file, or temporary file .

SQL> select name from v$tempfile;

NAME

/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf' TO '/u04/db_backup/Flash_recovery_area/oradata/SMRPATCH7/temp01.dbf';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf' TO '/u04/db_backup/Flash_recovery_area/oradata/SMRPATCH7/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf"

Oracle Database Commands and Practical Examples

HOME

Wednesday 12 June 2019

expdp error fails DETERMINE_FATAL_ERROR with ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

Planned failover on 12c

Move datafile Online on 12c oracle database