Friday, February 28, 2014

Parallel Backup of the Same Datafile In Oracle 11g Release 2 (Using RMAN)



You probably already know that you can parallelize the backup by declaring more than one channel so that each channel becomes a RMAN session. However, very few realize that each channel can back up only one datafile at a time. So even through there are several channels, each datafile is backed by only one channel, somewhat contrary to the perception that the backup is truly parallel.

In Oracle Database 11g RMAN, the channels can break the datafiles into chunks known as "sections." You can specify the size of each section. Here's an example:

[oracle@dg1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 28 01:38:21 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> run {
2> allocate channel c1 type disk format '/tmp/backup/%U';
3> allocate channel c2 type disk format '/tmp/backup/%U';
4> backup section size 100M datafile 1;
5> release channel c1;
6> release channel c2;
7> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=19 device type=DISK

allocated channel: c2
channel c2: SID=36 device type=DISK

Starting backup at 28-FEB-14
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/OCP/system01.dbf
backing up blocks 1 through 12800
channel c1: starting piece 1 at 28-FEB-14
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current control file in backup set
channel c2: starting piece 1 at 28-FEB-14
channel c2: finished piece 1 at 28-FEB-14
piece handle=/tmp/backup/0ep1nejf_1_1 tag=TAG20140228T013958 comment=NONE
..........
channel c2: finished piece 1 at 28-FEB-14
piece handle=/tmp/backup/0mp1nekn_1_1 tag=TAG20140228T013958 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 8 at 28-FEB-14
piece handle=/tmp/backup/0dp1nejf_8_1 tag=TAG20140228T013958 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 28-FEB-14

released channel: c1

released channel: c2

This RMAN command allocates two channels and backs up the SYSTEM' tablespace in parallel on two channels. Each channel takes a 100MB section of the datafile and backs it up in parallel. This makes backup of large files faster.

When backed up this way, the backups show up as sections as well.
RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    1.55G      DISK        00:02:27     27-FEB-14
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20140227T232051
        Piece Name: /tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6611490    27-FEB-14 /u01/app/oracle/oradata/OCP/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    653.19M    DISK        00:00:41     28-FEB-14
  List of Datafiles in backup set 13
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6664196    28-FEB-14 /u01/app/oracle/oradata/OCP/system01.dbf

  Backup Set Copy #1 of backup set 13
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:41     28-FEB-14       NO         TAG20140228T013958

    List of Backup Pieces for backup set 13 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    13      1   AVAILABLE   /tmp/backup/0dp1nejf_1_1
    14      2   AVAILABLE   /tmp/backup/0dp1nejf_2_1
    15      3   AVAILABLE   /tmp/backup/0dp1nejf_3_1
    16      4   AVAILABLE   /tmp/backup/0dp1nejf_4_1
    17      5   AVAILABLE   /tmp/backup/0dp1nejf_5_1
    19      6   AVAILABLE   /tmp/backup/0dp1nejf_6_1
    18      7   AVAILABLE   /tmp/backup/0dp1nejf_7_1
    21      8   AVAILABLE   /tmp/backup/0dp1nejf_8_1

Note how the pieces of the backup show up as sections of the file. As each section goes to a different channel, you can define them as different mount points (such as /tmp/backup and /tmp/backup), you can back them to tape in parallel as well.

However, if the large file #1 resides on only one disk, there is no advantage to using parallel backups. If you section this file, the disk head has to move constantly to address different sections of the file, outweighing the benefits of sectioning.

Note:  For more details contact dbaclass4u@gmail.com


DATA RECOVERY ADVISOR feature in Oracle 11g Release 2

Hi Guys,

Today I would like to post  about new feature in RMAN (Oracle 11g Release 2).     Earlier if we lost any datafile or control file we have to manually find it and restore it.   Sometimes it looks very bad time when we are trying to restore database in point-in-time-recovery.   Its due to we may put wrong time or SCN to restore the database .

Now Data Recovery Advisor is reducing our manual work as well as it will restore it database back to its last successfully working conditon.

Please see below how it works


Case 1: If you lost the non- mandatory one or more datafiles( means other than SYSTEM,SYSAUX, UNDO ).

step1 :
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:14:47 2014

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

Enter user-name: /as sysdba

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

SQL> create user dra identified by dra;

User created.

SQL> grant connect,resource to dra;

Grant succeeded.

SQL> alter user dra default tablespace users;

User altered.

SQL>connect dra
password *****

SQL> insert into TEST select rownum,'AMK',mod(rownum,7)+2 from dual  connect by level<10000;

9999 rows created.

SQL> insert into TEST select rownum,'AMK',mod(rownum,7)+2 from dual  connect by level<100000;

99999 rows created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step2 :  Taking database backup
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:20:23 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> backup database to destination '/tmp/backup';

Starting backup at 27-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/OCP/mgmt.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/OCP/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/OCP/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/OCP/undotbs01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/OCP/rman01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/OCP/mgmt_ad4j.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/OCP/stab01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/OCP/mgmt_ecm_depot1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/OCP/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-FEB-14

channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp tag=TAG20140227T232051 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_ncsnf_TAG20140227T232051_9jz0o185_.bkp tag=TAG20140227T232051 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-14

RMAN>
RMAN> exit

Step 3:  Removing datafile


[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:23:38 2014

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

Enter user-name: /as sysdba

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/OCP/system01.dbf
/u01/app/oracle/oradata/OCP/sysaux01.dbf
/u01/app/oracle/oradata/OCP/undotbs01.dbf
/u01/app/oracle/oradata/OCP/users01.dbf
/u01/app/oracle/oradata/OCP/mgmt_ecm_depot1.dbf
/u01/app/oracle/oradata/OCP/mgmt.dbf
/u01/app/oracle/oradata/OCP/mgmt_ad4j.dbf
/u01/app/oracle/oradata/OCP/stab01.dbf
/u01/app/oracle/oradata/OCP/rman01.dbf

9 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rm /u01/app/oracle/oradata/OCP/users01.dbf

Step 4:  Error 

[oracle@prodserver ~]$ sqlplus dra/dra

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:24:45 2014

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


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


SQL> create table test1 as select * from test;
create table test1 as select * from test
                                    *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/OCP/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory

Additional information: 3


Step 5:  How to recover it using DRA

oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:26:23 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
262        HIGH     OPEN      27-FEB-14     One or more non-system datafiles are missing

RMAN> advise failure 262;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
262        HIGH     OPEN      27-FEB-14     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/OCP/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_2366581336.hm

RMAN> repair failure preview;        --Note it will show preview only based on advise failure command.  If you have multiple issues, advise failure for a particular issue then repair failure works only based on advise failure.

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_2366581336.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

RMAN> repair failure;     

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_2366581336.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 27-FEB-14
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 00004 to /u01/app/oracle/oradata/OCP/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp
channel ORA_DISK_1: piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp tag=TAG20140227T232051
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 27-FEB-14

Starting recover at 27-FEB-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 27-FEB-14

sql statement: alter database datafile 4 online
repair failure complete

Step 6:  Final confirmation
[oracle@prodserver ~]$ sqlplus dra/dra

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:27:27 2014

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


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

SQL>
SQL>
SQL>
SQL> create table test2 as select * from test;

Table created.


Case 2:   If mandatory tablespace datafile is missed or deleted.

[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:39:13 2014

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

Enter user-name: /as sysdba

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/OCP/system01.dbf
/u01/app/oracle/oradata/OCP/sysaux01.dbf
/u01/app/oracle/oradata/OCP/undotbs01.dbf
/u01/app/oracle/oradata/OCP/users01.dbf
/u01/app/oracle/oradata/OCP/mgmt_ecm_depot1.dbf
/u01/app/oracle/oradata/OCP/mgmt.dbf
/u01/app/oracle/oradata/OCP/mgmt_ad4j.dbf
/u01/app/oracle/oradata/OCP/stab01.dbf
/u01/app/oracle/oradata/OCP/rman01.dbf

9 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rm /u01/app/oracle/oradata/OCP/system01.dbf
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:39:27 2014

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

Enter user-name: /as sysdba

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

SQL> select name from v$datafile;         -- note its working

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/OCP/system01.dbf
/u01/app/oracle/oradata/OCP/sysaux01.dbf
/u01/app/oracle/oradata/OCP/undotbs01.dbf
/u01/app/oracle/oradata/OCP/users01.dbf
/u01/app/oracle/oradata/OCP/mgmt_ecm_depot1.dbf
/u01/app/oracle/oradata/OCP/mgmt.dbf
/u01/app/oracle/oradata/OCP/mgmt_ad4j.dbf
/u01/app/oracle/oradata/OCP/stab01.dbf
/u01/app/oracle/oradata/OCP/rman01.dbf

9 rows selected.

SQL> desc dba_users              Note: its working
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(8)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)

[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:40:30 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> list failure;           Note:  Till now database didnt get a change to update something in system tablespace. Thats reason no failure

using target database control file instead of recovery catalog
no failures found that match specification

RMAN> exit


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:40:40 2014

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

Enter user-name: /as sysdba

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

SQL> shut immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/OCP/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:40:58 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
307        CRITICAL OPEN      27-FEB-14     System datafile 1: '/u01/app/oracle/oradata/OCP/system01.dbf' is missing

RMAN> advise failure 307;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
307        CRITICAL OPEN      27-FEB-14     System datafile 1: '/u01/app/oracle/oradata/OCP/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oracle/oradata/OCP/system01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:41:39 2014

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

Enter user-name: /as sysdba

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

SQL> shut abort
ORACLE instance shut down.


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:41:58 2014

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1345968 bytes
Variable Size            1543505488 bytes
Database Buffers          486539264 bytes
Redo Buffers               10850304 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:42:52 2014

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

connected to target database: OCP (DBID=2733899832, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
307        CRITICAL OPEN      27-FEB-14     System datafile 1: '/u01/app/oracle/oradata/OCP/system01.dbf' is missing

RMAN> advise failure 307;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
307        CRITICAL OPEN      27-FEB-14     System datafile 1: '/u01/app/oracle/oradata/OCP/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/OCP/system01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 1
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3014817469.hm


RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3014817469.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 1;
   recover datafile 1;
   sql 'alter database datafile 1 online';

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3014817469.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 1;
   recover datafile 1;
   sql 'alter database datafile 1 online';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 27-FEB-14
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 00001 to /u01/app/oracle/oradata/OCP/system01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp
channel ORA_DISK_1: piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T232051_9jz0j5nv_.bkp tag=TAG20140227T232051
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 27-FEB-14

Starting recover at 27-FEB-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 27-FEB-14

sql statement: alter database datafile 1 online
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

RMAN>

RMAN> exit


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:47:22 2014

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

Enter user-name: /as sysdba

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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
OCP       READ WRITE

SQL>

case 3:  If you lost Control file


[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:47:22 2014

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

Enter user-name: /as sysdba

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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
OCP       READ WRITE

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/OCP/co
                                                 ntrol01.ctl, /u01/app/oracle/f
                                                 ast_recovery_area/OCP/control0
                                                 2.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rm /u01/app/oracle/oradata/OCP/control01.ctl
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:57:31 2014

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

Enter user-name: /as sysdba

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

SQL> select name from v$database;
select name from v$database
                 *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/OCP/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:57:44 2014

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/OCP/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:57:51 2014

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

Enter user-name: /as sysdba

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

SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/OCP/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1345968 bytes
Variable Size            1543505488 bytes
Database Buffers          486539264 bytes
Redo Buffers               10850304 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:58:27 2014

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

connected to target database: OCP (not mounted)

RMAN> list failure;             Note: As of now its not yet recognized .

using target database control file instead of recovery catalog
no failures found that match specification

RMAN> exit


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 23:58:37 2014

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

Enter user-name: /as sysdba

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

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 23:58:48 2014

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

connected to target database: OCP (not mounted)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
399        CRITICAL OPEN      27-FEB-14     Control file /u01/app/oracle/oradata/OCP/control01.ctl is missing

RMAN> advise failure 399;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
399        CRITICAL OPEN      27-FEB-14     Control file /u01/app/oracle/oradata/OCP/control01.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file /u01/app/oracle/oradata/OCP/control01.ctl
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3314621009.hm


RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3314621009.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from '/u01/app/oracle/fast_recovery_area/OCP/control02.ctl';
   sql 'alter database mount';

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ocp/OCP/hm/reco_3314621009.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from '/u01/app/oracle/fast_recovery_area/OCP/control02.ctl';
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 27-FEB-14
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/OCP/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/OCP/control02.ctl
Finished restore at 27-FEB-14

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

RMAN> exit


Recovery Manager complete.
[oracle@prodserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 28 00:01:07 2014

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


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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
OCP       READ WRITE

Thursday, February 27, 2014

SET NEWNAME CLAUSE in Oracle 11g Release 2

Hi Guys,

This is another feature in Oracle 11g Release 2 in RMAN.  Till now we know its only for datafile.

Set NEWNAME Flexibility :

Suppose you are restoring datafiles from the backup, either on the same server or a different one such as staging. If the filesystem (or diskgroup) names are identifical, you won’t have to change anything. But that is hardly ever the case. In staging the filesystems may be different, or perhaps you are restoring a production database to an ASM diskgroup different from where it was originally created. In that case you have to let  RMAN know the new name of the datafile. The way to do it is using the SET NEWNAME command. Here is an example, where your restored files are located on /u02 instead of /u01 where they were codeviously.
run 
{
   set newname for datafile 1 to ‘/u02/oradata/system_01.dbf’;
   set newname for datafile 2 to ‘/u02/oradata/sysaux_01.dbf’;

   restore database;      … 
}


Here there are just two datafiles, but what if you have hundreds or even thousands? It will not only be a herculean task to enter all that information but it will be error-prone as well. Instead of entering each datafile by name, now you can use a single set newname clause for a tablespace. Here is how you can do it:
run 
{
 set newname for tablespace examples to '/u02/examples%b.dbf';
 … 
 … rest of the commands come here … 
}


If the tablespace has more than one datafile, they will all be uniquely created. You can use this clause for the entire database as well:
run 
{   
   set newname for database to '/u02/oradata/%b'; 
}


The term %b specifies the base filename without the path, e.g. /u01/oradata/file1.dbf will be recodesented as file1.dbf in %b. This is very useful for cases where you are moving the files to a different directory. You can also use it for creating image copies where you will create the backup in a different location with the same names as the parent file which will make it easy for identification.
One caveat: Oracle Managed Files don’t have a specific basename; so this can’t be used for those. Here are some more examples of the placeholders.
%f is the absolute file number 
%U is a system generated unique name similar to the %U in backup formats
%I is the Database ID
%N is the tablespace name
            
Note:  If you have any query , please send a mail to dbaclass4u@gmail.com

RMAN feature TO DESTINATION Keyword

Hi   Guys,


Oracle 11g Release 2 introduced new feature in RMAN utility i.e TO DESTINATION keyword.

Generally while taking RMAN backup either we have to configure the channel or allocate the channel to store backup pieces.  You can bypass these locations ,to store  backup in new location.

Note there is no format string like %U in the above command as we have been using in the backup commands earlier. Here is the output:

Your database should be in archivelog mode.

[oracle@prodserver ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 27 22:27:00 2014

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

connected to target database: OCP (DBID=2733899832)

RMAN> backup tablespace USERS to destination '/tmp/backup';

Starting backup at 27-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 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=00004 name=/u01/app/oracle/oradata/OCP/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T222746_9jyxdmkd_.bkp tag=TAG20140227T222746 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-14

RMAN> backup datafile 1 to destination '/tmp/backup';

Starting backup at 27-FEB-14
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/OCP/system01.dbf
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T222819_9jyxfmqw_.bkp tag=TAG20140227T222819 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_ncsnf_TAG20140227T222819_9jyxh270_.bkp tag=TAG20140227T222819 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-14

RMAN> backup database to destination '/tmp/backup';

Starting backup at 27-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/OCP/mgmt.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/OCP/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/OCP/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/OCP/undotbs01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/OCP/rman01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/OCP/mgmt_ad4j.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/OCP/stab01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/OCP/mgmt_ecm_depot1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/OCP/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T223322_9jyxq3go_.bkp tag=TAG20140227T223322 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 27-FEB-14
channel ORA_DISK_1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_ncsnf_TAG20140227T223322_9jyxtfpr_.bkp tag=TAG20140227T223322 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-14

RMAN>


This clause creates backup files in an organized manner. The above command creates a directory OCP (the name of the instance), under which it creates a directory called backupset, under which another directory with the name as the date of the file creation. Finally the backuppiece is created with a system generated tag. When you use this to backup archived logs, that backuppiece goes under the subdirectory archivelogs and so on.
You an also use this clause in ALLOCATE CHANNEL command as well:
RMAN> run {
 allocate channel c1 device type disk to destination '/tmp/backup';
backup tablespace USERS;
 release channel c1;
}

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=35 device type=DISK

Starting backup at 27-FEB-14
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/OCP/users01.dbf
channel c1: starting piece 1 at 27-FEB-14
channel c1: finished piece 1 at 27-FEB-14
piece handle=/tmp/backup/OCP/backupset/2014_02_27/o1_mf_nnndf_TAG20140227T224553_9jyygkvg_.bkp tag=TAG20140227T224553 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-14

released channel: c1

RMAN> run {
 allocate channel c1 device type disk to destination '/tmp/backup';
 }

allocated channel: c1
channel c1: SID=35 device type=DISK
released channel: c1

Note:  If you have any query , please send a mail to dbaclass4u@gmail.com