ORACLE DBA

Create single instance standby database for RAC Database

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Sun, 22 Jan 2012 03:01:23 PST

here i created a physical standby database stbydb for RAC database primdb instance names primdb1 and primdb2 , server names:- DC1 and DC2.

PRIMARY RAC DATABASE INSTANCE PRIMDB1 IN HOST MACHINE DC1
PRIMARY RAC DATABASE INSTANCE PRIMDB2 IN HOST MACHINE DC2

CREATING A STANDBY DATABASE IN SERVER DC2 WITH NAME STBYDB

ASSUMPTION:
Primary:

· The RAC Primary Database is in fully operating condition in Archivelog mode using ASM storage (shared location) as archivelog destination.
· The Primary Database is using ASM as Storage and OMFs.
· Listener LISTENER has already been created.

steps in primary database Servers:-
# Database must in Archivelog mode.
# Enable Force Logging.
# Create the Standby Redo logs.
# Create a password file for all instances( Password must same in all nodes).
# Update listener.ora file to include SID information on each node in cluster.
# Verify cluster Service is available.
# Verify TNSNAMES.ORA file.
# Modify init.ora Parameters For DataGuard Configuration.
# Create temporary directory to hold the RMAN backup of this database.
# Backup the Primary Database for Standby.
# copy the Primary Database backup to the standby database server to create standby database.

steps in Standby database server:-
# create required folders and directories.
# Modify the init.ora parameter on standby database.
# create a password file(Password must same as primary database instances)
# Update listener.ora file to include SID information
# Add required TNS entries for standby database on all servers.
# Verify TNSNAMES.ORA file.
# Create standby database.

[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [black] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 03:41:53 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Next log sequence to archive   17
Current log sequence           17
SQL> alter database force logging;

Database altered.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
primdb1          OPEN
primdb2          OPEN

SQL> select name,db_unique_name,database_role from gv$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB    primdb                         PRIMARY
PRIMDB    primdb                         PRIMARY

IN PRIMARY DATABASE(any one of the node)
=====================================================================================

alter system set log_file_name_convert='+DATA/stbydb,'+DATA/primdb' scope=spfile sid='*';
alter system set db_file_name_convert='+DATA/stbydb','+DATA/primdb' scope=spfile sid='*';
alter system set fal_client='primdb1' sid='primdb1';
alter system set fal_client='primdb2' sid='primdb2';
alter system set fal_server='stbydb' sid='*';
alter system set log_archive_config='dg_config=(primdb,stbydb)' sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_2='ENABLE' sid='*';
alter system set log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' sid='*';
alter system set log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_1='enable' sid='*';
alter system set log_archive_dest_state_3='enable' sid='*';
alter system set log_archive_max_processes=7 sid='*';
alter system set log_archive_min_succeed_dest=2 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' sid='*';
alter system set standby_file_management='auto' sid='*';


primdb2.__db_cache_size=520093696
primdb1.__db_cache_size=520093696
primdb2.__java_pool_size=16777216
primdb1.__java_pool_size=16777216
primdb2.__large_pool_size=16777216
primdb1.__large_pool_size=16777216
primdb2.__pga_aggregate_target=587202560
primdb1.__pga_aggregate_target=587202560
primdb2.__sga_target=872415232
primdb1.__sga_target=872415232
primdb2.__shared_io_pool_size=0
primdb1.__shared_io_pool_size=0
primdb2.__shared_pool_size=301989888
primdb1.__shared_pool_size=301989888
primdb2.__streams_pool_size=0
primdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/primdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/stbydb','+DATA/primdb'
*.db_name='primdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)'
primdb1.fal_client='primdb1'
primdb2.fal_client='primdb2'
*.fal_server='stbydb'
primdb2.instance_number=2
primdb1.instance_number=1
*.log_archive_config='dg_config=(primdb,stbydb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_archive_dest='+DATA/primdb/STANDBYLOG'
*.standby_file_management='auto'
primdb1.thread=1
primdb2.thread=2
primdb2.undo_tablespace='UNDOTBS1'
primdb1.undo_tablespace='UNDOTBS2'

SQL> select force_logging from v$database;

FOR
---
YES

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile sid='*';

System altered.



[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle force=y entries=5 ignorecase=y
[oracle@dc1 dbs]$

[oracle@dc2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle entries=5 force=y ignorecase=y

SQL> sho parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

create standby logfile on primary database
=====================================================================================
SQL> col member for a56
SQL> select group#,member,type from v$logfile order by 1;

  GROUP# MEMBER                                                   TYPE
---------- -------------------------------------------------------- -------
       1 +DATA/primdb/onlinelog/group_1.468.773206197             ONLINE
       1 +DATA/primdb/onlinelog/group_1.467.773206195             ONLINE
       2 +DATA/primdb/onlinelog/group_2.470.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.469.773206197             ONLINE
       3 +DATA/primdb/onlinelog/group_3.473.773206427             ONLINE
       3 +DATA/primdb/onlinelog/group_3.474.773206427             ONLINE
       4 +DATA/primdb/onlinelog/group_4.475.773206429             ONLINE
       4 +DATA/primdb/onlinelog/group_4.476.773206429             ONLINE

8 rows selected.


SQL> select group#,bytes from v$Log;

  GROUP#      BYTES
---------- ----------
       1    5242880
       2    5242880
       3    5242880
       4    5242880

SQL>
alter database add standby logfile thread 1
group 5 size 5M,
group 6 size 5M;

Database altered.

SQL> alter database add standby logfile thread 2
group 7 size 5M,
group 8 size 5M;

Database altered.


SQL> select group#,member ,type from v$Logfile order by 1;

  GROUP# MEMBER                                                   TYPE
---------- -------------------------------------------------------- -------
       1 +DATA/primdb/onlinelog/group_1.467.773206195             ONLINE
       1 +DATA/primdb/onlinelog/group_1.468.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.469.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.470.773206197             ONLINE
       3 +DATA/primdb/onlinelog/group_3.473.773206427             ONLINE
       3 +DATA/primdb/onlinelog/group_3.474.773206427             ONLINE
       4 +DATA/primdb/onlinelog/group_4.475.773206429             ONLINE
       4 +DATA/primdb/onlinelog/group_4.476.773206429             ONLINE
       5 +DATA/primdb/onlinelog/group_5.481.773209067             STANDBY
       5 +DATA/primdb/onlinelog/group_5.482.773209067             STANDBY
       6 +DATA/primdb/onlinelog/group_6.483.773209067             STANDBY
       6 +DATA/primdb/onlinelog/group_6.484.773209069             STANDBY
       7 +DATA/primdb/onlinelog/group_7.485.773209079             STANDBY
       7 +DATA/primdb/onlinelog/group_7.486.773209079             STANDBY
       8 +DATA/primdb/onlinelog/group_8.487.773209079             STANDBY
       8 +DATA/primdb/onlinelog/group_8.488.773209081             STANDBY

16 rows selected.


create pfile for standby database
=====================================================================================
SQL> create pfile='$ORACLE_HOME/dbs/initstbydb.ora' from spfile;

File created.

SQL>

edit the required parameters

remove the red coloured lines for the standby database initstbydb.ora file
edit/change the blue coloured lines as per standby database required.

primdb2.__db_cache_size=520093696 primdb1.__db_cache_size=520093696 primdb2.__java_pool_size=16777216 primdb1.__java_pool_size=16777216 primdb2.__large_pool_size=16777216 primdb1.__large_pool_size=16777216 primdb2.__pga_aggregate_target=587202560 primdb1.__pga_aggregate_target=587202560 primdb2.__sga_target=872415232 primdb1.__sga_target=872415232 primdb2.__shared_io_pool_size=0 primdb1.__shared_io_pool_size=0 primdb2.__shared_pool_size=301989888 primdb1.__shared_pool_size=301989888 primdb2.__streams_pool_size=0 primdb1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/primdb/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.0.0' *.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/stbydb','+DATA/primdb' *.db_name='primdb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)' primdb1.fal_client='primdb1' primdb2.fal_client='primdb2' *.fal_server='stbydb' primdb2.instance_number=2 primdb1.instance_number=1 *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' *.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.memory_target=1449132032 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.sec_case_sensitive_logon=FALSE *.standby_file_management='auto' primdb1.thread=1 primdb2.thread=2 primdb2.undo_tablespace='UNDOTBS1' primdb1.undo_tablespace='UNDOTBS2'

*************************************************************************************
after editing the initstbydb.ora file
*************************************************************************************

db_cache_size=520093696 java_pool_size=16777216 large_pool_size=16777216 pga_aggregate_target=587202560 sga_target=872415232 shared_pool_size=301989888 streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/stbydb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA/stbydb/controlfile/control01.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/primdb/','+DATA/stbydb/' *.db_name='primdb' db_unique_name='stbydb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)' fal_client='stbydb' *.fal_server='primdb' *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_2='SERVICE=primdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_3='LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.standby_file_management='auto' undo_tablespace='UNDOTBS1' SEC_CASE_SENSITIVE_LOGON=FALSE

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

create required folder for stbydb database and also add an entry in the /etc/oratab for stbydb database ,
create a password file for stbydb database.(must have similar password like primary RAC database )
add required tns entries in the tnsnames.ora and listener entry in the listener.ora file
take a RMAN backup of target database and target database controlfile from any one of the rac node
and scp the backup to the standby database host machine to create standby database.

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

[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$ . oraenv
ORACLE_SID = [oracle] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 backup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 04:37:01 2012

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

connected to target database: PRIMDB (DBID=447762734)

RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backup/%U';
new RMAN configuration parameters are successfully stored

RMAN> backup as compressed backupset database plus archivelog;

RMAN> backup current controlfile for standby;

RMAN> backup archivelog all;


creating directories
=====================================================

[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/primdb/adump
[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/backup

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@dc2 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd data
ASMCMD> mkdir STBYDB
ASMCMD> cd STBYDB
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
STANDBYLOG/
TEMPFILE/
ASMCMD>

create password file for standby database.
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 ignorecase=y
[oracle@dc2 dbs]$

copy the backup to standby server.
[oracle@dc1 backup]$ ls -ltr
total 290584
-rw-r----- 1 oracle oinstall   4157952 Jan 22 04:38 01n1cg5n_1_1
-rw-r----- 1 oracle oinstall 259842048 Jan 22 04:40 02n1cg5t_1_1
-rw-r----- 1 oracle oinstall   1114112 Jan 22 04:40 03n1cg9g_1_1
-rw-r----- 1 oracle oinstall    157184 Jan 22 04:40 04n1cg9u_1_1
-rw-r----- 1 oracle oinstall  18546688 Jan 22 04:46 05n1cglv_1_1
-rw-r----- 1 oracle oinstall  13416448 Jan 22 04:47 06n1cgn8_1_1
[oracle@dc1 backup]$ scp * oracle@dc2:/u01/app/oracle/backup/
01n1cg5n_1_1                                                100% 4061KB   4.0MB/s   00:00  
02n1cg5t_1_1                                                100%  248MB   4.3MB/s   00:58  
03n1cg9g_1_1                                                100% 1088KB   1.1MB/s   00:00  
04n1cg9u_1_1                                                100%  154KB 153.5KB/s   00:00  
05n1cglv_1_1                                                100%   18MB   2.0MB/s   00:09  
06n1cgn8_1_1                                                100%   13MB   2.6MB/s   00:05  
[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$


add an entry in the /etc/oratab file
vi /etc/oratab
stbydb:/u01/app/oracle/product/11.2.0/db_1:N


add tns entry in all nodes

example:-

[oracle@dc1 backup]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@dc1 admin]$ vi tnsnames.ora
PRIMDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = primdb)
  )
)

STBYDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = stbydb)
  )
)


