To recover when you’ve lost all members of an inactive redo log group

To recover when you’ve lost all members of an inactive redo log group, perform the following steps:

• ·         Verify that all members of a group have been damaged.
• ·         Verify that the log group status is INACTIVE.
• ·         Re-create the log group with the clear logfile command.
• ·         If the re-created log group has not been archived, then immediately back up your database.

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER
---------- ---------- ---------------- -------------------------------------------------- -------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.

SQL> ! rm /data01/app/oracle/datafiles/racdb/redo012.log

SQL> ! rm /data01/app/oracle/datafiles/racdb/redo01.log

SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> / -----------------------> hanged

If all members of an online redo log group are damaged, you won’t be able to open your database. In this situation, Oracle will allow you to only mount your database. Inspect your alert.log file, and verify that all members of a redo log group are damaged.

Alert log :
========
Sun Jun 07 15:18:13 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc2_5173.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc2_5173.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313


SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Next, run the following query to verify that the damaged log group is INACTIVE and determine whether it has been archived:

SQL> select group#, status, archived, thread#, sequence# from v$log;

If the status is INACTIVE, then this log group is no longer needed for crash recovery .Therefore, you can use the clear logfile command to re-create all members of a log group. The following example re-creates all log members of group 1:

SQL> alter database clear logfile group 1;

If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:

SQL> alter database clear unarchived logfile group 1;

SQL> select group#, status, archived, thread#, sequence# from v$log;

    GROUP# STATUS     ARC    THREAD#  SEQUENCE#
---------- ---------------- --- ---------- ----------
 1 INACTIVE     NO  1    19
 2 INACTIVE     NO  1    20
 3 CURRENT     NO  1    21


SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance racdb (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.log'
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'


SQL> alter database clear unarchived logfile group 1;

Database altered.

The clear logfile will drop and re-create all members of a log group for you. You can issue this
command even if you have only two log groups in your database.

If the online redo log group has not been archived, then it may be required for media recovery. In this case, use the clear unarchived logfile command to re-create the logfile group members. Back up your database as soon as possible in this situation.

The unarchived log group may be needed for media recovery if the last database backups were taken before the redo information in the log was created. This means if you attempt to perform media recovery, you won’t be able to recover any information in the damaged log file or any transactions that were created after that log. If the clear logfile command does not succeed because of an I/O error and it’s a permanent problem, then you will need to consider dropping the log group and re-creating it in a different location.

Media failure with one member of a multiplexed online non-current redo log group

If your online redo log file members are multiplexed, the log writer will continue to function as long as it can successfully write to one member of the current log group. If the problem is temporary, then as soon as the online redo log file becomes available, the log writer will start to write to the online redo log file as if there was never an issue. If the media failure is permanent (such as a bad disk), then you’ll need to replace the disk and drop and re-create the bad member to its original location. If you don’t have the option of replacing the bad disk, then you’ll need to drop the bad member and re-create it in an alternate location.

For permanent media failures, here are the instructions for dropping and re-creating one member of an online redo log group:

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER              FILESTATUS
---------- ---------- ---------------- ---------------------------------------------------------------------- -------------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log
2    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo03.log

rm /data01/app/oracle/datafiles/racdb/redo012.log -----> Drop a redo from group1 which is inactive and have one more multiplexed member .

Do few log switch and check the status again after drop :

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER      FILESTA
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log      INVALID
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.


Alert log :
========
Sun Jun 07 14:43:55 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc3_4548.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc3_4548.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Archived Log entry 12 added for thread 1 sequence 13 ID 0x346a8c39 dest 1:
Sun Jun 07 14:43:56 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_m000_5083.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures


SQL> alter database add logfile member '/data01/app/oracle/datafiles/racdb/redo012.log' reuse to group 1 ;

Database altered.

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

GROUP# THREAD# GROUPSTATUS      MEMBER  FILESTATUS
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
 1    1 INACTIVE      /data01/app/oracle/datafiles/racdb/redo01.log
1    1  INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log         INVALID
2    1 CURRENT      /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT      /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.

SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER   FILESTA
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.

Log-miner and Incomplete recovery

Problem Statement : Someone truncated a table accidentally and you want the data back . You are using oracle 11g .

Things you need to do get data back truncated data :
------------------------------------------------------------------
1. You should know the exact time or scn at which table got truncated . Logminer can help you here .
2. You need to perform incomplete recovery until scn at some test instance other than production
3. once your table is back on test instance just export it and import on production .


SQL> archive log list ;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     73
Next log sequence to archive   75
Current log sequence       75
SQL>
SQL> conn sumit/sumit
Connected.
SQL> create table tab(i int) ;

Table created.

SQL> insert into tab select 1 from dual ;

1 row created.

SQL> commit ;

Commit complete.

SQL> update tab set i=5 where i=1 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> insert into tab select 1 from dual ;

1 row created.

SQL> commit ;

Commit complete.

SQL> alter system switch logfile ;

System altered.

SQL> commit ;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> update sumit.tab set i=7 ;

2 rows updated.

SQL> commit ;

Commit complete.

SQL> truncate table sumit.tab ;

Table truncated.

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
0

SQL> commit ;

Commit complete.

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select name from v$archived_log ;

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

+DATA/orcl/archivelog/2015_06_06/thread_1_seq_73.288.881698907
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_77.269.881699637
+DATA/orcl/archivelog/2015_06_06/thread_1_seq_78.268.881699639

Determine the SCN for the truncate statement using logminer 

exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_73.288.881698907',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_77.269.881699637',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2015_06_06/thread_1_seq_78.268.881699639',options=>dbms_logmnr.addfile);

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);


