I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Tuesday, April 15, 2008

can i use the retention policy without using the Flash Recovery area?

can i use the retention policy without using the Flash Recovery area? yes Lets see here..
RMAN>list backup summary

C:\>dir *.bus
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

File Not Found

C:\>dir *.arc
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

File Not Found


RMAN> crosscheck backup
2> ;

using channel ORA_DISK_1

RMAN> list backup summary
2> ;


RMAN> show retention policy
2> ;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> run {
2>   allocate channel ch0 type disk;
3>   backup as compressed backupset database format
4>   'c:\rman_%s_%d_%T.bus'
5>   plus archivelog format 'c:\arch_%s_%d_%T.arc';
6>   release channel ch0;
7>   allocate channel ch1 device type disk format 'c:\arch_%s_%d_%T.arc';
8>   release channel ch1;
9>   delete force noprompt obsolete;
10>   allocate channel ch2 device type disk format 'c:\rman_%s_%d_%T.bus';
11>   release channel ch2;
12>   delete force noprompt obsolete;
13> }

released channel: ORA_DISK_1
allocated channel: ch0
channel ch0: sid=146 devtype=DISK


Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=24 recid=79 stamp=652108461
input archive log thread=1 sequence=25 recid=80 stamp=652112650
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_130_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:05
Finished backup at 15-APR-08

Starting backup at 15-APR-08
channel ch0: starting compressed full datafile backupset
channel ch0: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DB
F
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DB
F
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF

input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.D
BF
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\RMAN_131_ORCL1_20080415.BUS comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:45
Finished backup at 15-APR-08

Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=81 stamp=652112703
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_132_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting Control File and SPFILE Autobackup at 15-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_
04_15\O1_MF_S_652112718_408X3ZVJ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-08

released channel: ch0

allocated channel: ch1
channel ch1: sid=146 devtype=DISK

released channel: ch1

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           128    15-APR-08
  Backup Piece       128    15-APR-08          C:\ARCH_130_ORCL1_20080415.ARC
Archive Log          79     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_24_408RYXDF_.ARC
Archive Log          80     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_25_408X1SCR_.ARC
deleted backup piece
backup piece handle=C:\ARCH_130_ORCL1_20080415.ARC recid=128 stamp=652112652
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVEL
OG\2008_04_15\O1_MF_1_24_408RYXDF_.ARC recid=79 stamp=652108461
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVEL
OG\2008_04_15\O1_MF_1_25_408X1SCR_.ARC recid=80 stamp=652112650
Deleted 3 objects


released channel: ORA_DISK_1
allocated channel: ch2
channel ch2: sid=146 devtype=DISK

released channel: ch2

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
no obsolete backups found

RMAN>

C:\>dir *.bus
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

04/15/2008  02:24 PM        96,813,056 RMAN_131_ORCL1_20080415.BUS
               1 File(s)     96,813,056 bytes
               0 Dir(s)  61,960,724,480 bytes free

C:\>dir *.arc
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

04/15/2008  02:25 PM            56,832 ARCH_132_ORCL1_20080415.ARC
               1 File(s)         56,832 bytes
               0 Dir(s)  61,960,724,480 bytes free

C:\>


RMAN> run {
2>   allocate channel ch0 type disk;
3>   backup as compressed backupset database format
4>   'c:\rman_%s_%d_%T.bus'
5>   plus archivelog format 'c:\arch_%s_%d_%T.arc';
6>   release channel ch0;
7>  }

released channel: ORA_DISK_1
allocated channel: ch0
channel ch0: sid=146 devtype=DISK


Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=81 stamp=652112703
input archive log thread=1 sequence=27 recid=82 stamp=652112873
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_134_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting backup at 15-APR-08
channel ch0: starting compressed full datafile backupset
channel ch0: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DB
F
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DB
F
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF

