Test(= play) - Duplicate without connection to target/catalog

I saw some mail-lists. How to clone database without connection target database in the same server? On 11gR2, We can duplicate database without connection target database... DUPLICATE Without Connection to Target Database
Actually, I forgot it. I just thought without connection target and catalog databases. I knew this was waste our time. We should connect target database, Except we was using 11gR2 (just connect to catalog).

It was just my play, during I was testing some about RMAN. I used backupset from target database to restore new database (for standby database) then (graceful) failed over it.
On Target Database (ORCL):

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

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7kzfbnwy_.dbf
         2 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7kzfbot8_.dbf
         3 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7kzfboym_.dbf
         4 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7kzfbp8p_.dbf
         5 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7kzfpwwg_.dbf
         6 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
         7 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
         8 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_demo_7lods738_.dbf
         9 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_7m5969gl_.tmp

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_7m596119_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_7m5963pb_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_7m595wts_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_7m595yyc_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_7m595ps4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_7m595t1m_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_7m2z44ts_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_7m2z45x3_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_7m2z4b5w_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_7m2z4cwv_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_7m2z4hop_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_7m2z4js9_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_7_7m2z4nnh_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_7_7m2z4p1t_.log

[oracle@chart01 ~]$ ps -aef | grep pmon
oracle    4589     1  0 Feb08 ?        00:00:09 ora_pmon_orcl

SQL> select name from v$database;

NAME
---------
ORCL

SQL>  ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dupdb.ctl' reuse;

Database altered.

For Duplicate Database (DUPDB): make sure all paths no affect with target database !!!

