Using RMAN restore to create a duplicate database on a different HOST with different/same name

In my VM i have a database called TESTDB & i want to clone it to tstdbdup on a different host.

We will clone the DB with the same name first then later we will change the name

So i had followed the link "http://www.dbatutor.com/2010/12/rman-restore-rman-backup-to-different.html" & here are the screen shot of the work i did.

[oracle@localhost ~]$ ps -fe|grep pmon
grid      3931     1  0 14:54 ?        00:00:00 asm_pmon_+ASM
oracle    5414     1  0 17:06 ?        00:00:00 ora_pmon_testDB
oracle    5620  5231  0 17:24 pts/2    00:00:00 grep pmon


Step 1:Take a backup of the source database(TESTDB) as below.

RMAN> backup as compressed backupset database format '/u02/backup/rman_testDB_%s_%d_%T.bus' plus archivelog format '/u01/backup/arch_testDB_%s_%d_%T.arc';


Starting backup at 04-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=44 RECID=40 STAMP=781885927
input archived log thread=1 sequence=45 RECID=41 STAMP=781886354
input archived log thread=1 sequence=46 RECID=42 STAMP=782234385
input archived log thread=1 sequence=47 RECID=43 STAMP=782418077
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u01/backup/arch_testDB_26_TESTDB_20120504.arc tag=TAG20120504T182117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 04-MAY-12

Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/testdb/datafile/system.691.781606239
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.692.781606241
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.693.781606241
input datafile file number=00004 name=+DATA/testdb/datafile/users.694.781606241
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u02/backup/rman_testDB_27_TESTDB_20120504.bus tag=TAG20120504T182134 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 04-MAY-12

Starting backup at 04-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=48 RECID=44 STAMP=782418250
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u01/backup/arch_testDB_28_TESTDB_20120504.arc tag=TAG20120504T182411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12

Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=/u02/backup/c-2564934505-20120504-00 comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12

RMAN> list backup
2> ;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
25      31.25M     DISK        00:00:13     04-MAY-12
        BP Key: 25   Status: AVAILABLE  Compressed: YES  Tag: TAG20120504T182117
        Piece Name: /u01/backup/arch_testDB_26_TESTDB_20120504.arc

  List of Archived Logs in backup set 25
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    44      2513198    29-APR-12 2538899    29-APR-12
  1    45      2538899    29-APR-12 2558433    29-APR-12
  1    46      2558433    29-APR-12 2586068    02-MAY-12
  1    47      2586068    02-MAY-12 2590564    04-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26      Full    256.04M    DISK        00:02:28     04-MAY-12
        BP Key: 26   Status: AVAILABLE  Compressed: YES  Tag: TAG20120504T182134
        Piece Name: /u02/backup/rman_testDB_27_TESTDB_20120504.bus
  List of Datafiles in backup set 26
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2590572    04-MAY-12 +DATA/testdb/datafile/system.691.781606239
  2       Full 2590572    04-MAY-12 +DATA/testdb/datafile/sysaux.692.781606241
  3       Full 2590572    04-MAY-12 +DATA/testdb/datafile/undotbs1.693.781606241
  4       Full 2590572    04-MAY-12 +DATA/testdb/datafile/users.694.781606241

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
27      108.00K    DISK        00:00:01     04-MAY-12
        BP Key: 27   Status: AVAILABLE  Compressed: YES  Tag: TAG20120504T182411
        Piece Name: /u01/backup/arch_testDB_28_TESTDB_20120504.arc

  List of Archived Logs in backup set 27
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    48      2590564    04-MAY-12 2590660    04-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28      Full    9.42M      DISK        00:00:03     04-MAY-12
        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20120504T182412
        Piece Name: /u02/backup/c-2564934505-20120504-00
  SPFILE Included: Modification time: 04-MAY-12
  SPFILE db_unique_name: TESTDB
  Control File Included: Ckp SCN: 2590669      Ckp time: 04-MAY-12

<here in step 1 we have to note the control file backupset & the last sequence of the archivelog>

Step 2: First do some sanity check on the database TESTDB to find the log files,temp files & datafiles location , even i had also taken a trace file of the

control file of the source database. & stored the details in a notepad for later use.

Location of logfiles
=====================

SQL> select GROUP#,MEMBER from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         3
+DATA/testdb/onlinelog/group_3.698.781606385

         2
+DATA/testdb/onlinelog/group_2.697.781606383

         1
+DATA/testdb/onlinelog/group_1.696.781606381

Location of tempfiles
=====================

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
+DATA/testdb/tempfile/temp.699.781606405
         1 TEMP

Location of Datafiles
=============================

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

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
+DATA/testdb/datafile/system.691.781606239

         2
+DATA/testdb/datafile/sysaux.692.781606241

         3
+DATA/testdb/datafile/undotbs1.693.781606241


     FILE#
----------
NAME
--------------------------------------------------------------------------------
         4
+DATA/testdb/datafile/users.694.781606241


To find generic location of all datafiles,tempfiles,logfiles
==============================================================


SQL> select distinct substr(name,1,instr(name,'/',-1)) dbf_con_str from v$tempfile;

DBF_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/tempfile/

SQL> select distinct substr(name,1,instr(name,'/',-1)) dbf_con_str from v$datafile;

DBF_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/datafile/


SQL> select distinct substr(member,1,instr(member,'/',-1)) logfile_con_str from v$logfile where type='ONLINE';

LOGFILE_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/onlinelog/

trace control file from source<testdb> database(from bdump location as *.trc file)
=============================================================================

SQL> alter database backup controlfile to trace
  2  ;

Database altered.
SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/tes
                                                 tdb/testDB/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/tes
                                                 tdb/testDB/cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/tes
                                                 tdb/testDB/trace

[oracle@localhost ~]$ cd /u01/app/oracle/diag/rdbms/testdb/testDB/trace
[oracle@localhost trace]$ ls -ltr|grep control
[oracle@localhost trace]$ ls -ltr
total 2240

-rw-r----- 1 oracle asmadmin   7852 May  4 19:07 testDB_ora_6090.trc
-rw-r----- 1 oracle asmadmin 126399 May  4 19:07 alert_testDB.log


CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/testdb/onlinelog/group_1.696.781606381'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/testdb/onlinelog/group_2.697.781606383'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/testdb/onlinelog/group_3.698.781606385'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/testdb/datafile/system.691.781606239',
  '+DATA/testdb/datafile/sysaux.692.781606241',
  '+DATA/testdb/datafile/undotbs1.693.781606241',
  '+DATA/testdb/datafile/users.694.781606241'
CHARACTER SET AL32UTF8
;


Step 3: Modify the pfile from source database(testdb) , change it as below but keep the db_name unchanged<important>