COLUMN username   FORMAT A8
COLUMN operation  FORMAT A9
COLUMN sql_redo   FORMAT A50 WORD_WRAPPED
COLUMN sql_undo   FORMAT A50 WORD_WRAPPED
COLUMN timestamp  FORMAT A20

SELECT  username , operation, sql_redo, sql_undo, TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') timestamp, scn FROM v$logmnr_contents WHERE table_name='TAB'

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
0

Perform incomplete recovery until scn 1204306 on a TEST instance :

RMAN> run {
 restore database until scn 1204306 ;
 recover database until scn 1204306 ;
 }

Starting restore at 06-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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 +DATA/orcl/datafile/system.276.875242649
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.277.875242649
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.278.875242649
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.279.875242649
channel ORA_DISK_1: restoring datafile 00005 to +DATA2/orcl/datafile/users.294.875976397
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/user02.dbf
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2015_06_06/nnndf0_tag20150606t202335_0.270.881699015
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2015_06_06/nnndf0_tag20150606t202335_0.270.881699015 tag=TAG20150606T202335
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 06-JUN-15

Starting recover at 06-JUN-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 74 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115
archived log for thread 1 with sequence 75 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583
archived log for thread 1 with sequence 76 is already on disk as file +DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_74.272.881699115 thread=1 sequence=74
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_75.274.881699583 thread=1 sequence=75
archived log file name=+DATA/orcl/archivelog/2015_06_06/thread_1_seq_76.275.881699635 thread=1 sequence=76
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-JUN-15

SQL> alter database open resetlogs;

Database altered.

Check the data , now you just need to export the tab table and import in prod server :

SQL> select count(*) from sumit.tab ;

  COUNT(*)
----------
2

ORA-38760: This database instance failed to turn on flashback database

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

The flashback recovery area has run out of space. You see a message in the alert log similar to
the following:

Can not open flashback thread because there is no more space in flash recovery area

If the database has aborted earlier because of any flashback errors and you attempt to
start it, you get the following error:

You want to correct the problem, or at least shut the flashback down so that the normal
database operations can continue.

There are three solutions. Which to choose depends upon the nature of the emergency
and the resources you have at your disposal.

Solution 1 :
--------------
You can increase the size of the flashback area dynamically. To increase it to, say, 10GB, you
would issue the following:

SQL> alter system set db_recovery_file_dest_size = 10G;

Solution 2: Remove Restore Points