[oracle@chart01 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdupdb.ora
*.audit_file_dest='/u01/app/oracle/admin/dupdb/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dupdb.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='dupdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/app/oracle'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_trace=1
*.sga_target=700M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='orcl','dupdb','ORCL','DUPDB'
*.log_file_name_convert='orcl','dupdb','ORCL','DUPDB'

[oracle@chart01 ~]$ export ORACLE_SID=dupdb
[oracle@chart01 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 9 00:21:48 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  732352512 bytes
Fixed Size                  1347456 bytes
Variable Size             205521024 bytes
Database Buffers          520093696 bytes
Redo Buffers                5390336 bytes
Database mounted.
SQL>  select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

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

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7kzfbnwy_.dbf
         2 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7kzfbot8_.dbf
         3 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7kzfboym_.dbf
         4 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7kzfbp8p_.dbf
         5 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7kzfpwwg_.dbf
         6 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
         7 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
         8 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7lods738_.dbf
         9 /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_temp_7m5969gl_.tmp

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_3_7m596119_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_3_7m5963pb_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_2_7m595wts_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_2_7m595yyc_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_1_7m595ps4_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_1_7m595t1m_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_4_7m2z44ts_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_4_7m2z45x3_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_5_7m2z4b5w_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_5_7m2z4cwv_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_6_7m2z4hop_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_6_7m2z4js9_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_7_7m2z4nnh_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_7_7m2z4p1t_.log

[oracle@chart01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 9 01:45:12 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1301287541, not open)

RMAN> run{
restore database;
recover database;
}

Starting restore at 09-FEB-12
Starting implicit crosscheck backup at 09-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 09-FEB-12

Starting implicit crosscheck copy at 09-FEB-12
using channel ORA_DISK_1
Crosschecked 10 objects
Finished implicit crosscheck copy at 09-FEB-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DUPDB/autobackup/2012_02_08/o1_mf_n_774745439_7m577k3l_.bkp

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/DUPDB/datafile/o1_mf_system_7kzfbnwy_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7kzfbot8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7kzfboym_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7kzfbp8p_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7kzfpwwg_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l310vnk_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7l367j3l_.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7lods738_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7lrgnll6_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_nnnd0_TAG20120209T014117_7m5jgg70_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_nnnd0_TAG20120209T014117_7m5jgg70_.bkp tag=TAG20120209T014117
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
Finished restore at 09-FEB-12

Starting recover at 09-FEB-12
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=24
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_annnn_TAG20120209T014323_7m5jlcv5_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_02_09/o1_mf_annnn_TAG20120209T014323_7m5jlcv5_.bkp tag=TAG20120209T014323
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/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_24_7m5jvfqo_.arc thread=1 sequence=24
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_24_7m5jvfqo_.arc RECID=1 STAMP=774755293
unable to find archived log
archived log thread=1 sequence=25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/09/2012 01:48:15
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 25 and starting SCN of 2045943

RMAN> exit

Recovery Manager complete.

[oracle@chart01 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 9 01:48:27 2012
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 open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7m5jp64v_.dbf'

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_3_7m5jy095_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_3_7m5jy1fm_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_2_7m5jxtrl_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_2_7m5jxwhx_.log
/u01/app/oracle/oradata/DUPDB/onlinelog/o1_mf_1_7m5jxo0w_.log
/u01/app/oracle/fast_recovery_area/DUPDB/onlinelog/o1_mf_1_7m5jxprl_.log
/u01/app/oracle/fast_recovery_area/DUPDB/archivelog/2012_02_09/o1_mf_1_0_7m5jwzjs_.arc

7 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7m5jp64v_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_sysaux_7m5jp65h_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_undotbs1_7m5jp674_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_users_7m5jpcmm_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_example_7m5jp662_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7m5jp67p_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_apex_7m5jp6b2_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_tbs_demo_7m5jp6bn_.dbf
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_rcat_ts_7m5jp6cn_.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_temp_7m5k1lvf_.tmp

[oracle@chart01 ~]$ ps -aef | grep pmon
oracle    4589     1  0 Feb08 ?        00:00:09 ora_pmon_orcl
oracle    8920     1  0 02:01 ?        00:00:00 ora_pmon_dupdb

Why I didn't restore from backupset? I tested this idea as well. But I had to rename redo log files. That was the bad idea to do on the same server with target database. How do you guarantee all commands no affect to target database?
So, this was just my play and no affect with target database in the same server also. Maybe, my steps was wrong. But just fun and it worked for my play.

***By the way, We should use "duplicate" command (just connect target database and catalog databases), It's safe***

Related Posts:
DUPLICATE Without Connection to Target Database (11gR2)

Create the 2nd Standby Database from the 1st Standby Database(with Backup-Based Duplication)

This was nothing new. When you have the physical standby database, you may backup from this database server(don't need to backup from primary database server) and use backupset files to create auxiliary database or the second standby database.

I didn't know much with this, maybe I haven't had many DR sites, whatever :) This!!! I just posted and improved the language in the same time. "Creating the 2nd Standby Database from the 1st Standby Database(with Backup-Based Duplication)" Don't ask me why didn't I use "Creating a Standby Database with Active Database Duplication" :)

[oracle@mylinux ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 16:44:11 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

[oracle@linuxstb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 09:47:23 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

I had the primary database and the 1st standby database. I had rman backup on the 1st standby database. My plan, I need to build the 2nd standby database.

- Copied orapworcl file from the first standby database and created init file.

[oracle@linuxstb2 dbs]$ ls orapworcl initorcl.ora
initorcl.ora  orapworcl

[oracle@linuxstb2 dbs]$ cat  initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA/orcl/controlfile/control01.ctl','+DG_DATA/orcl/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+DG_DATA'
*.db_recovery_file_dest_size=4794089472
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=652M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

I created /u01/app/oracle/admin/orcl/adump path.
*** Make sure the first standby database, that is applied archivelog files before you did last backup, then copied backupset files to the 2nd standby database ***

- Created "CURRENT CONTROLFILE FOR STANDBY" from primary database, why didn't create from the first standby database...

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Starting backup at 10-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/10/2012 11:32:31
ORA-01671: control file is a backup, cannot make a standby control file

OK... went to the Primary database and then create "CONTROLFILE FOR STANDBY" and transferred to the 2nd standby database.

On Primary Database:

[oracle@mylinux ~]$ rman catalog rman/rman@catalog target/
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 18:31:07 2012

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

connected to target database: ORCL (DBID=1299414597)
connected to recovery catalog database

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/app/arch/%U';

Starting backup at 10-JAN-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-12
channel ORA_DISK_1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/15n0eciq_1_1 tag=TAG20120110T183255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 10-JAN-12

[oracle@mylinux ~]$ scp /u01/app/arch/15n0eciq_1_1 linuxstb2:/u01/app/arch/
15n0eciq_1_1                                                                                                  100% 9696KB   9.5MB/s   00:01

- Used rman to backup database on the 1st standby database and then transferred them to the 2nd standby database (If you have backupset files from the previous backup, you could backup incremental and transferred them)

On the 1st standby database:

[oracle@linuxstb ~]$ rman catalog rman/rman@catalog target/
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 11:39:41 2012

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

connected to target database: ORCL (DBID=1299414597, not open)
connected to recovery catalog database

RMAN> run{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/app/arch/%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
}2> 3> 4>

allocated channel: d1
channel d1: SID=23 device type=DISK

Starting backup at 10-JAN-12
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DG_DATA/orcl/datafile/system.310.771621043
input datafile file number=00002 name=+DG_DATA/orcl/datafile/sysaux.308.771621165
input datafile file number=00006 name=+DG_DATA/orcl/datafile/tbs_demo.303.771785451
input datafile file number=00007 name=+DG_DATA/orcl/datafile/tbs_rman.265.772146419
input datafile file number=00003 name=+DG_DATA/orcl/datafile/undotbs1.309.771621227
input datafile file number=00004 name=+DG_DATA/orcl/datafile/users.266.771621257
input datafile file number=00005 name=+DG_DATA/orcl/datafile/tbs_demo.283.771626027
channel d1: starting piece 1 at 10-JAN-12
channel d1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/16n0dkck_1_1 tag=TAG20120110T114000 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:15
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 10-JAN-12
channel d1: finished piece 1 at 10-JAN-12
piece handle=/u01/app/arch/17n0dkf1_1_1 tag=TAG20120110T114000 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-12
released channel: d1

[oracle@linuxstb ~]$ scp /u01/app/arch/16n0dkck_1_1 /u01/app/arch/17n0dkf1_1_1 linuxstb2:/u01/app/arch/
16n0dkck_1_1                                                                                                  100% 1038MB   5.8MB/s   02:59
17n0dkf1_1_1                                                                                                  100% 9728KB   9.5MB/s   00:01

- Went to the 2nd standby database and then created standby database

[oracle@linuxstb2 ~]$ export ORACLE_SID=orcl
[oracle@linuxstb2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 18:44:19 2012

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  682135552 bytes
Fixed Size                  1347120 bytes
Variable Size             406847952 bytes
Database Buffers          268435456 bytes
Redo Buffers                5505024 bytes

[oracle@linuxstb2 ~]$ rman catalog rman/rman@catalog target sys/oracle@orcl_stb1 auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 10 18:47:21 2012

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

connected to target database: ORCL (DBID=1299414597, not open)
connected to recovery catalog database
connected to auxiliary database: ORCL (not mounted)

RMAN> list backup;

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 10-JAN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
set until scn  1154827;
restore clone standby controlfile;
}
executing Memory Script

executing command: SET until clause

Starting restore at 10-JAN-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/arch/17n0dkf1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/arch/17n0dkf1_1_1 tag=TAG20120110T114000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
output file name=+DG_DATA/orcl/controlfile/control01.ctl
output file name=+DG_DATA/orcl/controlfile/control02.ctl
Finished restore at 10-JAN-12

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set until scn  1154827;
set newname for clone datafile  1 to new;
set newname for clone datafile  2 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  6 to new;
set newname for clone datafile  7 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 10-JAN-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DG_DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DG_DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/arch/16n0dkck_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/arch/16n0dkck_1_1 tag=TAG20120110T114000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 10-JAN-12

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=772224792 file name=+DG_DATA/orcl/datafile/system.257.772224677
datafile 2 switched to datafile copy
input datafile copy RECID=19 STAMP=772224792 file name=+DG_DATA/orcl/datafile/sysaux.270.772224677
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=772224792 file name=+DG_DATA/orcl/datafile/undotbs1.256.772224681
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=772224793 file name=+DG_DATA/orcl/datafile/users.261.772224683
datafile 5 switched to datafile copy
input datafile copy RECID=22 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_demo.262.772224683
datafile 6 switched to datafile copy
input datafile copy RECID=23 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_demo.271.772224679
datafile 7 switched to datafile copy
input datafile copy RECID=24 STAMP=772224793 file name=+DG_DATA/orcl/datafile/tbs_rman.269.772224681

contents of Memory Script:
{
set until scn  1154827;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause
Starting recover at 10-JAN-12
using channel ORA_AUX_DISK_1

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

Finished recover at 10-JAN-12
Finished Duplicate Db at 10-JAN-12

- Checked - On the 2nd standby database

[oracle@linuxstb2 ~]$ sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 10 18:56:26 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

*** If you have backupset files (+ controlfile also) from Primary Database. You do not need to create a control file backup especially for the standby database ***
*** But this case, My backupset files was created from the 1st standby database (no from primary database) ***

FIXING BLOCK CORRUPTION (RMAN 11G)

How do you fix block corruption? I just tested to fix block corruption on 11g. FIXING BLOCK CORRUPTION (RMAN 11G)

DUPLICATE Without Connection to Target Database

Backup-Based: Duplication Without a Target Connection













Oracle DUPLICATE Without Connection to Target Database, that's a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.

Start...
- Create password file

$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwAUX entries=5

Enter password for SYS:

- Create pfile (use ORACLE_SID=AUX)

SQL> create pfile='/oracle/product/11.2.0/dbhome_1/dbs/initAUX.ora' from spfile;

File created.

Modify initAUX.ora file.

*.audit_file_dest='/oracle/product/admin/AUX/adump'
*.compatible='11.2.0.0.0'
*.control_files='+DISK01/AUX/controlfile/current.263.696964775','+DISK02/AUX/controlfile/current.492.696964779'
*.db_block_size=8192
*.db_create_file_dest='+DISK01'
*.db_domain=''
*.db_name='AUX'
*.db_recovery_file_dest='+DISK02'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXXDB)'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=33554432
*.memory_target=813694976
*.open_cursors=300
*.processes=150
*.remote_listener='RHEL5-T-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'

- Check parameters and create some directories

$ mkdir -p /oracle/product/admin/AUX/adump

- Check Target Database in Catalog

$ sqlplus rman/password@catalog

SQL> select * from rc_database ;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 501 1224745511 ORCL 3626383 03-NOV-09

- STARTUP NOMOUNT (AUXILIARY) database

$ export ORACLE_SID=AUX
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 3 19:27:19 2009

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

- Duplicate Database by RMAN

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 4 12:38:14 2009

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

RMAN> CONNECT AUXILIARY /

connected to auxiliary database: AUX (not mounted)

RMAN> CONNECT CATALOG rman/password@catalog

connected to recovery catalog database

RMAN> DUPLICATE DATABASE orcl TO AUX ;

Starting Duplicate Db at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

Starting restore at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473
channel ORA_AUX_DISK_1: piece handle=+DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473 tag=TAG20091103T210428
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DISK01/aux/controlfile/current.282.702045937
output file name=+DISK02/aux/controlfile/current.349.702045941
Finished restore at 04-NOV-09

database mounted

contents of Memory Script:
{
set until scn 3657467;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DISK01
channel ORA_AUX_DISK_1: reading from backup piece /oracle/RMAN/12ktf0ar_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle/RMAN/12ktf0ar_1_1 tag=TAG20091103T210011
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:25
Finished restore at 04-NOV-09

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=702046260 file name=+DISK01/aux/datafile/system.283.702045995
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=702046260 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=702046261 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=702046261 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=702046261 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=702046261 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=25 STAMP=702046261 file name=+DISK01/aux/datafile/tbs_fda.288.702046001

contents of Memory Script:
{
set until scn 3657467;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355
archived log for thread 1 with sequence 2 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355 thread=1 sequence=1
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:33
Finished recover at 04-NOV-09

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes

sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DISK01/aux/datafile/system.283.702045995'
CHARACTER SET TH8TISASCII


contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DISK01/aux/datafile/sysaux.284.702045997",
"+DISK01/aux/datafile/undotbs1.287.702045999",
"+DISK01/aux/datafile/users.289.702046003",
"+DISK01/aux/datafile/example.286.702045999",
"+DISK01/aux/datafile/tbs1.285.702045999",
"+DISK01/aux/datafile/tbs_fda.288.702046001";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DISK01 in control file

cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/sysaux.284.702045997 RECID=1 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/undotbs1.287.702045999 RECID=2 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/users.289.702046003 RECID=3 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/example.286.702045999 RECID=4 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs1.285.702045999 RECID=5 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs_fda.288.702046001 RECID=6 STAMP=702046345

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=702046345 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=702046345 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=702046345 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=702046345 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=702046345 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=702046345 file name=+DISK01/aux/datafile/tbs_fda.288.702046001

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-NOV-09

- Check

SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
1555302662 AUX

Fun ;) to duplicate database without connection to target database.

Just Fun with RMAN Recovery...

My Test Database (ARCHIVELOG MODE) lost:

Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist

It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:

run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}

But It could not "resetlogs"... and alert log showed:

alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist

Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :

$ rman target / catalog rman/password@catalog

RMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09

RMAN> shutdown

database dismounted
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 810053632 bytes

Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09

RMAN> alter database mount;

Database altered.

RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}

executing command: SET until clause

Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09

Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527

using channel ORA_DISK_1

skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
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 +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09

Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

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

Finished recover at 03-NOV-09

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Just fun with RMAN (If A database has backup)... ;)

Mutiplex Controlfiles on ASM diskgroup

How can I multiplex my controlfile that is on ASM diskgroup? I use ASM on 10gR2.

I use rman autobackup controlfile to solve this case, But I have to startup database with resetlogs.

If USE RAC, Need to start database one node.

And I need to use pfile (assume /tmp/pfile), that easily to modify for testing.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl

$ export ORACLE_SID=db1
$ sqlplus / as sysdba

SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA1/db/spfiledb.ora

SQL> create pfile='/tmp/pfile' from spfile;

$ grep control_files /tmp/pfile
*.control_files='+DATA1/db/control01.ctl'

SQL> shutdown

SQL> startup mount pfile='/tmp/pfile';

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl


I need DBID value and autobackup controlfile:

$ rman target /

connected to target database: DB (DBID=2422235186, not open)
RMAN> backup current controlfile;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

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

RMAN> backup current controlfile;
Starting backup at 12-NOV-08
.
.
.

Starting Control File and SPFILE Autobackup at 12-NOV-08
piece handle=/tmp/c-2422235186-20081112-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-08

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down
DBID=2422235186
AUTOBACKUP CONTROLFILE=/tmp/c-2422235186-20081112-00

Modified control_files parameter on /tmp/pfile file:
*.control_files='+DATA1/db/control01.ctl','+DATA1/db/control04.ctl'

After that begin to recovery controlfile (startup nomount mode)

$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/pfile';

use rman =>

$ rman target /
connected to target database: db (not mounted)
RMAN> SET DBID 2422235186

RMAN> run {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=db1 devtype=DISK

.
.
.
database name (or database unique name) used for search: DB
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20081112
channel ORA_DISK_1: autobackup found: /tmp/c-2422235186-20081112-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+DATA1/db/control01.ctl
output filename=+DATA1/db/control04.ctl
Finished restore at 12-NOV-08

$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
Database altered.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl, +DATA1/db/control04.ctl

Now I have multiplex controlfiles(+DATA1/db/control01.ctl,+DATA1/db/control04.ctl) on ASM. I can create spfile from pfile (/tmp/pfile file) and stop/start anyway.

SQL> create spfile='+DATA1/db/spfiledb.ora' from pfile='/tmp/pfile'

**** However, If we don't need to use this idea, we may restore controlfile backup to new file and then change control_files parameter.
Or ... use asmca for help (copy file in ASM Disk Group)

Some Idea:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407

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

RMAN> startup nomount;

Oracle instance started

Total System Global Area 626327552 bytes

Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes

RMAN> restore controlfile to '+DATA' from '+DATA/orcl/controlfile/current.277.741286407';

Starting restore at 25-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl_2 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 25-APR-11

ASMCMD> ls +DATA/orcl/CONTROLFILE/
Current.277.741286407
current.285.749426511

SQL> alter system set control_files='+DATA/orcl/controlfile/current.277.741286407','+DATA/orcl/controlfile/current.285.749426511' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407, +DATA/orcl/controlfile/current.285.749426511

ORA-26040: Data block was loaded using the NOLOGGING option

After I recover my database, I found below error...
What's going on?

select count(*) from TEMP01;

*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 20)
ORA-01110: data file 4: '+DATA1/db/datafile/users.260.670078195'
ORA-26040: Data block was loaded using the NOLOGGING option

what is ORA-26040?
and this error can not solve by rman "BLOCKRECOVER".

If find out on internet you will find;

Error: ORA-26040
Text: Data block was loaded using the NOLOGGING option
---------------------------------------------------------------------------
Cause: Trying to access data in block that was loaded without
redo generation using the NOLOGGING/UNRECOVERABLE option

Action: Drop the object containing the block.

that's a idea to solve it, right!... Absolutely.

I asked myself How can I know before my database error like that.

This cause ... I use NOLOGGING mode, NOLOGGING mode which will prevent the table from being rolled forward after future changes are applied.

I haven't found ORA-26040 error (after test) when I insert/delete/update with NOLOGGING.

But I have found ... exactly, when I create new table with NOLOGGING.

EXAMPLE:

create table TEMP01 nologging as select * from all_objects;

How can I know, before datafiles fail and error ORA-26040.

Using rman to check by "report unrecoverable" command,
Anyway It'll be better when use rman to backup/recovery as well.

when I found this error, I will backup datafiles by rman.

SQL> create table TEMP01 nologging as select * from all_objects;

Table created.

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------------
TEMP01 USERS

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4 full or incremental +DATA1/db/datafile/users.260.670078195

RMAN> backup datafile 4;
.
.
Finished backup

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

Not Found

I don't sure why i use insert/update/delete with NOLOGGING mode and not found error.

SQL> create table TEMP02 logging as select * from all_objects where rownum <=1 ; Table created.

SQL> insert into TEMP02 nologging select * from all_objects;

414 rows created.

SQL> commit;

Commit complete.

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
Not Found

That make sure; If I need to leave from UNRECOVERABLE with creating new table, I should use LOGGING mode.

However, check... check and check database [use RMAN with "report unrecoverable" and backup ... backup] ... that make database leave from UNRECOVERABLE as well.

??? TSPITR [example]

Need to recover EXAMPLE tablespace (TSPITR)

Have TTTTT table:

SQL> create table TTTTT (id number) tablespace example;

Table created.

$ date
Thu May 1 01:31:13 ICT 2008

SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

$ date
Thu May 1 01:31:41 ICT 2008

DROP "PURGE" option:

SQL> drop table TTTTT purge;

Table dropped.

SQL> desc TTTTT
ERROR:
ORA-04043: object TTTTT does not exist

$ date
Thu May 1 01:34:06 ICT 2008

After drop table "PURGE" option:
Need recover "TTTTT" table on EXAMPLE tablespace ["Thu May 1 01:32:00 ICT 2008"]

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
+DATA/testdb/datafile/users.1085.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/undotbs1.1086.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/sysaux.1087.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/system.270.651074273 AVAILABLE SYSTEM
+DATA/testdb/datafile/example.1075.651074487 AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>

1. create inittspitr_testdb.ora file ($ORACLE_HOME/dbs) for auxiliary instance.

DB_NAME=testdb
DB_UNIQUE_NAME=tspitr_testdb
CONTROL_FILES='/oradata/testdb/tmp/controlfile.dbf'
DB_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
LOG_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
COMPATIBLE=11.1.0.0.0
remote_login_passwordfile='EXCLUSIVE'

2. Create service for target database (example: testdb).

3. use SQL*PLUS to start tspitr_testdb instance (auxiliary)

$ export ORACLE_SID=tspitr_testdb
$ sqlplus / as sysoper
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2142976 bytes
Variable Size 159386880 bytes
Database Buffers 50331648 bytes
Redo Buffers 5296128 bytes
.
.
.

4. use RMAN connect to the target and auxiliary database instances.

$export ORACLE_SID=tspitr_testdb
$rman target sys/password@testdb auxiliary / catalog rman/password@catalog
connected to target database: TESTDB (DBID=2434402578)
connected to recovery catalog database
connected to auxiliary database: TESTDB (not mounted)

5. execute TSPITR

RMAN> run{
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/example.1075.651074487'
TO '/oradata/testdb/tmp/example01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/system.270.651074273'
TO '/oradata/testdb/tmp/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/sysaux.1087.651074273'
TO '/oradata/testdb/tmp/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/undotbs1.1086.651074273'
TO '/oradata/testdb/tmp/undo01.dbf';
ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE DISK;
RECOVER TABLESPACE example until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

allocated channel: t1
channel t1: SID=97 device type=DISK

Starting recover at 01-MAY-08

contents of Memory Script:
{
# set the until clause
set until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 01-MAY-08

channel t1: starting datafile backup set restore
channel t1: restoring control file
.
.
.
Starting recover at 01-MAY-08

starting media recovery
.
.
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-MAY-08

released channel: t1
.
.
.

5. After auxiliary database instances "open" (resetlogs), that we check auxiliary database instances.

Connect...
SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------
ID NUMBER

We can recover "TTTTT" table:

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
/oradata/testdb/tmp/users.1085.651074273 AVAILABLE OFFLINE
/oradata/testdb/tmp/undo01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/sysaux01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/system01.dbf AVAILABLE SYSTEM
/oradata/testdb/tmp/example01.dbf AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>
This case we recover example tablespace, It'll recover example + system + sysaux + undo tablespaces except USERS tablespace or..... other, so we'll see online_status be offline.

RMAN Catalog

CONNECT catalog Database:

$sqlplus / as sysdba

SQL> create tablespace TBS_RMAN datafile '+DATA' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

SQL> create user rman identified by rman default tablespace TBS_RMAN ;

User created.

SQL> alter user rman quota unlimited on TBS_RMAN;

User altered.

SQL> grant connect, resource, recovery_catalog_owner to rman;

Grant succeeded.


Create catalog on TBS_RMAN tablespace:

$rman catalog rman/rman@catalog

RMAN> create catalog tablespace 'TBS_RMAN';

recovery catalog created


Register Database to Catalog:

$rman catalog rman/rman@catalog target /
RMAN> register database ;

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


...

??? TSPITR

Tablespace Point-in-Time Recovery
Rman automatic enable for "TSPITR", that make us to quickly recover one or more tablespaces in a database to an earlier time without affecting.

Situations:
- recover a logical database to point different from the rest of the physical database.
- recover data lost after DDL operations that change the structure of table. we can not use Flashback table to rewind a table to before the point of structural change such as a truncate table operation.
- recover a table after it dropped with the PURGE option.
- recover from the logical corruption of a table.


TSPITR using the RMAN RECOVER TABLESPACE command.

Example: use TSPITR with AUXILIARY database

RUN
{
SET NEWNAME FOR DATAFILE '+DATA/DB/example01.dbf'
TO '+DATA/AUX/example01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/system01.dbf'
TO '+DATA/AUX/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/sysaux01.dbf'
TO '+DATA/AUX/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/undo01.dbf'
TO '+DATA/AUX/undo01.dbf';
ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL t2 DEVICE 'TYPE SBT_TAPE';
RECOVER TABLESPACE example UNTIL TIME "to_date('Apr:30:2008 14:00:00', 'Mon:DD:YYYY HH24:MI:SS')";
}

ORA-00376: file 2 cannot be read at this time

I didn't know to write anything in my blog, So I used Tittle, that's "ORA-00376".

Actually, Enterprise Manager failed on Test Server(11G), I tried to find the problem and recreated EM.

I found something wrong in oc4j config DIR.

>>>
$ ls -ltr /oracle/11g/oc4j/j2ee/OC4J_DBConsole/config/

-rw-r--r-- 1 oracle oinstall 2845 Oct 31 15:08 system-application.xml
-rw-r--r-- 1 oracle oinstall 0 Apr 8 15:45 server.xml
>>>

Why server.xml file is no size. I didn't sure about the problem.
So, I think I should recreate EM.

>>>
$ emca -config dbcontrol db
.
.
.
Apr 8, 2008 4:55:49 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_16_55_19.log.
Apr 8, 2008 4:55:50 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 8, 2008 4:55:52 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Failed to unlock all EM-related accounts
Apr 8, 2008 4:55:52 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Failed to unlock all EM-related accounts
Refer to the log file at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_16_55_19.log for more details.
Could not complete the configuration. Refer to the log file at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_16_55_19.log for more details.
>>>

Perhaps something wrong on DB
Checked alert file (rdbms).

>>>
Tue Apr 08 16:59:46 2008
Errors in file /oracle/11g/diag/rdbms/testdb/testdb/trace/testdb_smon_2396.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+DATA/testdb/datafile/sysaux.1087.651074273'
>>>

After that, recovery that datafile.

>>>
SQL> select FILE#, status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 RECOVER
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
2 OFFLINE OFFLINE 2861632 07-APR-08

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ /oracle/11g/bin/rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Apr 8 17:01:49 2008

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

connected to target database: TESTDB (DBID=2434402578)

RMAN> recover datafile 2;

Starting recover at 08-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

starting media recovery

archived log for thread 1 with sequence 64 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_64.1054.651456629
archived log for thread 1 with sequence 65 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_65.1055.651462047
archived log for thread 1 with sequence 66 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_66.1056.651468027
archived log for thread 1 with sequence 67 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_67.1057.651473947
archived log for thread 1 with sequence 68 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_68.1058.651475295
archived log for thread 1 with sequence 69 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_69.1059.651475889
archived log for thread 1 with sequence 70 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_70.1060.651475903
archived log for thread 1 with sequence 71 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_71.1061.651475913
archived log for thread 1 with sequence 72 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_72.1063.651477283
archived log for thread 1 with sequence 73 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_73.1064.651510549
archived log for thread 1 with sequence 74 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_74.1065.651510651
archived log for thread 1 with sequence 75 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_75.1066.651510735
archived log for thread 1 with sequence 76 is already on disk as file +DATA/testdb/archivelog/2008_04_08/thread_1_seq_76.1067.651516283
archived log file name=+DATA/testdb/archivelog/2008_04_08/thread_1_seq_64.1054.651456629 thread=1 sequence=64
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-APR-08

RMAN> sql 'alter database datafile 2 online';

sql statement: alter database datafile 2 online

RMAN> exit

Recovery Manager complete.

SQL> select * from v$recover_file;

no rows selected
>>>

Try to create again

>>>
$ emca -config dbcontrol db

STARTED EMCA at Apr 8, 2008 5:06:05 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: testdb
Database Control is already configured for the database testdb
You have chosen to configure Database Control for managing the database testdb
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /oracle/11g ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: -----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oracle/11g

Local hostname ................ test01
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /oracle/11g
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 8, 2008 5:06:37 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_17_06_05.log.
Apr 8, 2008 5:06:37 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 8, 2008 5:06:40 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 8, 2008 5:08:07 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 8, 2008 5:08:10 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library is already configured.
Apr 8, 2008 5:08:10 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: EM_SWLIB_STAGE_LOC (value) will be ignored.
Apr 8, 2008 5:08:10 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 8, 2008 5:08:12 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /oracle/11g/bin/emctl config emkey -repos
Apr 8, 2008 5:08:12 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
WARNING: Configuring EM-Key failed.
Apr 8, 2008 5:08:12 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
WARNING: Error securing Database control.
Apr 8, 2008 5:08:12 PM oracle.sysman.emcp.EMDBPostConfig setWarnMsg
INFO: Error securing Database Control, Database Control has been brought up in non-secure mode. To secure the Database Control execute the following command(s):

1) Set the environment variable ORACLE_SID to testdb
2) /oracle/11g/bin/emctl stop dbconsole
3) /oracle/11g/bin/emctl config emkey -repos -sysman_pwd <>
4) /oracle/11g/bin/emctl secure dbconsole -sysman_pwd <>
5) /oracle/11g/bin/emctl start dbconsole