*.audit_file_dest='/u01/app/oracle/admin/testDBDUP/adump'
*.audit_trail='db'
*.blank_trimming=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/testdbdup/controlfile/current.695.781606377'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='testDB'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='+DATA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testDBDUPXDB)'
*.open_cursors=300
*.pga_aggregate_target=158334976
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=476053504
*.undo_tablespace='UNDOTBS1'


Step 4: Start(nomount) the instance using the modified pfile


[oracle@localhost tmp]$ export ORACLE_SID=testDB
[oracle@localhost tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 19:34:30 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/inittestdb.ora';
ORACLE instance started.

Total System Global Area  477073408 bytes
Fixed Size                  1337324 bytes
Variable Size             146802708 bytes
Database Buffers          322961408 bytes
Redo Buffers                5971968 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost tmp]$ ps -fe|grep pmon
grid      3931     1  0 14:54 ?        00:00:00 asm_pmon_+ASM
oracle    6502     1  0 19:36 ?        00:00:00 ora_pmon_testDB
oracle    6562  6287  0 19:37 pts/1    00:00:00 grep pmon


Step 5: Now from RMAN connect to the database as below & restore the control file < make sure u have created the ASM disk path i.e.

+DATA/testdbdup/controlfile/ > and mount the database


[oracle@localhost rdbms]$  echo $ORACLE_SID
testDB
[oracle@localhost rdbms]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 4 19:54:21 2012

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

connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/u02/backup/c-2564934505-20120504-00';

Starting restore at 04-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+DATA/testdbdup/controlfile/control01.ctl
Finished restore at 04-MAY-12


RMAN> alter database mount
2> ;

database mounted
released channel: ORA_DISK_1


Step 6: Now connect to the database mounted in sqlplus & rename the logfiles and tempfiles


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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PRIMARY

SQL>
SQL>
SQL> alter database rename file '+DATA/testdb/onlinelog/group_3.698.781606385' to '+DATA/testdbdup/onlinelog/log01.dbf';

Database altered.

SQL> alter database rename file '+DATA/testdb/onlinelog/group_2.697.781606383' to '+DATA/testdbdup/onlinelog/log02.dbf';

Database altered.

SQL> alter database rename file '+DATA/testdb/onlinelog/group_1.696.781606381' to '+DATA/testdbdup/onlinelog/log03.dbf';

Database altered.

SQL> alter database rename file '+DATA/testdb/tempfile/temp.699.781606405' to '+DATA/testdbdup/tempfile/tmp01.dbf';

Database altered.


Step 7: Do the set new name for the datafiles as in below rman run script & execute as below . set until clause as sequence number+1


RMAN> run{

set until sequence 49;
set newname for datafile 1 to '+DATA/testdbdup/datafile/system01.dbf';
set newname for datafile 2 to '+DATA/testdbdup/datafile/sysaux01.dbf';
set newname for datafile 3 to '+DATA/testdbdup/datafile/undotbs1.dbf';
set newname for datafile 4 to '+DATA/testdbdup/datafile/users01.dbf';
restore database;
switch datafile all;
recover database;
}14> 15> 16> 17> 18>

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-MAY-12
Starting implicit crosscheck backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 04-MAY-12

Starting implicit crosscheck copy at 04-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-MAY-12

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

List of Cataloged Files
=======================
File Name: +data/TESTDB/CONTROLFILE/current.742.782422753

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 +DATA/testdbdup/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/testdbdup/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/testdbdup/datafile/undotbs1.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DATA/testdbdup/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/rman_testDB_27_TESTDB_20120504.bus
channel ORA_DISK_1: piece handle=/u02/backup/rman_testDB_27_TESTDB_20120504.bus tag=TAG20120504T182134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 04-MAY-12

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=782425582 file name=+DATA/testdbdup/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=782425582 file name=+DATA/testdbdup/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=782425582 file name=+DATA/testdbdup/datafile/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=782425582 file name=+DATA/testdbdup/datafile/users01.dbf

Starting recover at 04-MAY-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 48 is already on disk as file +DATA/testdb/archivelog/2012_05_04/thread_1_seq_48.743.782418251
archived log file name=+DATA/testdb/archivelog/2012_05_04/thread_1_seq_48.743.782418251 thread=1 sequence=48
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-MAY-12




Step 8: Now take a trace of the control file & modify the database name(db_name) in it and save this file as .sql file


SQL> alter database backup controlfile to trace;

Database altered.

CREATE CONTROLFILE SET DATABASE "TSTDBDUP" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/testdbdup/onlinelog/log03.dbf'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/testdbdup/onlinelog/log02.dbf'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/testdbdup/onlinelog/log01.dbf'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/testdbdup/datafile/system01.dbf',
  '+DATA/testdbdup/datafile/sysaux01.dbf',
  '+DATA/testdbdup/datafile/undotbs1.dbf',
  '+DATA/testdbdup/datafile/users01.dbf'
CHARACTER SET AL32UTF8
;

Step 9: Now shutdown the database and make changes for db_name in init.ora file from testDB to tstdbdup.

<note: the database name shouldnot exceed more than 8 characters can see the error message below>

**********************************************************