The alternative to increasing the size of the flashback area is to remove some of the older restore points that you no longer need. The following is a query to list the restore points you currently have:

SQL> col name format a25
SQL> select name, storage_size from v$restore_point;

NAME STORAGE_SIZE
------------------------- ------------
RP0 207028224
RP1 0
RP2 915701760
PRE_TEST1 0

These results show that restore points RP0 and RP2 have storage associated with them.
This is because they are guaranteed restore points You should remove them to make some room in the flash recovery area. To remove a restore point, issue a drop restore point command:

SQL> drop restore point rp2;
Restore point dropped.

SQL> drop restore point rp0;
Restore point dropped.

Restore points are created by executing SQL statements such as the following:

SQL> create restore point rp1;

This statement creates a named point in time to which you can flash back the database,
through the SQL statement (provided, of course, that you have turned on the flashback for the
database). Once you have a restore point, you can rewind or flash back to that point in time
using a statement such as this:
SQL> flashback database to rp1;

There are two types of restore points—normal and guaranteed. The preceding example of
creating a restore point creates a normal one. You may be able to flash back to that point, provided
enough flashback logs are available. If the flashback logs are not available (perhaps
because the space in the flashback recovery area ran out and Oracle had to delete some flashback
logs to make room for the newer occupants), then your flashback operation will fail. The
solution—a guaranteed restore point. To create a guaranteed restore point, you will have to
specifically ask for the guarantee:

A guaranteed restore point stores information needed to flash back in a special way. When
space pressures in the flash recovery area force the database to remove the unneeded files,
flashback logs are the first to go, unless these are for a guaranteed restore point. The flashback
logs of the guaranteed restore points are stored even when the flash recovery area runs out of
space. The only way to reclaim the space is to drop the guaranteed restore point. Dropping the
guaranteed restore points frees up that space.

Dropping restore point should clear up space, and you may be able to start the database.

Solution 3: Disable Flashback

If solutions 1 and 2 fail or are not applicable, you may want to disable flashback in the database
temporarily. First shut down the database (if not down already):

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

SQL> startup mount

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.

Then disable flashback in the database:

SQL> alter database flashback off;
Database altered.

This will stop the flashback operations and will stop generating flashback logs. This
should reduce the space requirement on the flash recovery area. To free up some space, you
may want to delete some more files such as archived redo logs, unneeded backups, and so on.

RMAN-06495: must explicitly specify DBID with SET DBID command

Your backup strategy doesn’t take advantage of either a flash recovery area or a recovery catalog.
You are trying to restore a control file as follows, and you receive an error message stating
that you must explicitly set the database identifier (DBID):

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;


Error : RMAN-06495: must explicitly specify DBID with SET DBID command


You don’t know the DBID for your database, and you aren’t sure how to find the DBID.
Without a control file for your database, you can’t mount the database and query the DBID
value from the V$DATABASE view.

Solution

RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506593;

RMAN> restore controlfile from autobackup;

You can determine the DBID of your database in one of the following ways:

• You can derive the DBID from an autobackup file.
• You can retrieve the DBID from RMAN output.
• You can write the DBID to the alert.log file.
• You can derive DBID from a file dump.

Description of %F Format Variable
------------------------------------------------

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

c-IIIIIIIIII-YYYYMMDD-QQ

Retrieving the DBID from RMAN Output

RMAN> connect target /
connected to target database: BRDSTN(DBID=2601506593)

Writing the DBID to the Alert.log File

Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:

COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));

After running the previous code, you should see a text message in your target database
alert.log file that looks like this:
DBID: 2601506593

The KSDWRT procedure writes a text message to your database alert.log file. In this case,
the hold_dbid SQL variable is populated with the DBID. If you write your target database DBID
to the alert.log file on a regular basis, you should be able to identify it easily should the need
arise.

Dumping Files

If any of the datafiles, online redo log files, or archived redo log files are physically available,
you can use the SQL alter system dump statement to write the DBID to a trace file. Your database
does not have to be mounted for this to work. For example, here is the syntax for taking a
datafile dump:

