Database instances on ASM will be automatically re-started from server reboot, if ASM and instances are registered in ORATAB (with ASM as the first entry). But, if Clusterware (CRS) is installed on the server, ORATAB will not re-start any database.
The correct way is to register databases, including single instance database, to the OCR and let the cluster server to restart the database automatically. To be registered to OCR, there is no requirement for a database to be clustered. This illustration is for a single instance database.
1. Run ocrdump to backup the OCR
2. Check the current list of services
pdb1s:/localhome/oracle$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.pdwas.db application OFFLINE OFFLINE
ora....s1.inst application OFFLINE OFFLINE
ora....s2.inst application OFFLINE OFFLINE
ora...._taf.cs application OFFLINE OFFLINE
ora....as1.srv application OFFLINE OFFLINE
ora....as2.srv application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE pdb1s
ora....1S.lsnr application ONLINE ONLINE pdb1s
ora....b1s.gsd application ONLINE ONLINE pdb1s
ora....b1s.ons application ONLINE ONLINE pdb1s
ora....b1s.vip application ONLINE ONLINE pdb1s
ora....SM2.asm application ONLINE ONLINE pdb2s
ora....2S.lsnr application ONLINE ONLINE pdb2s
ora....b2s.gsd application ONLINE ONLINE pdb2s
ora....b2s.ons application ONLINE ONLINE pdb2s
ora....b2s.vip application ONLINE ONLINE pdb2s
3. Use two commands to add single instance database PFPS, which is only on server PDB1S (and not on any RAC), to the OCR.
$ srvctl add database -d pfps -o $ORACLE_HOME -s open
$ srvctl add instance -d pfps -i pfps -n pdb1s
pdb1s:/localhome/oracle$srvctl add database -d pfps -o $ORACLE_HOME -s open
pdb1s:/localhome/oracle$srvctl add instance -d pfps -i pfps -n pdb1s
-- Check the change
pdb1s:/localhome/oracle$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.pdwas.db application OFFLINE OFFLINE
ora....s1.inst application OFFLINE OFFLINE
ora....s2.inst application OFFLINE OFFLINE
ora...._taf.cs application OFFLINE OFFLINE
ora....as1.srv application OFFLINE OFFLINE
ora....as2.srv application OFFLINE OFFLINE
ora.pfps.db application OFFLINE OFFLINE
ora....ps.inst application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE phdssdb1s
ora....1S.lsnr application ONLINE ONLINE phdssdb1s
ora....b1s.gsd application ONLINE ONLINE phdssdb1s
ora....b1s.ons application ONLINE ONLINE phdssdb1s
ora....b1s.vip application ONLINE ONLINE phdssdb1s
ora....SM2.asm application ONLINE ONLINE phdssdb2s
ora....2S.lsnr application ONLINE ONLINE phdssdb2s
ora....b2s.gsd application ONLINE ONLINE phdssdb2s
ora....b2s.ons application ONLINE ONLINE phdssdb2s
ora....b2s.vip application ONLINE ONLINE phdssdb2s
3. Use svrctl to stop / start the database and services
$ srvctl stop database -d pfps -o immediate
(used Sql*Plus to stop the db at the first time)
pdb1s:/localhome/oracle$ srvctl start database -d pfps
-- Check the effect
pdb1s:/localhome/oracle$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.pdwas.db application OFFLINE OFFLINE
ora....s1.inst application OFFLINE OFFLINE
ora....s2.inst application OFFLINE OFFLINE
ora...._taf.cs application OFFLINE OFFLINE
ora....as1.srv application OFFLINE OFFLINE
ora....as2.srv application OFFLINE OFFLINE
ora.pfps.db application ONLINE ONLINE pdb2s
ora....ps.inst application ONLINE ONLINE pdb1s
ora....SM1.asm application ONLINE ONLINE pdb1s
ora....1S.lsnr application ONLINE ONLINE pdb1s
ora....b1s.gsd application ONLINE ONLINE pdb1s
ora....b1s.ons application ONLINE ONLINE pdb1s
ora....b1s.vip application ONLINE ONLINE pdb1s
ora....SM2.asm application ONLINE ONLINE pdb2s
ora....2S.lsnr application ONLINE ONLINE pdb2s
ora....b2s.gsd application ONLINE ONLINE pdb2s
ora....b2s.ons application ONLINE ONLINE pdb2s
ora....b2s.vip application ONLINE ONLINE pdb2s
4. Other commands to check the details:
$ crs_stat -p
$ crs_stat | more
Friday, March 21, 2008
Backup OCR under Oracle Clusterware (CRS)
The OCR (Oracle Configuration Repository) is the registry of all resources in the cluster, and resources registered in the OCR are restarted automatically when the cluster restarts.
The system will take periodic automatic backups of the OCR which are stored in $ORA_CRS_HOME/cdata or $ORA_CRS_HOME/cdata/<Cluster name> on hard disk. These backups will taken every 4 hours, every day and every week. Only the master node takes backups of the OCR and any node can become the master node (depending on node evictions).
$ echo $ORA_CRS_HOME
$ echo $CRS_HOME
/u01/crs/oracle/product/crs
$ cd $CRS_HOME/cdata
pdb2p:/u01/crs/oracle/product/crs/cdata$ls
dssclus-p-01
localhost
pdb2p:/u01/crs/oracle/product/crs/cdata$ls -al dssclus-p-01
total 111008
drwxrwxr-x 2 oracle oinstall 256 Mar 21 11:22 .
drwxrwxr-x 4 oracle oinstall 256 Sep 13 2007 ..
-rw-r--r-- 1 root system 8118272 Mar 21 11:22 backup00.ocr
-rw-r--r-- 1 root system 8118272 Mar 21 07:22 backup01.ocr
-rw-r--r-- 1 root system 8118272 Mar 21 03:22 backup02.ocr
-rw-r--r-- 1 root system 8118272 Mar 19 23:22 day.ocr
-rw-r--r-- 1 root system 8118272 Mar 20 23:22 day_.ocr
-rw-r--r-- 1 root system 8118272 Mar 12 15:22 week.ocr
-rw-r--r-- 1 root system 8118272 Mar 19 15:22 week_.ocr
or
pdb2p:/localhome/oracle$ocrconfig -showbackup
pdb2p 2008/03/21 11:22:58 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/21 07:22:57 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/21 03:22:56 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/19 23:22:52 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/12 15:22:17 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
It is recommended by Oracle to take a manual backup of the OCR before trying to add a resource into the OCR. Here is how to manually back up OCR:
pdb2p:/localhome/oracle$cd $CRS_HOME
pdb2p:/u01/crs/oracle/product/crs$cd bin
pdb2p:/u01/crs/oracle/product/crs/bin$ls -al ocrdump
-rwxr-x--x 1 oracle oinstall 2267 Nov 23 10:47 ocrdump
pdb2p:/u01/crs/oracle/product/crs/bin$ocrdump
PROT-304: Failed to create dump file [OCRDUMPFILE]
pdb2p:/u01/crs/oracle/product/crs/bin$ocrdump ~/myfile
pdb2p:/u01/crs/oracle/product/crs/bin$ls -al ~/my*
-rw-r--r-- 1 oracle oinstall 52597 Mar 21 12:56 /localhome/oracle/myfile
Other ocrdump options:
$ ocrdump -stdout -keyname SYSTEM
writes the subtree of SYSTEM in the cluster registry to stdout
$ ocrdump -stdout -xml
writes cluster registry contents to stdout in xml format
$ ocrdump -stdout more
See the cluster registry contents
Restore OCR and others
1. Use ocrconfig to restore OCR from the backup file, for example:
$ ocrconfig -restore $CRS_HOME/cdata/backupfilename
See Note 371378.1 from more details.
2. You can use "ocrcheck" to see the physical location of OCR:
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048044
Used space (kbytes) : 6212
Available space (kbytes) : 1041832
ID : 1390610369
Device/File Name : /dev/ocr_sdisk1
Device/File integrity check succeeded
Device/File Name : /dev/ocr_sdisk2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
$ ls -al /dev/ocr*
crw-r----- 1 root oinstall 42, 15 Dec 20 2007 ocr_sdisk1
crw-r----- 1 root oinstall 42, 16 Dec 20 2007 ocr_sdisk2
OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file $CRS_Home/srvm/admin/ocrlog.ini
3. You can use "cluvfy comp ocr -n all" to verify OCR integrity.
The system will take periodic automatic backups of the OCR which are stored in $ORA_CRS_HOME/cdata or $ORA_CRS_HOME/cdata/<Cluster name> on hard disk. These backups will taken every 4 hours, every day and every week. Only the master node takes backups of the OCR and any node can become the master node (depending on node evictions).
$ echo $ORA_CRS_HOME
$ echo $CRS_HOME
/u01/crs/oracle/product/crs
$ cd $CRS_HOME/cdata
pdb2p:/u01/crs/oracle/product/crs/cdata$ls
dssclus-p-01
localhost
pdb2p:/u01/crs/oracle/product/crs/cdata$ls -al dssclus-p-01
total 111008
drwxrwxr-x 2 oracle oinstall 256 Mar 21 11:22 .
drwxrwxr-x 4 oracle oinstall 256 Sep 13 2007 ..
-rw-r--r-- 1 root system 8118272 Mar 21 11:22 backup00.ocr
-rw-r--r-- 1 root system 8118272 Mar 21 07:22 backup01.ocr
-rw-r--r-- 1 root system 8118272 Mar 21 03:22 backup02.ocr
-rw-r--r-- 1 root system 8118272 Mar 19 23:22 day.ocr
-rw-r--r-- 1 root system 8118272 Mar 20 23:22 day_.ocr
-rw-r--r-- 1 root system 8118272 Mar 12 15:22 week.ocr
-rw-r--r-- 1 root system 8118272 Mar 19 15:22 week_.ocr
or
pdb2p:/localhome/oracle$ocrconfig -showbackup
pdb2p 2008/03/21 11:22:58 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/21 07:22:57 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/21 03:22:56 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/19 23:22:52 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
pdb2p 2008/03/12 15:22:17 /u01/crs/oracle/product/crs/cdata/dssclus-p-01
It is recommended by Oracle to take a manual backup of the OCR before trying to add a resource into the OCR. Here is how to manually back up OCR:
pdb2p:/localhome/oracle$cd $CRS_HOME
pdb2p:/u01/crs/oracle/product/crs$cd bin
pdb2p:/u01/crs/oracle/product/crs/bin$ls -al ocrdump
-rwxr-x--x 1 oracle oinstall 2267 Nov 23 10:47 ocrdump
pdb2p:/u01/crs/oracle/product/crs/bin$ocrdump
PROT-304: Failed to create dump file [OCRDUMPFILE]
pdb2p:/u01/crs/oracle/product/crs/bin$ocrdump ~/myfile
pdb2p:/u01/crs/oracle/product/crs/bin$ls -al ~/my*
-rw-r--r-- 1 oracle oinstall 52597 Mar 21 12:56 /localhome/oracle/myfile
Other ocrdump options:
$ ocrdump -stdout -keyname SYSTEM
writes the subtree of SYSTEM in the cluster registry to stdout
$ ocrdump -stdout -xml
writes cluster registry contents to stdout in xml format
$ ocrdump -stdout more
See the cluster registry contents
Restore OCR and others
1. Use ocrconfig to restore OCR from the backup file, for example:
$ ocrconfig -restore $CRS_HOME/cdata/backupfilename
See Note 371378.1 from more details.
2. You can use "ocrcheck" to see the physical location of OCR:
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048044
Used space (kbytes) : 6212
Available space (kbytes) : 1041832
ID : 1390610369
Device/File Name : /dev/ocr_sdisk1
Device/File integrity check succeeded
Device/File Name : /dev/ocr_sdisk2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
$ ls -al /dev/ocr*
crw-r----- 1 root oinstall 42, 15 Dec 20 2007 ocr_sdisk1
crw-r----- 1 root oinstall 42, 16 Dec 20 2007 ocr_sdisk2
OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file $CRS_Home/srvm/admin/ocrlog.ini
3. You can use "cluvfy comp ocr -n all" to verify OCR integrity.
Thursday, March 13, 2008
Change DBID
After you clone a database, it takes the same DBID from the source database. If you use same RMAN catalog to backup those two databases, it will not work because DBID has to be unique in one RMAN catalog.
Oracle provides the NID utility for changing DBNAME and DBID of RAC Database (9.0.1.4 to 10.2.0.4). See Metalink Note 464922.1 (2007) for detailed steps.
In 8i, there was a package for changing DBID. I post my old note here (and the Metalink Note number is not valid any more).
-- Change database's ID (Note: 174625.1)
-- 8/26/02
SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 561977372 bytes
Fixed Size 75804 bytes
Variable Size 293294080 bytes
Database Buffers 268435456 bytes
Redo Buffers 172032 bytes
Database mounted.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR> select dbid, name from v$database;
DBID NAME
---------- ---------
463045413 OWXE2
1 row selected.
SVRMGR> execute dbms_backup_restore.zeroDbid(0);
Statement processed.
Note: the database MUST not be open.
Otherwise, the database is damaged by running this package.
SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup nomount;
ORACLE instance started.
Total System Global Area 561977372 bytes
Fixed Size 75804 bytes
Variable Size 293294080 bytes
Database Buffers 268435456 bytes
Redo Buffers 172032 bytes
SVRMGR> @f:\myfiles\rebuild_owxe2_control.sql
Statement processed.
CREATE CONTROLFILE SET DATABASE "OWXE2" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 29041
LOGFILE
GROUP 1 'D:\OWXE2\ORADATA\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\OWXE2\ORADATA\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\OWXE2\ORADATA\REDO03.LOG' SIZE 100M
DATAFILE
'D:\OWXE2\ORADATA\SYSTEM01.DBF',
'D:\OWXE2\ORADATA\RBS01.DBF',
......
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR> select dbid, name from v$database;
DBID NAME
---------- ---------
1624780754 OWXE2
1 row selected.
Oracle provides the NID utility for changing DBNAME and DBID of RAC Database (9.0.1.4 to 10.2.0.4). See Metalink Note 464922.1 (2007) for detailed steps.
In 8i, there was a package for changing DBID. I post my old note here (and the Metalink Note number is not valid any more).
-- Change database's ID (Note: 174625.1)
-- 8/26/02
SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 561977372 bytes
Fixed Size 75804 bytes
Variable Size 293294080 bytes
Database Buffers 268435456 bytes
Redo Buffers 172032 bytes
Database mounted.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR> select dbid, name from v$database;
DBID NAME
---------- ---------
463045413 OWXE2
1 row selected.
SVRMGR> execute dbms_backup_restore.zeroDbid(0);
Statement processed.
Note: the database MUST not be open.
Otherwise, the database is damaged by running this package.
SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup nomount;
ORACLE instance started.
Total System Global Area 561977372 bytes
Fixed Size 75804 bytes
Variable Size 293294080 bytes
Database Buffers 268435456 bytes
Redo Buffers 172032 bytes
SVRMGR> @f:\myfiles\rebuild_owxe2_control.sql
Statement processed.
CREATE CONTROLFILE SET DATABASE "OWXE2" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 29041
LOGFILE
GROUP 1 'D:\OWXE2\ORADATA\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\OWXE2\ORADATA\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\OWXE2\ORADATA\REDO03.LOG' SIZE 100M
DATAFILE
'D:\OWXE2\ORADATA\SYSTEM01.DBF',
'D:\OWXE2\ORADATA\RBS01.DBF',
......
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR> select dbid, name from v$database;
DBID NAME
---------- ---------
1624780754 OWXE2
1 row selected.
Tuesday, March 11, 2008
Redo logs in RAC
In Rac database, each of instances has it's own thread number (specified in parameter file):
DBNAME_INSTNAME.thread=1
DBNAME_INSTNAME.thread=2
There is a column THREAD# in v$log that tells which Log Group is used by which instance:
SQL> select group#, thread#, bytes, members, status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ---------
1 1 52428800 2 CURRENT
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT
You can use below commands to add log groups to each instance when the database on the RAC is in "opan":
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 6 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 8 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> select group#, thread#, bytes, members, status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 52428800 2 INACTIVE
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT
5 1 52428800 2 INACTIVE
6 1 52428800 2 CURRENT
7 2 52428800 2 INACTIVE
8 2 52428800 2 INACTIVE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+PDATA/pdb/onlinelog/group_1.383.636108915
+PFRA/pdb/onlinelog/group_1.273.636108915
+PDATA/pdb/onlinelog/group_2.396.636108917
+PFRA/pdb/onlinelog/group_2.274.636108917
+PDATA/pdb/onlinelog/group_3.431.636110325
+PFRA/pdb/onlinelog/group_3.275.636110325
+PDATA/pdb/onlinelog/group_4.422.636110325
+PFRA/pdb/onlinelog/group_4.276.636110325
+PDATA/pdb/onlinelog/group_5.567.649501249
+PFRA/pdb/onlinelog/group_5.2498.649501249
+PDATA/pdb/onlinelog/group_6.626.649501251
+PFRA/pdb/onlinelog/group_6.9012.649501251
+PDATA/pdb/onlinelog/group_7.489.649501427
+PFRA/pdb/onlinelog/group_7.14183.649501427
+PDATA/pdb/onlinelog/group_8.568.649501447
+PFRA/pdb/onlinelog/group_8.13797.649501447
Other views relate to redo logs and archive logs:
V$ARCHIVED_LOG - Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST - Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES - Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG - Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG_HISTORY - Contains log history information such as which logs have been archived and the SCN range for each archived log.
V$THREAD - Displays thread information.
LOG_ARCHIVE_TRACE initialization parameter to specify a trace level:
0 Disable archivelog tracing. This is the default.
1 Track archival of redo log file.
2 Track archival status for each archivelog destination.
Note:
1. If you restore RMAN backups of RAC database to a single instance on another node, you may need to rename the log files before opening the database in case the file path is different. And you may remove the redolog groups for redo threads of other instances. See Metalink Note 415579.1.
2. After I added more log groups to a RAC database, the below message in alert log went away:
Tue Feb 19 01:02:09 2008
Thread 2 advanced to log sequence 30618
Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:02:32 2008
Thread 2 cannot allocate new log, sequence 30619
Checkpoint not complete
Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Thread 2 advanced to log sequence 30619
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 cannot allocate new log, sequence 30620
Checkpoint not complete
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 advanced to log sequence 30620
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:03:06 2008
Thread 2 cannot allocate new log, sequence 30621
Checkpoint not complete
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
DBNAME_INSTNAME.thread=1
DBNAME_INSTNAME.thread=2
There is a column THREAD# in v$log that tells which Log Group is used by which instance:
SQL> select group#, thread#, bytes, members, status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ---------
1 1 52428800 2 CURRENT
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT
You can use below commands to add log groups to each instance when the database on the RAC is in "opan":
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 6 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 8 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> select group#, thread#, bytes, members, status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 52428800 2 INACTIVE
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT
5 1 52428800 2 INACTIVE
6 1 52428800 2 CURRENT
7 2 52428800 2 INACTIVE
8 2 52428800 2 INACTIVE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+PDATA/pdb/onlinelog/group_1.383.636108915
+PFRA/pdb/onlinelog/group_1.273.636108915
+PDATA/pdb/onlinelog/group_2.396.636108917
+PFRA/pdb/onlinelog/group_2.274.636108917
+PDATA/pdb/onlinelog/group_3.431.636110325
+PFRA/pdb/onlinelog/group_3.275.636110325
+PDATA/pdb/onlinelog/group_4.422.636110325
+PFRA/pdb/onlinelog/group_4.276.636110325
+PDATA/pdb/onlinelog/group_5.567.649501249
+PFRA/pdb/onlinelog/group_5.2498.649501249
+PDATA/pdb/onlinelog/group_6.626.649501251
+PFRA/pdb/onlinelog/group_6.9012.649501251
+PDATA/pdb/onlinelog/group_7.489.649501427
+PFRA/pdb/onlinelog/group_7.14183.649501427
+PDATA/pdb/onlinelog/group_8.568.649501447
+PFRA/pdb/onlinelog/group_8.13797.649501447
Other views relate to redo logs and archive logs:
V$ARCHIVED_LOG - Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST - Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES - Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG - Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG_HISTORY - Contains log history information such as which logs have been archived and the SCN range for each archived log.
V$THREAD - Displays thread information.
LOG_ARCHIVE_TRACE initialization parameter to specify a trace level:
0 Disable archivelog tracing. This is the default.
1 Track archival of redo log file.
2 Track archival status for each archivelog destination.
Note:
1. If you restore RMAN backups of RAC database to a single instance on another node, you may need to rename the log files before opening the database in case the file path is different. And you may remove the redolog groups for redo threads of other instances. See Metalink Note 415579.1.
2. After I added more log groups to a RAC database, the below message in alert log went away:
Tue Feb 19 01:02:09 2008
Thread 2 advanced to log sequence 30618
Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:02:32 2008
Thread 2 cannot allocate new log, sequence 30619
Checkpoint not complete
Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Thread 2 advanced to log sequence 30619
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 cannot allocate new log, sequence 30620
Checkpoint not complete
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 advanced to log sequence 30620
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:03:06 2008
Thread 2 cannot allocate new log, sequence 30621
Checkpoint not complete
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Monday, February 25, 2008
Trick on Restoring Cold Backup from RMAN to ASM
User made mistakes by deleteing objects owned by SYS on 2/20/08 afternoon, so I tried to restore the database from RMAN cold backup of 2/19/08 22:03.
After the restore, I got "ORA-01113: file 1 needs media recovery" error when using "alter database open;" to open database. Then, I ran followings to open the database:
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
The above actions uses redo logs to recover the database. After the database was up, we found the mistakes that users made on 2/20/08 were still in the database. Even the alert log says all data files were restored I was not sure if the redo logs brought the mistakes in, or if the RMAN restore failed or not because there are no timestamps on files in ASM and there are no additional files created in ASM by the restore job.
I had to restore the database again using a backup on an earlier date, and used "recover database until cancel using backup controlfile;" to recover the database after reading Metalink Note 272321.1. Below are what I did to restore the database successfully in 2nd run.
-- Find the backup
RMAN> list backup of database completed between 'sysdate-9' and 'sysdate-5';
-- Preview
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131 preview;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239936 Incr 0 39.01G SBT_TAPE 00:31:57 16-FEB-08
BP Key: 2239943 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cpj8t1uq_1_1_646875098 Media: PH0313
List of Datafiles in backup set 2239936
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/system.270.630660291
5 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_data_1
7 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dev_user_1
13 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_3
14 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239937 Incr 0 44.25G SBT_TAPE 00:33:54 16-FEB-08
BP Key: 2239944 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cnj8t1uq_1_1_646875098 Media: X228L2
List of Datafiles in backup set 2239937
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_idx_1
10 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/law_data_1
11 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_1
16 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_3
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239938 Incr 0 43.63G SBT_TAPE 00:35:33 16-FEB-08
BP Key: 2239945 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_coj8t1uq_1_1_646875098 Media: EX0213
List of Datafiles in backup set 2239938
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/sysaux.272.630660299
9 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_idx_1
12 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_2
15 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_2
18 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/symantec_i3_orcl.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239939 Incr 0 45.18G SBT_TAPE 00:36:11 16-FEB-08
BP Key: 2239946 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cmj8t1uq_1_1_646875098 Media: X441L2
List of Datafiles in backup set 2239939
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/undotbs1.271.630660297
4 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/users.274.630660303
8 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_data_1
17 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_4
19 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_4
archive logs generated after SCN 32529107158 not found in repository
Media recovery start SCN is 32529107158
Recovery must be done beyond SCN 32529107158 to clear data files fuzziness
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
-- Restore
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dbrep/datafile/system.270.630660291
restoring datafile 00005 to +DATA/dbrep/datafile/cis_data_1
restoring datafile 00007 to +DATA/dbrep/datafile/dev_user_1
restoring datafile 00013 to +DATA/dbrep/datafile/dss_data_3
restoring datafile 00014 to +DATA/dbrep/datafile/dss_idx_1
channel ch00: reading from backup piece bk_cpj8t1uq_1_1_646875098
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +DATA/dbrep/datafile/cis_idx_1
restoring datafile 00010 to +DATA/dbrep/datafile/law_data_1
restoring datafile 00011 to +DATA/dbrep/datafile/dss_data_1
restoring datafile 00016 to +DATA/dbrep/datafile/dss_idx_3
channel ch01: reading from backup piece bk_cnj8t1uq_1_1_646875098
channel ch02: starting datafile backupset restore
channel ch02: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +DATA/dbrep/datafile/sysaux.272.630660299
restoring datafile 00009 to +DATA/dbrep/datafile/pdw_idx_1
restoring datafile 00012 to +DATA/dbrep/datafile/dss_data_2
restoring datafile 00015 to +DATA/dbrep/datafile/dss_idx_2
restoring datafile 00018 to +DATA/dbrep/datafile/symantec_i3_orcl.dbf
channel ch02: reading from backup piece bk_coj8t1uq_1_1_646875098
channel ch03: starting datafile backupset restore
channel ch03: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/dbrep/datafile/undotbs1.271.630660297
restoring datafile 00004 to +DATA/dbrep/datafile/users.274.630660303
restoring datafile 00008 to +DATA/dbrep/datafile/pdw_data_1
restoring datafile 00017 to +DATA/dbrep/datafile/dss_data_4
restoring datafile 00019 to +DATA/dbrep/datafile/dss_idx_4
channel ch03: reading from backup piece bk_cmj8t1uq_1_1_646875098
channel ch00: restored backup piece 1
piece handle=bk_cpj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch00: restore complete, elapsed time: 00:21:57
channel ch01: restored backup piece 1
piece handle=bk_cnj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch01: restore complete, elapsed time: 00:26:32
channel ch02: restored backup piece 1
piece handle=bk_coj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch02: restore complete, elapsed time: 00:31:57
channel ch03: restored backup piece 1
piece handle=bk_cmj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch03: restore complete, elapsed time: 00:32:22
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
RMAN>
-- Bring the database up in SQL*Plus
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DATA/dbrep/datafile/system.270.630660291'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32529107158 generated at 02/16/2008 23:30:33 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_490_630660282.dbf
ORA-00280: change 32529107158 for thread 1 is in sequence #490
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
After the restore, I got "ORA-01113: file 1 needs media recovery" error when using "alter database open;" to open database. Then, I ran followings to open the database:
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
The above actions uses redo logs to recover the database. After the database was up, we found the mistakes that users made on 2/20/08 were still in the database. Even the alert log says all data files were restored I was not sure if the redo logs brought the mistakes in, or if the RMAN restore failed or not because there are no timestamps on files in ASM and there are no additional files created in ASM by the restore job.
I had to restore the database again using a backup on an earlier date, and used "recover database until cancel using backup controlfile;" to recover the database after reading Metalink Note 272321.1. Below are what I did to restore the database successfully in 2nd run.
-- Find the backup
RMAN> list backup of database completed between 'sysdate-9' and 'sysdate-5';
-- Preview
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131 preview;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239936 Incr 0 39.01G SBT_TAPE 00:31:57 16-FEB-08
BP Key: 2239943 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cpj8t1uq_1_1_646875098 Media: PH0313
List of Datafiles in backup set 2239936
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/system.270.630660291
5 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_data_1
7 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dev_user_1
13 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_3
14 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239937 Incr 0 44.25G SBT_TAPE 00:33:54 16-FEB-08
BP Key: 2239944 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cnj8t1uq_1_1_646875098 Media: X228L2
List of Datafiles in backup set 2239937
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/cis_idx_1
10 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/law_data_1
11 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_1
16 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_3
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239938 Incr 0 43.63G SBT_TAPE 00:35:33 16-FEB-08
BP Key: 2239945 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_coj8t1uq_1_1_646875098 Media: EX0213
List of Datafiles in backup set 2239938
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/sysaux.272.630660299
9 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_idx_1
12 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_2
15 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_2
18 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/symantec_i3_orcl.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2239939 Incr 0 45.18G SBT_TAPE 00:36:11 16-FEB-08
BP Key: 2239946 Status: AVAILABLE Compressed: NO Tag: TAG20080216T233131
Handle: bk_cmj8t1uq_1_1_646875098 Media: X441L2
List of Datafiles in backup set 2239939
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/undotbs1.271.630660297
4 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/users.274.630660303
8 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/pdw_data_1
17 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_data_4
19 0 Incr 32529107158 16-FEB-08 +DATA/dbrep/datafile/dss_idx_4
archive logs generated after SCN 32529107158 not found in repository
Media recovery start SCN is 32529107158
Recovery must be done beyond SCN 32529107158 to clear data files fuzziness
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
-- Restore
RMAN> run {
2> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
3> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
5> ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
6> restore database from tag=TAG20080216T233131;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
allocated channel: ch00
channel ch00: sid=317 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch01
channel ch01: sid=316 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch02
channel ch02: sid=315 devtype=SBT_TAPE
channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
allocated channel: ch03
channel ch03: sid=314 devtype=SBT_TAPE
channel ch03: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
Starting restore at 25-FEB-08
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/dbrep/datafile/system.270.630660291
restoring datafile 00005 to +DATA/dbrep/datafile/cis_data_1
restoring datafile 00007 to +DATA/dbrep/datafile/dev_user_1
restoring datafile 00013 to +DATA/dbrep/datafile/dss_data_3
restoring datafile 00014 to +DATA/dbrep/datafile/dss_idx_1
channel ch00: reading from backup piece bk_cpj8t1uq_1_1_646875098
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +DATA/dbrep/datafile/cis_idx_1
restoring datafile 00010 to +DATA/dbrep/datafile/law_data_1
restoring datafile 00011 to +DATA/dbrep/datafile/dss_data_1
restoring datafile 00016 to +DATA/dbrep/datafile/dss_idx_3
channel ch01: reading from backup piece bk_cnj8t1uq_1_1_646875098
channel ch02: starting datafile backupset restore
channel ch02: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +DATA/dbrep/datafile/sysaux.272.630660299
restoring datafile 00009 to +DATA/dbrep/datafile/pdw_idx_1
restoring datafile 00012 to +DATA/dbrep/datafile/dss_data_2
restoring datafile 00015 to +DATA/dbrep/datafile/dss_idx_2
restoring datafile 00018 to +DATA/dbrep/datafile/symantec_i3_orcl.dbf
channel ch02: reading from backup piece bk_coj8t1uq_1_1_646875098
channel ch03: starting datafile backupset restore
channel ch03: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/dbrep/datafile/undotbs1.271.630660297
restoring datafile 00004 to +DATA/dbrep/datafile/users.274.630660303
restoring datafile 00008 to +DATA/dbrep/datafile/pdw_data_1
restoring datafile 00017 to +DATA/dbrep/datafile/dss_data_4
restoring datafile 00019 to +DATA/dbrep/datafile/dss_idx_4
channel ch03: reading from backup piece bk_cmj8t1uq_1_1_646875098
channel ch00: restored backup piece 1
piece handle=bk_cpj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch00: restore complete, elapsed time: 00:21:57
channel ch01: restored backup piece 1
piece handle=bk_cnj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch01: restore complete, elapsed time: 00:26:32
channel ch02: restored backup piece 1
piece handle=bk_coj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch02: restore complete, elapsed time: 00:31:57
channel ch03: restored backup piece 1
piece handle=bk_cmj8t1uq_1_1_646875098 tag=TAG20080216T233131
channel ch03: restore complete, elapsed time: 00:32:22
Finished restore at 25-FEB-08
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
RMAN>
-- Bring the database up in SQL*Plus
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DATA/dbrep/datafile/system.270.630660291'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32529107158 generated at 02/16/2008 23:30:33 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_490_630660282.dbf
ORA-00280: change 32529107158 for thread 1 is in sequence #490
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
Subscribe to:
Comments (Atom)