[oracle@localhost tmp]$ echo $ORACLE_SID
testdbdup
[oracle@localhost tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 20:51:14 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/inittestdb.ora'
ORA-01127: database name 'TESTDBDUP' exceeds size limit of 8 characters
SQL> exit
Disconnected

***********************************************************

[oracle@localhost tmp]$ export ORACLE_SID=tstdbdup
[oracle@localhost tmp]$ vi control_testdbdup.sql
[oracle@localhost tmp]$ echo $ORACLE_SID
tstdbdup
[oracle@localhost tmp]$
[oracle@localhost tmp]$
[oracle@localhost tmp]$
[oracle@localhost tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 20:54:11 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/inittestdb.ora'
ORACLE instance started.

Total System Global Area  477073408 bytes
Fixed Size                  1337324 bytes
Variable Size             146802708 bytes
Database Buffers          322961408 bytes
Redo Buffers                5971968 bytes
SQL> @/tmp/control_testdbdup.sql
CREATE CONTROLFILE SET DATABASE "TSTDBDUP" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '+DATA/testdbdup/controlfile/control01.ctl'
ORA-17502: ksfdcre:4 Failed to create file
+DATA/testdbdup/controlfile/control01.ctl
ORA-15005: name "testdbdup/controlfile/control01.ctl" is already used by an
existing alias

<Note: remove the existing control file from ASM location and again execute the command>

SQL> @/tmp/control_testdbdup.sql

Control file created.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


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> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TSTDBDUP  READ WRITE           PRIMARY

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost tmp]$

Duplicate database from 2 node RAC to single instance in the same host using tape

$ rman target sys/OracleEnterpriseMgr@ORCLLIVE1 auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 3 15:13:59 2012

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

connected to target database: ORCLLIVE (DBID=XXXXXXX)
connected to auxiliary database: DWREP (not mounted)

RMAN> duplicate target database to DWREP;

Starting Duplicate Db at 03-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2828 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCLLIVE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DWREP'' 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  db_name =  ''ORCLLIVE'' comment= ''Modified by RMAN duplicate'' scope=spfile

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

Oracle instance shut down

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2213632 bytes
Variable Size               2818574592 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  12152832 bytes

Starting restore at 03-MAY-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2514 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 +ARCHIVE/ORCLLIVE/backup/c-XXXXXXX-20120503-01
channel ORA_AUX_DISK_1: piece handle=+ARCHIVE/ORCLLIVE/backup/c-XXXXXXX-20120503-01 tag=TAG20120503T144805
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/dwrep/controlfile/control01.ctl
output file name=+ARCHIVE/dwrep/controlfile/control02.ctl
Finished restore at 03-MAY-12

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  421561242;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   set newname for datafile  7 to
 "+data";
   set newname for datafile  8 to
 "+data";
   set newname for datafile  9 to
 "+data";
   set newname for datafile  10 to
 "+data";
   set newname for datafile  11 to
 "+data";
   set newname for datafile  12 to
 "+data";
   set newname for datafile  13 to
 "+data";
   set newname for datafile  14 to
 "+data";
   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

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 03-MAY-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 +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: restoring datafile 00005 to +data
channel ORA_AUX_DISK_1: restoring datafile 00006 to +data
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data
channel ORA_AUX_DISK_1: restoring datafile 00008 to +data
channel ORA_AUX_DISK_1: restoring datafile 00009 to +data
channel ORA_AUX_DISK_1: restoring datafile 00010 to +data
channel ORA_AUX_DISK_1: restoring datafile 00011 to +data
channel ORA_AUX_DISK_1: restoring datafile 00012 to +data
channel ORA_AUX_DISK_1: restoring datafile 00013 to +data
channel ORA_AUX_DISK_1: restoring datafile 00014 to +data
channel ORA_AUX_DISK_1: reading from backup piece +ARCHIVE/ORCLLIVE/backup/cbeliv
channel ORA_AUX_DISK_1: piece handle=+ARCHIVE/ORCLLIVE/backup/ORCLLIVE_ORCLLIVE_1bna2e2r_1_1 tag=TAG20120503T142739
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:24:05
Finished restore at 03-MAY-12

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

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=782321922 file name=+DATA/dwrep/datafile/system.460.782321381
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=782321922 file name=+DATA/dwrep/datafile/sysaux.461.782321323
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=782321922 file name=+DATA/dwrep/datafile/undotbs1.314.782320863
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=782321922 file name=+DATA/dwrep/datafile/users.459.782321431
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=782321922 file name=+DATA/dwrep/datafile/undotbs2.390.782320829
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.329.782320477
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distindex.388.782320895
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.391.782320549
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.393.782320631
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=782321922 file name=+DATA/dwrep/datafile/sysaux.328.782321017
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.392.782320715
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=782321922 file name=+DATA/dwrep/datafile/system.278.782321375
datafile 13 switched to datafile copy
input datafile copy RECID=27 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.389.782320789
datafile 14 switched to datafile copy
input datafile copy RECID=28 STAMP=782321922 file name=+DATA/dwrep/datafile/t24distdata.284.782321053

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

executing command: SET until clause

Starting recover at 03-MAY-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 274 is already on disk as file +ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_1_seq_274.1151.782318755
archived log for thread 1 with sequence 275 is already on disk as file +ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_1_seq_275.882.782318849
archived log for thread 2 with sequence 216 is already on disk as file +ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_2_seq_216.5352.782318847
archived log file name=+ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_1_seq_274.1151.782318755 thread=1 sequence=274
archived log file name=+ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_2_seq_216.5352.782318847 thread=2 sequence=216
archived log file name=+ARCHIVE/ORCLLIVE/archivelog/2012_05_03/thread_1_seq_275.882.782318849 thread=1 sequence=275
media recovery complete, elapsed time: 00:01:07
Finished recover at 03-MAY-12

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''DWREP'' 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    4275781632 bytes

Fixed Size                     2213632 bytes
Variable Size               2835351808 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  12152832 bytes

sql statement: alter system set  db_name =  ''DWREP'' 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    4275781632 bytes

Fixed Size                     2213632 bytes
Variable Size               2835351808 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  12152832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DWREP" RESETLOGS ARCHIVELOG
  MAXLOGFILES    320
  MAXLOGMEMBERS      5
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      584
 LOGFILE
  GROUP  1 ( '+archive', '+data' ) SIZE 2 G  REUSE,
  GROUP  2 ( '+archive', '+data' ) SIZE 2 G  REUSE
 DATAFILE
  '+DATA/dwrep/datafile/system.460.782321381'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'
  GROUP  3 ( '+data', '+archive' ) SIZE 2 G  REUSE,
  GROUP  4 ( '+data', '+archive' ) SIZE 2 G  REUSE

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/dwrep/datafile/sysaux.461.782321323",
 "+DATA/dwrep/datafile/undotbs1.314.782320863",
 "+DATA/dwrep/datafile/users.459.782321431",
 "+DATA/dwrep/datafile/undotbs2.390.782320829",
 "+DATA/dwrep/datafile/t24distdata.329.782320477",
 "+DATA/dwrep/datafile/t24distindex.388.782320895",
 "+DATA/dwrep/datafile/t24distdata.391.782320549",
 "+DATA/dwrep/datafile/t24distdata.393.782320631",
 "+DATA/dwrep/datafile/sysaux.328.782321017",
 "+DATA/dwrep/datafile/t24distdata.392.782320715",
 "+DATA/dwrep/datafile/system.278.782321375",
 "+DATA/dwrep/datafile/t24distdata.389.782320789",
 "+DATA/dwrep/datafile/t24distdata.284.782321053";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/sysaux.461.782321323 RECID=1 STAMP=782322076
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/undotbs1.314.782320863 RECID=2 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/users.459.782321431 RECID=3 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/undotbs2.390.782320829 RECID=4 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.329.782320477 RECID=5 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distindex.388.782320895 RECID=6 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.391.782320549 RECID=7 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.393.782320631 RECID=8 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/sysaux.328.782321017 RECID=9 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.392.782320715 RECID=10 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/system.278.782321375 RECID=11 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.389.782320789 RECID=12 STAMP=782322077
cataloged datafile copy
datafile copy file name=+DATA/dwrep/datafile/t24distdata.284.782321053 RECID=13 STAMP=782322077

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=782322076 file name=+DATA/dwrep/datafile/sysaux.461.782321323
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=782322077 file name=+DATA/dwrep/datafile/undotbs1.314.782320863
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=782322077 file name=+DATA/dwrep/datafile/users.459.782321431
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=782322077 file name=+DATA/dwrep/datafile/undotbs2.390.782320829
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.329.782320477
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distindex.388.782320895
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.391.782320549
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.393.782320631
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=782322077 file name=+DATA/dwrep/datafile/sysaux.328.782321017
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.392.782320715
datafile 12 switched to datafile copy
input datafile copy RECID=11 STAMP=782322077 file name=+DATA/dwrep/datafile/system.278.782321375
datafile 13 switched to datafile copy
input datafile copy RECID=12 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.389.782320789
datafile 14 switched to datafile copy
input datafile copy RECID=13 STAMP=782322077 file name=+DATA/dwrep/datafile/t24distdata.284.782321053

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

database opened
Finished Duplicate Db at 03-MAY-12

Linux bootup ERROR:fsck.ext3:Bad magic number in super-block while trying to open /dev/sde1

Below is the step to resolve the error "Linux bootup ERROR:fsck.ext3:Bad magic number in super-block while trying to open /dev/sde1"
 

• While trying to modify/remove entry from /etc/fstab it is not allowing by telling it is read only so follow the steps to first mount it in RW mode
• If the correct root filesystem is already mounted, but just as read-only you can remount it read-write with

            mount -o remount,rw /

           then try to modify the /etc/fstab file

Changing the CHARACTERSET of database from WE8MSWIN1252 to AL32UTF8

These are the steps followed for changing the database characterset from WE8MSWIN1252 to AL32UTF8

I have to create a new DB with correct CHARACTERSET(AL32UTF8) & import the database dump to it, So in order to avoid losing data we have performed the below steps

We have followed document Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1] for it============================
START OF SCREENSHOT
============================
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> drop user csmig cascade;
drop user csmig cascade
          *
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist

SQL> @?/rdbms/admin/csminst.sql
SQL> rem
SQL> rem $Header: csmig/sql/csminst.sql /main/29 2008/09/09 00:47:40 ssubrama Exp $ csminst.sql
SQL> rem
SQL> rem Copyright (c) 1988, 2008, Oracle. All rights reserved.
SQL> rem
SQL> rem NAME
SQL> rem   csminst.sql
SQL> rem DESCRIPTION
SQL> rem   Create tables for Database Character Set Migration Utility
SQL> rem NOTE
SQL> rem   This script must be run while connected as SYS
SQL> rem MODIFIED
SQL> rem   ssubrama     09/04/08 - bug 7047837 fix create user
SQL> rem   nli       07/25/08 - fix bug 7256242, add the ORADATA user
SQL> rem   ssubrama     06/09/08 - bug 7047837 cleanup csmig schema for security
SQL> rem   xpeng        10/18/07 - fix bug 6460895
SQL> rem   ssubrama     01/09/07 - bug 5738695 add copyright information
SQL> rem   nli       08/02/06 - bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNS
SQL> rem   ywu       07/14/04 - up version
SQL> rem   fayang       04/23/04 - add column UNNESTED in CSM$TABLES
SQL> rem   fayang       04/06/04 - add SCNCOL# in EXTABLES and add a view for EXTABLES
SQL> rem   ywu       02/19/04 - fix bug 3434808, delete hard code passwd.
SQL> rem   ywu       07/29/03 - grant privilege to system for external table
SQL> rem   ywu       05/30/03 - add a table for data dictionary
SQL> rem   ywu       05/09/03 - add led parameter to csm$errors
SQL> rem   ywu       10/22/02 - add another error for codepoint exceed
SQL> rem   ywu       08/30/02 - up version
SQL> rem   ywu       07/01/02 - add size information
SQL> rem   ywu       07/02/02 - add resumable
SQL> rem   plinsley     01/24/02 - add include/exclude
SQL> rem   plinsley     04/02/01 - up version
SQL> rem   plinsley     03/23/01 - #1509940
SQL> rem   plinsley     03/26/01 - update views
SQL> rem   plinsley     12/15/00 - remove order by from view
SQL> rem   plinsley     11/03/00 - Add converter process column
SQL> rem   plinsley     10/04/00 - split tables that cross files
SQL> rem   plinsley     09/21/00 - Long ROWIDs in id
SQL> rem   plinsley     08/09/00 - Adding constraint dependency handling
SQL> rem   mtozawa      06/29/00 - add csmv$ views
SQL> rem   mtozawa      06/27/00 - add browid to csm$columns
SQL> rem   mtozawa      06/02/00 - specify the storage clause for csm$errors
SQL> rem   mtozawa      05/26/00 - bug1314547:optimize split mechanism, add blocks
SQL> rem   mtozawa      05/19/00 - Change table names from SCN$* to CSM$*
SQL> rem   mtozawa      03/08/00 - add SPLIT support
SQL> rem   mtozawa      12/21/99 - add property column to SCN$TABLES for IOT
SQL> rem   mtozawa      11/05/99 - remove storage params from SCN$ERRORS
SQL> rem   mtozawa      11/04/99 - add maxsiz to SCN$COLUMNS
SQL> rem   mtozawa      09/26/99 - Creation
SQL> rem
SQL>
SQL>
SQL> rem *****************************************************************
SQL> rem  The user CSMIG owns tables and procedures of Database Scanner
SQL> rem *****************************************************************
SQL> WHENEVER SQLERROR EXIT
SQL>
SQL> create user csmig identified by csmig password expire account lock
  2  /
User created.
SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> grant select on sys.obj$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.col$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.icol$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.ind$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.cdef$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.con$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.trigger$ to csmig
  2  /
Grant succeeded.
SQL> rem *****************************************************************
SQL> rem  DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
SQL> rem *****************************************************************
SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
  2  /
User altered.
SQL>
SQL> rem *****************************************************************
SQL> rem  Add version information for csm$* schema
SQL> rem  The schema version must be bumped up as csm$* schema get updated.
SQL> rem  VERSION HISTORY:
SQL> rem    1 ... 8.1.7
SQL> rem *****************************************************************
SQL> insert into sys.props$
  2  select 'NLS_CSMIG_SCHEMA_VERSION', 'x',
  3         'Character set migration utiltiy schema version #'
  4    from dual
  5   where not exists
  6         (select 'x' from sys.props$ where name = 'NLS_CSMIG_SCHEMA_VERSION')
  7  /
