Export metadata only using expdp.

expdp system/password schemas=SCHEMA_NAME directory=DMPDIR dumpfile=file_name.dmp logfile=explogfile.log content=METADATA_ONLY

How to Restore RMAN Backup from ASM to file System Database.

1. Connect into RMAN and restore spfile from Backup.

[oracle@OEL564ASMN1 dbs]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:11:25 2016

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

connected to target database (not started)

RMAN> STARTUP FORCE NOMOUNT

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_2/dbs/initgolddb.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

RMAN> RESTORE spfile FROM '/u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak';

Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 22-NOV-16

RMAN> exit


Recovery Manager complete.

2. Connect into database and modify the parameter (control_files, db_create_file_dest, db_recovery_file_dest).

[oracle@OEL564ASMN1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:11:58 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> alter system set control_files='/u01/app/oracle/oradata/controlfile01.ctl' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata/' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

3. Restore control file from RMAN Backup.

[oracle@OEL564ASMN1 dbs]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:14:18 2016

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

connected to target database: GOLDDB (not mounted)

RMAN> RESTORE CONTROLFILE TO '/u01/app/oracle/oradata/controlfile01.ctl' FROM '/u01/rman_backup/rman_bkp_ctl_GOLDDB_928602759_11_1_0brlimk7_1_1';

Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-16

RMAN> exit


Recovery Manager complete.

4. Mount the database and create control file to trace file for find the data file name which we need to rename.

[oracle@OEL564ASMN1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:16:14 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/oradata/control_file_records.sql';

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL564ASMN1 dbs]$

5. Open the control file and find out the name of datafile for generate recovery scripts.

[oracle@OEL564ASMN1 dbs]$ cat /u01/app/oracle/oradata/control_file_records.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="golddb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- 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
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/golddb/datafile/system.256.925664777',
  '+DATA/golddb/datafile/sysaux.257.925664781',
  '+DATA/golddb/datafile/undotbs1.258.925664783',
  '+DATA/golddb/datafile/users.259.925664783',
  '+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- 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.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- 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
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/golddb/datafile/system.256.925664777',
  '+DATA/golddb/datafile/sysaux.257.925664781',
  '+DATA/golddb/datafile/undotbs1.258.925664783',
  '+DATA/golddb/datafile/users.259.925664783',
  '+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- 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 '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
[oracle@OEL564ASMN1 dbs]$

6. Restore the database from RMAN Backup. [ Recovery will replay error cause of redo log issue]

[oracle@OEL564ASMN1 ~]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:22:19 2016

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

connected to target database: GOLDDB (DBID=1679955469, not open)

RMAN> RUN
{
2> SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/system.256.925664777'  TO '/u01/app/oracle/oradata/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/sysaux.257.925664781' TO '/u01/app/oracle/oradata/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/undotbs1.258.925664783' TO '/u01/app/oracle/oradata/undotbs101.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/users.259.925664783' TO '/u01/app/oracle/oradata/users01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/example.265.925664949' TO '/u01/app/oracle/oradata/example01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
3> 4> 5> 6> 7> 8> 9> 10> 11>
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-NOV-16
Starting implicit crosscheck backup at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-NOV-16

Starting implicit crosscheck copy at 22-NOV-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-NOV-16

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

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 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-NOV-16

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=928607018 file name=/u01/app/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=928607018 file name=/u01/app/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=928607018 file name=/u01/app/oracle/oradata/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=928607018 file name=/u01/app/oracle/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=928607018 file name=/u01/app/oracle/oradata/example01.dbf

Starting recover at 22-NOV-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc RECID=3 STAMP=928607021
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc RECID=4 STAMP=928607022
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/22/2016 18:23:44
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 984242

RMAN>

7. Connect into database and perform log clear and open the database with resetlog.

[oracle@OEL564ASMN1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:46:22 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> alter database open RESETLOGS;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/undotbs101.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/example01.dbf

SQL>
SQL> col member format a35
col member format a65
set lines 130
set pages 100
select member, group# from v$logfile;SQL> SQL> SQL> SQL>

MEMBER                                                                GROUP#
----------------------------------------------------------------- ----------
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmq7_.log             3
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkf99_.log             2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2jp_.log             1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2pw_.log             1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkfht_.log             2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmxq_.log             3

6 rows selected.

SQL>

How to recover missing/corrupted Oracle Inventory.

There is two inventory in oracle are Global Inventory ($ORACLE_BASE/OraInventory) and a Local Inventory ($ORACLE_HOME/inventory). If you lost this inventory you can recover by following steps.

1. Find the location of oraInst.loc file which may have different location depending on your OS.

/var/opt/oracle/oraInst.loc file
or
/etc/oraInst.loc

2. Modify the file oraInst.loc file ( before modify take backup of this file).

cp /etc/oraInst.loc /etc/oraInst.loc.bak

mkdir /u01/oracle/oraInventory

cat /etc/oraInst.loc

---file contents---
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall
---file contents---

3. Change the permissions to be appropriate

chmod 644 /etc/oraInst.loc

4. For consistency, copy the file to Oracle home directory

cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak

cp /etc/oraInst.loc $ORACLE_HOME/oraInst.loc

5. Run Oracle Universal Installer from Oracle home as below:

cd /u01/appdr/oracle/product/11.2.0/db_1

./runInstaller -silent -attachHome ORACLE_HOME="/u01/appdr/oracle/product/11.2.0/db_1" ORACLE_HOME_NAME="OraDb11g_home1"

Output should like below:

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 65536 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/appdr/oraInventory

'AttachHome' was successful.

6. Verify oracle patch details 

$ORACLE_HOME/OPatch/opatch lsinventory -detail

ORA-00600: internal error code, arguments: [4194] (Undo corruptions)

Recovery of Online Redo Log: Thread 1 Group 3 Seq 418 Reading mem 0
  Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\BOND\REDO03.LOG
Block recovery completed at rba 418.70.16, scn 0.13842291
Sun Aug 28 15:36:51 2016
Errors in file d:\oracle\product\10.2.0\admin\bond\bdump\bond_smon_4584.trc:
ORA-01595: error freeing extent (3) of rollback segment (5))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [93], [5], [], [], [], [], []

Cause : Undo Corruption.

Solution : Create a new undo tablespace and set it default undo tablespace (undo_tablespace).

1. Create pfile from spfile to edit

 >create pfile from spfile;

2. Shutdown the instance

>shutdown immediate;

3. Set the following parameters in the pfile

  undo_management = 'manual'

  event = '10513 trace name context forever, level 2'

4. Srart database in restrict mode.

 >startup restrict pfile=<initsid.ora>

5. Checking the status of segment.

 >select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

6. Create new undo tablespace

 >create undo tablespace <new undo tablespace> datafile <datafile> size 1000M;

7. Shutdown the database.

 >shutdown immediate;

8. Modify the following parameters in the pfile

*.undo_management='AUTO'

*.undo_tablespace='<new undo tablespace>'

9. Srart the database using the pfile.

 >startup pfile=<initsid.ora>;

10. Create the spfile using pfile.

 > create spfile from pfile;

11. Shutdown the database.

>shutdown immediate;

12. Startup database.

>startup;  --> Using new spfile

 

Log

 

SQL> create pfile from spfile;

File created.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict pfile=D:\ORACLE\PRODUCT\10.2.0\db_1\database\INITbond.ora;

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1292036 bytes

Variable Size             587204860 bytes

Database Buffers           16777216 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

SQL>

SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME                STATUS           SEGMENT_NAME

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

SYSTEM                         ONLINE           SYSTEM

SQL> create undo tablespace UNDOTBS2 datafile 'D:\oracle\product\10.2.0\oradata\bond\UNDOTBS02.DBF' size 2000M;

Tablespace created.

SQL>

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=D:\ORACLE\PRODUCT\10.2.0\db_1\database\INITbond.ora;

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1292036 bytes

Variable Size             587204860 bytes

Database Buffers           16777216 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL> create spfile from pfile;

File created.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1292036 bytes

Variable Size             587204860 bytes

Database Buffers           16777216 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

SQL>

RMAN Backup Strategy Implementation in Production Database

RMAN: Recovery Manager (RMAN) is simple and powerful utility of Oracle database to manage Oracle database backup, restore and recovery. Key parts of maintaining oracle database are ensuring backup, restore and recover database from any disaster.

Full document in Word

Backup Strategy: To ensure high availability, perform easy backup and restore operation we will implement two types of backup, one is Full Backup (Level 0) and another is Incremental (Level 0) backup.

1.      Level 0 backup should run on holiday of each week (Saturday).

2.      Level 1 backup should run on each of week days (Sunday-Friday).

Implementation: To implement RMAN Backup strategy we will perform the following steps.

1.      Configuring RMAN.

2.      Prepare full backup and incremental backup scripts.

3.      Create cron job for performing RMAN Backup.

Configuring RMAN: To take RMAN backup we should run oracle database in archive mode. Perform the following to configure RMAN.

1.      Check database are run in archive mode or not if it is not perform the steps 2.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL>

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     30

Next log sequence to archive   32

Current log sequence           32

SQL>

2.      Enabling archivelog

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

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1291847248 bytes
Database Buffers          838860800 bytes
Redo Buffers                5001216 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

3.      Define the location and size of FRA

SQL> alter system set DB_RECOVERY_FILE_DEST='+FRA' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=100G scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size                  2227984 bytes

Variable Size             822083824 bytes

Database Buffers          436207616 bytes

Redo Buffers                8847360 bytes

Database mounted.

Database opened.

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE

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

db_recovery_file_dest_size           big integer 100G

SQL>

[NB: db_recovery_file_dest_size parameter value are depend on your system how much archive log file are generate per hour]

4.      Now take an RMAN backup using rman utility. In this document we are not using recovery catalog database so that control file will be used to contain catalog information.

[oracle@DBHOST01 ~]$ echo $ORACLE_SID

CBSDB

[oracle@DBHOST01 ~]$ rman target/

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Apr 23 19:11:05 2016

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

connected to target database: CBSDB (DBID=624768357)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ASMDB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_CBSDB.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> backup database;

Starting backup at 23-APR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=46 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/cbsdb/datafile/system.256.880569463

input datafile file number=00002 name=+DATA/cbsdb/datafile/sysaux.257.880569467

input datafile file number=00005 name=+DATA/cbsdb/datafile/example.265.880569629

input datafile file number=00006 name=+DATA/cbsdb/datafile/test_tbs.267.881349853

input datafile file number=00003 name=+DATA/cbsdb/datafile/undotbs1.258.880569467

input datafile file number=00004 name=+DATA/cbsdb/datafile/users.259.880569471

input datafile file number=00007 name=+DATA/cbsdb/datafile/tbs_user_data_1.268.882462007

input datafile file number=00008 name=+DATA/cbsdb/datafile/tbs_user_data_1.269.882458361

channel ORA_DISK_1: starting piece 1 at 23-APR-16

channel ORA_DISK_1: finished piece 1 at 23-APR-16

piece handle=+FRA/cbsdb/backupset/2016_04_23/nnndf0_tag20160423t191539_0.355.909947741 tag=TAG20160423T191539 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45

Finished backup at 23-APR-16

Starting Control File and SPFILE Autobackup at 23-APR-16

piece handle=+FRA/cbsdb/autobackup/2016_04_23/s_909947845.356.909947847 comment=NONE

Finished Control File and SPFILE Autobackup at 23-APR-16

RMAN> LIST BACKUP;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

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

1       98.86M     DISK        00:00:11     28-MAY-15

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150528T171856

Piece Name: +FRA/asmdb/backupset/2016_04_23/nnndf0_tag20160423t191539_0.355.909947741

……….

[NB: We did not configure rman default backup location for that rman send backup file to default location in FRA. We can change default backup location]

5.      This is optional steps for change default backup location.

Configure Control File Backup Destination
=============================================
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman_backup/cf_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman_backup/cf_%F';
new RMAN configuration parameters are successfully stored

Configure Datafile Backup Destination
=============================================
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/rman_backup/df_%d_%t_%s_%p';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   /u02/rman_backup/df_%d_%t_%s_%p';
new RMAN configuration parameters are successfully stored

---- End of RMAN Configuration ----

Prepare full backup and incremental backup scripts: In these steps we will create two shell file to perform full and incremental rman backup.

1.      Full RMAN Backup Scripts.

[oracle@DBHOST01 scripts]$ mkdir /u01/rman_backup/

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ cd /u01/rman_backup/

[oracle@DBHOST01 rman_backup]$

[oracle@DBHOST01 rman_backup]$ mkdir scripts

[oracle@DBHOST01 rman_backup]$ mkdir log

[oracle@DBHOST01 rman_backup]$ cd /u01/rman_backup/scripts/

[oracle@DBHOST01 scripts]$ vi disk_full_db_backup.rcv

[oracle@DBHOST01 scripts]$ cat disk_full_db_backup.rcv

RUN

{

ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df1_%d_%t_%s_%p_%U';

ALLOCATE CHANNEL CH2 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';

BACKUP

INCREMENTAL LEVEL 0

DATABASE;

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP ARCHIVELOG ALL FORMAT '+RMAN_BACKUP/rman_bkp_ar_%d_%t_%s_%p_%U.arch' DELETE INPUT;

BACKUP CURRENT CONTROLFILE FORMAT '+RMAN_BACKUP/rman_bkp_ctl_%d_%t_%s_%p_%U';

BACKUP SPFILE FORMAT '+RMAN_BACKUP/rman_bkp_spf_%d_%s_%T.bak';

}

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ vi disk_run_full_backup.sh

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ cat disk_run_full_backup.sh

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=cbsdb; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

cdate=`date '+%Y%m%d'`

ctime=`date '+%H%M%S'`

logfile=/u01/rman_backup/log/disk_full_db_backup-on-$cdate-at-$ctime.log

rman target sys/oracle  cmdfile '/u01/rman_backup/scripts/disk_full_db_backup.rcv' log $logfile

[oracle@DBHOST01 ~]$

2.      Incremental RMAN Backup Scripts.

[oracle@DBHOST01 scripts]$ vi disk_inc_db_backup.rcv

[oracle@DBHOST01 scripts]$ cat disk_inc _db_backup.rcv

RUN

{

ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df1_%d_%t_%s_%p_%U';

ALLOCATE CHANNEL CH2 TYPE DISK FORMAT '+RMAN_BACKUP/rman_bkp_df2_%d_%t_%s_%p_%U';

BACKUP

INCREMENTAL LEVEL 1

DATABASE;

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP ARCHIVELOG ALL FORMAT '+RMAN_BACKUP/rman_bkp_ar_%d_%t_%s_%p_%U.arch' DELETE INPUT;

BACKUP CURRENT CONTROLFILE FORMAT '+RMAN_BACKUP/rman_bkp_ctl_%d_%t_%s_%p_%U';

BACKUP SPFILE FORMAT '+RMAN_BACKUP/rman_bkp_spf_%d_%s_%T.bak';

}

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ vi disk_run_inc_backup.sh

[oracle@DBHOST01 scripts]$

[oracle@DBHOST01 scripts]$ cat disk_run_inc_backup.sh

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=cbsdb; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

cdate=`date '+%Y%m%d'`

ctime=`date '+%H%M%S'`

logfile=/u01/rman_backup/log/disk_full_db_backup-on-$cdate-at-$ctime.log

rman target sys/oracle  cmdfile '/u01/rman_backup/scripts/disk_inc_db_backup.rcv' log $logfile

[oracle@DBHOST01 ~]$

3.      Create cron job for performing RMAN Backup: Cron is an operating system jobs which run at scheduled time and perform specific action. Now we will set cron job which will perform full back up at Saturday night and incremental backup at every night.

[oracle@DBHOST01 ~]$ crontab -l

no crontab for oracle

[oracle@DBHOST01 ~]$ crontab -e

no crontab for oracle - using an empty one

crontab: installing new crontab

[oracle@DBHOST01 ~]$

[oracle@DBHOST01 ~]$ crontab -l

* 01 * * 0 /u01/rman_backup/scripts/disk_run_full_backup.sh

* 01 * * 5 /u01/rman_backup/scripts/disk_run_inc_backup.sh

* 01 * * 4 /u01/rman_backup/scripts/disk_run_inc_backup.sh

* 01 * * 3 /u01/rman_backup/scripts/disk_run_inc_backup.sh

* 01 * * 2 /u01/rman_backup/scripts/disk_run_inc_backup.sh

* 01 * * 1 /u01/rman_backup/scripts/disk_run_inc_backup.sh

* 01 * * 6 /u01/rman_backup/scripts/disk_run_inc_backup.sh

[oracle@DBHOST01 ~]$

ORA-01172: recovery of thread 1 stuck at block 2438 of file 2

Cause: This error usually means that blocks of file 2 are corrupted, In this case we need to recover this blocks.

Solution : We need to recover all of corrupted block by using data file recovery or Database recovery. In my case I have recover database.

1. Data file recover.

SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>

2. Database Recovery

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
========================================================================

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  1219136 bytes
Variable Size             301991360 bytes
Database Buffers          922746880 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 2438 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
 

ORA-01113: file 5 needs media recovery

SQL> ALTER TABLESPACE TEST_USER ONLINE;
ALTER TABLESPACE TEST_USER ONLINE
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'D:\APP\ORADATA\DBRMAN\TEST_USER01.DBF'


Elapsed: 00:00:00.10
SQL> RECOVER TABLESPACE TEST_USER;
ORA-00279: change 2227017 generated at 12/17/2014 12:21:31 needed for thread 1
ORA-00289: suggestion :
D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000002_0865847632.0001
ORA-00280: change 2227017 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> ALTER TABLESPACE TEST_USER ONLINE;

Tablespace altered.

Elapsed: 00:00:00.46
SQL>

RMAN point in time Restore & Recovery.

1. STARTUP NOMOUNT PFILE='D:\app\oradata\DBRMAN_BACKUP\dbrman_pfile.ora';

2. RESTORE CONTROLFILE FROM 'D:\app\oradata\DBRMAN_BACKUP\BKP_DFDBRMAN_865846921_113_1_3HPPNHK9';

3. ALTER DATABASE MOUNT;

4. RUN
{
  ALLOCATE CHANNEL DISK1 TYPE DISK;
  SET UNTIL TIME "to_date('2014-12-09:09:02:00', 'yyyy-mm-dd:hh24:mi:ss')";
  RESTORE DATABASE;
  RECOVER DATABASE;
};

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN
C:\Users\rajib.pradhan>RMAN TARGET/
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 9 09:03:41 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBRMAN (DBID=3796675194)
RMAN> SHUTDOWN IMMEDIATE;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP NOMOUNT PFILE='D:\app\oradata\DBRMAN_BACKUP\dbrman_pfile.ora';
connected to target database (not started)
Oracle instance started
Total System Global Area     535662592 bytes
Fixed Size                     1375792 bytes
Variable Size                377487824 bytes
Database Buffers             150994944 bytes
Redo Buffers                   5804032 bytes
RMAN> RESTORE CONTROLFILE FROM 'D:\app\oradata\DBRMAN_BACKUP\BKP_DFDBRMAN_865846921_113_1_3HPPNHK9';
Starting restore at 09-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\APP\ORADATA\DBRMAN\CONTROL01.CTL
Finished restore at 09-DEC-14
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
RMAN> RUN
2> {
3>   ALLOCATE CHANNEL DISK1 TYPE DISK;
4>   SET UNTIL TIME "to_date('2014-12-09:09:02:00', 'yyyy-mm-dd:hh24:mi:ss')";
5>   RESTORE DATABASE;
6>   RECOVER DATABASE;
7> };
allocated channel: DISK1
channel DISK1: SID=63 device type=DISK
executing command: SET until clause
Starting restore at 09-DEC-14
Starting implicit crosscheck backup at 09-DEC-14
Crosschecked 24 objects
Finished implicit crosscheck backup at 09-DEC-14
Starting implicit crosscheck copy at 09-DEC-14
Finished implicit crosscheck copy at 09-DEC-14
searching for all files in the recovery area
cataloging files...
no files cataloged

channel DISK1: starting datafile backup set restore
channel DISK1: specifying datafile(s) to restore from backup set
channel DISK1: restoring datafile 00001 to D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
channel DISK1: restoring datafile 00002 to D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
channel DISK1: restoring datafile 00003 to D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
channel DISK1: restoring datafile 00004 to D:\APP\ORADATA\DBRMAN\USERS01.DBF
channel DISK1: restoring datafile 00005 to D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
channel DISK1: restoring datafile 00006 to D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
channel DISK1: restoring datafile 00007 to D:\APP\ORADATA\DBRMAN\UNDOTBS02.DBF
channel DISK1: restoring datafile 00010 to D:\APP\ORADATA\DBRMAN\EXAMPLE01.DBF
channel DISK1: reading from backup piece D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_865846474_112_1_3GPPNH6A
channel DISK1: piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_865846474_112_1_3GPPNH6A tag=TAG20141209T085434
channel DISK1: restored backup piece 1
channel DISK1: restore complete, elapsed time: 00:01:55
Finished restore at 09-DEC-14
Starting recover at 09-DEC-14
starting media recovery
archived log for thread 1 with sequence 218 is already on disk as file D:\APP\ORADATA\DBRMAN\REDO02.LOG
archived log file name=D:\APP\ORADATA\DBRMAN\REDO02.LOG thread=1 sequence=218
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-DEC-14
released channel: DISK1
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
RMAN>