To secure Em Key, run /oracle/11g/bin/emctl config emkey -remove_from_repos -sysman_pwd <>
Apr 8, 2008 5:08:12 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 8, 2008 5:08:13 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /oracle/11g/bin/emctl start dbconsole
Apr 8, 2008 5:08:13 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_17_06_05.log for more details.
Could not complete the configuration. Refer to the log file at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_17_06_05.log for more details.
$ /oracle/11g/bin/emctl stop dbconsole
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
OC4J Configuration issue. /oracle/11g/oc4j/j2ee/OC4J_DBConsole_Error occurred during initialization of VM
java.lang.Error: Properties init: Could not determine current working directory._testdb not found.
$ /oracle/11g/bin/emctl config emkey -repos -sysman_pwd pass
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
EM Configuration issue. /oracle/11g/Error occurred during initialization of VM
java.lang.Error: Properties init: Could not determine current working directory._testdb not found.

$ /oracle/11g/bin/emctl secure dbconsole -sysman_pwd pass

Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
http://test01:1158/em/console/aboutApplication
DBCONSOLE already stopped... Done.
Agent is already stopped... Done.
Securing dbconsole... Started.
Securing dbconsole... Failed.
EMKey is misconfigured.
>>>

Mission failed, So try drop RESOS and then create again: Yo!