SQL> connect / as sysdba
SQL> startup nomount;
SQL> alter system dump datafile '/<PATH>/system01.dbf' block min 1 block max 10;

Use this syntax to take a dump of an archived redo log file or online redo log file:

SQL> alter system dump logfile '<log file name>';
The trace file with the DBID will be in your user dump destination. If you search for the
string “Db ID,” you should find something similar to this output:
Db ID=2601506593=0x9b0fd721, Db Name='BRDSTN'

Every Oracle database has an internal unique DBID that can be queried from V$DATABASE
as follows:

SQL> select dbid from v$database;
DBID
------------------------------
2601506593

ASMCMD - New commands in 11gR1

The ASMCMD cp option allows you to copy files between ASM disk groups and OS file systems and between two ASM servers. 

The following file copy can be performed 

- ASM Diskgroup to OS file system 
- OS file system to ASM Diskgroup 
- ASM Diskgroup to another diskgroup
ASM Diskgroup to OS File system 

ASMCMD> cp +DGEXT/orcl/datafile/tbsext.256.628847401 /home/oracle/tbsext.dbf 
source +DGEXT/orcl/datafile/tbsext.256.628847401 
target /home/oracle/tbsext.dbf 
copying file(s)... 
file, /home/oracle/tbsext.dbf, copy committed. 

OS File system to ASM Diskgroup 

ASMCMD> cp /home/oracle/tbsext.dbf +DGEXTBK/prod/datafile/tbsext.dbf 
source /home/oracle/tbsext.dbf 
target +DGEXTBK/prod/datafile/tbsext.dbf 
copying file(s)... 
file, +DGEXTBK/prod/datafile/tbsext.dbf, copy committed.

SQL> create diskgroup dgext external redundancy disk '/dev/raw/raw1','/dev 
/raw/raw2'; 
Diskgroup created. 

SQL> create diskgroup DGEXTBK external redundancy disk '/dev/raw/raw3','/dev/raw /raw4'; 
Diskgroup created. 

ASMCMD> cd DGEXTBK 
ASMCMD> mkdir prod 
ASMCMD> cd prod 
ASMCMD> mkdir datafile 
ASMCMD> cd datafile 
ASMCMD> pwd 
+DGEXTBK/prod/datafile 

SQL> create tablespace TBSEXT datafile '+DGEXT' size 10m; 
Tablespace created. 

SQL> select name from v$datafile; 

NAME 
------------------------------------------------------------- 
+DGEXT/orcl/datafile/tbsext.256.628847401
cp +DGEXT/orcl/datafile/tbsext.256.628847401 +DGEXTBK/prod/datafile/tbsext  

ASMCMD> cp +DGEXT/orcl/datafile/tbsext.256.628847401 +DGEXTBK/prod/datafile/tbsext  
source +DGEXT/orcl/datafile/tbsext.256.628847401  
target +DGEXTBK/prod/datafile/tbsext  
copying file(s)...file, +DGEXTBK/prod/datafile/tbsext, copy committed.  


The tbsext is alias name created in the folder +DGEXTBK/prod/datafile.

SMCMD md_backup and md_restore 

Disk group to be backed up: DGEXT 

The full mode restores the diskgroup exactly as it was at the time of backup

ASMCMD> md_restore -b dgbk -t full -g dgext 

Current Diskgroup being restored: DGEXT 

ASMCMD-09352: CREATE DISKGROUP failed 

ORA-15018: diskgroup cannot be created 

ORA-15030: diskgroup name "DGEXT" is in use by another diskgroup (DBD ERROR: OCIStmtExecute) 

Diskgroup dropped. 

Current Diskgroup being restored: DGEXT 

Diskgroup DGEXT created! 

System template XTRANSPORT modified! 

System template ONLINELOG modified! 

System template DATAGUARDCONFIG modified! 

System template AUTOBACKUP modified! 

System template TEMPFILE modified! 

System template ARCHIVELOG modified! 

System template ASM_STALE modified! 

System template BACKUPSET modified! 

System template DUMPSET modified! 

System template FLASHBACK modified! 

System template PARAMETERFILE modified! 

System template CONTROLFILE modified! 