input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.D
BF
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\RMAN_135_ORCL1_20080415.BUS comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:45
Finished backup at 15-APR-08

Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=28 recid=83 stamp=652112925
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_136_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting Control File and SPFILE Autobackup at 15-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_
04_15\O1_MF_S_652112928_408XBKCZ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-08

released channel: ch0

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           129    15-APR-08
  Backup Piece       129    15-APR-08          C:\RMAN_131_ORCL1_20080415.BUS
Backup Set           130    15-APR-08
  Backup Piece       130    15-APR-08          C:\ARCH_132_ORCL1_20080415.ARC
Backup Set           131    15-APR-08
  Backup Piece       131    15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\AUTOBACKUP\2008_04_15\O1_MF_S_652112718_408X3ZVJ_.BKP
Backup Set           132    15-APR-08
  Backup Piece       132    15-APR-08          C:\ARCH_134_ORCL1_20080415.ARC
Archive Log          81     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_26_408X3HRP_.ARC
Archive Log          82     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_27_408X8RXN_.ARC

RMAN>


i have archivelog which is at FRA ,it also apply there and as well yours own defined backupsets path.


Wednesday, April 9, 2008

disaster recovery

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 9 16:23:47 2008

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


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

SQL> select name,dbid from v$database
  2  /

NAME            DBID
--------- ----------
ORCL      1179381888

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

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     3
Next log sequence to archive   5
Current log sequence           5
SQL> conn scott/tiger
Connected.
SQL> create table t as select * from all_objects
  2  /

Table created.

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL (DBID=1179381888)

RMAN> configure controlfile autobackup on
2> ;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> run
2> {
3> allocate channel t1 type disk;
4> backup database;
5> release channel t1;
6> allocate channel t2 type disk;
7> backup archivelog all;
8> release channel t2;
9> }

using target database controlfile instead of recovery catalog
allocated channel: t1
channel t1: sid=136 devtype=DISK
.
.
.

RMAN>

-take alls FRA backupsets to somewhere else safe i.e tape cartridge.
-Delete alls database files including spfile,pfile and password file
-Delete also cdump,udump,bdump,create.

Now you have been in disaster scenario.Now what you have to do is install the same version of oralce with same
directory structure and instance.

You may create the old instance by OS laterz after installing the oracle.


C:\>oradim -new -sid orcl
Instance created.

after instance creation ,create password file by using orapwd utility

C:\>orapwd file=C:\oracle\product\10.1.0\Db_2\database\PWDorcl.ora password=sys

put alls backup files from FRA which is in tape cartridge to its respected location.

Connect to RMAN

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN>

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_2\DATABASE
\INITORCL.ORA'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area     113246208 bytes

Fixed Size                      787708 bytes
Variable Size                 61602564 bytes
Database Buffers              50331648 bytes
Redo Buffers                    524288 bytes

RMAN>
RMAN> restore spfile from 'C:\oracle\product\10.1.0\flash_recovery_area\ORCL\AUT
OBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP'
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=29 devtype=DISK

channel ORA_DISK_1: autobackup found: C:\oracle\product\10.1.0\flash_recovery_ar
ea\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-APR-08

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup

connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:13:56
RMAN-04014: startup failed: ORA-02778: Name given for the log directory is invalid

RMAN> ^C


create bdump,udump,cdump and create folder at C:\oracle\product\10.1.0\admin\orcl

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:17:41
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> restore controlfile from autobackup
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AR
EA\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605800_3ZSG2SLS_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 09-APR-08

RMAN> startup mount

database is already started
database mounted

RMAN> restore database
2> ;

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 09-APR-08
Starting implicit crosscheck backup at 09-APR-08
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-APR-08

RMAN> recover database
2> ;

Starting recover at 09-APR-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file C:\ORACLE\PRODUCT\10.
1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC thread=1 sequence=8
unable to find archive log
archive log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/09/2008 18:31:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 332376

RMAN> alter database open resetlogs
2> ;

database opened

RMAN> exit


Recovery Manager complete.

C:\>

Followers

About Me

My photo
Melbourne, Victoria, Australia