>>>
$ emca -repos drop

STARTED EMCA at Apr 8, 2008 5:27:30 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Apr 8, 2008 5:27:30 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
SEVERE: No SID specified
Database SID: testdb
Listener port number: 1521
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 8, 2008 5:27:45 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_17_27_30.log.
Apr 8, 2008 5:27:45 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...

Apr 8, 2008 5:31:09 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 8, 2008 5:31:09 PM

$ emca -repos create

STARTED EMCA at Apr 8, 2008 5:31:55 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Apr 8, 2008 5:31:55 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
SEVERE: No SID specified
Database SID: testdb
Listener port number: 1521
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 8, 2008 5:32:09 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/11g/cfgtoollogs/emca/testdb/emca
Apr 8, 2008 5:32:09 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...

Apr 8, 2008 5:42:13 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 8, 2008 5:42:14 PM

$ emca -config dbcontrol db

STARTED EMCA at Apr 8, 2008 5:43:25 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: testdb
Database Control is already configured for the database testdb
You have chosen to configure Database Control for managing the database testdb
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /oracle/11g ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: -----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oracle/11g

Local hostname ................ test01
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /oracle/11g
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 8, 2008 5:43:51 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/11g/cfgtoollogs/emca/testdb/emca_2008_04_08_17_43_25.log.
Apr 8, 2008 5:43:51 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 8, 2008 5:43:56 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 8, 2008 5:45:00 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 8, 2008 5:45:04 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Apr 8, 2008 5:45:04 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Apr 8, 2008 5:45:15 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Apr 8, 2008 5:45:15 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 8, 2008 5:45:26 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 8, 2008 5:45:26 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 8, 2008 5:45:58 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 8, 2008 5:45:58 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://test01:1158/em <<<<<<<<<<<
Apr 8, 2008 5:46:02 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /oracle/11g/timeten01_testdb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 8, 2008 5:46:02 PM

>>>

Wow! done.


Enjoy!