SHALOK: Unapproachable and Unfathomable is the Supreme Lord God; whoever speaks of Him shall be liberated. Listen, O friends, Nanak prays, to the wonderful story of the Holy. || 1 ||
ASHTAPADEE: In the Company of the Holy, one’s face becomes radiant. In the Company of the Holy, all filth is removed. In the Company of the Holy, egotism is
eliminated. In the Company of the Holy, spiritual wisdom is revealed. In the Company of the Holy, God is understood to be near at hand. In the Company of the Holy, all conflicts are settled. In the Company of the Holy, one obtains the jewel of the Naam. In the Company of the Holy, one’s efforts are directed toward the One Lord. What mortal can speak of the Glorious Praises of the Holy? O Nanak, the glory of the Holy people merges into God. || 1 ||
Now, we will mount this Oracle ZFS applicance clone on another server and will create the database on that server. I ahe another fresh node rac3.rac.meditate.com. Which is fresh server. I installed non-clustered database binaries on it and mounted the clone on that server. To see how to build rac3 server follow post 1,2,3,4 of my blog. To see how to mount the Oracle ZFS applicance clone on this server (rac3) look at the previous post i.e. post 43. The other solution is to create a standy by database on Oracle ZFS Appliance share and then take its sanp and create clone of snap and mount it on new server and over there on new server open this standby database on clone as read-write database. For now I will use image copy in this post.
Once all above is done, I will restore the spfile of the database as follow:
14:04 : oracle : rac3.rac.meditate.com : @GSINGH : /u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 13 14:04:58 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> set DBID = 686335423
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initGSINGH.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes
RMAN> run {
2> allocate channel c1 device type disk;
3> restore spfile to '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initGSINGH.ora' from '/zfs/backup/1hnhl9f3_1_1';
4> }
allocated channel: c1
channel c1: SID=19 device type=DISK
Starting restore at 13-AUG-12
channel c1: restoring spfile from AUTOBACKUP /zfs/backup/1hnhl9f3_1_1
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-AUG-12
released channel: c1
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
14:10 : oracle : rac3.rac.meditate.com : @GSINGH : /u01/app/oracle/product/11.2.0/dbhome_1/dbs
Fix spfile for ASM as this will be a non ASM database and cluster database=false and also fix the location of controlfile to $ORACLE_HOMe/dbs.
Now start teh database in nomount state using this restored spfile and create the contrlfile with new redolog locations (non ASM) and image copy datafiles that we backed up using RMAN.
Create controlfile as follows:
CREATE CONTROLFILE REUSE DATABASE "GSINGH" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/redo/redo_group_1.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/redo/redo_group_2.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/zfs/backup/data_D-GSINGH_I-686335423_TS-SYSTEM_FNO-1_1anhl98s',
'/zfs/backup/data_D-GSINGH_I-686335423_TS-SYSAUX_FNO-2_1bnhl98s',
'/zfs/backup/data_D-GSINGH_I-686335423_TS-UNDOTBS1_FNO-3_1dnhl9ci',
'/zfs/backup/data_D-GSINGH_I-686335423_TS-USERS_FNO-4_1gnhl9f0',
'/zfs/backup/data_D-GSINGH_I-686335423_TS-EXAMPLE_FNO-5_1cnhl9ci',
'/zfs/backup/data_D-GSINGH_I-686335423_TS-UNDOTBS2_FNO-6_1enhl9dj'
CHARACTER SET AL32UTF8
;
Add tempfiles in the end after recovery if you want.
After teh controlfile is created using the above command just mount the database using command:
alter database mount;
After that start the recovery, in this particular cacse I moved the archive log files manually from other nodes nut we can incremental image backps as well and also the archive log backups on to the Oracle ZFS appliance and recover using them.
Recover database as follows:
16:21 : oracle : rac3.rac.meditate.com : @GSINGH : /u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 13 16:21:46 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until time '2012-08-10 17:00:00' using backup controlfile;
ORA-00279: change 2259222 generated at 08/07/2012 10:23:17 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_38_786540867.dbf
ORA-00280: change 2259222 for thread 2 is in sequence #38
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_38.300.790683799
ORA-00279: change 2259222 generated at 08/02/2012 16:47:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_41_786540867.dbf
ORA-00280: change 2259222 for thread 1 is in sequence #41
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_41.298.790683797
ORA-00279: change 2259224 generated at 08/07/2012 10:23:17 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_42_786540867.dbf
ORA-00280: change 2259224 for thread 1 is in sequence #42
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_41.298.790683797' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_42.301.790772513
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_38.300.790683799' no
longer needed for this recovery
ORA-00279: change 2260187 generated at 08/07/2012 10:23:54 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_39_786540867.dbf
ORA-00280: change 2260187 for thread 2 is in sequence #39
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_39.302.790772515
ORA-00279: change 2347764 generated at 08/08/2012 11:01:50 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_40_786540867.dbf
ORA-00280: change 2347764 for thread 2 is in sequence #40
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_39.302.790772515' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_39.302.790772515
ORA-00310: archived log contains sequence 39; sequence 40 required
ORA-00334: archived log:
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_39.302.790772515'
SQL> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_40.303.790772515
SP2-0734: unknown command beginning "/u01/app/o..." - rest of line ignored.
SQL> recover database until time '2012-08-10 17:00:00' using backup controlfile;
ORA-00279: change 2347764 generated at 08/08/2012 11:01:50 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_40_786540867.dbf
ORA-00280: change 2347764 for thread 2 is in sequence #40
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_40.303.790772515
ORA-00279: change 2347764 generated at 08/07/2012 10:23:17 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_42_786540867.dbf
ORA-00280: change 2347764 for thread 1 is in sequence #42
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_42.301.790772513
ORA-00279: change 2347765 generated at 08/08/2012 11:01:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_43_786540867.dbf
ORA-00280: change 2347765 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_42.301.790772513' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_43.305.790946237
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_40.303.790772515' no
longer needed for this recovery
ORA-00279: change 2348669 generated at 08/08/2012 11:02:38 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_41_786540867.dbf
ORA-00280: change 2348669 for thread 2 is in sequence #41
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_41.304.790946235
ORA-00279: change 2410687 generated at 08/10/2012 11:17:12 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_44_786540867.dbf
ORA-00280: change 2410687 for thread 1 is in sequence #44
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_43.305.790946237' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_1_seq_44.307.791199249
ORA-00279: change 2410688 generated at 08/10/2012 11:17:12 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_42_786540867.dbf
ORA-00280: change 2410688 for thread 2 is in sequence #42
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_41.304.790946235' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_42.306.790948027
ORA-00279: change 2425270 generated at 08/10/2012 11:47:05 needed for thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_43_786540867.dbf
ORA-00280: change 2425270 for thread 2 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_42.306.790948027' no
longer needed for this recovery
Specify log: {
filename
AUTO
CANCEL}
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/thread_2_seq_43.308.791199251
Log applied.
Media recovery complete.
SQL>
Once recovery is compelete, open the database with reset logs. I got teh following error, its becasue of a bug. Just do the fix given below and try to re-open the database and it will work.
SQL>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
SQL>
Now you just need to update the spfile with parameter:
_no_recovery_through_resetlogs=TRUE
Create pfile from spfile and update spfile and now create spfile from that pfile and open the database as :
SQL> create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileGSINGH.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initGSINGH.ora';
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2229464 bytes
Variable Size 146803496 bytes
Database Buffers 318767104 bytes
Redo Buffers 4030464 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
Here we go, we have a database on ZFS applicance.
SQL> SQL> select * from gv$instance;
INST_ID INSTANCE_NUMBER INSTANCE_NAME
---------- --------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 1 GSINGH
rac3.rac.meditate.com
11.2.0.3.0 13-AUG-12 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/zfs/backup/data_D-GSINGH_I-686335423_TS-SYSTEM_FNO-1_1anhl98s
/zfs/backup/data_D-GSINGH_I-686335423_TS-SYSAUX_FNO-2_1bnhl98s
/zfs/backup/data_D-GSINGH_I-686335423_TS-UNDOTBS1_FNO-3_1dnhl9ci
/zfs/backup/data_D-GSINGH_I-686335423_TS-USERS_FNO-4_1gnhl9f0
/zfs/backup/data_D-GSINGH_I-686335423_TS-EXAMPLE_FNO-5_1cnhl9ci
/zfs/backup/data_D-GSINGH_I-686335423_TS-UNDOTBS2_FNO-6_1enhl9dj
6 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
17:49 : oracle : rac3.rac.meditate.com : @GSINGH : /u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
84G 8.8G 71G 12% /
/dev/sda1 99M 13M 82M 14% /boot
tmpfs 754M 0 754M 0% /dev/shm
192.168.1.195:/export/share0_first_clone
9.7G 2.2G 7.5G 23% /zfs
17:49 : oracle : rac3.rac.meditate.com : @GSINGH : /u01/app/oracle/product/11.2.0/dbhome_1/dbs
$
So we have our database running on the clone of ZFS on new server.
As you wil keep working on the database the delta of clone will keep increasing and so will be the size of the clone. We just updated teh headers of teh databases and the sixe of teh clone increased to 156mb as below.
In the Company of the Holy, one meets the Incomprehensible Lord. In the Company of the Holy, one flourishes forever. In the Company of the Holy, the five passions are brought to rest. In the Company of the Holy, one enjoys the essence of ambrosia. In the Company of the Holy, one becomes the dust of all. In the Company of the Holy, one’s speech is enticing. In the Company of the Holy, the mind does not wander. In the Company of the Holy, the mind becomes stable. In the Company of the Holy, one is rid of Maya. In the Company of the Holy, O Nanak, God is totally pleased. || 2 ||