1 row created.
SQL> update sys.props$ set value$ = 5 where name = 'NLS_CSMIG_SCHEMA_VERSION'
  2  /
1 row updated.
SQL> rem *****************************************************************
SQL> rem  Database Scanner leaves the last scan parameters in CSM$PARAMETERS
SQL> rem  Each background process will read scan parameters from here.
SQL> rem *****************************************************************
SQL> create table csmig.csm$parameters
  2  ( name      varchar2(30) not null,                         /* paraneter name */
  3    value     varchar2(80) not null                        /* parameter value */
  4  )
  5  /
Table created.
SQL> drop public synonym csm$parameters
  2  /
drop public synonym csm$parameters
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$parameters for csmig.csm$parameters
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  Database Scanner saves the query string in CSM$QUERY
SQL> rem *****************************************************************
SQL> create table csmig.csm$query
  2  (
  3    value    clob not null                       /* query value */
  4  )
  5  /
Table created.
SQL> drop public synonym csm$query
  2  /
drop public synonym csm$query
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$query for csmig.csm$query
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  Database Scanner enumerate all tables need to be scanned
SQL> rem  Each background process will pick up a row from here for table to scan
SQL> rem *****************************************************************
SQL> create table csmig.csm$tables
  2  ( usr#      number not null,                   /* user id of the table owner */
  3    obj#      number not null,                       /* object id of the table */
  4    minrowid  rowid,          /* Minimum rowid of the split range of the table */
  5    maxrowid  rowid,          /* Maximum rowid of the split range of the table */
  6    property  number,                                        /* table property */
  7    blocks    number,                   /* number of blocks used by this table */
  8    files     number,                    /* number of files used by this table */
  9    who       number,              /* internal thread id who scanned the table */
 10    whoconv   number,            /* internal thread id who converted the table */
 11    lngconv   number,             /* internal thread id who converted long col */
 12    scnstart  date,                                 /* time table scan started */
 13    scnend    date,                               /* time table scan completed */
 14    scncols   number,                       /* number of columns to be scanned */
 15    scnrows   number,                                /* number of rows scanned */
 16    cnvstart  date,                              /* time table convert started */
 17    cnvend    date,                            /* time table convert completed */
 18    lngstart  date,                         /* time table convert long started */
 19    lngend    date,                       /* time table convert long completed */
 20    cnvcols   number,                     /* number of columns to be converted */
 21    cnvrows   number,                        /* number of rows to be converted */
 22    lngrows   number,               /* number of rows  of long to be converted */
 23    addsize   number,
 24    lastupd   rowid,                                      /* ROWID lastupdated */
 25    pstcvrows number,                      /* how many rows have been converted */
 26    lastupdlg rowid,                                      /* ROWID lastupdated */
 27    pstcvrowslg  number,                   /* how many rows have been converted */
 28    unnested  number                       /* if this table is unnested or not */
 29  )
 30  /
Table created.
SQL> drop public synonym csm$tables
  2  /
drop public synonym csm$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$tables for csmig.csm$tables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$COLUMNS contains statistic information of column data
SQL> rem *****************************************************************
SQL> create table csmig.csm$columns
  2  ( usr#       number not null,                  /* user id of the table owner */
  3    obj#       number not null,                      /* object id of the table */
  4    browid     rowid,                        /* rowid of the row in csm$tables */
  5    col#       number not null,                                   /* column id */
  6    intcol#    number not null,                /* internal column id (for ADT) */
  7    dty#       number not null,                            /* column data type */
  8    frm#       number not null,                          /* character set form */
  9    numrows    number not null,                /* number of rows in this table */
 10    nulcnt     number not null,                    /* number of null cell data */
 11    cnvcnt     number not null,    /* number of cell data that need to convert */
 12    cnvtype    number default 0 not null,                      /* convert type */
 13                 /* 1 = data in data dictionary, and can be converted by csconv*/
 14    errcnt     number not null,      /* number of cell data that has exception */
 15    sizerr     number not null, /* number of cell data that exceed column size */
 16    cnverr     number not null, /* number of cell data that undergo lossy conv.*/
 17    maxsiz     number not null,               /* max post conversion data size */
 18    chrsiz     number not null,            /* truncation due to char semantics */
 19    cnvsuc     number,                         /* cells converted successfully */
 20    cnvtrn     number,                      /* cells converted with truncation */
 21    cnvlos     number,                    /* cells converted with lossy result */
 22    cnvfai     number                               /* cells failed to convert */
 23  )
 24  /
Table created.
SQL> drop public synonym csm$columns
  2  /
drop public synonym csm$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$columns for csmig.csm$columns
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$EXTABLES contains exception tables
SQL> rem *****************************************************************
SQL> create table csmig.csm$extables
  2  ( usr#       number not null,                  /* user id of the table owner */
  3    obj#       number not null,                      /* object id of the table */
  4    col#       number,                                            /* column id */
  5    intcol#    number,                         /* internal column id (for ADT) */
  6    dty#       number,                                     /* column data type */
  7    frm#       number,                                   /* character set form */
  8    property   number default 0 not null,                   /* property of row */
  9    scncol#    number                                /* column id to be scaned */
 10  )
 11  /
Table created.
SQL> drop public synonym csm$extables
  2  /
drop public synonym csm$extables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$extables for csmig.csm$extables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$ERRORS contains individual exception information
SQL> rem *****************************************************************
SQL> create table csmig.csm$errors
  2  ( err#       number not null,                              /* exception type */
  3    usr#       number not null,            /* user id of the object/data owner */
  4    obj#       number not null,                                  /* object id  */
  5    col#       number,                                 /* column id / position */
  6    intcol#    number,                         /* internal column id (for ADT) */
  7    typ#       number,                       /* column data type / object type */
  8    frm#       number,                                   /* character set form */
  9    cnvsize    number,                            /* post conversion data size */
 10    id$        varchar2(1000),              /* rowid / name to identify object */
 11    csidleds   number,                      /* number of charset id from led   */
 12    csidled1   number,                      /* first charset id from led       */
 13    csidled2   number,                      /* second charset id from led      */
 14    csidled3   number,                      /* third charset id from led       */
 15    langidleds number,                      /* number of language id from led  */
 16    langidled1 number,                      /* first language id from led      */
 17    langidled2 number,                      /* second language id from led     */
 18    langidled3 number                        /* third language id from led     */
 19  )
 20  pctfree 0 pctused 99
 21  storage(next 100K maxextents unlimited pctincrease 0)
 22  /
Table created.
SQL> drop public synonym csm$errors
  2  /