System template DATAFILE modified! 

System template CHANGETRACKING modified! 

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

           1 DGEXT                EXTERN

The 'nodg' mode restore the attributes,templates, and alias directory structure specified in the backup file to an existing disk group.

ASMCMD> md_restore -b dgbk -t nodg -g dgext 

Current Diskgroup being restored: DGEXT 

System template XTRANSPORT modified! 

System template ONLINELOG modified! 

System template DATAGUARDCONFIG modified! 

System template AUTOBACKUP modified! 

System template TEMPFILE modified! 

System template ARCHIVELOG modified! 

System template ASM_STALE modified! 

System template BACKUPSET modified! 

System template DUMPSET modified! 

System template FLASHBACK modified! 

System template PARAMETERFILE modified! 

System template CONTROLFILE modified! 

System template DATAFILE modified! 

System template CHANGETRACKING modified!

The 'newdg' mode allows the user to override the disk group name,disk, and failgroup specifications as part of a diskgroup creation,but retains 

the attribute,template and, alias directory structure from the backup

ASMCMD>  md_restore -b dgbk -t newdg -o 'DGEXT:DG' 

Current Diskgroup being restored: DGEXT 

Current Diskgroup name replace by: DG 

Diskgroup DG created! 

System template XTRANSPORT modified! 

System template ONLINELOG modified! 

System template DATAGUARDCONFIG modified! 

System template AUTOBACKUP modified! 

System template TEMPFILE modified! 

System template ARCHIVELOG modified! 

System template ASM_STALE modified! 

System template BACKUPSET modified! 

System template DUMPSET modified! 

System template FLASHBACK modified! 

System template PARAMETERFILE modified! 

System template CONTROLFILE modified! 

System template DATAFILE modified! 

System template CHANGETRACKING modified!

SQL> select group_number,name,type from v$asm_diskgroup 

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

           1 DG                   EXTERN

How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command

SQL> select file_id , file_name from dba_data_files ;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
4 +DATA/orcl/datafile/users.279.875242649
3 +DATA/orcl/datafile/undotbs1.278.875242649
2 +DATA/orcl/datafile/sysaux.277.875242649
1 +DATA/orcl/datafile/system.276.875242649
5 +DATA2/orcl/datafile/users.294.875976397
6 /data01/app/oracle/product/11.2.0.3/db_1/dbs/user02.dbf

6 rows selected.


SQL> alter system switch logfile;

System altered.

SQL> alter database datafile 6 offline;

Database altered.

SQL> select file_name, file_id, online_status from dba_data_files where file_id=6;

FILE_NAME                           FILE_ID   ONLINE_STATUS
---------------------------------------------------------------------------------------------------- ------
/data01/app/oracle/product/11.2.0.3/db_1/dbs/user02.dbf      6      RECOVER


ASMCMD> cp /data01/app/oracle/product/11.2.0.3/db_1/dbs/user02.dbf +DATA/ORCL/DATAFILE/user02.dbf
copying /data01/app/oracle/product/11.2.0.3/db_1/dbs/user02.dbf -> +DATA/ORCL/DATAFILE/user02.dbf

MOUNTED  EXTERN  N      DATA2/
ASMCMD> cd DATA/ORCL/DATAFILE
ASMCMD> pwd
+DATA/ORCL/DATAFILE
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
                                            N    user02.dbf => +DATA/ASM/DATAFILE/user02.dbf.258.880490555
DATAFILE  UNPROT  COARSE   MAY 23 20:00:00  Y    USERS.279.875242649
DATAFILE  UNPROT  COARSE   MAY 23 20:00:00  Y    UNDOTBS1.278.875242649
DATAFILE  UNPROT  COARSE   MAY 23 20:00:00  Y    SYSTEM.276.875242649
DATAFILE  UNPROT  COARSE   MAY 23 20:00:00  Y    SYSAUX.277.875242649

SQL> select file_name, file_id, online_status from dba_data_files where file_id=6 ;

FILE_NAME                    FILE_ID ONLINE_STATUS
--------------------------------------------------------------------------
+DATA/orcl/datafile/user02.dbf 6     ONLINE    

