Monday 25 May 2020

RMAN -Recovery from Loss of the TEMPFILES example

Recovery from Loss of the TEMPFILES 

  • Login database  
oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> sqlplus sys/xxxx123@plm as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 24 11:47:04 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PLM       READ WRITE           PRIMARY

  • Check Tempfile location
SQL> SELECT NAME FROM V$TEMPFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/plm/temp01.dbf

SQL>
  • Remove tempfile at OS level
host rm -f /u01/app/oracle/oradata/plm/temp01.dbf
  • Create global temporary table it will through error

SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES ;
CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES
                                                                                               *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select * from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u01/app/oracle/oradata/plm/temp01.dbf
         1 TEMP                            206569472      25216 ONLINE
           1 YES 3.4360E+10    4194302           80  205520896       25088



  • Add new datafile on TEMP tablespace 

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/plm/temp02.dbf' size 1g;

Tablespace altered.

SQL>
  • Still it will through same error 
SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;
CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES
                                                                                     *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

  • OFFLINE tempfile datafile which thow an error.
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/plm/temp01.dbf' OFFLINE;

Database altered.

  • Check the datafile on temp tablespace.
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/plm/temp01.dbf
/u01/app/oracle/oradata/plm/temp02.dbf

  • Create Global temporary table again this time it will create successfully
SQL>  CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;

Table created.

SQL>

Posted by at No comments:
Labels:

Friday 22 May 2020

RMAN - Inactive Redo Log Group recovery during Database Startup

Recovery from Loss of an Inactive Redo Log Group on Database Startup


Run the following query and select a GROUP# which its status is INACTIVE. 

  • Check the inactive group of file
SQL> SELECT G.GROUP#, G.STATUS FROM V$LOG G ORDER BY 1;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

  • Check the location of file group 3 and remove database at OS level.
SQL> col MEMBER for a150
SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=3;

SQL>

MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/plm/redo03.log


  • Shutdown database
SQL> SHUTDOWN IMMEDIATE ;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Startup , It will through an error.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 2717908992 bytes
Fixed Size                  2928104 bytes
Variable Size             704643608 bytes
Database Buffers         1996488704 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 8227
Session ID: 1 Serial number: 51969


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

oracle@suse1:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 21 18:17:08 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.
  • Start database in mount stage

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2717908992 bytes
Fixed Size                  2928104 bytes
Variable Size             704643608 bytes
Database Buffers         1996488704 bytes
Redo Buffers               13848576 bytes
Database mounted.

  • Clear logfile group3 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.
  • Open the database
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> @display_logs

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
/u01/app/oracle/fast_recovery_area                                 4560       1379

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>


SQL> col MEMBER for a30
SELECT G.GROUP#, G.STATUS G_STATUS, M.STATUS M_STATUS,    SUBSTR( MEMBER, INSTR(MEMBER,'o1'))  MEMBER FROM V$LOG G, V$LOGFILE M WHERE G.GROUP#=M.GROUP# ORDER BY 1;SQL>

    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------
         1 INACTIVE                 /u01/app/oracle/oradata/plm/re
                                    do01.log

         2 ACTIVE                   /u01/app/oracle/oradata/plm/re
                                    do02.log

         3 CURRENT                  /u01/app/oracle/oradata/plm/re
                                    do03.log


SQL> exit

Posted by at No comments:
Labels:

RMAN- ORA-00313: open failed for members of log group Current Redo Log Group recovery using RMAN

Scenario:
If current redo log deleted at OS level , How to recover database error.


  • Check the status of redo log group

SQL>  SELECT G.GROUP#, G.STATUS FROM V$LOG G ORDER BY 1;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

  • Group 1 is in current state
SQL> SELECT G.GROUP# FROM V$LOG G WHERE STATUS='CURRENT';

    GROUP#
----------
         1
  • Check the location of current redo log group in current state.
SQL> SELECT 'host rm -f ' || MEMBER AS CODE FROM V$LOGFILE WHERE GROUP#=(SELECT G.GROUP# FROM V$LOG G WHERE STATUS='CURRENT');

CODE
--------------------------------------------------------------------------------
host rm -f /u01/app/oracle/oradata/plm/redo01.log

  • Remove file at OS level
SQL> host rm -f /u01/app/oracle/oradata/plm/redo01.log
  • Check status of database 
SQL> host ps -ef | grep pmon
grid      2919     1  0 May21 ?        00:00:03 asm_pmon_+ASM1
grid      4252     1  0 May21 ?        00:00:03 mdb_pmon_-MGMTDB
oracle    4499     1  0 May21 ?        00:00:03 ora_pmon_RAC1
oracle    9241     1  0 May21 ?        00:00:03 ora_pmon_plm
oracle   15302 11529  0 13:00 pts/0    00:00:00 /bin/bash -c ps -ef | grep pmon
oracle   15304 15302  0 13:00 pts/0    00:00:00 grep pmon
  • Create table and create check point so we can see error in alert log.
SQL> CREATE TABLE TEST.TEST AS SELECT * FROM DBA_TABLES;

Table created.


SQL> alter system switch logfile;

System altered.


  • Alert.log  details:

Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri May 22 13:02:07 2020
Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri May 22 13:02:07 2020
Errors in file /u01/app/oracle/diag/rdbms/plm/plm/trace/plm_arc1_9509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/plm/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313



Actions to restore the lost redo log group
  • Shutdown database 
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

  • Startup database in mount stage
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 2717908992 bytes
Fixed Size                  2928104 bytes
Variable Size             704643608 bytes
Database Buffers         1996488704 bytes
Redo Buffers               13848576 bytes
Database mounted.

  • Clear logfile group 1
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

oracle@suse1:~> rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 22 13:45:50 2020

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

connected to target database: PLM (DBID=1811128417, not open)
  • Restore database 
RMAN> RESTORE DATABASE;

Starting restore at 22-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/plm/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/plm/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/plm/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/plm/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_21/o1_mf_nnndf_FULL_DB_hddvcjly_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_21/o1_mf_nnndf_FULL_DB_hddvcjly_.bkp tag=FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 22-MAY-20

  • Recover database 
RMAN> RECOVER DATABASE;

Starting recover at 22-MAY-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_8_hddvz6y8_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_10_hddx2olb_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_11_hddx8kn1_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_12_hddx8lyl_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_13_hddx8p22_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_14_hddy5dt7_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_15_hdfc7n4y_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_16_hdfcbbf5_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_17_hdfcctsj_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_18_hdfyjmp6_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_21/o1_mf_1_8_hddvz6y8_.arc thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/22/2020 13:48:07
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 1679803
  • Recover database until cancel
SQL> RECOVER DATABASE until cancel;
ORA-00279: change 1679803 generated at 05/21/2020 17:41:18 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc
ORA-00280: change 1679803 for thread 1 is in sequence #9


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/PLM/archivelog/2020_05_22/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

  • Open database in resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL>

  • Check the status of redo log
SQL> @display_logs

    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------
         1 CURRENT                  /u01/app/oracle/oradata/plm/re
                                    do01.log

         2 UNUSED                   /u01/app/oracle/oradata/plm/re
                                    do02.log

         3 UNUSED                   /u01/app/oracle/oradata/plm/re
                                    do03.log



SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> @display_logs

    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------
         1 CURRENT                  /u01/app/oracle/oradata/plm/re
                                    do01.log

         2 INACTIVE                 /u01/app/oracle/oradata/plm/re
                                    do02.log

         3 INACTIVE                 /u01/app/oracle/oradata/plm/re
                                    do03.log


SQL>


Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)