drop public synonym csm$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$errors for csmig.csm$errors
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$LANGID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$langid
  2  ( obj#       number not null,                                  /* object id  */
  3    langid     number,                                 /* language id from led */
  4    count      number
  5  )
  6  /
Table created.
SQL> drop public synonym csm$langid
  2  /
drop public synonym csm$langid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$langid for csmig.csm$langid
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$CHARSETID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$charsetid
  2  ( obj#       number not null,                                  /* object id  */
  3    csid       number,                                 /* language id from led */
  4    count      number
  5  )
  6  /
Table created.
SQL> drop public synonym csm$charsetid
  2  /
drop public synonym csm$charsetid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$charsetid for csmig.csm$charsetid
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$INDEXES lists indexes to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$indexes
  2  ( obj#       number not null                       /* object id of the index */
  3  )
  4  /
Table created.
SQL> drop public synonym csm$indexes
  2  /
drop public synonym csm$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$indexes for csmig.csm$indexes
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$CONSTRAINTS lists constraints to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$constraints
  2  (
  3    rid        number not null,                   /* root constraint id         */
  4    lvl        number,                            /* constraint level           */
  5    con#       number not null                    /* internal constraint number */
  6  )
  7  /
Table created.
SQL> drop public synonym csm$constraints
  2  /
drop public synonym csm$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$constraints for csmig.csm$constraints
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$TRIGGERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$triggers
  2  ( obj#       number not null                     /* object id of the trigger */
  3  )
  4  /
Table created.
SQL> drop public synonym csm$triggers
  2  /
drop public synonym csm$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$triggers for csmig.csm$triggers
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$DICTUSERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$dictusers
  2  ( user#       number not null,                /* usre id for all data dictionary */
  3    username    varchar2(30)
  4  )
  5  /
Table created.
SQL> drop public synonym csm$dictusers
  2  /
drop public synonym csm$dictusers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$dictusers for csmig.csm$dictusers
  2  /
Synonym created.
SQL> insert into csmig.csm$dictusers
  2    select distinct u.user_id, u.username from all_users u, sys.ku_noexp_view k
  3    where (k.OBJ_TYPE='USER' and k.name=u.username) or (u.username in ('SYSTEM', 'ORDDATA'))
  4  /
17 rows created.
SQL> rem
SQL> rem  define CSMV$ views
SQL> rem
SQL> rem *****************************************************************
SQL> rem  CSMV$TABLES lists tables (to be) scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$tables
  2        (owner_id, owner_name, table_id, table_name, MIN_ROWID, MAX_ROWID,
  3         BLOCKS, SCAN_COLUMNS, SCAN_ROWS, SCAN_START, SCAN_END)
  4      as
  5  select c.usr#, u.username, c.obj#, o.name,
  6         rowidtochar(c.minrowid), rowidtochar(c.maxrowid),
  7         c.blocks, c.scncols, c.scnrows,
  8         to_char(c.scnstart,'hh24:mi:ss'), to_char(c.scnend,'hh24:mi:ss')
  9    from csm$tables c, all_users u, sys.obj$ o
 10   where c.usr#=u.user_id and c.obj#=o.obj#
 11  /
View created.
SQL> drop public synonym csmv$tables
  2  /
