- Doing the failover from RAC to single instance
- Take a backup from singleinstance primary for recreation of failed RAC primary again
<list of all databases>
$ ps -fe|grep pmon
grid 4063456 1 0 Apr 25 - 2:39 asm_pmon_+ASM
oracle 5505028 1 0 May 18 - 0:38 ora_pmon_CBELIVESBY
oracle 12845220 1 0 20:01:18 - 0:06 ora_pmon_TRAINSBY
oracle 18022502 18415664 0 10:24:58 pts/0 0:00 grep pmon
$ export ORACLE_SID=TRAINSBY
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 22 10:25:40 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
<primary database>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ id
uid=1101(oracle) gid=1000(oinstall) groups=1200(dba),1300(asmdba)
$ echo $ORACLE_SID
TRAINSBY
<login to primary single instance database to take backup>
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 22 10:36:55 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TRAIN (DBID=1173390908)
RMAN>
RMAN> report schema
2> ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TRAINSBY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 950 SYSTEM *** +DATA/trainsby/datafile/system.318.783876789
2 1020 SYSAUX *** +DATA/trainsby/datafile/sysaux.319.783876647
3 3050 UNDOTBS1 *** +DATA/trainsby/datafile/undotbs1.268.783876647
4 5 USERS *** +DATA/trainsby/datafile/users.317.783876789
5 3325 UNDOTBS2 *** +DATA/trainsby/datafile/undotbs2.310.783876647
6 18692 T24DISTDATA *** +DATA/trainsby/datafile/t24distdata.292.783876643
7 5596 T24DISTINDEX *** +DATA/trainsby/datafile/t24distindex.346.783876645
8 14596 T24DISTDATA *** +DATA/trainsby/datafile/t24distdata.349.783876645
9 4096 T24DISTDATA *** +DATA/trainsby/datafile/t24distdata.309.783876647
10 4096 T24DISTDATA *** +DATA/trainsby/datafile/t24distdata.320.783876647
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 601 TEMP 32767 +DATA/trainsby/tempfile/temp.278.783884917
<backup database>
RMAN> run
2> {sql "alter system switch logfile";
3> allocate channel ch1 type disk format '/u02/backup/rman/Primary_bkp_for_stndby_%U';
4> backup database;
5> backup current controlfile for standby;
6> sql "alter system archive log current";
7> }
sql statement: alter system switch logfile
allocated channel: ch1
channel ch1: SID=1325 device type=DISK
Starting backup at 22-MAY-12
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/trainsby/datafile/t24distdata.292.783876643
input datafile file number=00008 name=+DATA/trainsby/datafile/t24distdata.349.783876645
input datafile file number=00007 name=+DATA/trainsby/datafile/t24distindex.346.783876645
input datafile file number=00009 name=+DATA/trainsby/datafile/t24distdata.309.783876647
input datafile file number=00010 name=+DATA/trainsby/datafile/t24distdata.320.783876647
input datafile file number=00005 name=+DATA/trainsby/datafile/undotbs2.310.783876647
input datafile file number=00003 name=+DATA/trainsby/datafile/undotbs1.268.783876647
input datafile file number=00002 name=+DATA/trainsby/datafile/sysaux.319.783876647
input datafile file number=00001 name=+DATA/trainsby/datafile/system.318.783876789
input datafile file number=00004 name=+DATA/trainsby/datafile/users.317.783876789
channel ch1: starting piece 1 at 22-MAY-12
channel ch1: finished piece 1 at 22-MAY-12
piece handle=/u02/backup/rman/Primary_bkp_for_stndby_0fnbk3up_1_1 tag=TAG20120522T104128 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 22-MAY-12
channel ch1: finished piece 1 at 22-MAY-12
piece handle=/u02/backup/rman/Primary_bkp_for_stndby_0gnbk414_1_1 tag=TAG20120522T104128 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-MAY-12
Starting backup at 22-MAY-12
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including standby control file in backup set
channel ch1: starting piece 1 at 22-MAY-12
channel ch1: finished piece 1 at 22-MAY-12
piece handle=/u02/backup/rman/Primary_bkp_for_stndby_0hnbk417_1_1 tag=TAG20120522T104247 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-MAY-12
sql statement: alter system archive log current
released channel: ch1
RMAN> exit
Recovery Manager complete.
<LIST BACKUPS on standby serve @ /u02/backup/rman >
$ pwd
/u02/backup/rman
$ ls -ltr
total 774880
-rw-r----- 1 oracle asmadmin 394477568 May 22 10:42 Primary_bkp_for_stndby_0fnbk3up_1_1
-rw-r----- 1 oracle asmadmin 1130496 May 22 10:42 Primary_bkp_for_stndby_0gnbk414_1_1
-rw-r----- 1 oracle asmadmin 1130496 May 22 10:42 Primary_bkp_for_stndby_0hnbk417_1_1
<SCP backups to priamry DB server>
$ scp Primary_bkp_for_stndby* oracle@10.1.8.15:/u02/backup/rman/
oracle@10.1.8.15's password:
Primary_bkp_for_stndby_0fnbk3up_1_1 100% 376MB 28.9MB/s 00:13
Primary_bkp_for_stndby_0gnbk414_1_1 100% 1104KB 1.1MB/s 00:00
Primary_bkp_for_stndby_0hnbk417_1_1 100% 1104KB 1.1MB/s 00:00
$ ps -fe|grep pmon
grid 4063456 1 0 Apr 25 - 2:40 asm_pmon_+ASM
oracle 5505028 1 0 May 18 - 0:39 ora_pmon_CBELIVESBY
oracle 12845220 1 0 20:01:18 - 0:06 ora_pmon_TRAINSBY
- Recreation of RAC done as below.
$ ps -fe|grep pmon
grid 7208962 1 0 May 07 - 1:45 asm_pmon_+ASM1
oracle 9830492 43974732 0 10:43:59 pts/1 0:00 grep pmon
oracle 24707132 1 0 May 20 - 0:26 ora_pmon_LIVECBE1
oracle 29032610 1 0 May 13 - 2:29 ora_pmon_CBELIVE1
oracle 32768210 1 1 23:48:48 - 0:05 ora_pmon_UAT1
oracle 37748968 1 0 May 20 - 0:15 ora_pmon_femobile1
oracle 41091102 1 0 10:33:27 - 0:00 ora_pmon_TRAIN1
<export the RAC 1st node>
$ export ORACLE_SID=TRAIN1
$ pwd
/home/oracle
$ cd /tmp
$ ls -ltr|grep init
-rw-r--r-- 1 oracle asmadmin 2000 May 22 10:37 initTRAIN2205.ora
<modified the init file to include standby parameters>
$ vi initTRAIN2205.ora
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db1
$ echo $ORACLE_SID
TRAIN1
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 22 10:53:54 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
THE INSTANCE NAME IS TRAIN1
SQL>
<Startup with the pfile>
SQL> startup nomount pfile='/tmp/initTRAIN2205.ora';
ORACLE instance started.
Total System Global Area 1.1458E+10 bytes
Fixed Size 2217120 bytes
Variable Size 9831451488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 13496320 bytes
<create spfile from pfile>
SQL> create spfile='+DATA/train/spfiletrain.ora' from pfile='/tmp/initTRAIN2205.ora';
File created.
<shutdown immedate>
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
<startup nomount using the new spfile>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.1458E+10 bytes
Fixed Size 2217120 bytes
Variable Size 9831451488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 13496320 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
$ echo $ORACLE_SID
TRAIN1
$ id
uid=1101(oracle) gid=1000(oinstall) groups=1200(dba),1300(asmdba)
$ echo $ORACLE_SID
TRAIN1
<connect to primary and auxiliary for creating duplicate database>
$ rman target sys/oracle@TRAINSBY auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 22 10:59:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TRAIN (DBID=1173390908)
connected to auxiliary database: TRAIN (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
Starting Duplicate Db at 05/22/12 10:59:44
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=3770 instance=TRAIN1 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/train/controlfile/current.482.783946787'', ''+ARCHIVE/train/controlfile/current.5329.783946789'' comment=
''Set by RMAN'' scope=spfile";
restore clone standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/train/controlfile/current.482.783946787'', ''+ARCHIVE/train/controlfile/current.5329.783946789'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 05/22/12 10:59:48
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/rman/Primary_bkp_for_stndby_0hnbk417_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/rman/Primary_bkp_for_stndby_0hnbk417_1_1 tag=TAG20120522T104247
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/train/controlfile/current.481.783946789
output file name=+ARCHIVE/train/controlfile/current.5318.783946789
Finished restore at 05/22/12 10:59:49
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
set newname for datafile 7 to
"+data";
set newname for datafile 8 to
"+data";
set newname for datafile 9 to
"+data";
set newname for datafile 10 to
"+data";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 05/22/12 10:59:58
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: restoring datafile 00005 to +data
channel ORA_AUX_DISK_1: restoring datafile 00006 to +data
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data
channel ORA_AUX_DISK_1: restoring datafile 00008 to +data
channel ORA_AUX_DISK_1: restoring datafile 00009 to +data
channel ORA_AUX_DISK_1: restoring datafile 00010 to +data
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/rman/Primary_bkp_for_stndby_0fnbk3up_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/rman/Primary_bkp_for_stndby_0fnbk3up_1_1 tag=TAG20120522T104128
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:15
Finished restore at 05/22/12 11:06:14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=31 STAMP=783947174 file name=+DATA/train/datafile/system.476.783947057
datafile 2 switched to datafile copy
input datafile copy RECID=32 STAMP=783947174 file name=+DATA/train/datafile/sysaux.448.783946917
datafile 3 switched to datafile copy
input datafile copy RECID=33 STAMP=783947174 file name=+DATA/train/datafile/undotbs1.447.783946909
datafile 4 switched to datafile copy
input datafile copy RECID=34 STAMP=783947174 file name=+DATA/train/datafile/users.475.783947059
datafile 5 switched to datafile copy
input datafile copy RECID=35 STAMP=783947174 file name=+DATA/train/datafile/undotbs2.466.783946901
datafile 6 switched to datafile copy
input datafile copy RECID=36 STAMP=783947174 file name=+DATA/train/datafile/t24distdata.480.783946799
datafile 7 switched to datafile copy
input datafile copy RECID=37 STAMP=783947174 file name=+DATA/train/datafile/t24distindex.346.783946873
datafile 8 switched to datafile copy
input datafile copy RECID=38 STAMP=783947174 file name=+DATA/train/datafile/t24distdata.479.783946841
datafile 9 switched to datafile copy
input datafile copy RECID=39 STAMP=783947174 file name=+DATA/train/datafile/t24distdata.483.783946885
datafile 10 switched to datafile copy
input datafile copy RECID=40 STAMP=783947174 file name=+DATA/train/datafile/t24distdata.295.783946893
Finished Duplicate Db at 05/22/12 11:08:16
RMAN> exit
Recovery Manager complete.
$ ps -fe|grep pmon
grid 7208962 1 0 May 07 - 1:45 asm_pmon_+ASM1
oracle 21364882 43974732 0 11:08:25 pts/1 0:00 grep pmon
oracle 24707132 1 0 May 20 - 0:26 ora_pmon_LIVECBE1
oracle 29032610 1 0 May 13 - 2:29 ora_pmon_CBELIVE1
oracle 32768210 1 0 23:48:48 - 0:05 ora_pmon_UAT1
oracle 34996306 1 0 10:55:17 - 0:00 ora_pmon_TRAIN1
oracle 37748968 1 0 May 20 - 0:15 ora_pmon_femobile1
$
<connect to the new RAC node1 standby DB>
$ echo $ORACLE_SID
TRAIN1
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 22 11:08:43 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
THE INSTANCE NAME IS TRAIN1
>
SQL>
<check the status of standby database>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN MOUNTED PHYSICAL STANDBY
<enable managed recovery on standby database>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select NAME,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- --------------------
TRAIN MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
<change protection mode to maximum availability mode , but before that u should have changed the log_archive_dest_2 to sysnc noaffirm>
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select NAME,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- --------------------
TRAIN MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN MOUNTED PHYSICAL STANDBY
- Switchover from single instance Primary to just created RAC single node standby
<log in to primary database>
$ export ORACLE_SID=TRAINSBY
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 22 11:09:46 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter system switch logfile;
/
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN READ WRITE PRIMARY
<switchover status >
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
<switchover to standby>
SQL> alter database commit to switchover to standby;
Database altered.
<shutdown the database>
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
<startup mount>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2216200 bytes
Variable Size 3959426808 bytes
Database Buffers 6710886400 bytes
Redo Buffers 16945152 bytes
Database mounted.
<check database has converted to standby>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN MOUNTED PHYSICAL STANDBY
SQL> select NAME,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- --------------------
TRAIN MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
<start managed recovery in the standby database>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL>
<check switchover status>
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
<switchover to primary>
SQL> alter database commit to switchover to primary;
Database altered.
<shutdown immediate>
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
<start new primary>
SQL> startup
ORACLE instance started.
Total System Global Area 1.1458E+10 bytes
Fixed Size 2217120 bytes
Variable Size 9831451488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 13496320 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
- Switch over from standby to primary
<check switchover status>
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
<switchover to primary>
SQL> alter database commit to switchover to primary;
Database altered.
<shutdown immediate>
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
<start new primary>
SQL> startup
ORACLE instance started.
Total System Global Area 1.1458E+10 bytes
Fixed Size 2217120 bytes
Variable Size 9831451488 bytes
Database Buffers 1610612736 bytes
Redo Buffers 13496320 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
- Reopening of RAC node2 after switch over
$ ps -fe|grep pmon
grid 7208982 1 0 May 07 - 2:04 asm_pmon_+ASM2
oracle 20578400 21627052 0 11:02:49 pts/0 0:00 grep pmon
oracle 25821194 1 0 May 13 - 2:58 ora_pmon_CBELIVE2
<export 2nd RAC node>
$ export ORACLE_SID=TRAIN2
$ id
uid=1101(oracle) gid=1000(oinstall) groups=1200(dba),1300(asmdba)
$ echo $ORACLE_SID
TRAIN2
<connect through sqlplus>
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 22 11:23:46 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
<start instance>
SQL> startup
ORACLE instance started.
Total System Global Area 1.1458E+10 bytes
Fixed Size 2217120 bytes
Variable Size 8388610912 bytes
Database Buffers 3053453312 bytes
Redo Buffers 13496320 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
<check if it is opened in read write mode>
SQL>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TRAIN READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
$ ps -fe|grep pmon
grid 7208982 1 0 May 07 - 2:05 asm_pmon_+ASM2
oracle 20119650 1 0 11:23:51 - 0:00 ora_pmon_TRAIN2
oracle 24051730 21627052 0 11:27:35 pts/0 0:00 grep pmon
oracle 25821194 1 0 May 13 - 2:59 ora_pmon_CBELIVE2
sahi hai mamu...
ReplyDelete