SQL> select file_name, file_id, online_status from dba_data_files
  FILE_ID FILE_NAME
---------- -------------------------------------------------------------
4 +DATA/orcl/datafile/users.279.875242649
3 +DATA/orcl/datafile/undotbs1.278.875242649
2 +DATA/orcl/datafile/sysaux.277.875242649
1 +DATA/orcl/datafile/system.276.875242649
5 +DATA2/orcl/datafile/users.294.875976397
6 +DATA/orcl/datafile/user02.dbf

How to Check Clusterware Version and Name

To check name of cluster and version :

/app/grid/product/11.2.0.4/grid/bin/cemutlo -n

node-cluster01

/app/grid_base>crsctl query crs softwareversion

Oracle Clusterware version on node [node1] is [12.1.0.2.0]

/app/grid_base>crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [12.1.0.2.0]

RMAN Recovery catalog

SQL> create tablespace cattbs datafile '/data01/app/oracle/datafiles/racdb/catalog.dbf' size 500M ;

Tablespace created.

SQL> create user rman identified by rman default tablespace cattbs quota unlimited on cattbs;

User created.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

RMAN> connect catalog rman/rman

connected to recovery catalog database

[oracle@DB11G dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 20 09:38:17 2015

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

connected to target database: RACDB (DBID=876245389)

RMAN> connect catalog rman/rman

connected to recovery catalog database

RMAN> list incarnation ;


RMAN> list incarnation ;


RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       14      RACDB    876245389        PARENT  1          17-SEP-11
1       2       RACDB    876245389        CURRENT 995548     02-MAY-15


Cataloging Older Files

RMAN> catalog datafilecopy '/u01/app/oracle/users01.dbf';
cataloged datafile copy
datafile copy filename=/u01/app/oracle/users01.dbf recid=2 stamp=604202000
RMAN>

RMAN> catalog backuppiece '/disk1/backups/backup_820.bkp';
RMAN> catalog archivelog '/disk1/arch_logs/archive1_731.dbf',
'/disk1/arch_logs/archive1_732.dbf';

The files you want to catalog can exist only on disk and not on tape, and they must belong
to one of the following types:
• Datafile copy
• Control file copy
• Archived redo log
• Backup piece

Cataloging a Datafile Copy As an Incremental Backup
====================================================

RMAN> catalog datafilecopy '?/oradata/users01.bak' level 0;

Once you catalog a datafile copy as a level 0 backup, you can then perform an incremental backup by using that copy as your base.

RMAN> catalog start with '/disk1/arch_logs/';
RMAN> catalog start with '/disk1/arch_logs/' noprompt;

Updating the Recovery Catalog

RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
RMAN>

Undo tablespace datafile dropped or corrupted

SQL> select file_name from dba_data_files ; 

FILE_NAME
--------------------------------------------------------------------------------
/data01/app/oracle/datafiles/racdb/users01.dbf
/data01/app/oracle/datafiles/racdb/undotbs01.dbf
/data01/app/oracle/datafiles/racdb/sysaux01.dbf
/data01/app/oracle/datafiles/racdb/system01.dbf

SQL>
SQL>
SQL>rm /data01/app/oracle/datafiles/racdb/undotbs01.dbf

Database will crash here with below alert log error . 

Alert Log:
===========
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_dbw0_3249.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/data01/app/oracle/datafiles/racdb/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_ora_3364.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/data01/app/oracle/datafiles/racdb/undotbs01.dbf'
ORA-1157 signalled during: alter database open ...

[oracle@DB11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 18 07:35:15 2015

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

Connected to an idle instance.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area  563728384 bytes
Fixed Size    2230272 bytes
Variable Size  171968512 bytes
Database Buffers  385875968 bytes
Redo Buffers    3653632 bytes
Database mounted.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/data01/app/oracle/datafiles/racdb/undotbs01.dbf'

SQL> alter database datafile  '/data01/app/oracle/datafiles/racdb/undotbs01.dbf' offline drop ;

SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shu immediate ;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

----> we will resolve this error later first create new undo and start database asap ,

SQL> drop tablespace undotbs1; 
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1240252155$' found, terminate
dropping tablespace

SQL> create UNDO tablespace undotbs2 datafile '/data01/app/oracle/datafiles/racdb/undotbs03.dbf' size 50M ;

Tablespace created.

SQL>  alter system set undo_management = auto scope=spfile;

System altered.

SQL> show parameter undo ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management     string MANUAL
undo_retention     integer 900
undo_tablespace     string UNDOTBS1


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

Total System Global Area  563728384 bytes
Fixed Size    2230272 bytes
Variable Size  171968512 bytes
Database Buffers  385875968 bytes
Redo Buffers    3653632 bytes
Database mounted.
Database opened.
SQL> Alter system set undo_tablespace=undotbs2 ;

System altered.

SQL> show parameter undo ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management     string AUTO
undo_retention     integer 900
undo_tablespace     string UNDOTBS2

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1240252155$' found, terminate
dropping tablespace


SQL> drop rollback segment '_SYSSMU1_1240252155$' ;
drop rollback segment '_SYSSMU1_1240252155$'
                      *
ERROR at line 1:
ORA-02175: invalid rollback segment name

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status like '%RECOVERY%' ;

SEGMENT_NAME           STATUS   TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_1240252155$   NEEDS RECOVERY UNDOTBS1
_SYSSMU2_1240252155$   NEEDS RECOVERY UNDOTBS1
_SYSSMU3_4004931649$       NEEDS RECOVERY UNDOTBS1
_SYSSMU4_1126976075$       NEEDS RECOVERY UNDOTBS1
_SYSSMU5_4011504098$       NEEDS RECOVERY UNDOTBS1
_SYSSMU6_3654194381$       NEEDS RECOVERY UNDOTBS1
_SYSSMU7_4222772309$       NEEDS RECOVERY UNDOTBS1
_SYSSMU8_3612859353$       NEEDS RECOVERY UNDOTBS1
_SYSSMU9_3945653786$       NEEDS RECOVERY UNDOTBS1
_SYSSMU10_3271578125$       NEEDS RECOVERY UNDOTBS1



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

Total System Global Area  563728384 bytes
Fixed Size    2230272 bytes
Variable Size  171968512 bytes
Database Buffers  385875968 bytes
Redo Buffers    3653632 bytes
Database mounted.
Database opened.
SQL> create pfile from spfile ;

File created.

Edit pfile and start instance using pfile :

*._offline_rollback_segments=(_SYSSMU1_1240252155$,_SYSSMU2_111974964$,_SYSSMU3_4004931649$,_SYSSMU4_1126976075$,_SYSSMU5_4011504098$,_SYSSMU6_3654194381$,_SYSSMU7_4222772309$,_SYSSMU8_3612859353$,_SYSSMU9_3945653786$,_SYSSMU10_3271578125$)


SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create spfile from pfile ;

File created.

SQL> select segment_name,status,tablespace_name from dba_rollback_segs ;

SEGMENT_NAME       STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM       ONLINE SYSTEM
_SYSSMU30_2181141343$       ONLINE UNDOTBS2
_SYSSMU29_4137772411$       ONLINE UNDOTBS2
_SYSSMU28_1117717027$       ONLINE UNDOTBS2
_SYSSMU27_1646655687$       ONLINE UNDOTBS2
_SYSSMU26_73850059$       ONLINE UNDOTBS2
_SYSSMU25_1059101193$       ONLINE UNDOTBS2
_SYSSMU24_1011611768$       ONLINE UNDOTBS2
_SYSSMU23_1356515410$       ONLINE UNDOTBS2
_SYSSMU22_2758924745$       ONLINE UNDOTBS2
_SYSSMU21_480817652$       ONLINE UNDOTBS2

11 rows selected.

SQL> show parameter undo ; 

NAME     TYPE VALUE

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

undo_management     string AUTO

undo_retention     integer 900

undo_tablespace     string UNDOTBS2

Create spfile from pfile bounce db and start it using spfile now .