drop public synonym csmv$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$tables for csmig.csmv$tables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$COLUMNS lists columns scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$columns
  2       (owner_id, owner_name, table_id, table_name, column_id, column_intid,
  3        column_name, column_type, total_rows, null_rows, conv_rows, error_rows,
  4        exceed_size_rows, data_loss_rows, cs_exceed_size_rows, max_post_convert_size)
  5      as
  6  select c.usr#, u.username, c.obj#, o.name, c.col#, c.intcol#, co.name,
  7         decode(c.frm#, 2, 'N', '') ||
  8         decode(c.dty#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB',''),
  9         c.numrows, c.nulcnt, c.cnvcnt, c.errcnt, c.sizerr, c.cnverr, c.chrsiz, c.maxsiz
 10    from csm$columns c, all_users u, sys.obj$ o, sys.col$ co
 11   where c.usr#=u.user_id and c.obj#=o.obj# and c.obj#=co.obj#
 12     and c.col#=co.col# and c.intcol#=co.intcol#
 13  /
View created.
SQL> drop public synonym csmv$columns
  2  /
drop public synonym csmv$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$columns for csmig.csmv$columns
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$ERRORS lists exceptional data cell information
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$errors
  2        (owner_id, owner_name, table_id, table_name,
  3         column_id, column_intid, column_name, data_rowid,
  4         column_type, error_type)
  5      as
  6  select e.usr#, u.username, e.obj#, o.name,
  7         e.col#, e.intcol#, c.name, e.id$,
  8         decode(e.frm#, 2, 'N', '') ||
  9         decode(e.typ#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB'),
 10         decode(e.err#, 0, 'CONVERTIBLE', 1, 'EXCEED_SIZE', 2, 'DATA_LOSS',
 11                        3, 'CS_EXCEED_SIZE')
 12    from csm$errors e, all_users u, sys.obj$ o, sys.col$ c
 13   where e.usr#=u.user_id and e.obj#=o.obj#
 14     and e.obj#=c.obj# and e.col#=e.col# and e.intcol#=c.intcol#
 15  /
View created.
SQL> drop public synonym csmv$errors
  2  /
drop public synonym csmv$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$errors for csmig.csmv$errors
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$INDEXES lists all indexes to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$indexes
  2        (index_owner_id, index_owner_name, index_id, index_name,
  3         index_status#, index_status,
  4         table_owner_id, table_owner_name, table_id, table_name,
  5         column_id, column_intid, column_name)
  6      as
  7  select iu.user_id, iu.username, io.obj#, io.name, id.flags,
  8         decode(bitand(id.flags,1), 1, 'UNUSABLE', 'VALID'),
  9         bu.user_id, bu.username, bo.obj#, bo.name,
 10         cl.col#, cl.intcol#, cl.name
 11    from csm$indexes ci, sys.icol$ ic, sys.ind$ id, all_users iu,
 12         sys.obj$ io, all_users bu, sys.obj$ bo, sys.col$ cl
 13   where ci.obj#=ic.obj# and ci.obj#=id.obj#
 14     and ci.obj#=io.obj# and io.owner#=iu.user_id
 15     and ic.bo# =bo.obj# and bo.owner#=bu.user_id
 16     and ic.bo#=cl.obj# and ic.col#=cl.col# and ic.intcol#=cl.intcol#
 17  /
View created.
SQL> drop public synonym csmv$indexes
  2  /
drop public synonym csmv$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$indexes for csmig.csmv$indexes
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$CONSTRAINTS lists all constraints to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$constraints
  2        (owner_id, owner_name, constraint_id, constraint_name,
  3         constraint_type#, constraint_type, table_id, table_name,
  4         constraint_rid, constraint_level)
  5      as
  6  select c.owner#, u.username, c.con#, c.name, cd.type#,
  7         decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY_KEY', 3, 'UNIQUE',
  8                          4, 'REFERENTIAL', 'UNKNOWN'),
  9         o.obj#, o.name, cc.rid, cc.lvl
 10    from csm$constraints cc, sys.cdef$ cd, sys.con$ c, all_users u, sys.obj$ o
 11   where cc.con#=cd.con# and cc.con#=c.con#
 12     and c.owner#=u.user_id and cd.obj#=o.obj#
 13  /
View created.
SQL> drop public synonym csmv$constraints
  2  /
drop public synonym csmv$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$constraints for csmig.csmv$constraints
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$TRIGGERS lists all triggers to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$triggers
  2        (trigger_owner_id, trigger_owner_name, trigger_id, trigger_name,
  3         table_owner_id, table_owner_name, table_id, table_name)
  4      as
  5  select ru.user_id, ru.username, tr.obj#, ro.name, bu.user_id,
  6         bu.username, tr.baseobject, bo.name
  7    from csm$triggers ct, sys.trigger$ tr, all_users ru, sys.obj$ ro,
  8         all_users bu, sys.obj$ bo
  9   where ct.obj#=tr.obj# and ct.obj#=ro.obj# and ro.owner#=ru.user_id
 10     and tr.baseobject=bo.obj# and bu.user_id=bo.owner#
 11  /
View created.
SQL> drop public synonym csmv$triggers
  2  /
drop public synonym csmv$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$triggers for csmig.csmv$triggers
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$EXTABLES lists all distinct objects to be scaned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$extables
  2        (obj#, usr#, property)
  3      as
  4  select     distinct(obj#), usr#, property
  5    from csm$extables where property=0;
View created.
SQL> /
View created.
SQL> rem *****************************************************************
SQL> rem  SYS.CSMV$KTFBUE wraps the fixed table sys.x$ktfbue in data
SQL> rem  dictionary for users only with DBA privilege to access
SQL> rem *****************************************************************
SQL> create or replace view sys.csmv$ktfbue as select * from sys.x$ktfbue;
View created.
SQL> /
View created.
SQL> grant select on sys.csmv$ktfbue to dba;
Grant succeeded.
SQL> /
Grant succeeded.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ echo $ORACLE_SID
labdb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:04:02 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn csmig/csmig
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter account csmig unlock;
alter account csmig unlock
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter user csmig account unlock;
User altered.
SQL> conn csmig/csmig
ERROR:
ORA-28001: the password has expired

Changing password for csmig
New password:
Retype new password:
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied

Password changed
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter user csmig identified by oracle;
User altered.
SQL> conn csmig/oracle
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> exit\
SP2-0584: EXIT variable "\" was non-numeric
Usage: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ]
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ which csscaner
/usr/bin/which: no csscaner in (/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/)
[oracle@localhost ~]$ which csscan
/u01/app/oracle/oracle11201/bin/csscan
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ csscan
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ ldd `which sysresv`
        linux-gate.so.1 =>  (0x00696000)
        libclntsh.so.11.1 => /u01/app/oracle/oracle11201/lib/libclntsh.so.11.1 (0x00697000)
        libnnz11.so => /u01/app/oracle/oracle11201/lib/libnnz11.so (0x00110000)
        libdl.so.2 => /lib/libdl.so.2 (0x0035d000)
        libm.so.6 => /lib/libm.so.6 (0x00361000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00388000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x003a0000)
        libc.so.6 => /lib/libc.so.6 (0x003b7000)
        libaio.so.1 => /usr/lib/libaio.so.1 (0x00508000)
        /lib/ld-linux.so.2 (0x00590000)
[oracle@localhost ~]$ echo $ORACLE_HOME
/u01/app/oracle/oracle11201
[oracle@localhost ~]$ cat /etc/oratab
#

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/grid11201:N
labdb:/u01/app/oracle/oracle11201:N
[oracle@localhost ~]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
[oracle@localhost ~]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/oracle11201/lib:/usr/lib
[oracle@localhost ~]$ echo $CLASSPATH
[oracle@localhost ~]$ csscan

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:00:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username:
Password:

ORA-01017: invalid username/password; logon denied
Scanner terminated unsuccessfully.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:01:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACUAABAAAASYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@localhost ~]$ ls -ltr
total 80
drwxr-x--- 3 oracle oinstall  4096 Feb 25 19:55 oradiag_oracle
drwxr-xr-x 2 oracle oinstall  4096 Mar 21 00:02 backup
-rw-r--r-- 1 oracle oinstall 17710 Mar 29 18:38 dgdiag_phystby_labdb_Mar29_0638.out
-rw-r--r-- 1 oracle oinstall    69 Apr 22 08:42 afiedt.buf
-rw-r--r-- 1 oracle oinstall 25279 Apr 23 19:02 csminst.log
-rw-r--r-- 1 oracle oinstall   270 Apr 23 20:00 scan.out
-rw-r--r-- 1 oracle oinstall  7717 Apr 23 20:02 instchkc.txt
-rw-r--r-- 1 oracle oinstall   582 Apr 23 20:02 instchkc.out
-rw-r--r-- 1 oracle oinstall  1357 Apr 23 20:02 instchkc.err
[oracle@localhost ~]$ view instchkc.err

12.d) When using (full) export/import to go to a new/other AL32UTF8 database and there was NO "Truncation" data, only

"Convertible" and "Changeless" in the csscan done in point 4: expdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=expdpFull.log
impdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=impdpDB_testDB.log table_exists_action=replace

Disaster recovery using RMAN(losing both spfile & control file)

Hi ,
Here i had simulated a situation by manually deleting the spfile & control files from ASM location(we need to shutdown database in order to be able to delete control files)

Before this we need to check for the RMAN backup details.(backup sets)

The steps i followed are as below
========================


[oracle@localhost ~]$ ps -fe|grep pmon
grid      4157     1  0 16:18 ?        00:00:00 asm_pmon_+ASM
oracle    4405     1  0 16:25 ?        00:00:01 ora_pmon_LABDBSBY
oracle   13004 12464  0 20:46 pts/5    00:00:00 grep pmon
[oracle@localhost ~]$ export ORACLE_SID=labdb
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 16 20:51:16 2012

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

connected to target database (not started)

RMAN> set dbid=1788478824

executing command: SET DBID

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/labdb/spfilelabdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-15173: entry 'spfilelabdb.ora' does not exist in directory 'labdb'
ORA-06512: at line 4

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

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

RMAN> restore spfile from autobackup;

Starting restore at 16-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120416
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120415
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120414
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120413
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120412
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120411
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120410
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/16/2012 21:11:30
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile to '+DATA/LABDB/spfilelabdb.ora' from '+DATA/labdb/autobackup/2012_04_16/s_780771691.600.780771707';

Starting restore at 16-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA/labdb/autobackup/2012_04_16/s_780771691.600.780771707
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-APR-12

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     803500032 bytes

Fixed Size                     1339516 bytes
Variable Size                222302084 bytes
Database Buffers             574619648 bytes
Redo Buffers                   5238784 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 16-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

recovery area destination: +DATA
database name (or database unique name) used for search: LABDB
channel ORA_DISK_1: AUTOBACKUP +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120416
channel ORA_DISK_1: restoring control file from AUTOBACKUP +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/labdb/controlfile/current.260.780787841
Finished restore at 16-APR-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 16-APR-12
Starting implicit crosscheck backup at 16-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 16-APR-12

Starting implicit crosscheck copy at 16-APR-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-APR-12

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

List of Cataloged Files
=======================
File Name: +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_211.602.780771917
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_212.603.780772319
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_213.606.780772735
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_214.610.780773129
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_215.612.780774801
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_216.613.780774867
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_217.616.780775293
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_218.617.780775679
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_219.620.780776059
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_220.622.780776441
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_221.623.780776827
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_222.625.780777205
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_223.628.780777587
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_224.630.780777965
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_225.631.780778341
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_226.634.780778727
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_227.635.780779109
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_228.638.780779499
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_229.640.780779879
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_230.641.780780261
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_231.643.780780649
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_232.646.780781035
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_233.647.780781419
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_234.650.780781805
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_235.652.780782187
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_236.653.780782567
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_237.655.780782953
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_238.658.780783337
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_239.659.780783729
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_240.662.780784119

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 +DATA/labdb/datafile/system.256.776203549
channel ORA_DISK_1: restoring datafile 00002 to +DATA/labdb/datafile/sysaux.257.776203551
channel ORA_DISK_1: restoring datafile 00003 to +DATA/labdb/datafile/undotbs1.258.776203551
channel ORA_DISK_1: restoring datafile 00004 to +DATA/labdb/datafile/users.259.776203551
channel ORA_DISK_1: reading from backup piece +DATA/labdb/backupset/2012_04_16/nnndf0_tag20120416t165621_0.597.780771385
channel ORA_DISK_1: piece handle=+DATA/labdb/backupset/2012_04_16/nnndf0_tag20120416t165621_0.597.780771385 tag=TAG20120416T165621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:59
Finished restore at 16-APR-12

RMAN> recover database;

Starting recover at 16-APR-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 210 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_210.598.780771501
archived log for thread 1 with sequence 211 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_211.602.780771917
archived log for thread 1 with sequence 212 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_212.603.780772319
archived log for thread 1 with sequence 213 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_213.606.780772735
archived log for thread 1 with sequence 214 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_214.610.780773129
archived log for thread 1 with sequence 215 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_215.612.780774801
archived log for thread 1 with sequence 216 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_216.613.780774867
archived log for thread 1 with sequence 217 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_217.616.780775293
archived log for thread 1 with sequence 218 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_218.617.780775679
archived log for thread 1 with sequence 219 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_219.620.780776059
archived log for thread 1 with sequence 220 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_220.622.780776441
archived log for thread 1 with sequence 221 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_221.623.780776827
archived log for thread 1 with sequence 222 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_222.625.780777205
archived log for thread 1 with sequence 223 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_223.628.780777587
archived log for thread 1 with sequence 224 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_224.630.780777965
archived log for thread 1 with sequence 225 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_225.631.780778341
archived log for thread 1 with sequence 226 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_226.634.780778727
archived log for thread 1 with sequence 227 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_227.635.780779109
archived log for thread 1 with sequence 228 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_228.638.780779499
archived log for thread 1 with sequence 229 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_229.640.780779879
archived log for thread 1 with sequence 230 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_230.641.780780261
archived log for thread 1 with sequence 231 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_231.643.780780649
archived log for thread 1 with sequence 232 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_232.646.780781035
archived log for thread 1 with sequence 233 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_233.647.780781419
archived log for thread 1 with sequence 234 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_234.650.780781805
archived log for thread 1 with sequence 235 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_235.652.780782187
archived log for thread 1 with sequence 236 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_236.653.780782567
archived log for thread 1 with sequence 237 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_237.655.780782953
archived log for thread 1 with sequence 238 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_238.658.780783337
archived log for thread 1 with sequence 239 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_239.659.780783729
archived log for thread 1 with sequence 240 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_240.662.780784119
archived log for thread 1 with sequence 241 is already on disk as file +DATA/labdb/onlinelog/group_3.261.779847493
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_210.598.780771501 thread=1 sequence=210
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_211.602.780771917 thread=1 sequence=211
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_212.603.780772319 thread=1 sequence=212
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_213.606.780772735 thread=1 sequence=213
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_214.610.780773129 thread=1 sequence=214
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_215.612.780774801 thread=1 sequence=215
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_216.613.780774867 thread=1 sequence=216
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_217.616.780775293 thread=1 sequence=217
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_218.617.780775679 thread=1 sequence=218
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_219.620.780776059 thread=1 sequence=219
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_220.622.780776441 thread=1 sequence=220
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_221.623.780776827 thread=1 sequence=221
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_222.625.780777205 thread=1 sequence=222
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_223.628.780777587 thread=1 sequence=223
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_224.630.780777965 thread=1 sequence=224
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_225.631.780778341 thread=1 sequence=225
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_226.634.780778727 thread=1 sequence=226
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_227.635.780779109 thread=1 sequence=227
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_228.638.780779499 thread=1 sequence=228
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_229.640.780779879 thread=1 sequence=229
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_230.641.780780261 thread=1 sequence=230
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_231.643.780780649 thread=1 sequence=231
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_232.646.780781035 thread=1 sequence=232
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_233.647.780781419 thread=1 sequence=233
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_234.650.780781805 thread=1 sequence=234
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_235.652.780782187 thread=1 sequence=235
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_236.653.780782567 thread=1 sequence=236
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_237.655.780782953 thread=1 sequence=237
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_238.658.780783337 thread=1 sequence=238
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_239.659.780783729 thread=1 sequence=239
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_240.662.780784119 thread=1 sequence=240
archived log file name=+DATA/labdb/onlinelog/group_3.261.779847493 thread=1 sequence=241
media recovery complete, elapsed time: 00:00:32
Finished recover at 16-APR-12

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/16/2012 21:54:02
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>