3zecisi3: May 2014

alert.log file on standby:

Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file E:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_pr00_3232.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

Check for the files taht need recover on standby:

SQL> select * from v$recover_file where error like '%FILE%';

     FILE# ONLINE ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         6 ONLINE ONLINE
FILE MISSING                                                               0

SQL>

Get the details on missing datafile from the primary:

SQL> select file#, name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------

         1
E:\APP\ORACLE\ORADATA\STANDBY1\SYSTEM01.DBF

         2
E:\APP\ORACLE\ORADATA\STANDBY1\SYSAUX01.DBF

         3
E:\APP\ORACLE\ORADATA\STANDBY1\UNDOTBS01.DBF

     FILE#
----------
NAME
--------------------------------------------------------------------------------

         4
E:\APP\ORACLE\ORADATA\STANDBY1\USERS01.DBF

         5
E:\APP\ORACLE\ORADATA\STANDBY1\EXAMPLE01.DBF

         6
E:\APP\ORACLE\ORADATA\STANDBY1\AUD_AUX01.DBF

6 rows selected.

Check MRP is not running on standby:

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';
alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\U
NNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.

SQL> alter system set standby_file_management=MANUAL scope=both;

System altered.

SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';

Database altered.

Check recovery process on standby:

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> shutdown immediate
ORA-01109: database not open

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

Total System Global Area 835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             285213864 bytes
Database Buffers          541065216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SQL>
SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------

   1518564                     4 YES    681574400
13-JAN-14 02.08.19.000000000 PM
                                                                            YES
SWITCHOVER_START_GRP

SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;

Database altered.

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

PROCESS   DELAY_MINS
--------- ----------
MRP0               0

SQL>

SQL> drop tablespace aud_aux including contents and datafiles;
drop tablespace aud_aux including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace AUD_AUX on primary database due to guaranteed
restore points.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,        GUARANTEE_FLASHBACK_D
ATABASE,STORAGE_SIZE        FROM V$RESTORE_POINT;

NAME
--------------------------------------------------------------------------------

       SCN
----------
TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------
SWITCHOVER_START_GRP
   1518415
13-JAN-14 02.07.10.000000000 PM
                    4 YES    629145600

SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> drop tablespace aud_aux including contents and datafiles;

Tablespace dropped.

SQL>

Wednesday, May 28, 2014

ORA-01111: name for data file 6 is unknown - on standby after adding datafile to primary

ORA-38881: Cannot drop tablespace