[oracle@dc1 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (40 msec)
[oracle@dc1 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:37

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (0 msec)
[oracle@dc1 admin]$


[oracle@dc2 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:52

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (0 msec)
[oracle@dc2 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:56

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (10 msec)
[oracle@dc2 admin]$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

start the standby database in nomount mode

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ? stbydb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:00:18 2012

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

Connected to an idle instance.

SQL> startup nomount

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [stbydb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@PRIMDB auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 05:33:25 2012

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

connected to target database: PRIMDB (DBID=447762734)
connected to auxiliary database: PRIMDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.
*************************************************************************************

alert log file inforamtion

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/stbydb/standbylog
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
Sun Jan 22 05:52:53 2012
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:52:53 2012
MRP0 started with pid=29, OS id=5844
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: alter database recover managed standby database disconnect from session

Sun Jan 22 05:54:16 2012
alter database recover managed standby database cancel
Sun Jan 22 05:54:17 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 5844 to terminate
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (stbydb)
Managed Standby Recovery Canceled (stbydb)
Completed: alter database recover managed standby database cancel


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:56:07 2012
MRP0 started with pid=29, OS id=6135
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION



Completed: ALTER DATABASE   MOUNT
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
    returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC1]: Error 16191 connecting to primdb for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
ARC6: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE

work around:-
======================================

create password file again on both rac node database and also on standby database

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle ignorecase=y force=y entries=5

add the parameter on all databases
SEC_CASE_SENSITIVE_LOGON=FALSE      
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@dc2 dbs]$ vi initstbydb.ora
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:59:26 2012

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             335547844 bytes
Database Buffers          528482304 bytes
Redo Buffers                5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> show parameter SEC_CASE_SENSITIVE_LOGON=FALSE      
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             335547844 bytes
Database Buffers          528482304 bytes
Redo Buffers                5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Next log sequence to archive   0
Current log sequence           38
SQL>


verification
=============================================

(on primary database)

SQL> create tablespace new datafile size 2m;



SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.


(on standby database)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2

6 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


(on both RAC primary instances)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.

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

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB    stbydb                         PHYSICAL STANDBY

Thanks and Regards,
Rajesh Kumar Govindarajan.

references:-
http://www.oracledba.org/10g/dr/10gR2_dataguard_RAC_to_RAC.html
http://jhdba.wordpress.com/2008/04/11/how-not-to-set-up-remote-log-shipping-dataguard-and-streams/
http://www.dba-oracle.com/t_ora_16191_primary_log_shipping_client_not_logged_on_standby_tips.htm

Steps to Rename RAC instance name

noreply@blogger.com (Rajeshkumar Govindarajan) — Sat, 21 Jan 2012 13:57:13 PST

Steps to Rename RAC instance name

In this demo, The RAC database name:- RACDB
I have renamed the database instance racdb1 to rac1db (HOSTNAME DC1)
and instance racdb2 to rac2db (HOSTNAME DC2)
1. take a backup of the initracdb1.ora and initracdb2.ora files.

[oracle@dc1 dbs]$ cp initracdb1.ora initracdb1.ora_bkp
[oracle@dc1 dbs]$ cat initracdb1.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

2. create a new pfile from spfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/proddb/spfileracdb.ora
SQL> create pfile='$ORACLE_HOME/dbs/initrac2db.ora' from spfile;

File created.

3. Shutdown the instance on all nodes

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

4. Rename or Edit the racdb1 and racdb2 to rac1db and rac2db respectively.

before changing the instance names in init.ora file

racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb1.__oracle_base='/u01/app/oracle'
racdb2.__oracle_base='/u01/app/oracle'
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'

after editing

rac2db.__db_cache_size=708837376
rac1db.__db_cache_size=708837376
rac2db.__java_pool_size=4194304
rac1db.__java_pool_size=4194304
rac2db.__large_pool_size=4194304
rac1db.__large_pool_size=4194304
rac1db.__oracle_base='/u01/app/oracle'
rac2db.__oracle_base='/u01/app/oracle'
rac2db.__pga_aggregate_target=394264576
rac1db.__pga_aggregate_target=394264576
rac2db.__sga_target=1056964608
rac1db.__sga_target=1056964608
rac2db.__shared_io_pool_size=0
rac1db.__shared_io_pool_size=0
rac2db.__shared_pool_size=331350016
rac1db.__shared_pool_size=331350016
rac2db.__streams_pool_size=0
rac1db.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
rac2db.instance_number=2
rac1db.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
rac2db.thread=2
rac1db.thread=1
rac2db.undo_tablespace='UNDOTBS2'
rac1db.undo_tablespace='UNDOTBS1'

5.create a new password file on all the nodes on new instance SID names.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwrac1db password=oracle
[oracle@dc1 dbs]$ 
[oracle@dc2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwrac2db password=oracle

6.Add an entry in the /etc/oratab file on all the nodes
example:-
on host machine DC1
vi /etc/oratab
rac1db:/u01/app/oracle/product/11.2.0/db_1:N
on host machine DC2
vi /etc/oratab
rac2db:/u01/app/oracle/product/11.2.0/db_1:N

7.Startup the database with new init.ora file and create a spfile using new pfile.

[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [rac2db] ? rac1db
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 06:05:54 2012

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

8.Edit/add the spfile location in the initrac1db.ora and initrac2db.ora file
example:-
[oracle@dc2 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

[oracle@dc1 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

9.remove the old instance name and add a new instance name using srvctl command.

[oracle@dc2 dbs]$ srvctl remove instance -i racdb1 -d racdb 
Remove instance from the database racdb? (y/[n]) y
[oracle@dc2 dbs]$ srvctl remove instance -i racdb2 -d racdb
Remove instance from the database racdb? (y/[n]) y

[oracle@dc2 dbs]$ srvctl add instance -i rac1db -d racdb -n dc1
[oracle@dc2 dbs]$ srvctl add instance -i rac2db -d racdb -n dc2
[oracle@dc2 dbs]$ srvctl start database -d racdb

10.Verify it.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
rac2db           OPEN
rac1db           OPEN

SQL> select name from v$database;

NAME
---------
RACDB

[oracle@dc2 dbs]$ srvctl config database -d racdb
Database unique name: racdb
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racdb
Database instances: rac1db,rac2db
Disk Groups: 
Services: 
Database is administrator managed

Thanks and Regards,
Rajesh Kumar Govindarajan.

Rename RAC Database

noreply@blogger.com (Rajeshkumar Govindarajan) — Sat, 21 Jan 2012 13:40:09 PST

Here is the document to rename RAC database and next post we can see how to rename rac instance.

here is an example.

renamed RAC database proddb to racdb in servers dc1 and dc2.

SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
proddb1          dc1.localdomain
proddb2          dc2.localdomain

SQL> select name from v$database;

NAME
---------
PRODDB

taken backup of pfile initproddb1.ora and initproddb2.ora on both nodes.

[oracle@dc1 dbs]$ cat initproddb1.ora
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc1 dbs]$ cp initproddb1.ora initproddb1.ora_orig 
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd $ORACLE_HOME/dbs
[oracle@dc2 dbs]$ cat initproddb2.ora 
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc2 dbs]$ cp initproddb2.ora initproddb2.ora_bkp
[oracle@dc2 dbs]$

create pfile from spfile.
taken controlfile backup to rename rac database.

SQL> create pfile='/tmp/initproddb1.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --- or ----
SQL> 
SQL> alter database backup controlfile to trace as '/tmp/ctl_trc_bkp.txt';

Database altered.

stop the RAC database using srvctl command.

[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is running on node dc1
Instance proddb2 is running on node dc2
[oracle@dc1 ~]$ srvctl config database -d proddb
Database unique name: proddb
Database name: proddb
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/proddb/spfileproddb.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: proddb
Database instances: proddb1,proddb2
Disk Groups: DATA
Services: 
Database is administrator managed
[oracle@dc1 ~]$ 

[oracle@dc1 ~]$ srvctl stop database -d proddb
[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is not running on node dc1
Instance proddb2 is not running on node dc2
[oracle@dc1 ~]$

edit/replace proddb to racdb in the newly created pfile
and also the controlfile name.
change the value of the parameter cluster_database to false.

racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'

startup the database in nomount mode with newly created pfile.

[oracle@dc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 7 17:35:55 2012

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

Connected to an idle instance.

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

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             276826356 bytes
Database Buffers          771751936 bytes
Redo Buffers                4673536 bytes
SQL> 

create a script using backup of controlfile trace to re-create controlfile(to rename database)  
CREATE CONTROLFILE SET DATABASE "RACDB" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/proddb/onlinelog/group_1.346.771671611'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/proddb/onlinelog/group_2.341.771671615'  SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/proddb/datafile/system.256.771671395',
'+DATA/proddb/datafile/sysaux.282.771671401',
'+DATA/proddb/datafile/undotbs1.344.771671403',
'+DATA/proddb/datafile/users.343.771671403',
'+DATA/proddb/datafile/undotbs2.339.771671757'
CHARACTER SET WE8MSWIN1252
;

SQL> @'/tmp/create_ctl.sql';

Control file created.

SQL> sho parameter cluster_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

change the value of the parameter cluster_database to true and startup the database.

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

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.

create new spfile using current pfile.

SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile='/tmp/initproddb1.ora';

File created.

SQL> sho parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> 
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3,GROUP 4;

SQL> ALTER DATABASE OPEN RESETLOGS;

create a new init pfile in the dbs directory and copy and paste the spfile location.

[oracle@dc1 dbs]$ vi initracdb1.ora 
SPFILE='+DATA/proddb/spfileracdb.ora'
[oracle@dc1 dbs]$ scp initracdb1.ora dc2:$ORACLE_HOME/dbs/initracdb2.ora
initracdb1.ora                                100%   38     0.0KB/s   00:00    

create a new password file on both servers for database racdb.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb1 password=oracle
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb2.ora password=oracle
[oracle@dc2 dbs]$ 

add an entry in the /etc/oratab file on both servers
[oracle@dc2 ~]$vi /etc/oratab
racdb2:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@dc1 ~]$vi /etc/oratab
racdb1:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@dc2 ~]$ . oraenv
ORACLE_SID = [oracle] ? racdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 8 23:36:03 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.
SQL> 

SQL> select name from v$database;

NAME
---------
RACDB

SQL> select instance_name,status from gv$Instance;

INSTANCE_NAME    STATUS
---------------- ------------
racdb1           OPEN
racdb2           OPEN

register the database with grid.

[oracle@dc1 dbs]$ srvctl status database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

[oracle@dc1 dbs]$ srvctl remove database -d proddb
Remove the database proddb? (y/[n]) y
[oracle@dc1 dbs]$ srvctl add database -d racdb -o $ORACLE_HOME
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb1 -n dc1
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb2 -n dc2

[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is not running on node dc1
Instance racdb2 is not running on node dc2

[oracle@dc1 dbs]$ srvctl start database -d racdb
[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is running on node dc1
Instance racdb2 is running on node dc2

finally, Change the instance names in the tnsnames.ora and listener.ora files.

regards,
Rajesh Kumar Govindarajan.

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Tue, 18 Oct 2011 07:04:16 PDT

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

Step1:

using winSCP or anyother tool copy the patch file p6890831_111070_Linux-x86-64.zip to the environment from the machine where you are having patch file.

Step2:
login : oracle
password:
Uncompress the binary source
$unzip p6890831_111070_Linux-x86-64.zip
Example:-
After unzipping , you can find the below folder Disk1
c111ptqhdd009: /home/oracle
$ cd /oracle/app/patches/
c111ptqhdd009: /oracle/app/patches
$ ls
Disk1 p6890831_111070_Linux-x86-64.zip

Step3:
Bring down the application

Step4:
login : oracle
password:
Bring down the database
sqlplus '/as sysdba'
sql>shutdown immediate;
sql>exit
>ps -ef |grep pmon

Make sure that oracle is not running
>lsnrctl stop
>lsnrctl status
Make sure that the listener is not running.
Take a cold database backup
Controlfiles, datafiles, redo logs, archive files, tempfiles must be included in the backup. If the files are in different paths, the same has to be backedup.
>mkdir /backup_date
>cp /App1/oracle/oradata/orcl/*.* /backup/backup_date
>cd $ORACLE_HOME/dbs
>cp inittest.ora inittest.ora_bkpdate
>cp spfiletest.ora spfiletest.or_bkpdate
>cp orapwtest.ora orapwtest.ora_bkpdate

Step5:
login : oracle
password:
Take a backup of home path
>mkdir product_bkp
>cd /App1/oracle/product
>tar czf /backup/oracle_11106.tar.gz 11.1.0

Step6:
dbhome, coraenv,oraenv backup
login : root
password:
#cd /usr/local/bin
#cp dbhome dhome_bkpdate
#cp coraenv coraenv_bkpdate
#cp oraenv oraenv_bkpdate

Step7:
login : oracle
Installation of patchset

Invoke the runInstaller using the software xming to install patch graphically.

cd /oracle/app/patches/Disk1/
>./runInstaller

Select the ORACLE_HOME to upgrade and then click next, the prerequisites steps will complete last click INSTALL. At the end up the installation it will ask to run root.sh as root user. We need root user password or sudo user privilege to run root.sh script. Follow the step 8

Step8:
login : root
password:
or
login : your_SUDU_USERNAME
password:

cd /App1/oracle/product/11.1.0/db_1
./root.sh
Or
Sudu ./root.sh

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /App1/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ... overwrite (Y/N) : Y
Copying oraenv to /usr/local/bin ... overwrite (Y/N) : Y
Copying coraenv to /usr/local/bin ... overwrite (Y/N) : Y

Step9:
login : oracle
password:

Prerequisite check before upgradation
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql> startup upgrade;
sql>spool pre_upgrade.log
sql> @utlu111i.sql

If the all the values obtained above are valid, please proceed to step 10 , otherwise please correct it.

Step10:
Upgradation script execution
login : oracle
password:

cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql> spool upgrade_date.log
sql> @catupgrd.sql

Step11:
login : oracle
password:
Take check the validity, version of the installed components

sqlplus '/as sysdba'
sql>set pages 250
sql> set lines 1000
sql> col comp_name for a40
sql> select comp_name, version, status from dba_registry;

Note : The status column of the displayed output should be 'Valid' and the version column should be '11.1.0.7'
Upgradation ends with this step12. If in case any problem during the installation and the old version needs to be reverted back

sql>shutdown immediate;

Step12:
Recompiling the invalid objects
login : oracle
password:
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql>startup;
sql>spool recompile.log
sql> @utlrp.sql

Fallback plan Or To revert back to old version (11.1.0.6.0)
1. untar the binary backup
2. Backup the database the current database and then replace them with cold back taken before applying the patch.

login : root
password:
cd /usr/local/bin
#cp dbhome_bkpdate dbhome
#cp coraenv_bkpdate coraenv
#cp oraenv_bkpdate oraenv

login : oracle
password:

oracle binary restoration
cd /App1/oracle/product
>\rm -rf 11.1.0
>mkdir 11.1.0
>cp /backup/oracle_11106.tar.gz 11.1.0
>cd 11.1.0
>gunzip oracle_11106.tar.gz
>tar -xvf oracle_11106.tar

Datafile backup restoration
cd /App/oracle/oradata/orcl/
>\rm *
>cd /backup/backup_date
>cp *.* /App1/oracle/oradata/orcl/
>cd $ORACLE_HOME/dbs
>cp inittest.ora_bkpdate inittest.ora
>cp spfiletest.ora_bkpdate spfiletest.ora
>cp orapwtest.ora_bkpdate orapwtest.ora

sqlplus '/as sysdba'
sql>startup;
sql>select * from v$version;
sql>select comp_name, version, status from dba_registry;

Output should be 11.1.0.6 for version column and valid for status column.

*******************************************************************************
Thank You

Physical Standby Database for Read/Write Testing and Reporting and revert back to the physical standby database using flashback restore point.

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Thu, 01 Sep 2011 12:19:40 PDT

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

You can repeat this cycle of activate, flashback and revert as many times as is necessary.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

step 1:
Cancel Redo Apply and create a guaranteed restore point.


SQL> select flashback_on from v$database;



FLASHBACK_ON

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

NO



SQL> show parameter recovery  



NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /home/u01/app/oracle/flash_rec

                                                 overy_area

db_recovery_file_dest_size           big integer 2G

recovery_parallelism                 integer     0

SQL> 



SQL> alter database recover managed standby database cancel;



Database altered.



SQL> alter database recover managed standby database cancel;



Database altered.



SQL> create restore point before_switch_to_primary guarantee flashback database;



Restore point created.



SQL> select scn,name,time from v$restore_point;



       SCN

----------

NAME

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

TIME

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

    481243

BEFORE_SWITCH_TO_PRIMARY

31-AUG-11 10.49.34.000000000 AM



alertlog file 

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

Wed Aug 31 10:48:09 2011

MRP0: Background Media Recovery process shutdown (dbstby)

Wed Aug 31 10:48:10 2011

Managed Standby Recovery Canceled (dbstby)

Wed Aug 31 10:48:10 2011

Completed: alter database recover managed standby database cancel

Wed Aug 31 10:49:33 2011

Starting background process RVWR

RVWR started with pid=49, OS id=1629

Wed Aug 31 10:49:34 2011

Created guaranteed restore point BEFORE_SWITCH_TO_PRIMARY

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



SQL> select current_scn from v$database;



CURRENT_SCN

-----------

     481243



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     47

Next log sequence to archive   0

Current log sequence           49

SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 2:
Prepare the primary database to have the physical standby be diverged.


SQL> select current_scn from v$database;



CURRENT_SCN

-----------

     481860



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbtest/arch/

Oldest online log sequence     47

Next log sequence to archive   49

Current log sequence           49

SQL> 



SQL> alter system archive log current;



System altered.



SQL> alter system set log_archive_dest_state_2=defer;



System altered.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 3 Activate the physical standby database.


SQL> alter database activate standby database;



Database altered.





SQL> startup mount force;

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;



Database altered.



SQL> alter database open;



Database altered.





SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> 





Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database.



alert log file 

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

Wed Aug 31 10:54:18 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[9]: Assigned to RFS process 8542

RFS[9]: Identified database type as 'physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

RFS[9]: No standby redo logfiles created

Wed Aug 31 10:54:32 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[10]: Assigned to RFS process 8884

RFS[10]: Identified database type as 'physical standby'

RFS[10]: No standby redo logfiles created

RFS[10]: Archived Log: '/home/u01/app/oracle/oradata/dbstby/arch/1_49_760329654.arc'

Wed Aug 31 10:55:33 2011

alter database activate standby database

Wed Aug 31 10:55:33 2011

ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (dbstby)

Wed Aug 31 10:55:35 2011

RESETLOGS after complete recovery through change 481244

Resetting resetlogs activation ID 1153152819 (0x44bbb733)

Online log /home/u01/app/oracle/oradata/dbstby/redo01.log: Thread 1 Group 1 was previously cleared

Online log /home/u01/app/oracle/oradata/dbstby/redo02.log: Thread 1 Group 2 was previously cleared

Online log /home/u01/app/oracle/oradata/dbstby/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 481242

Wed Aug 31 10:55:35 2011

Setting recovery target incarnation to 5

Wed Aug 31 10:55:35 2011

Converting standby mount to primary mount.

Wed Aug 31 10:55:35 2011

ACTIVATE STANDBY: Complete - Database mounted as primary (dbstby)

Completed: alter database activate standby database

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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL> alter system switch logfile;



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbtest/arch/

Oldest online log sequence     51

Next log sequence to archive   53

Current log sequence           53

SQL>


SQL> startup force mount

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL> select name,scn,time from v$restore_point;



NAME                                  SCN TIME

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

BEFORE_SWITCH_TO_PRIMARY           481243 31-AUG-11 10.49.34.000000000 AM



SQL> flashback database to restore point BEFORE_SWITCH_TO_PRIMARY;



Flashback complete.



SQL> alter database convert to physical standby ;



Database altered.



SQL> startup mount force;

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.



SQL> alter database recover managed standby database disconnect;



Database altered.



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     53

Next log sequence to archive   0

Current log sequence           54

SQL>

reference:-
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Part Number B14239-05

Duplicate a database without connecting to a target database and a recovery catalog (11gR2 New Features)

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Sun, 07 Aug 2011 15:04:17 PDT

Users can duplicate a database without connecting to a target database. The only requirements for this operation are a connection to a catalog and an auxiliary database. This new functionality is useful when the target database is not always available.
Users can also duplicate a database without connecting to a target database and a recovery catalog. In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, datafile copies, archived logs, and control file copies for database duplication. This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.

here is an demo, In our example i am going to create a duplicate database with the name CLONEDB from the database DB2

1. Take backup of target database.
2. Copy the backupsets to the machine where you would like to clone the target database.
3. Create a new pfile for clone database or copy the pfile of target database and replace/edit the pfile entries for the clone database.
4. Create a password file
5. Create required folders for dump and database files.
6. Add an entry in /etc/oratab file
7. Create pfile for duplicate database
8. Add required TNS entry in tnsnames.ora file
9. startup nomount the clone/duplicate database.
10. Duplicate command in auxiliary instance.

  
SQL> select name from v$database;

NAME
--------------------------------------------------------
DB2

[oracle@node2 backupnew]$ . oraenv
ORACLE_SID = [db2] ? 
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node2 backupnew]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 8 00:31:43 2011

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

connected to target database: DB2 (DBID=1922019997)

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/%d_%s_%I_%T';

RMAN>  backup archivelog all format '/u01/app/oracle/backup/%d_%s_%I_%T';

RMAN> EXIT;

[oracle@node2 backup]$ cd /u01/app/oracle/backup
[oracle@node2 backup]$ pwd
/u01/app/oracle/backup
[oracle@node2 backup]$ ls -ltr
total 463128
-rw-r----- 1 oracle dba  28600320 Aug  8 00:33 DB2_26_1922019997_20110808
-rw-r----- 1 oracle dba 265560064 Aug  8 00:35 DB2_27_1922019997_20110808
-rw-r----- 1 oracle dba   1114112 Aug  8 00:35 DB2_28_1922019997_20110808
-rw-r----- 1 oracle dba     94208 Aug  8 00:35 DB2_29_1922019997_20110808
-rw-r----- 1 oracle dba  89183232 Aug  8 00:36 DB2_30_1922019997_20110808
-rw-r----- 1 oracle dba  89189376 Aug  8 00:37 DB2_31_1922019997_20110808
******************************************************************************
copy the backups to the second server where you would like to clone the database

[oracle@node1 oracle]$ pwd
/u01/app/oracle
[oracle@node1 oracle]$ mkdir backup

[oracle@node2 backup]$ pwd
/u01/app/oracle/backup
[oracle@node2 backup]$ scp * oracle@192.168.2.101:/u01/app/oracle/backup
DB2_26_1922019997_20110808                    100%   27MB  13.6MB/s   00:02    
DB2_27_1922019997_20110808                    100%  253MB   6.5MB/s   00:39    
DB2_28_1922019997_20110808                    100% 1088KB   1.1MB/s   00:00    
DB2_29_1922019997_20110808                    100%   92KB  92.0KB/s   00:00    
DB2_30_1922019997_20110808                    100%   85MB   6.5MB/s   00:13    
DB2_31_1922019997_20110808                    100%   85MB  14.2MB/s   00:06    
[oracle@node2 backup]$ 

[oracle@node2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@node2 dbs]$ ls | grep init
initdb2.ora
init.ora
initorcl2.ora
[oracle@node2 dbs]$ scp initdb2.ora oracle@192.168.2.101:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initclonedb.ora

edit/replace the name and directory/folder for the clone database

eg:- I have replaced/edited the pfile as below
clonedb.__db_cache_size=541065216
clonedb.__java_pool_size=4194304
clonedb.__large_pool_size=4194304
clonedb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=260046848
clonedb.__sga_target=780140544
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=218103808
clonedb.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/clonedb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.open_cursors=300
*.pga_aggregate_target=258998272
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=776994816
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/db2/','/u01/app/oracle/clonedb/'
log_file_name_convert='/u01/app/oracle/db2/','/u01/app/oracle/clonedb/'
*******************************************************************************
create a password file for clonedb 
*******************************************************************************
[oracle@node1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwclonedb password=oracle 

create required folders and directories for database files and dump files
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/adump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/udump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/bdump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/cdump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/clonedb

add an entry in the /etc/oratab file
clonedb:/u01/app/oracle/product/11.2.0/dbhome_1:N       

[oracle@node1 clonedb]$ . oraenv
ORACLE_SID = [RAC1] ? clonedb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node1 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 8 01:05:39 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1339344 bytes
Variable Size             222298160 bytes
Database Buffers          549453824 bytes
Redo Buffers                5296128 bytes
SQL> 

[oracle@node1 clonedb]$ . oraenv
ORACLE_SID = [RAC1] ? clonedb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node1 clonedb]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 8 01:06:32 2011

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

connected to auxiliary database: CLONEDB (not mounted)

RMAN> duplicate database to clonedb backup location '/u01/app/oracle/backup' nofilenamecheck; 


SQL> select name from v$database;

NAME
---------
CLONEDB

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

RMAN Tablespace Point-in-Time Recovery (TSPITR) 11gR2 Enhancements

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 07 Aug 2011 13:56:56 PDT

One of the good new features in 11g Release 2 is that it enables us to recover from a case of a dropped tablespace. TSPITR (tablespace point in time recovery) has been around in earlier releases, but we could not recover a dropped tablespace.

here is an demo for your reference

  
 
22:33:43 SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
-------------------------------------------------------- ---------------
/u01/app/oracle/db2/users01.dbf                          USERS
/u01/app/oracle/db2/undotbs01.dbf                        UNDOTBS1
/u01/app/oracle/db2/sysaux01.dbf                         SYSAUX
/u01/app/oracle/db2/system01.dbf                         SYSTEM

22:33:52 SQL> create tablespace newtbs1 datafile '/u01/app/oracle/db2/newtbs01.dbf' size 10m;

Tablespace created.

22:34:31 SQL> create user shyam identified by shyam default tablespace newtbs1;

User created.

22:35:24 SQL> grant connect,resource to shyam;

Grant succeeded.

22:35:57 SQL> conn shyam/shyam
Connected.
22:36:16 SQL> create table tb1 ( id number);

Table created.

22:36:26 SQL> insert into tb1 values (123);

1 row created.

Commit complete.
22:36:41 SQL> create table tb2 ( id number);

Table created.

22:37:01 SQL> insert into tb2 values (234);

1 row created.

Commit complete.
22:37:14 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TB1                            TABLE
TB2                            TABLE


22:37:20 SQL> conn / as sysdba
Connected.
22:38:16 SQL> alter system switch logfile;

System altered.

22:38:33 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     799576

[oracle@node2 backupnew]$ . oraenv
ORACLE_SID = [db2] ? 
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node2 backupnew]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 7 22:39:17 2011

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

connected to target database: DB2 (DBID=1922019997)

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/backupnew/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/backupnew/%d_%s_%I_

%T';


Starting backup at 07-AUG-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
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=3 RECID=1 STAMP=758566663
input archived log thread=1 sequence=4 RECID=2 STAMP=758566818
input archived log thread=1 sequence=5 RECID=3 STAMP=758566872
input archived log thread=1 sequence=6 RECID=4 STAMP=758574317
input archived log thread=1 sequence=7 RECID=5 STAMP=758574431
input archived log thread=1 sequence=8 RECID=6 STAMP=758574724
input archived log thread=1 sequence=9 RECID=7 STAMP=758575149
input archived log thread=1 sequence=10 RECID=8 STAMP=758575987
input archived log thread=1 sequence=11 RECID=9 STAMP=758581906
input archived log thread=1 sequence=12 RECID=10 STAMP=758581906
input archived log thread=1 sequence=13 RECID=11 STAMP=758582156
input archived log thread=1 sequence=14 RECID=12 STAMP=758582234
input archived log thread=1 sequence=15 RECID=13 STAMP=758582326
input archived log thread=1 sequence=16 RECID=14 STAMP=758582432
input archived log thread=1 sequence=17 RECID=15 STAMP=758582528
input archived log thread=1 sequence=18 RECID=16 STAMP=758582961
input archived log thread=1 sequence=19 RECID=17 STAMP=758584593
input archived log thread=1 sequence=20 RECID=18 STAMP=758587114
input archived log thread=1 sequence=21 RECID=19 STAMP=758587224
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_22_1922019997_20110807 tag=TAG20110807T224025 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-AUG-11

Starting backup at 07-AUG-11
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=/u01/app/oracle/db2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/db2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/db2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/db2/newtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/db2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807 tag=TAG20110807T224041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807 tag=TAG20110807T224041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-11

Starting backup at 07-AUG-11
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=22 RECID=20 STAMP=758587349
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_25_1922019997_20110807 tag=TAG20110807T224229 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-11

RMAN> exit


Recovery Manager complete.
[oracle@node2 backupnew]$ 

22:38:46 SQL> conn shyam/shyam
Connected.
22:43:37 SQL> create table tb3 (id number);

Table created.

22:47:33 SQL> insert into tb3 values(123);

1 row created.

Commit complete.
22:48:27 SQL> select * from tb3;

        ID
----------
       123

22:48:37 SQL> conn / as sysdba
Connected.
22:48:47 SQL> alter system switch logfile;

System altered.

22:48:54 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     800205

22:49:06 SQL> 


22:50:14 SQL> drop tablespace newtbs1 including contents and datafiles;

Tablespace dropped.

22:51:06 SQL> 


RMAN> recover tablespace newtbs1 until scn 800205  auxiliary destination '/u01/app/oracle/backup/backupnew';

Starting recover at 07-AUG-11
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Aunx'

initialization parameters used for automatic instance:
db_name=DB2
db_unique_name=Aunx_tspitr_DB2
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/backup/backupnew
log_archive_dest_1='location=/u01/app/oracle/backup/backupnew'
#No auxiliary parameter file used


starting up automatic instance DB2

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created


List of tablespaces that have been dropped from the target database:
Tablespace newtbs1

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 07-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 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 /u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807 tag=TAG20110807T224041
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/backup/backupnew/DB2/controlfile/o1_mf_73xmbtfc_.ctl
Finished restore at 07-AUG-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to 
 "/u01/app/oracle/db2/newtbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
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

renamed tempfile 1 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 07-AUG-11
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 /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/db2/newtbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807 tag=TAG20110807T224041
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 07-AUG-11

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_73xmc12w_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_73xmc148_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_73xmc13r_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "NEWTBS1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 07-AUG-11
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_22_73xkvxsz_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_23_73xl7yvr_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_24_73xlmfdj_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_22_73xkvxsz_.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_23_73xl7yvr_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_24_73xlmfdj_.arc thread=1 sequence=24
media recovery complete, elapsed time: 00:00:03
Finished recover at 07-AUG-11

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  NEWTBS1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupnew''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupnew''";
}
executing Memory Script

sql statement: alter tablespace  NEWTBS1 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupnew''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupnew''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_Aunx":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_Aunx" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_Aunx is:
   EXPDP>   /u01/app/oracle/backup/backupnew/tspitr_Aunx_72915.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace NEWTBS1:
   EXPDP>   /u01/app/oracle/db2/newtbs01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_Aunx" successfully completed at 23:11:06
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_Aunx" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Aunx":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_Aunx" successfully completed at 23:11:35
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  NEWTBS1 read write';
sql 'alter tablespace  NEWTBS1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  NEWTBS1 read write

sql statement: alter tablespace  NEWTBS1 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_temp_73xmgrpk_.tmp deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_3_73xmglwq_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_2_73xmgk7t_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_1_73xmghq1_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_73xmc13r_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_73xmc148_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_73xmc12w_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/controlfile/o1_mf_73xmbtfc_.ctl deleted
Finished recover at 07-AUG-11

RMAN> 



23:14:57 SQL>  select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
-------------------------------------------------------- ---------------
/u01/app/oracle/db2/users01.dbf                          USERS
/u01/app/oracle/db2/undotbs01.dbf                        UNDOTBS1
/u01/app/oracle/db2/sysaux01.dbf                         SYSAUX
/u01/app/oracle/db2/system01.dbf                         SYSTEM
/u01/app/oracle/db2/newtbs01.dbf                         NEWTBS1


SQL> alter tablespace newtbs1 online;

Tablespace altered.


23:15:28 SQL> conn shyam/shyam
Connected.
23:15:54 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TB1                            TABLE
TB2                            TABLE
TB3                            TABLE

RMAN cloning in archive log mode

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 22 May 2011 14:24:05 PDT

i have cloned the database SUNU in RAC2 machine to RAC1 Machine in the name DBDEMO.

****************************************************************
RAC2 MACHINE
****************************************************************
ORACLE_SID = [test] ? sunu
[oracle@rac2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 15 19:27:41 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? sunu
[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 15 19:34:50 2011

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

connected to target database: SUNU (DBID=555109830)

RMAN> backup full database format '/home/u01/app/backup/%d_%U.bkp' plus archivelog format '/home/u01/app/backup/%d_%U.bkp';

[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ ls -ltr
total 516356
-rw-r----- 1 oracle oinstall 2124800 May 15 19:37 SUNU_01mcdp74_1_1.bkp
-rw-r----- 1 oracle oinstall 518946816 May 15 19:37 SUNU_02mcdp76_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 15 19:37 SUNU_03mcdp8j_1_1.bkp
-rw-r----- 1 oracle oinstall 2560 May 15 19:37 SUNU_04mcdp8l_1_1.bkp
[oracle@rac2 backup]$

step 2:-
copy the initsunu.ora file to rac1 machine and also create a password file in rac1 machine for dbdemo database
add an entry in oratab file in rac1 machine
also move the backup files to rac1 machine to exact folder as it is in rac2(source) machine

note: make sure the folder is available in RAC1 machine
here we are taking backup in /home/u01/app/backup so now i am going to create a folder in RAC1 machine and also moving the backup from RAC2 to RAC1.
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/backup/

or you can create a symbolic link for the backup location

example, in machine RAC1(the machine/host/server i dont have exact folder or exact mount point as it is in RAC2 machine(target)
do like this,

[oracle@rac2 dbs]$ cd /home/u01/app/backup/
[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/test_bk/

and then create a symbolic link:
[oracle@rac1 app]$ ln -s /home/u01/app/test_bk /home/u01/app/backup

[oracle@rac1 app]$ ls
backup oracle test_bk
[oracle@rac1 app]$ cd backup/
[oracle@rac1 backup]$ ls -ltr
total 671932
-rw-r----- 1 oracle oinstall 7029760 May 22 12:35 SUNU_09mcoc44_1_1.bkp
-rw-r----- 1 oracle oinstall 97460224 May 22 12:36 SUNU_0amcoc48_1_1.bkp
-rw-r----- 1 oracle oinstall 6144 May 22 12:36 SUNU_0cmcoc7a_1_1.bkp
-rw-r----- 1 oracle oinstall 1097728 May 22 12:36 SUNU_0bmcoc78_1_1.bkp
-rw-r----- 1 oracle oinstall 39838720 May 22 12:36 SUNU_0dmcvels_1_1.bkp
-rw-r----- 1 oracle oinstall 534740992 May 22 12:38 SUNU_0emcvem5_1_1.bkp
-rw-r----- 1 oracle oinstall 27648 May 22 12:38 SUNU_0gmcvepg_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 22 12:38 SUNU_0fmcvepe_1_1.bkp
[oracle@rac1 backup]$

now we are going to copy the init files from RAC2 to RAC1 machine using scp , as shown below.
you can mention hostname or ipaddress in scp command

[oracle@rac2 dbs]$ scp initsunu.ora oracle@192.168.1.1:$ORACLE_HOME/dbs/
initsunu.ora 100% 1070 1.0KB/s 00:00
***********************************************************************
IN RAC1 MACHINE
***********************************************************************

i copied the initsunu.ora file to the RAC1 machine and now i am going to replace
the database name sunu by dbdemo
also,
adding the parameter log_file_name_convert and db_file_name_convert parameters to clone database with different directory structure.

dbdemo.__db_cache_size=541065216
dbdemo.__java_pool_size=4194304
dbdemo.__large_pool_size=4194304
dbdemo.__shared_pool_size=192937984
dbdemo.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/dbdemo/adump'
*.background_dump_dest='/home/u01/app/oracle/admin/dbdemo/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/u01/app/oracle/oradata/dbdemo/control01.ctl','/home/u01/app/oracle/oradata/dbdemo/control02.ctl','/home/u01/app/oracle/oradata/dbdemo/control03.ctl'
*.core_dump_dest='/home/u01/app/oracle/admin/dbdemo/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbdemo'
*.db_recovery_file_dest='/home/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbdemoXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=248512512
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/u01/app/oracle/admin/dbdemo/udump'
db_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
log_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
*.sga_target=745537536

Now i am going to add an entry in the /etc/oratab file for dbdemo database

dbdemo:/home/u01/app/oracle/product/10.2.0/db_1:N

going to create required folders for the dbdemo database files

[oracle@rac1 dbdemo]$ pwd
/home/u01/app/oracle/admin/dbdemo

[oracle@rac1 dbdemo]$ ls
adump bdump cdump dpdump hdump pfile udump
[oracle@rac1 dbdemo]$

[oracle@rac1 oradata]$ pwd
/home/u01/app/oracle/oradata
[oracle@rac1 oradata]$ ls
dbdemo
[oracle@rac1 oradata]$

creating a pwd file for dbdemo database

[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
bash: orapwd: command not found
[oracle@rac1 oradata]$ . oraenv
ORACLE_SID = [orcl] ? dbdemo
[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
[oracle@rac1 oradata]$

now i am going to start dbdemo database in nomount mode
and then do cloning

before that we should have add the tns entry of sunu database in RAC1 machine

[oracle@rac1 backup]$ cd /home/u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ ls -ltr

add/check this entries available in both machines else add this entry in tnsnames.ora file of both machines.

SUNU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sunu)
)
)

DBDEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdemo)
)
)
[oracle@rac1 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:21

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (0 msec)
[oracle@rac1 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac1 admin]$

[oracle@rac2 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:09

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (10 msec)
[oracle@rac2 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:15

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac2 admin]$

we are checking connectivity b/n the two machines using tnsping command.

now all the pre cloning steps finished.

now we can start the cloning in RAC1 machine.

startup nomount the DATABASE dbdemo in RAC1 machine

making/creating flash recovery area in RAC1 machine

[oracle@rac1 oracle]$ mkdir -p /home/u01/app/oracle/flash_recovery_area
[oracle@rac1 oracle]$ ls
admin flash_recovery_area oradata oraInventory product
[oracle@rac1 oracle]$ cd flash_recovery_area/
[oracle@rac1 flash_recovery_area]$ ls
[oracle@rac1 flash_recovery_area]$ mkdir dbdemo
[oracle@rac1 flash_recovery_area]$ ls
dbdemo

[oracle@rac1 dbs]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:40:50 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 201329660 bytes
Database Buffers 541065216 bytes
Redo Buffers 2969600 bytes
SQL>

[oracle@rac1 flash_recovery_area]$ . oraenv
ORACLE_SID = [ORCL1] ? dbdemo
[oracle@rac1 flash_recovery_area]$ rman target sys/oracle@SUNU auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 19 20:42:06 2011

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

connected to target database: SUNU (DBID=555109830)
connected to auxiliary database: DBDEMO (not mounted)

RMAN> duplicate target database to dbdemo;

[oracle@rac1 dbdemo]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbdemo]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 21:20:23 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
DBDEMO

ORA-01591: lock held by in-doubt distributed transaction

noreply@blogger.com (Rajeshkumar Govindarajan) — Tue, 26 Apr 2011 11:44:06 PDT

ORA-01591: lock held by in-doubt distributed transaction
i restarted the server but of no use

solution:

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

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

6.44.24689

SQL> rollback force '6.44.24689';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('6.44.24689');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

reference:

http://asktom.oracle.com/

Happy New Year 2011

noreply@blogger.com (Rajeshkumar Govindarajan) — Fri, 31 Dec 2010 23:10:33 PST

Dear All,

“Another fresh new year is here . . .
Another year to live!
To banish worry, doubt, and fear,
To love and laugh and give!

This bright new year is given me
To live each day with zest . . .
To daily grow and try to be
My highest and my best!

I have the opportunity
Once more to right some wrongs,
To pray for peace, to plant a tree,
And sing more joyful songs!

“We will open the book. Its pages are blank. We are going to put words on them ourselves. The book is called Opportunity and its first chapter is New Year's Day.”

Thanks and Regards,
Rajesh Kumar Govindarajan

ORA-00959: tablespace '_$deleted$3$0' does not exist Error

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Thu, 02 Dec 2010 03:20:47 PST

20-AUG-2010 in Oracle forum OTN, a interesting thread , here i documented it for future reference.

thread: ORA-00959: tablespace '_$deleted$3$0' does not exist

i thank the forum Opponent Phiri Lighton and participants especially nicolas,ogan and chinar here.

here, we will see the examples , What are the possiblities to get ORA-950 Error

Changes
Made changes to the TEMP tablespace setup.
1. Created a temporary tablespace called TEMP1.
2. Set this tablespace to become the default temporary tablespace.
3. Dropped the existing permanent TEMP tablespace.
4. Renamed the default temporary tablesapce TEMP1 to TEMP.
Reason for this problem/issue
There is an open unpublished bug where basic problem is that a tablespace is renamed to one that use
to exist on the database. Here are the steps that will cause this problem.
create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa
Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it
is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the
rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is
introduced. The change is not taken care of properly, and is flushed to other dictionary tables
causing this problem.
Solution
To resolve in this case, just alter the user to have the new temporary tablespace name, replacing the
_$deleted entry.
SQL> create temporary tablespace temp2 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create user testuser identified by testuser temporary tablespace temp2;
User created.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> alter tablespace temp3 rename to temp2;
Tablespace altered.
- Now look in ts$ and will see the _$deleted construct which includes the ts#.
SQL> select ts#, name from ts$;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDO
6 MYDATA
7 MYINDEXES
8 TESTCASES
9 _$deleted$9$0
10 TEMP2
11 rows selected.
- And when you check the users temporary_tablespace, it too was changed to the _$deleted construct
which causes all the problems.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER _$deleted$9$0
- To resolve, just alter the user to a valid temporary tablespace.
SQL> alter user testuser temporary tablespace temp2;
User altered.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER TEMP2
To avoid this problem altogether, until the bug is fixed, do not rename a tablespace to one that use to
exist on the database. For example, here would be steps to complete the temp tablespace change.
create tablespace NEWTEMP
... change everyone and default temporary tablespace NEWTEMP
drop tablespace TEMP ... exisiting one
create tablespace TEMP ... building it the way you want
.... flip everyone back and default temporary tablespace TEMP
drop tablespace NEWTEMP

reference: ORACLE OTN Forum, MetaLink

Recover a missing/excluded datafile after re-create new control file from controlfile trace file

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 01 Nov 2010 04:27:54 PDT

This is the situation in which lost all control file, and DBA accidently create control file (recreated) with old controlfile trace file backup. After recover,DBA found some datafile is missing.
Here is the steps to recover those missing database datafiles.

16:33:32 SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf

16:33:43 SQL> set time off;
SQL> set time off;
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter system switch logfile;

System altered.



SQL> alter database backup controlfile to trace;

Database altered.

SQL>

[oracle@rac1 udump]$ cp rev1_ora_6901.trc /home/oracle/Desktop/ctlnew.sql

[oracle@rac1 udump]$ pwd
/u01/app/oracle/admin/rev1/udump
[oracle@rac1 udump]$


SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/rev1/users02.dbf' size 2m;

Tablespace altered.

SQL> select file#,name from v$datafile; 
     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/oradata/rev1/users02.dbf

SQL>
CREATE CONTROLFILE REUSE DATABASE "REV1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/rev1/system01.dbf',
  '/u01/app/oracle/oradata/rev1/undotbs01.dbf',
  '/u01/app/oracle/oradata/rev1/sysaux01.dbf',
  '/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl

[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl
[oracle@rac1 rev1]$ pwd
/u01/app/oracle/oradata/rev1
[oracle@rac1 rev1]$ ls -ltr
total 791480
-rw-r-----  1 oracle oinstall 104865792 Oct 31 15:44 temp01.dbf
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:30 redo02.log
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:39 redo03.log
-rw-r-----  1 oracle oinstall   2105344 Oct 31 16:53 users02.dbf
-rw-r-----  1 oracle oinstall  10493952 Oct 31 16:53 users01.dbf
-rw-r-----  1 oracle oinstall  26222592 Oct 31 16:53 undotbs01.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 31 16:53 system01.dbf
-rw-r-----  1 oracle oinstall 251666432 Oct 31 16:53 sysaux01.dbf
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:53 redo01.log
[oracle@rac1 rev1]$


SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             146801344 bytes
Database Buffers          293601280 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance;

STATUS
----------
STARTED

SQL> shu immediate
ORA-01507: database not mounted


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

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             146801344 bytes
Database Buffers          293601280 bytes
Redo Buffers                2973696 bytes

SQL> @/home/oracle/Desktop/recreate_ctlfile.sql

Control file created.


SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.
SQL> col name format a55
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -------------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/u01/app/oracle/oradata/rev1/users02.dbf';

Database altered.

SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log ORA-00342: archived log does not have expected resetlogs SCN 805021
ORA-00334: archived log: '/u01/app/oracle/oradata/rev1/redo01.log'


SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name from v$datafile; 
     FILE# NAME
---------- -------------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/oradata/rev1/users02.dbf

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

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- ----------
         1 /u01/app/oracle/oradata/rev1/system01.dbf               SYSTEM
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf              ONLINE
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf               ONLINE
         4 /u01/app/oracle/oradata/rev1/users01.dbf                ONLINE
         5 /u01/app/oracle/oradata/rev1/users02.dbf                OFFLINE

SQL>
SQL> alter database datafile 5 online;

Database altered.

SQL> alter system switch logfile;

System altered.

reference:
http://hemantoracledba.blogspot.com/2010/08/adding-datafile-that-had-been-excluded.html

RMAN TSPITR DEMO

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 31 Oct 2010 05:32:23 PDT

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.for theoritical part please refer oracle documentation.

Here in this demo i am going to create and truncate a table in tablespace USERS for schema(username) RAJESH and recover truncated table TEST1 of users RAJESH schema in tablespace USERS.

We can use RMAN TSPITR in the following conditions

1. Recovering data lost after an erroneous TRUNCATE TABLE statement;
2. Recovering from logical corruption of a table;
3. Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database;
4. Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP

SQL> alter user rajesh identified by rajesh default tablespace users;

User altered.

SQL> conn rajesh/rajesh;
Connected.
SQL> create table test1(id number);

Table created.

SQL> insert into test1 values(123);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID
----------
       123
       123
       123
       123

SQL>

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [cdbs1] ? rev1
[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 31 15:48:18 2010

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

connected to target database: REV1 (DBID=1886963043)

RMAN> backup full database plus archivelog;


Starting backup at 31-OCT-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=109 stamp=733321071
input archive log thread=1 sequence=19 recid=110 stamp=733321246
input archive log thread=1 sequence=20 recid=111 stamp=733321261
input archive log thread=1 sequence=21 recid=112 stamp=733351761
input archive log thread=1 sequence=22 recid=113 stamp=733351877
input archive log thread=1 sequence=23 recid=114 stamp=733851812
input archive log thread=1 sequence=24 recid=115 stamp=733851923
input archive log thread=1 sequence=25 recid=116 stamp=733852130
input archive log thread=1 sequence=26 recid=117 stamp=733852142
channel ORA_DISK_1: starting piece 1 at 31-OCT-10
channel ORA_DISK_1: finished piece 1 at 31-OCT-10
piece handle=/u01/app/oracle/backup/13lrrcfp_1_1 tag=TAG20101031T154911 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 31-OCT-10

Starting backup at 31-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/rev1/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/rev1/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/rev1/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 31-OCT-10
channel ORA_DISK_1: finished piece 1 at 31-OCT-10
piece handle=/u01/app/oracle/backup/14lrrcg2_1_1 tag=TAG20101031T154920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 31-OCT-10

Starting backup at 31-OCT-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=118 stamp=733852258
channel ORA_DISK_1: starting piece 1 at 31-OCT-10
channel ORA_DISK_1: finished piece 1 at 31-OCT-10
piece handle=/u01/app/oracle/backup/15lrrcj4_1_1 tag=TAG20101031T155059 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 31-OCT-10

Starting Control File and SPFILE Autobackup at 31-OCT-10
piece handle=/u01/app/oracle/flash_recovery_area/REV1/autobackup/2010_10_31/o1_mf_s_733852263_6dtjrkts_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-OCT-10

RMAN> exit


Recovery Manager complete.
[oracle@rac1 ~]$

SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter system switch logfile;

System altered.

SQL> create tablespace demo datafile '/u01/app/oracle/oradata/rev1/demo01.dbf' size 2m;

Tablespace created.

SQL> create user sai identified by sai default tablespace demo;

User created.

SQL> grant connect, resource to sai;

Grant succeeded.

SQL> conn sai/sai;
Connected.
SQL> create table test2(id number);

Table created.

SQL> insert into test2 values(555);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

        ID
----------
       555
       555
       555

SQL> set time on;
15:54:48 SQL> conn rajesh/rajesh;
Connected.
15:54:57 SQL> select * from test1;

        ID
----------
       123
       123
       123
       123

15:55:15 SQL> truncate table test1;

Table truncated.

15:55:26 SQL> select * from test1;

no rows selected

15:55:32 SQL>

15:55:32 SQL> conn sys/oracle as sysdba;
Connected.
15:59:30 SQL> col name format a51;
15:59:45 SQL> col status for a10;
15:59:51 SQL> select name,status from v$datafile;

NAME                                                STATUS
--------------------------------------------------- ----------
/u01/app/oracle/oradata/rev1/system01.dbf           SYSTEM
/u01/app/oracle/oradata/rev1/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/rev1/sysaux01.dbf           ONLINE
/u01/app/oracle/oradata/rev1/users01.dbf            RECOVER
/u01/app/oracle/oradata/rev1/demo01.dbf             ONLINE


16:00:08 SQL> conn sai/sai;
Connected.
16:00:55 SQL> create table test3(id number);

Table created.

16:01:08 SQL> insert into test3 values(666);

1 row created.

16:01:22 SQL> /

1 row created.

16:01:22 SQL> commit;

Commit complete.

16:01:25 SQL> select * from test3;

        ID
----------
       666
       666

16:01:30 SQL>

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [rev1] ?
[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 31 15:56:19 2010

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

connected to target database: REV1 (DBID=1886963043)

RMAN> recover tablespace USERS until time "to_date('31-OCT-2010 15:55:00','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION='/u01/app/oracle';

Starting recover at 31-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID='huEl'

initialization parameters used for automatic instance:
db_name=REV1
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_REV1_huEl
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle
control_files=/u01/app/oracle/cntrl_tspitr_REV1_huEl.f


starting up automatic instance REV1

Oracle instance started

Total System Global Area     201326592 bytes

Fixed Size                     1218508 bytes
Variable Size                146802740 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2973696 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  time "to_date('31-OCT-2010 15:55:00','DD-MON-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;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 31-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/REV1/autobackup/2010_10_31/o1_mf_s_733852408_6dtjx0yz_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/REV1/autobackup/2010_10_31/o1_mf_s_733852408_6dtjx0yz_.bkp tag=TAG20101031T155328
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/cntrl_tspitr_REV1_huEl.f
Finished restore at 31-OCT-10

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  time "to_date('31-OCT-2010 15:55:00','DD-MON-YYYY HH24:MI:SS')";
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'USERS' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  4 to
 "/u01/app/oracle/oradata/rev1/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace USERS offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 31-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=40 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/14lrrcg2_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/14lrrcg2_1_1 tag=TAG20101031T154920
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:17
Finished restore at 31-OCT-10

datafile 1 switched to datafile copy
input datafile copy recid=17 stamp=733852877 filename=/u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_system_6dtk9896_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=18 stamp=733852877 filename=/u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_undotbs1_6dtk98by_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  4 online

Starting recover at 31-OCT-10
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 27 is already on disk as file /u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_27_6dtjrbnj_.arc
archive log thread 1 sequence 28 is already on disk as file /u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_28_6dtk80s9_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_27_6dtjrbnj_.arc thread=1 sequence=27
archive log filename=/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_28_6dtk80s9_.arc thread=1 sequence=28
media recovery complete, elapsed time: 00:00:03
Finished recover at 31-OCT-10

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclehuEl\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=huEl^'\)\)\(CONNECT_DATA=\(SID=huEl\)\)\) as sysdba\" point_in_time_recover=y tablespaces= USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace  USERS online";
sql "alter tablespace  USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script


Export: Release 10.2.0.1.0 - Production on Sun Oct 31 16:02:07 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              A
EXP-00091: Exporting questionable statistics.
. . exporting table                            NEW
EXP-00091: Exporting questionable statistics.
. . exporting table                          TEST1
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Sun Oct 31 16:02:54 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                            "A"
. importing RAJESH's objects into RAJESH
. . importing table                          "NEW"
. . importing table                        "TEST1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace  USERS online

sql statement: alter tablespace  USERS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/cntrl_tspitr_REV1_huEl.f deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_system_6dtk9896_.dbf deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_undotbs1_6dtk98by_.dbf deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/datafile/o1_mf_temp_6dtkdg6g_.tmp deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/onlinelog/o1_mf_1_6dtkd95j_.log deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/onlinelog/o1_mf_2_6dtkdbmb_.log deleted
auxiliary instance file /u01/app/oracle/TSPITR_REV1_HUEL/onlinelog/o1_mf_3_6dtkdbrn_.log deleted
Finished recover at 31-OCT-10

RMAN>

16:03:06 SQL> select name,status from v$datafile;

NAME                                                STATUS
--------------------------------------------------- ----------
/u01/app/oracle/oradata/rev1/system01.dbf           SYSTEM
/u01/app/oracle/oradata/rev1/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/rev1/sysaux01.dbf           ONLINE
/u01/app/oracle/oradata/rev1/users01.dbf            OFFLINE
/u01/app/oracle/oradata/rev1/demo01.dbf             ONLINE

16:03:06 SQL> select name,status from v$datafile;

NAME                                                STATUS
--------------------------------------------------- ----------
/u01/app/oracle/oradata/rev1/system01.dbf           SYSTEM
/u01/app/oracle/oradata/rev1/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/rev1/sysaux01.dbf           ONLINE
/u01/app/oracle/oradata/rev1/users01.dbf            OFFLINE
/u01/app/oracle/oradata/rev1/demo01.dbf             ONLINE

16:03:45 SQL> conn rajesh/rajesh;
Connected.
16:05:26 SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/rev1/users01.dbf'


16:05:32 SQL> conn sys/oracle as sysdba;
Connected.
16:05:42 SQL> alter tablespace users online;

Tablespace altered.

16:05:52 SQL> conn rajesh/rajesh;
Connected.
16:05:58 SQL> select * from test1;

        ID
----------
       123
       123
       123
       123

16:06:05 SQL> conn sai/sai;
Connected.
16:06:11 SQL> select * from test2;

        ID
----------
       555
       555
       555

16:06:16 SQL> select * from test3;

        ID
----------
       666
       666

16:06:20 SQL> conn sys/oracle as sysdba;
Connected.
16:06:32 SQL> select name,status from v$datafile;

NAME                                                STATUS
--------------------------------------------------- ----------
/u01/app/oracle/oradata/rev1/system01.dbf           SYSTEM
/u01/app/oracle/oradata/rev1/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/rev1/sysaux01.dbf           ONLINE
/u01/app/oracle/oradata/rev1/users01.dbf            ONLINE
/u01/app/oracle/oradata/rev1/demo01.dbf             ONLINE

16:06:38 SQL>

hope, this will helps you.

reference:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10734/rcmtspit.htm

RMAN-03002 : ORA-01119: error in creating database file ORA-27038:

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 25 Oct 2010 09:03:19 PDT

this is one of the interview question
your database corrupted/lost after adding a datafile to database you are only having the backup of database before adding the datafile, the newly added datafile is not available in the backupset , what will happen how will you recover? Is the new added datafile will be available after the restore backup command?

the answer is yes the control contains the information about the newly added datafile so RMAN creates a new datafile with the same name and location and applies logs to the datafile to recover. it is similar to sql statement alter database create datafile statement.
here is an demo.

SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf


[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [cdbs1] ? rev1
[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 25 20:48:50 2010

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

connected to target database: REV1 (DBID=1886963043)

RMAN> backup full database plus archivelog;


Starting backup at 25-OCT-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=109 stamp=733321071
input archive log thread=1 sequence=19 recid=110 stamp=733321246
input archive log thread=1 sequence=20 recid=111 stamp=733321261
input archive log thread=1 sequence=21 recid=112 stamp=733351761
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0slrc3qs_1_1 tag=TAG20101025T204929 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 25-OCT-10

Starting backup at 25-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/rev1/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/rev1/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/rev1/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0tlrc3r5_1_1 tag=TAG20101025T204939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-OCT-10

Starting backup at 25-OCT-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=22 recid=113 stamp=733351877
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0ulrc3u7_1_1 tag=TAG20101025T205118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-OCT-10

Starting Control File and SPFILE Autobackup at 25-OCT-10
piece handle=/u01/app/oracle/flash_recovery_area/REV1/autobackup/2010_10_25/o1_mf_s_733351882_6dc83mcy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-OCT-10

RMAN>

SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/rev1/users02.dbf' SIZE 2m;

Tablespace altered.

SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf
/u01/app/oracle/oradata/rev1/users02.dbf

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 25 21:03:50 2010

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

connected to target database: REV1 (DBID=1886963043)

RMAN> restore database;

Starting restore at 25-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

creating datafile fno=5 name=/u01/app/oracle/oradata/rev1/users02.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/25/2010 21:14:13
ORA-01119: error in creating database file '/u01/app/oracle/oradata/rev1/users02.dbf'
ORA-27038: created file already exists
Additional information: 1


SQL> ! mv /u01/app/oracle/oradata/rev1/users02.dbf /u01/app/oracle/user02.dbf

RMAN> restore database;

Starting restore at 25-OCT-10
using channel ORA_DISK_1

creating datafile fno=5 name=/u01/app/oracle/oradata/rev1/users02.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/rev1/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/rev1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/rev1/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/0tlrc3r5_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/0tlrc3r5_1_1 tag=TAG20101025T204939
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 25-OCT-10

RMAN> recover database;

Starting recover at 25-OCT-10
using channel ORA_DISK_1

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

Finished recover at 25-OCT-10

RMAN>

RMAN> sql 'alter database open';

sql statement: alter database open
RMAN> exit


Recovery Manager complete.
SQL> select status from v$instance;

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf
/u01/app/oracle/oradata/rev1/users02.dbf

hope, this will helps you.

reference:
http://forums.oracle.com/forums/thread.jspa?messageID=3687911

Re-create lost controlfile

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Fri, 24 Sep 2010 12:31:18 PDT

Here in this demo i deleted all control files and re-created using trace file.
In Oracle forum OTN related to re-create controlfile, confusion between shutdown immediate and shutdown abort after controlfile lost. this demo helps you to re-create lost control file


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/control01.ctl
/u01/app/oracle/oradata/rev1/control02.ctl
/u01/app/oracle/oradata/rev1/control03.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf

SQL> col member format a50
SQL> select a.group#,a.member,b.status from v$logfile a, v$log b where a.group#=b.group#;

  GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ----------------
       3 /u01/app/oracle/oradata/rev1/redo03.log            ACTIVE
       2 /u01/app/oracle/oradata/rev1/redo02.log            CURRENT
       1 /u01/app/oracle/oradata/rev1/redo01.log            ACTIVE

to create control file trace


SQL> alter database backup controlfile to trace;

Database altered.
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/admin/rev1/udump
                                             
or

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
2  AS '/home/oracle/Desktop/create_ctl.sql';

Database altered.

you can find the sample controlfile trace file in the location user_dump_dest /u01/app/oracle/admin/rev1/udump the last recently created file.


u01/app/oracle/admin/rev1/udump/rev1_ora_10939.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: rac1.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: rev1
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 10939, image: oracle@rac1.localdomain (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-09-21 16:52:23.473
*** SESSION ID:(142.32) 2010-09-21 16:52:23.473
*** 2010-09-21 16:52:23.472
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="rev1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "REV1" NORESETLOGS FORCE LOGGING ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/rev1/system01.dbf',
'/u01/app/oracle/oradata/rev1/undotbs01.dbf',
'/u01/app/oracle/oradata/rev1/sysaux01.dbf',
'/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/rev1/temp01.dbf'
   SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

copy and paste the following lines in the notepad/text editor from the above trace files and save it as create_ctl.sql


CREATE CONTROLFILE REUSE DATABASE "REV1" RESETLOGS FORCE LOGGING ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/rev1/system01.dbf',
'/u01/app/oracle/oradata/rev1/undotbs01.dbf',
'/u01/app/oracle/oradata/rev1/sysaux01.dbf',
'/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

remove the control files(control01.ctl,control02.ctl,control03.ctl) from the location /u01/app/oracle/oradata/rev1/ for testing purpose to re-create it.


SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/rev1/control01.ctl
/u01/app/oracle/oradata/rev1/control02.ctl
/u01/app/oracle/oradata/rev1/control03.ctl

SQL> host rm -rf /u01/app/oracle/oradata/rev1/control01.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/rev1/control02.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/rev1/control03.ctl

SQL> shutdown immediate

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             167772864 bytes
Database Buffers          272629760 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

run the create_ctl.sql script to recreate a lost control files


SQL> @/home/oracle/Desktop/create_ctl.sql

Control file created.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/rev1/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 557988 generated at 09/21/2010 16:13:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.arc
ORA-00280: change 557988 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/rev1/system01.dbf'

apply the current online redolog files for recover the database here in our example the current
logfile is redo02.log


SQL> col member format a50
SQL> select a.group#,a.member,b.status from v$logfile a, v$log b where a.group#=b.group#;

  GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ----------------
       3 /u01/app/oracle/oradata/rev1/redo03.log            ACTIVE
       2 /u01/app/oracle/oradata/rev1/redo02.log            CURRENT
       1 /u01/app/oracle/oradata/rev1/redo01.log            ACTIVE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 557988 generated at 09/21/2010 16:13:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.arc
ORA-00280: change 557988 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

hope, this will helps you

SYSAUX datafile or tablespace corrupted or lost no backup available

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Tue, 07 Sep 2010 01:44:47 PDT

Datafiles of the sysaux tablespace are not as critical as datafiles in the system tablespace but they are part of the Oracle configuration. Anyway you should never delete any datafile directly from the OS.
rman target /

report schema; -- identify number of your datafile
sql 'alter database datafile ? offline';
sql 'alter database open';

You should be able to open the database but you could have problems (depends on Oracle options you use)... If you don't want to experience/solve them export what you need and import in another database.

you can create a tablespace then :

alter user ??? default tablespace ???;

You should also give him quota on this tablespace

alter user ??? quota unlimited on ???;

Replace ??? by adequate values (username , tablespace name)


SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             197132992 bytes
Database Buffers          243269632 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             201327296 bytes
Database Buffers          239075328 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/jay/sysaux01.dbf'

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 2 11:45:07 2010

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

connected to target database: JAY (DBID=3148849783, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /u01/app/oracle/oradata/jay/system01.dbf
2    25       UNDOTBS1             ***     /u01/app/oracle/oradata/jay/undotbs01.dbf
3    0        SYSAUX               ***     /u01/app/oracle/oradata/jay/sysaux01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/jay/users01.dbf
5    5        SAMPLE               ***     /u01/app/oracle/oradata/jay/sam01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    5        TEMP                 5           /u01/app/oracle/oradata/jay/newtemp1.dbf

RMAN> sql 'alter database datafile 3 offline';

sql statement: alter database datafile 3 offline

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>



SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
jay              OPEN

SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/jay/system01.dbf           SYSTEM
/u01/app/oracle/oradata/jay/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/jay/sysaux01.dbf           OFFLINE
/u01/app/oracle/oradata/jay/users01.dbf            ONLINE
/u01/app/oracle/oradata/jay/sam01.dbf              ONLINE


SQL> select username from dba_users
  2  where default_tablespace='SYSAUX';

USERNAME
------------------------------
SYSMAN
DBSNMP
OLAPSYS
SI_INFORMTN_SCHEMA
ORDPLUGINS
XDB
WMSYS
DMSYS
EXFSYS
ANONYMOUS
CTXSYS

USERNAME
------------------------------
ORDSYS
MDSYS
RAJESH

14 rows selected.


SQL> ALTER USER RAJESH DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

User altered.

reference: from otn

restore the server parameter file(spfile) from RMAN backup

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Tue, 31 Aug 2010 06:04:47 PDT

1.Connect to target Database with RMAN.

i)If you have lost your spfile and dont have pfile then, follow the steps to recover spfile using RMAN.

RMAN>CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID=3148849783

2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT

Example:

3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;

To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
RMAN> restore spfile to '/tmp/spfilejay.ora' from autobackup;

If you want to restore to a pfile then use,
RMAN> restore spfile to pfile '/tmp/initjay.ora';

4)Restore Spfile from control file autobackup;
RMAN> run
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> restore spfile from autobackup;
4> }

Note after restore spfile from RMAN shutdown and startup the database
else you will receive the error message
ORA-00205: error in identifying control file, check alert log for more info


1. Restore spfile from autobackup;
RMAN> set dbid=3148849783

executing command: SET DBID

RMAN> startup force nomount;

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

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

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> restore spfile from autobackup;

2. Restore spfile to non-default location
RMAN> restore spfile to '/tmp/spfilejay.ora' from autobackup;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: JAY
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100831
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-AUG-10

3. If you want to restore to a pfile from restored spfile

RMAN> restore spfile to pfile '/tmp/initjay.ora';

Starting restore at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE to PFILE
output filename=/tmp/initjay.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/JAY_c-3148849783-20100831-00 tag=TAG20100831T174354
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 31-AUG-10

4.Restore spfile from Controlfile AutoBackup
RMAN> set dbid=3148849783

executing command: SET DBID

RMAN> startup force nomount;

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

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

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> run
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> restore spfile from autobackup; 4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20100831
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-AUG-10

Reference:
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

Restore Lost Control file using RMAN DEMO

noreply@blogger.com (Rajeshkumar Govindarajan) — Tue, 31 Aug 2010 00:03:46 PDT

Restore controlfile from RMAN Controlfile auto backup

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/JAY_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backup/jay/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_jay.f'; # default

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> BACKUP DATAFILE 1;

Starting backup at 31-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/jay/system01.dbf
channel ORA_DISK_1: starting piece 1 at 31-AUG-10
channel ORA_DISK_1: finished piece 1 at 31-AUG-10
piece handle=/u01/app/oracle/backup/jay/1vlmncgc_1_1 tag=TAG20100831T110340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:17
Finished backup at 31-AUG-10

Starting Control File and SPFILE Autobackup at 31-AUG-10
piece handle=/u01/app/oracle/backup/JAY_c-3148849783-20100831-00 comment=NONE
Finished Control File and SPFILE Autobackup at 31-AUG-10


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jay/control01.ctl
/u01/app/oracle/oradata/jay/control02.ctl
/u01/app/oracle/oradata/jay/control03.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control01.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control02.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control03.ctl

SQL> SHU ABORT;
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             184550080 bytes
Database Buffers          255852544 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL>

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 31 11:17:10 2010

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

connected to target database: jay (not mounted)

RMAN> set DBID=3148849783 
executing command: SET DBID

RMAN> run 
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP; 
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: JAY
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100831
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 31-AUG-10

Restore controlfile from Backup

RMAN> backup current controlfile; 
Starting backup at 31-AUG-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 31-AUG-10
channel ORA_DISK_1: finished piece 1 at 31-AUG-10
piece handle=/u01/app/oracle/backup/jay/1tlmnb7j_1_1 tag=TAG20100831T104154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 31-AUG-10

RMAN> exit
SQL> ! rm -rf /u01/app/oracle/oradata/jay/control01.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control02.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control03.ctl

SQL> shu abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             180355776 bytes
Database Buffers          260046848 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from '/u01/app/oracle/backup/jay/1tlmnb7j_1_1';

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 31-AUG-10

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/31/2010 10:47:17
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'

RMAN> recover database;

Starting recover at 31-AUG-10
Starting implicit crosscheck backup at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 31-AUG-10

Starting implicit crosscheck copy at 31-AUG-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-AUG-10

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s3sdy2_.arc

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s3rd5v_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s3sdy2_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/jay/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s3rd5v_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s3sdy2_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/oradata/jay/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 31-AUG-10

RMAN> alter database open resetlogs;

database opened

RMAN>

RE-CREATE USING CONTROLFILE SNAPSHOT

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jay/control01.ctl
/u01/app/oracle/oradata/jay/control02.ctl
/u01/app/oracle/oradata/jay/control03.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control01.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control02.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/jay/control03.ctl

SQL> shu abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             176161472 bytes
Database Buffers          264241152 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [jay] ?
[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 31 10:25:24 2010

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

connected to target database: jay (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_jay.f';

Starting restore at 31-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 31-AUG-10

RMAN> alter database mount; 
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 31-AUG-10
Starting implicit crosscheck backup at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 31-AUG-10

Starting implicit crosscheck copy at 31-AUG-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-AUG-10

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s2n977_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/jay/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/jay/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/jay/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/jay/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/jay/1mlmljvq_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/jay/1mlmljvq_1_1 tag=TAG20100830T185905
channel ORA_DISK_1: restore complete, elapsed time: 00:01:27
Finished restore at 31-AUG-10

RMAN> recover database;

Starting recover at 31-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s29o76_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s2n977_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/jay/redo01.log
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s29o76_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s2n977_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/oradata/jay/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:04
Finished recover at 31-AUG-10

RMAN> alter database open resetlogs;

database opened

RMAN>

Limitations When Using a Backup Control File

After you restore your database using a backup control file, you must run RECOVER DATABASE and perform an OPEN RESETLOGS on the database.

Reference:
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

ORA-00283: ORA-00314: ORA-00312: Error and solution

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 30 Aug 2010 23:58:16 PDT

While trying to restore controlfile from backup , while recovery i got the error

ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2 doesn't match 11
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jay/redo02.log'

Reason:

In any case, the ArchiveLog must be backed up. Else, a RESTORE alone cannot do a RECOVER.

If your database backup did not include the ArchiveLogs, then the backup you created does not have the Redo information that Oracle must apply to the Database Backup. That is why you got the "unknown log'.
Also, if the controlfile backup is before the archivelog backup, the controlfile, even when restored, is not aware of the archivelogs in the backup created subsequent to it.
RMAN can still do a RECOVER , implicitly using the "BACKUP CONTROLFILE" and doing a rollforward but it needs to have to restore the ArchiveLog first -- and the information about which Backupset contains the ArchiveLog is not available to it. You would need to CATALOG the ArchiveLog BackupSet and then restore the archivelogs from there.
(If you use an RMAN Recovery Catalog database, then of course, the Catalog has information about the ArchiveLogs and the BackupSets containing the ArchiveLogs so RMAN queries the Catalog to identify the BackupSets and extracts the necessary ArchiveLogs from the Backupsets).

Solution:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL> ! rm -rf /u01/app/oracle/oradata/jay/control*

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

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             188744384 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 31 11:44:44 2010

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

connected to target database: jay (not mounted)

RMAN> restore controlfile from '/u01/control01.ctl';

Starting restore at 31-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 31-AUG-10

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/31/2010 11:45:45
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/31/2010 11:45:51
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'
ORA-01207: file is more recent than control file - old control file

RMAN> recover database;

Starting recover at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/31/2010 11:46:04
RMAN-06094: datafile 1 must be restored

RMAN> restore database;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/jay/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/jay/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/jay/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/jay/1mlmljvq_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/jay/1mlmljvq_1_1 tag=TAG20100830T185905
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/jay/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/jay/1vlmncgc_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/jay/1vlmncgc_1_1 tag=TAG20100831T110340
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 31-AUG-10

RMAN> recover database;

Starting recover at 31-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s357q5_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s357v5_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s357hd_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s44djv_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s44dr5_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s44dwq_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s4jbd0_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s4jc79_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s4jgmp_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s4jgsl_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_5_67s4jmr8_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_6_67s4jmvt_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_7_67s4jrxw_.arc
archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_8_67s4js5b_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_9_67s633xq_.arc
archive log thread 1 sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_10_67s6340m_.arc
archive log thread 1 sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_11_67s633qq_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s66btj_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s66jb0_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_2_67qdks9h_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_3_67qfn02l_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_4_67qfn0cq_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_5_67qfmzj5_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_30/o1_mf_1_1_67qgpfo4_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s357q5_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s357v5_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s357hd_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s44djv_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s44dr5_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s44dwq_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s4jbd0_.arc thread=1 sequence=1
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_67s4jc79_.arc thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_3_67s4jgmp_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_4_67s4jgsl_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_5_67s4jmr8_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_6_67s4jmvt_.arc thread=1 sequence=6
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_7_67s4jrxw_.arc thread=1 sequence=7
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_8_67s4js5b_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_9_67s633xq_.arc thread=1 sequence=9
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_10_67s6340m_.arc thread=1 sequence=10
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_11_67s633qq_.arc thread=1 sequence=11
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/31/2010 11:53:18
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_1_67s6698c_.arc'
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2 doesn't match 11
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jay/redo02.log'

RMAN> exit


Recovery Manager complete.

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             188744384 bytes
Database Buffers          251658240 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'


SQL> alter database clear logfile '/u01/app/oracle/oradata/jay/redo02.log'; 
Database altered.


SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/jay/system01.dbf'


SQL>  recover database until cancel;
ORA-00279: change 696770 generated at 08/31/2010 11:23:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_31/o1_mf_1_2_%u_.arc
ORA-00280: change 696770 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL>

Disaster recovery using RMAN Demo

noreply@blogger.com (Rajeshkumar Govindarajan) — Sat, 28 Aug 2010 03:35:10 PDT

As part of disaster recovery exercise or to test the validity of a RMAN backup, a full restore and recovery of databases can be performed on scratch or test servers utilising the production RMAN backups which have been restored from the tape backups on these test or scratch servers.

This note will illustrate the above procedure by detailing the steps required to restore the backup of a production database

1. same server with same location

2. Different server with same location

The following assumptions are made in this note:

1.The RMAN backups have been restored from tape backups to the same backup location on the test server as the production server where the backup was originally taken
2.The identical directory structure as is present on production has been created on the test server. This will apply to not only the location of the database files (data, control files, redo log files), but also to the bdump,cdump, udump and adump locations.

3.Controlfile autobackup has been enabled. This is important.

#Overview

Restore the spfile from the autobackup
Restore the controlfile from the autobackup
Restore the data files
Recover by applying archived redo log files
Open the database with resetlogs

1.Restore the SPFILE

[oracle@rac1 backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Aug 28 03:12:17 2010

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

connected to target database (not started)

RMAN> set dbid=3148849783 
executing command: SET DBID

RMAN> startup force nomount; 
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initjay.ora'

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

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> run 
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> RESTORE SPFILE FROM AUTOBACKUP;

4> } 
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20100828
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100828-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-AUG-10

RMAN>
RMAN> shutdown immediate

Oracle instance shut down

2.Restore the Control File

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     444596224 bytes

Fixed Size                     1219904 bytes
Variable Size                130024128 bytes
Database Buffers             310378496 bytes
Redo Buffers                   2973696 bytes

RMAN> set dbid=3148849783 
executing command: SET DBID

RMAN> run
 2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> RESTORE controlfile FROM AUTOBACKUP;

4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: JAY
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/JAY/autobackup/2010_08_28/o1_mf_s_728189319_67jbxhyh_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 28-AUG-10


3.Restore the database


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 28-AUG-10
Starting implicit crosscheck backup at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 28-AUG-10

Starting implicit crosscheck copy at 28-AUG-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-AUG-10

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/JAY/autobackup/2010_08_28/o1_mf_s_728189319_67jbxhyh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/jay/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/jay/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/jay/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/jay/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/JAY_0mlmei9b_1_1.bckp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/JAY_0mlmei9b_1_1.bckp tag=TAG20100828T024706channel ORA_DISK_1: restore complete, elapsed time: 00:01:47
Finished restore at 28-AUG-10

4.Recover the database

RMAN> recover database;
Starting recover at 28-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 22 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_22_67jbxb7n_.arc
archive log thread 1 sequence 23 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc
archive log thread 1 sequence 24 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc
archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc
archive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc
archive log thread 1 sequence 27 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc
archive log thread 1 sequence 28 is already on disk as file /u01/app/oracle/oradata/jay/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_22_67jbxb7n_.arc thread=1 sequence=22
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc thread=1 sequence=23
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc thread=1 sequence=24
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc thread=1 sequence=25
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc thread=1 sequence=26
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc thread=1 sequence=27
archive log filename=/u01/app/oracle/oradata/jay/redo03.log thread=1 sequence=28media recovery complete, elapsed time: 00:00:06
Finished recover at 28-AUG-10

 The recovery will fail at a point where it cannot restore any more archived redo log
files.

I had full backup so no error else it gives error , no problem just exit from rman relogin

and open the database with resetlog.

RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs

RMAN> exit


Recovery Manager complete.

5.Verify the database
[oracle@rac1 backup]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 28 03:22:35 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
JAY       3148849783

SQL>

DBCA hangs at 85%

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Fri, 27 Aug 2010 12:32:31 PDT

cause:

kernel BUG at kernel/exit.c:904!
kernel/exit.c:904!
solution:

Upgraded the Kernel to version RHEL4.7

also
the same solution for the Enterprise Manager Agent Starts on RHEL 4
refer metalink Doc ID: 729543.1
Linux Crashes when Enterprise Manager Agent Starts on RHEL 4 Update 6 and 7

reference:
http://forums.oracle.com/forums/thread.jspa?threadID=871254
http://newappsdba.blogspot.com/2009/06/kernel-bug-and-oracle-enterprise-linux.html

Transparent Application Failover TAF Demo

noreply@blogger.com (Rajeshkumar Govindarajan) — Fri, 27 Aug 2010 11:58:20 PDT

Transparent Application Failover TAF Demo

here, i demonstrated the demo of TAF , i concentrate only the practical demo, for theoretical part refer the oracle documentation.

1. BASIC METHOD

ADD THE ENTRIES TO THE RAC DATABASE NODE1 tnsnames.ora file

CDBS_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip.localdomain)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip.localdomain)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (FAILOVER_MODE=
      (TYPE=SELECT)
      (METHOD=BASIC)
     )
    )
  )

Make sure all the resources are up and running.
root@racnode1 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....s1.inst application    ONLINE    ONLINE    racnode1
ora....s2.inst application    ONLINE    ONLINE    racnode2
ora....bs1.srv application    ONLINE    ONLINE    racnode1
ora....bs2.srv application    ONLINE    ONLINE    racnode2
ora...._srv.cs application    ONLINE    ONLINE    racnode2
ora.cdbs.db    application    ONLINE    ONLINE    racnode2
ora....SM1.asm application    ONLINE    ONLINE    racnode1
ora....E1.lsnr application    ONLINE    ONLINE    racnode1
ora....de1.gsd application    ONLINE    ONLINE    racnode1
ora....de1.ons application    ONLINE    ONLINE    racnode1
ora....de1.vip application    ONLINE    ONLINE    racnode1
ora....SM2.asm application    ONLINE    ONLINE    racnode2
ora....E2.lsnr application    ONLINE    ONLINE    racnode2
ora....de2.gsd application    ONLINE    ONLINE    racnode2
ora....de2.ons application    ONLINE    ONLINE    racnode2
ora....de2.vip application    ONLINE    ONLINE    racnode2

[oracle@racnode1 admin]$ sqlplus system/oracle@CDBS_TAF

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 26 17:36:45 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show user;
USER is "SYSTEM"

SQL> col osuser format a10
SQL> col username format a10
SQL> col failover_type format a15
SQL> col failover_method format a18

SQL> select username,osuser,failover_method,failover_type from v$session
  2  where username='SYSTEM';

USERNAME   OSUSER     FAILOVER_METHOD    FAILOVER_TYPE
---------- ---------- ------------------ ---------------
SYSTEM     oracle     BASIC              SELECT


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdbs1

SQL> select count(*) from demotb;

  COUNT(*)
----------
       954

SQL> select * from demotb;

run the long running query


simultaneously open another terminal and give shutdown abort instance cdbs1

SQL> shu abort
ORACLE instance shut down.

but the query runs smoothly without any interruption after the completion of the query, verify the instance name

SQL>select instance_name from v$instance;

INSTANCE_NAME
----------------
cdbs2

automatically it switch over to the instance cdbs2 , because of this user can access the database and no failure in queries. users can access without interruption. 
USER will not receive any error message while running query.

2. PRECONNECT METHOD

Add the entries to the tnsnames.ora file on both nodes.

CDBS1_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (INSTANCE_NAME = cdbs1)
      (FAILOVER_MODE=
      (BACKUP=CDBS2_TAF)
      (TYPE=SELECT)
      (METHOD=PRECONNECT)
     )
    )
  )


CDBS2_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (INSTANCE_NAME = cdbs2)
      (FAILOVER_MODE=
      (BACKUP=CDBS1_TAF)
      (TYPE=SELECT)
      (METHOD=PRECONNECT)
     )
    )
  )

connect to the database on node1

[oracle@racnode1 ~]$ sqlplus system/oracle@CDBS1_TAF

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 27 12:23:11 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
cdbs1

SQL> COL USERNAME FOR a10
SQL> col osuser for a10
SQL> col failover_type format a15
SQL> col failover_method for a18
SQL> select username,failover_type,failover_method from v$session
  2  where username='SYSTEM';

USERNAME   FAILOVER_TYPE   FAILOVER_METHOD
---------- --------------- ------------------
SYSTEM     SELECT          PRECONNECT

SQL> select count(*) from demo;

  COUNT(*)
----------
     49783

run the long running query.

SQL> select * from demo;

simultaneously issue shutdown abort in new terminal

SQL> shu abort;
ORACLE instance shut down.
SQL>

the query runs smoothly, user will not receive any kind of error messages.
then check the instance name in node1 machine.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdbs2

SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';

USERNAME   OSUSER     FAILOVER_TYPE   FAILOVER_METHOD
---------- ---------- --------------- ------------------
SYSTEM     oracle     SELECT          PRECONNECT

SQL>

checking node2 machine before and after TAF.

Before TAF
SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';

USERNAME                       OSUSER                         FAILOVER_TYPE
------------------------------ ------------------------------ -------------
FAILOVER_M
----------
SYSTEM                         oracle                         NONE
NONE


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdbs2

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdbs2

After TAF.
SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';

USERNAME                       OSUSER                         FAILOVER_TYPE
------------------------------ ------------------------------ -------------
FAILOVER_M
----------
SYSTEM                         oracle                         SELECT
PRECONNECT


SQL>

Hope, this will helps you to understand the TAF.

for theoritical part please refer oracle documentation.

Regards,
RAJESHKUMAR G

[PRKC-1044 : Failed to check remote command execution setup for node

noreply@blogger.com (Rajeshkumar Govindarajan) — Fri, 27 Aug 2010 11:58:52 PDT

[PRKC-1044 : Failed to check remote command execution setup for node racnode1.localdomain using shells /usr/bin/ssh and /usr/bin/rsh Permission denied.]]

solution:

Configure SSH between two nodes

#

Log in as the software owner (in this example, the oracle user).

note: dont use su - oracle , logout and login as oracle user.
make sure you login as oracle user.

#

To ensure that you are logged in as the Oracle user, and that the user ID matches the expected user ID you have assigned to the Oracle user, enter the commands id. Ensure that Oracle user group and user and the terminal window process group and user IDs are identical.
example: check it on both nodes

[oracle@racnode1 ~]$ id 
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)
[oracle@racnode2 ~]$ id 
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

#
While logged in as oracle perform the following on each of the nodes of the RAC:

1. cd $HOME
2. mkdir ~/.ssh
3. chmod 700 ~/.ssh
4. /usr/bin/ssh-keygen -t rsa
5. /usr/bin/ssh-keygen -t dsa

On Node 1:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. Copy the authorized_keys file to the node 2. scp authorized_keys racnode2:/home/oracle/.ssh

On Node 2:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. scp authorized_keys racnode1:/home/oracle/.ssh

On each node, enter the following commands to start the SSH agent, and to load the SSH keys into memory:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add

At the prompt, enter the pass phrase for each key that you generated.

* Now perform a ssh between all the nodes including the node-priv hostnames as well. Check to make sure that ssh is configured well without prompting for the password (on both the nodes):
#

Can you make sure your sshd_config file as following entries uncommented?

X11Forwarding yes

X11DisplayOffset 10

X11UseLocalhost yes

If not then edit and uncomment them. Restart the ssh once you make these changes.

Restart OpenSSH server

Type the following command:

#/etc/init.d/ssh restart

Add the entries to the .bashrc file as oracle user

#

if [ -t 0 ]; then
stty intr ^C
fi

#

Try creating a symbolic link:

ln -s /usr/bin/ssh /usr/local/bin/ssh
ln -s /usr/bin/scp /usr/local/bin/scp

After you set the dsa and rsa keys in to the authorized_keys file.

From NODE 1

ssh racnode1 date
ssh racnode2 date

ssh racnode1.localdomain date
ssh racnode2.localdomain date

ssh racnode1-priv date
ssh racnode2-priv date

ssh racnode1-priv.localdomain date
ssh racnode2-priv.localdomain date

From NODE 2

ssh racnode1 date
ssh racnode2 date

ssh racnode1.localdomain date
ssh racnode2.localdomain date

ssh racnode1-priv date
ssh racnode2-priv date

ssh racnode1-priv.localdomain date
ssh racnode2-priv.localdomain date

Hope, This will resolve your issue.

references:
http://download.oracle.com/docs/cd/B28359_01/install.111/b28263/prelinux.htm#BABJBAEB

ORA-01122: ORA-01110: ORA-01206: After RMAN duplicate in source database

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Sat, 21 Aug 2010 02:26:48 PDT

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DATA/demodb/datafile/system.296.726853295'
ORA-01206: file is not part of this database - wrong database id

Reason:

RMAN Duplicate command updates source database, datafile headers.

Solution:

Restore and Recover the database with RMAN Backup.

to avoid this error add the following entries in pfile(clone/duplicate database)
db_file_name_convert='+DATA/DEMODB/DATAFILE/','/u01/app/oracle/oradata/dupdb/'
log_file_name_convert='+DATA/DEMODB/ONLINELOG/','/u01/app/oracle/oradata/dupdb/'

or set new name command in rman script.


[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [+ASM2] ? demodb
[oracle@rac2 ~]$ rlsqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 21 14:32:31 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and OLAP options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select status from v$instance;

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

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+DATA/demodb/datafile/system.296.726853295'
ORA-01206: file is not part of this database - wrong database id

oracle@rac2 backup]$ . oraenv
ORACLE_SID = [demodb] ? demodb
[oracle@rac2 backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Aug 21 14:34:47 2010

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

connected to target database: DEMODB (DBID=3723121283, not open)

RMAN> restore database
2> ;

Starting restore at 21-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/demodb/datafile/system.296.726853295
restoring datafile 00002 to +DATA/demodb/datafile/undotbs1.298.726853331
restoring datafile 00003 to +DATA/demodb/datafile/sysaux.297.726853315
restoring datafile 00004 to +DATA/demodb/datafile/users.299.726853339
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/DEMODB_02ll6n2k_1_1.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/DEMODB_02ll6n2k_1_1.bkp tag=TAG20100813T000329
channel ORA_DISK_1: restore complete, elapsed time: 00:02:38
Finished restore at 21-AUG-10

RMAN> recover database;

Starting recover at 21-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file +DATA/demodb/archivelog/2010_08_13/thread_1_seq_3.311.726883477
archive log thread 1 sequence 4 is already on disk as file +DATA/demodb/archivelog/2010_08_13/thread_1_seq_4.312.726885007
archive log thread 1 sequence 5 is already on disk as file +DATA/demodb/archivelog/2010_08_13/thread_1_seq_5.313.726940211
archive log thread 1 sequence 6 is already on disk as file +DATA/demodb/archivelog/2010_08_13/thread_1_seq_6.314.726940893
archive log thread 1 sequence 7 is already on disk as file +DATA/demodb/archivelog/2010_08_13/thread_1_seq_7.315.726940901
archive log filename=+DATA/demodb/archivelog/2010_08_13/thread_1_seq_3.311.726883477 thread=1 sequence=3
archive log filename=+DATA/demodb/archivelog/2010_08_13/thread_1_seq_4.312.726885007 thread=1 sequence=4
archive log filename=+DATA/demodb/archivelog/2010_08_13/thread_1_seq_5.313.726940211 thread=1 sequence=5
media recovery complete, elapsed time: 00:00:39
Finished recover at 21-AUG-10

RMAN> exit


Recovery Manager complete.
[oracle@rac2 backup]$ . oraenv
ORACLE_SID = [demodb] ?
[oracle@rac2 backup]$ rlsqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 21 14:44:48 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and OLAP options

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery




SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/demodb/datafile/system.296.726853295
+DATA/demodb/datafile/undotbs1.298.726853331
+DATA/demodb/datafile/sysaux.297.726853315
+DATA/demodb/datafile/users.299.726853339

SQL>

Recover Corrupt/Missing OCR and Voting Disk with No Backup

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Fri, 20 Aug 2010 02:18:36 PDT

Recover Corrupt/Missing OCR and Voting Disk without Backup

It happens. Not very often, but it can happen. You are faced with a corrupt or missing Oracle Cluster Registry (OCR) and have no backup to recover from.

/u01/crs/oracle/product/10.2.0/crs/log/rac1/alertrac1.log

[client(20186)]CRS-1006:The OCR location /dev/raw/raw1 is inaccessible.
Details in /u01/crs/oracle/product/10.2.0/crs/log/rac1/client/ocrcheck_20186.log.

ocrcheck_20186.log file contents
2010-08-20 12:19:19.796: [ default][4143896256]a_init:7!: Backend init unsuccessful : [22]
2010-08-20 12:19:19.804: [OCRCHECK][4143896256]Failed to initialize OCR context:
[PROC-22: The OCR backend has an invalid format]
2010-08-20 12:19:19.804: [OCRCHECK][4143896256]Failed to initialize ocrchek2
2010-08-20 12:19:19.804: [OCRCHECK][4143896256]Exiting [status=failed].

[root@rac1 bin]# ./ocrcheck
PROT-601: Failed to initialize ocrcheck
[root@rac1 bin]# ./crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

[root@rac1 bin]#
[root@rac1 bin]# ./crsctl query css votedisk
OCR initialization failed with invalid format: PROC-22: The OCR backend has an invalid format
[root@rac1 bin]#

[root@rac2 bin]# ./crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

verify all the database are disconnect and shutdown

1.Execute rootdelete.sh from All Nodes.

The rootdelete.sh script can be found at $ORA_CRS_HOME/install/rootdelete.sh on all nodes in the cluster


[root@rac1 install]# pwd
/u01/crs/oracle/product/10.2.0/crs/install
[root@rac1 install]# ./rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed with invalid format: PROC-22: The OCR backend has an invalid format
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down...
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in '/etc/oracle/scls_scr'
[root@rac1 install]#

[root@rac2 install]# pwd
/u01/crs/oracle/product/10.2.0/crs/install
[root@rac2 install]# ./rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed with invalid format:
PROC-22: The OCR backend has an invalid format
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down...
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in '/etc/oracle/scls_scr'
[root@rac2 install]#

2.Run rootdeinstall.sh from the Primary Node.

[root@rac1 install]# ./rootdeinstall.sh

Removing contents from OCR device
2560+0 records in
2560+0 records out

3.Run root.sh from the Primary Node. (same node as above)

[root@rac1 install]# cd ..
[root@rac1 crs]# ./root.sh
WARNING: directory '/u01/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/u01/crs/oracle/product' is not owned by root
WARNING: directory '/u01/crs/oracle' is not owned by root
WARNING: directory '/u01/crs' is not owned by root
WARNING: directory '/u01' is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/u01/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/u01/crs/oracle/product' is not owned by root
WARNING: directory '/u01/crs/oracle' is not owned by root
WARNING: directory '/u01/crs' is not owned by root
WARNING: directory '/u01' is not owned by root
assigning default hostname rac1 for node 1.
assigning default hostname rac2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: rac1 rac1-priv rac1
node 2: rac2 rac2-priv rac2
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Now formatting voting device: /dev/raw/raw2
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
CSS is inactive on these nodes.
rac2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
[root@rac1 crs]#
[root@rac2 crs]# pwd
/u01/crs/oracle/product/10.2.0/crs
[root@rac2 crs]# ./root.sh
WARNING: directory '/u01/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/u01/crs/oracle/product' is not owned by root
WARNING: directory '/u01/crs/oracle' is not owned by root
WARNING: directory '/u01/crs' is not owned by root
WARNING: directory '/u01' is not owned by root
Checking to see if Oracle CRS stack is already configured

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
rac2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

Oracle 10.2.0.1 users should note that running root.sh on the last node will fail. Most notably is the silent mode VIPCA configuration failing because of BUG 4437727 in 10.2.0.1.

to workaround these errors.

[root@rac2 bin]# pwd
/u01/crs/oracle/product/10.2.0/crs/bin
[root@rac2 bin]# ./vipca &

When the "VIP Configuration Assistant" appears, this is how I answered the screen prompts:

Welcome: Click Next
Network interfaces: Select only the public interface - eth0
Virtual IPs for cluster nodes:
Node Name: rac1
IP Alias Name: rac1-vip.localdomain
IP Address: 192.168.1.111
Subnet Mask: 255.255.255.0

Node Name: rac2
IP Alias Name: rac2-vip.localdomain
IP Address: 192.168.1.112
Subnet Mask: 255.255.255.0

Summary: Click Finish
Configuration Assistant Progress Dialog: Click OK after configuration is complete.
Configuration Results: Click Exit

4.Configure Server-Side ONS using racgons.

CRS_home/bin/racgons add_config hostname1:port hostname2:port


[root@rac1 crs]# cd bin
[root@rac1 bin]# pwd
/u01/crs/oracle/product/10.2.0/crs/bin
[root@rac1 bin]# ./racgons add_config rac1:6200 rac2:6200
[root@rac1 bin]# ./onsctl ping
Number of onsconfiguration retrieved, numcfg = 2
onscfg[0]
{node = rac1, port = 6200}
Adding remote host rac1:6200
GETHOSTBYNAME(rac1): 2
onscfg[1]
{node = rac2, port = 6200}
Adding remote host rac2:6200
GETHOSTBYNAME(rac2): 2
ons is running ...
[root@rac1 bin]#

5.Configure Network Interfaces for Clusterware.
Log in as the owner of the Oracle Clusterware software which is typically the oracle user account and configure all network interfaces. The first step is to identify the current interfaces and IP addresses using oifcfg iflist.


[root@rac1 bin]# pwd
/u01/crs/oracle/product/10.2.0/crs/bin
[root@rac1 bin]# ./oifcfg iflist
eth0  192.168.1.0
eth1  192.168.2.0
[root@rac1 bin]# ./oifcfg setif -global eth0/192.168.1.0:public
[root@rac1 bin]# ./oifcfg setif -global eth1/192.168.2.0:cluster_interconnect
[root@rac1 bin]# ./oifcfg getif
eth0  192.168.1.0  global  public
eth1  192.168.2.0  global  cluster_interconnect
[root@rac1 bin]#

6.Add TNS Listener using NETCA.

As the Oracle Clusterware software owner (typically oracle), add a cluster TNS listener configuration to OCR using netca. This may give errors if the listener.ora contains the entries already. If this is the case, move the listener.ora to /tmp from the $ORACLE_HOME/network/admin or from the $TNS_ADMIN directory if the TNS_ADMIN environmental is defined and then run netca. Add all the listeners that were added during the original Oracle Clusterware software installation.

[oracle@rac2 admin]$ mv listener.ora /tmp/listener.ora.original

[oracle@rac1 admin]$ mv listener.ora /tmp/listener.ora.original
[oracle@rac1 admin]$ netca &

Screen Name	Response
Select the Type of Oracle Net Services Configuration	Select Cluster configuration
Select the nodes to configure	Select all
Type of Configuration	Select Listener configuration.
Listener Configuration Next 6 Screens	The following screens are now like any other normal listener configuration. You can simply accept the default parameters for the next six screens: What do you want to do: Add Listener name: LISTENER Selected protocols: TCP Port number: 1521 Configure another listener: No Listener configuration complete! [ Next ] You will be returned to this Welcome (Type of Configuration) Screen.
Type of Configuration	Select Naming Methods configuration.
Naming Methods Configuration	The following screens are: Selected Naming Methods: Local Naming Naming Methods configuration complete! [ Next ] You will be returned to this Welcome (Type of Configuration) Screen.
Type of Configuration	Click Finish to exit the NETCA.

7.Add all Resources Back to OCR using srvctl


[root@rac1 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

As a final step, log in as the Oracle Clusterware software owner (typically oracle) and add all resources back to the OCR using the srvctl command.

Please ensure that these commands are not run as the root user account.

Add ASM INSTANCE(S) to OCR:

srvctl add asm -n -i -o

[oracle@rac1 bin]$ pwd
/u01/crs/oracle/product/10.2.0/crs/bin
[oracle@rac1 bin]$ ./srvctl add asm -i +ASM1 -n rac1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 bin]$ ./srvctl add asm -i +ASM2 -n rac2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 bin]$

Add DATABASE to OCR:

srvctl add database -d -o

[oracle@rac1 bin]$ ./srvctl add database -d cdbs -o /u01/app/oracle/product/10.2.0/db_1

Add INSTANCE(S) to OCR:

srvctl add instance -d -i -n

[oracle@rac1 bin]$ ./srvctl add instance -d cdbs -i cdbs1 -n rac1
[oracle@rac1 bin]$ ./srvctl add instance -d cdbs -i cdbs2 -n rac2

Add SERVICE(S) to OCR:

srvctl add service -d -s -r -P

where TAF_policy is set to NONE, BASIC, or PRECONNECT

[oracle@rac1 bin]$ ./srvctl add service -d cdbs -s cdbs_srvc -r cdbs1,cdbs2 -P BASIC

After completing the steps above, the OCR should have been successfully recreated. Bring up all of the resources that were added to the OCR and run cluvfy to verify the cluster configuration.


[oracle@rac1 bin]$ ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....s1.inst application    OFFLINE   OFFLINE
ora....s2.inst application    OFFLINE   OFFLINE
ora....bs1.srv application    OFFLINE   OFFLINE
ora....bs2.srv application    OFFLINE   OFFLINE
ora....srvc.cs application    OFFLINE   OFFLINE
ora.cdbs.db    application    OFFLINE   OFFLINE
ora....SM1.asm application    OFFLINE   OFFLINE
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    OFFLINE   OFFLINE
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2

[oracle@rac1 bin]$ srvctl start asm -n rac1
[oracle@rac1 bin]$ srvctl start asm -n rac2
[oracle@rac1 bin]$ srvctl start database -d cdbs
[oracle@rac1 bin]$ srvctl start service -d cdbs
[oracle@rac1 bin]$ ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....s1.inst application    ONLINE    ONLINE    rac1
ora....s2.inst application    ONLINE    ONLINE    rac2
ora....bs1.srv application    ONLINE    ONLINE    rac1
ora....bs2.srv application    ONLINE    ONLINE    rac2
ora....srvc.cs application    ONLINE    ONLINE    rac1
ora.cdbs.db    application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2
[oracle@rac1 bin]$

[oracle@rac1 bin]$ cluvfy stage -post crsinst -n rac1,rac2

Performing post-checks for cluster services setup

Checking node reachability...
Node reachability check passed from node "rac1".


Checking user equivalence...
User equivalence check passed for user "oracle".

Checking Cluster manager integrity...


Checking CSS daemon...
Daemon status check passed for "CSS daemon".

Cluster manager integrity check passed.

Checking cluster integrity...


Cluster integrity check passed


Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.

Uniqueness check for OCR device passed.

Checking the version of OCR...
OCR of correct Version "2" exists.

Checking data integrity of OCR...
Data integrity check for OCR passed.

OCR integrity check passed.

Checking CRS integrity...

Checking daemon liveness...
Liveness check passed for "CRS daemon".

Checking daemon liveness...
Liveness check passed for "CSS daemon".

Checking daemon liveness...
Liveness check passed for "EVM daemon".

Checking CRS health...
CRS health check passed.

CRS integrity check passed.

Checking node application existence...


Checking existence of VIP node application (required)
Check passed.

Checking existence of ONS node application (optional)
Check passed.

Checking existence of GSD node application (optional)
Check passed.


Post-check for cluster services setup was successful.
[oracle@rac1 bin]$

source and reference:
http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_70.shtml#Recover%20Corrupt/Missing%20OCR