------------------------------------------------RMAN.bat--------------------
@ECHO off
SETLOCAL
IF [%1]==[] goto s_start
ECHO GETDATE.cmd
ECHO Returns the date independent of regional settings
ECHO Creates the environment variables %v_year% %v_month% %v_day%
ECHO.
ECHO SYNTAX
ECHO GETDATE
ECHO.
ECHO.
GOTO :eof
:s_start
FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)
SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~6,4%
rem ECHO Today is Year: [%yy%] Month: [%mm%] Day: [%dd%]
ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
ren C:\00\1.txt USED66665_%v_month%%v_day%%v_year%.txt
set oracle_sid=sns6
RMAN TARGET sys/oracle@sns1011srv cmdfile='c:\rman.sql'
if exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO COMPLETED
if not exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO FAILED
:COMPLETED
Cscript.exe C:\Email_rman.vbs
exit
:FAILED
Cscript.exe C:\Emailfail_rman.vbs
-----------------------------rman.sql----------------------------
show all;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\archive0910\sns1011\rmanbackup_db_%d_%D%M%Y';
exit;
---------------------------------Email_rman.vbs-----------------------------------
Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "LD Oracle RMAN Job Completed Successfully"
objMessage.From = "rjain@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Backup Job Completed Successfully on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
----------------------------------Emaildail_rman.vbs--------------------------------
Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "LD Oracle RMAN Job Failed ORANOP02P"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Job Failed on ORANOP02P !" & strComputer
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
------------------------------------------
Showing posts with label rman. Show all posts
Showing posts with label rman. Show all posts
Thursday, May 20, 2010
Tuesday, April 13, 2010
RMAN Block Corruption Detection & Recovery
Block Media Recovery with RMAN
sql>select file# block# from v$database_block_corruption; --corrupted block
Rman>> block recover datafile block ; ------recover corrupted block
Rman>blockrecover corruption list;
RMAN> backup validate check logical database;
sql>select file# block# from v$database_block_corruption; --corrupted block
Rman>> block recover datafile
Rman>blockrecover corruption list;
RMAN> backup validate check logical database;
RMAN recover & open database if archive log missing
RMAN recover and open the database if the archive log required for recovery is missing
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
RMAN recovery from loss of all online redo log files
RMAN recovery from loss of all online redo log files
SQL> select thread#,sequence#,status from v$Log;
set oracle_sid=sns6
rman Target SYS/linux@sns1011srv
RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 2021 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
SQL> select thread#,sequence#,status from v$Log;
set oracle_sid=sns6
rman Target SYS/linux@sns1011srv
RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 2021 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Monday, April 12, 2010
RECOVER THE DATABASE IN DIFFERENT SERVER ON DIFFERENT DIRECTORY FROM ACTUAL BACKUP LOCATION DIRECTORY
SCENIRIO IS I HAVE TAKEN RMAN BACKUP ON A SERVER IN D:\ARCHIVE0910\SNS1011\ LOCATION AND COPY THE BACKUP SET TO B SERVER IN DIFF. DIRECTORY E:\archive1011\sns1011\ LOCATION AND WANT TO RECOVER THE DATABASE ON B SERVER BUT IT IS GIVING ERROR "ORA-19505 failed to identify file "
RMAN> restore database;
Starting restore at 09-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\SNSD1011\SYSTEM01.ORA
restoring datafile 00002 to E:\SNSD1011\UNDOTBS01.ORA
restoring datafile 00003 to E:\SNSD1011\SYSAUX01.ORA
restoring datafile 00004 to E:\SNSD1011\INDX01.ORA
restoring datafile 00005 to E:\SNSD1011\USERS01.ORA
channel ORA_DISK_1: reading from backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_
1
ORA-19870: error reading backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1
ORA-19505: failed to identify file "D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
failover to previous backup
creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2010 11:33:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'
Steps: check directory path in init.ora and control.ora but every path is fine as per new server
solution:
if recovery catalog exist then
crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archivelog all;
if not recovery catalog
delete force obsolete;
delete expired archivelog all;
change archivelog all crosscheck;
crosscheck backup of database;
delete expired backup;
----NOW TRY TO RESTORE DATABASE BUT FOLLOWING ERROR----
RMAN> restore database;
Starting restore at 10-APR-10
using channel ORA_DISK_1
creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/10/2010 13:12:43
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'
===========================FINAL SOLUTION========
RMAN> list backup of database;
-----NOW DELETE ALL EXPIRED BACKUP
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;
----NOT NECCESSARY----
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> delete force obsolete;
RMAN> delete expired archivelog all;
----------------------
RMAN> list backup of database;
SHOWING NO BACKUP
RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 4.72G DISK 00:20:28 10-APR-10
BP Key: 30 Status: AVAILABLE Compressed: YES Tag: TAG20100410T115004
Piece Name: E:\ARCHIVE1011\SNS1011\0ULAP4RC_1_1
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 385085943 10-APR-10 E:\SNSD1011\SYSTEM01.ORA
2 Full 385085943 10-APR-10 E:\SNSD1011\UNDOTBS01.ORA
3 Full 385085943 10-APR-10 E:\SNSD1011\SYSAUX01.ORA
4 Full 385085943 10-APR-10 E:\SNSD1011\INDX01.ORA
5 Full 385085943 10-APR-10 E:\SNSD1011\USERS01.ORA
RMAN> restore database;
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> restore database;
Starting restore at 09-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\SNSD1011\SYSTEM01.ORA
restoring datafile 00002 to E:\SNSD1011\UNDOTBS01.ORA
restoring datafile 00003 to E:\SNSD1011\SYSAUX01.ORA
restoring datafile 00004 to E:\SNSD1011\INDX01.ORA
restoring datafile 00005 to E:\SNSD1011\USERS01.ORA
channel ORA_DISK_1: reading from backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_
1
ORA-19870: error reading backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1
ORA-19505: failed to identify file "D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
failover to previous backup
creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2010 11:33:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'
Steps: check directory path in init.ora and control.ora but every path is fine as per new server
solution:
if recovery catalog exist then
crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archivelog all;
if not recovery catalog
delete force obsolete;
delete expired archivelog all;
change archivelog all crosscheck;
crosscheck backup of database;
delete expired backup;
----NOW TRY TO RESTORE DATABASE BUT FOLLOWING ERROR----
RMAN> restore database;
Starting restore at 10-APR-10
using channel ORA_DISK_1
creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/10/2010 13:12:43
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'
===========================FINAL SOLUTION========
RMAN> list backup of database;
-----NOW DELETE ALL EXPIRED BACKUP
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;
----NOT NECCESSARY----
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> delete force obsolete;
RMAN> delete expired archivelog all;
----------------------
RMAN> list backup of database;
SHOWING NO BACKUP
RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 4.72G DISK 00:20:28 10-APR-10
BP Key: 30 Status: AVAILABLE Compressed: YES Tag: TAG20100410T115004
Piece Name: E:\ARCHIVE1011\SNS1011\0ULAP4RC_1_1
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 385085943 10-APR-10 E:\SNSD1011\SYSTEM01.ORA
2 Full 385085943 10-APR-10 E:\SNSD1011\UNDOTBS01.ORA
3 Full 385085943 10-APR-10 E:\SNSD1011\SYSAUX01.ORA
4 Full 385085943 10-APR-10 E:\SNSD1011\INDX01.ORA
5 Full 385085943 10-APR-10 E:\SNSD1011\USERS01.ORA
RMAN> restore database;
RMAN> recover database;
SQL> alter database open resetlogs;
Wednesday, February 24, 2010
RMAN BACKUP TO NETWORK DRIVE
RMAN BACKUP TO NETWORK DRIVE
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'z:/network/backup_db_%d_S_%s_P_%p_T_%t';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'z:\network\%F';
RMAN> backup current controlfile;
------------------------------ERROR1
Starting backup at 22-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-10
channel ORA_DISK_1: finished piece 1 at 22-FEB-10
piece handle=E:\SNS0910\BACKUP_DB_SNS0910_S_580_P_1_T_711634422 tag=TAG20100222T
121342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 22-FEB-10
Starting Control File Autobackup at 22-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File Autobackup command on ORA_DISK_1 channel at
02/22/2010 12:13:50
ORA-19504: failed to create file "Z:\C-45805873-20100222-02"
ORA-27056: could not delete file
OSD-04029: unable to get file attributes
O/S-Error: (OS 3) The system cannot find the path specified.
--------------------SOLUTION1
Basically all you need to do is update Oracle Services (both listener and Database) to run with Domain Account which has access on both servers with Administrator Privilege.
After you update the services ;
1- Restart the services
2-Use UNC naming instead of using drive letter on your script. (UNC naming part is not mentioned in the note)
3- Run the RMAN backup
Worked fine on my case.
------------------------------ERROR2
C:\>rman target sys@sns0910srv
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 22 13:49:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12631: Username retrieval failed
--------------------SOLUTION2
file:///C:/oracle/product/10.2.0/db_1/network/ADMIN/sqlnet.ora
set to none
SQLNET.AUTHENTICATION_SERVICES= (NONE)
------------------------------ERROR3
ERROR IS STILL SAME
provide permission that shared folder or drive for that USER(KGUPTA2)
--------------------DONOT DO ABOVE PRACTICE JUST DO FOLLOWING------------
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
------Error----
RMAN> backup current controlfile;
Starting backup at 24-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/24/2010 13:02:
14
ORA-19504: failed to create file "\\ORANOUP01P.UNICON.COM\KSH\BACKUP_DB_SNS0910_
S_637_P_1_T_711810132"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
-------
The problem is at the OS level, not within Oracle. To troubleshoot ORA-27040 errors, you need to test from the OS. You need to check these disk issues
------Steps for correct solution
1) give that user permission to kgupta2 user but same error
2) give folder>properites>shared>permission to kgupta2 but same error
3) give everyone to full permission but same error
4) Just do one thing: give permission for that source computer on that shared folderr>properites>shared>permission on desination server
4) Now RMAN BACKUP TO NETWORK DRIVE work fine
------------------------Final Solution-----
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
2) for network drive
give permission for that source computer on that shared folderr>properites>shared>permission on desination server
3) Rman backup will work fine
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'z:/network/backup_db_%d_S_%s_P_%p_T_%t';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'z:\network\%F';
RMAN> backup current controlfile;
------------------------------ERROR1
Starting backup at 22-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-10
channel ORA_DISK_1: finished piece 1 at 22-FEB-10
piece handle=E:\SNS0910\BACKUP_DB_SNS0910_S_580_P_1_T_711634422 tag=TAG20100222T
121342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 22-FEB-10
Starting Control File Autobackup at 22-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File Autobackup command on ORA_DISK_1 channel at
02/22/2010 12:13:50
ORA-19504: failed to create file "Z:\C-45805873-20100222-02"
ORA-27056: could not delete file
OSD-04029: unable to get file attributes
O/S-Error: (OS 3) The system cannot find the path specified.
--------------------SOLUTION1
Basically all you need to do is update Oracle Services (both listener and Database) to run with Domain Account which has access on both servers with Administrator Privilege.
After you update the services ;
1- Restart the services
2-Use UNC naming instead of using drive letter on your script. (UNC naming part is not mentioned in the note)
3- Run the RMAN backup
Worked fine on my case.
------------------------------ERROR2
C:\>rman target sys@sns0910srv
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 22 13:49:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12631: Username retrieval failed
--------------------SOLUTION2
file:///C:/oracle/product/10.2.0/db_1/network/ADMIN/sqlnet.ora
set to none
SQLNET.AUTHENTICATION_SERVICES= (NONE)
------------------------------ERROR3
ERROR IS STILL SAME
provide permission that shared folder or drive for that USER(KGUPTA2)
--------------------DONOT DO ABOVE PRACTICE JUST DO FOLLOWING------------
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
------Error----
RMAN> backup current controlfile;
Starting backup at 24-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/24/2010 13:02:
14
ORA-19504: failed to create file "\\ORANOUP01P.UNICON.COM\KSH\BACKUP_DB_SNS0910_
S_637_P_1_T_711810132"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
-------
The problem is at the OS level, not within Oracle. To troubleshoot ORA-27040 errors, you need to test from the OS. You need to check these disk issues
------Steps for correct solution
1) give that user permission to kgupta2 user but same error
2) give folder>properites>shared>permission to kgupta2 but same error
3) give everyone to full permission but same error
4) Just do one thing: give permission for that source computer on that shared folderr>properites>shared>permission on desination server
4) Now RMAN BACKUP TO NETWORK DRIVE work fine
------------------------Final Solution-----
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
2) for network drive
give permission for that source computer on that shared folderr>properites>shared>permission on desination server
3) Rman backup will work fine
Wednesday, November 11, 2009
Oracle File Formats
-------------------Backup Format-----
1
%c The copy number of the backup piece within a set of duplexed
backup pieces. If you did not duplex a backup, then this variable
is 1 for backup sets and 0 for proxy copies.
If one of these commands is enabled, then the variable shows the
copy number. The maximum value for %c is 256.
%d The name of the database.
%D The current day of the month (in format DD)
%F Combination of DBID, day, month, year, and sequence into a unique
and repeatable generated name.
%M The month (format MM)
%n The name of the database, padded on the right with x characters
to a total length of eight characters. (AKA: Porn star alias name)
For example, if the scott is the database name, %n= scottxxx.
%p The piece number within the backup set. This value starts at 1
for each backup set and is incremented by 1 as each backup piece
is created. Note: If you specify PROXY, then the %p variable must
be included in the FORMAT string either explicitly or implicitly within %U.
%s The backup set number. This number is a counter in the control file that
is incremented for each backup set. The counter value starts at 1 and is
unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result.
Also, CREATE CONTROLFILE initializes the counter back to 1.
%t The backup set time stamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for
the backup set.
%T The year, month, and day (YYYYMMDD)
%u An 8-character name constituted by compressed representations of
the backup set number and the time the backup set was created.
%U A convenient shorthand for %u_%p_%c that guarantees uniqueness in
generated backup filenames.
If you do not specify a format, RMAN uses %U by default.
%Y The year (YYYY)
%% Specifies the '%' character. e.g. %%Y translates to %Y.
------------ARCHIVELOG Format------
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%R resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
---------------------------
1
%c The copy number of the backup piece within a set of duplexed
backup pieces. If you did not duplex a backup, then this variable
is 1 for backup sets and 0 for proxy copies.
If one of these commands is enabled, then the variable shows the
copy number. The maximum value for %c is 256.
%d The name of the database.
%D The current day of the month (in format DD)
%F Combination of DBID, day, month, year, and sequence into a unique
and repeatable generated name.
%M The month (format MM)
%n The name of the database, padded on the right with x characters
to a total length of eight characters. (AKA: Porn star alias name)
For example, if the scott is the database name, %n= scottxxx.
%p The piece number within the backup set. This value starts at 1
for each backup set and is incremented by 1 as each backup piece
is created. Note: If you specify PROXY, then the %p variable must
be included in the FORMAT string either explicitly or implicitly within %U.
%s The backup set number. This number is a counter in the control file that
is incremented for each backup set. The counter value starts at 1 and is
unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result.
Also, CREATE CONTROLFILE initializes the counter back to 1.
%t The backup set time stamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for
the backup set.
%T The year, month, and day (YYYYMMDD)
%u An 8-character name constituted by compressed representations of
the backup set number and the time the backup set was created.
%U A convenient shorthand for %u_%p_%c that guarantees uniqueness in
generated backup filenames.
If you do not specify a format, RMAN uses %U by default.
%Y The year (YYYY)
%% Specifies the '%' character. e.g. %%Y translates to %Y.
------------ARCHIVELOG Format------
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%R resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
---------------------------
Saturday, July 18, 2009
RMAN Recovery with Previous Incarnation
rman>list incarnation of database;
sql>shutdown immediate
sql>startup mount
rman>reset database to incarnation 3;
rman>restore database until scn 4312345;
rman>recover database until scn 4312345;
rman>list incarnation;
rman> alter database open resetlogs;
rman>list incarnation;
sql>shutdown immediate
sql>startup mount
rman>reset database to incarnation 3;
rman>restore database until scn 4312345;
rman>recover database until scn 4312345;
rman>list incarnation;
rman> alter database open resetlogs;
rman>list incarnation;
Wednesday, July 8, 2009
Recover Database with missing Archieve Logs
Recover Database witn missing Archieve Logs
I am trying the restore my old database but due to missing of one archieve log. i m not able to restore and recover from rman
Not able to open database
ora 19870 ora 19505 ora 27041 osd 04002
rman 00571 rman 00569 rman 03002 rman 06053 rman 06025
--------------A---------------------------------------
crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archive all;
Note: not able to resync because not able to connect to recovery catalog because database is not open.
---------------B----------------------------------------
Point in time Recovery
-------------
1)
restore database UNTIL TIME "TO_DATE('03/27/09 10:05:00','MM/DD/YY = HH24:MI:SS')";
recover database UNTIL TIME "TO_DATE('03/27/09 10:05:00','MM/DD/YY = HH24:MI:SS')";
rman-03002 rman-20207
2)
restore database until scn 1000;
recover database until scn 1000;
3)
restore database until sequence 923 thread 1;
recover database until sequence 923 thread 1;
Note: not recover because of missing sequence.
---------------C----------------------------------------
list incranation
reset database to incranation inc_key;
restore database until sequence 923 thread 1;
recover database until sequence 923 thread 1;
Note: not recover because of missing sequence.
--------------D----------------------------------------
alter database backup controlfile to trace as 'c:\newcontrolfile.txt'
create new control file from above.
--------------E-FINAL SOLUTION---------------------------------------
shutdown immediate;
add into init.ora _allow_resetlogs_corruption=true
startup mount;
sql>recover database until cancel using backup controlfile;
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Alter database open resetlogs
===============================================================
I am trying the restore my old database but due to missing of one archieve log. i m not able to restore and recover from rman
Not able to open database
ora 19870 ora 19505 ora 27041 osd 04002
rman 00571 rman 00569 rman 03002 rman 06053 rman 06025
--------------A---------------------------------------
crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archive all;
Note: not able to resync because not able to connect to recovery catalog because database is not open.
---------------B----------------------------------------
Point in time Recovery
-------------
1)
restore database UNTIL TIME "TO_DATE('03/27/09 10:05:00','MM/DD/YY = HH24:MI:SS')";
recover database UNTIL TIME "TO_DATE('03/27/09 10:05:00','MM/DD/YY = HH24:MI:SS')";
rman-03002 rman-20207
2)
restore database until scn 1000;
recover database until scn 1000;
3)
restore database until sequence 923 thread 1;
recover database until sequence 923 thread 1;
Note: not recover because of missing sequence.
---------------C----------------------------------------
list incranation
reset database to incranation inc_key;
restore database until sequence 923 thread 1;
recover database until sequence 923 thread 1;
Note: not recover because of missing sequence.
--------------D----------------------------------------
alter database backup controlfile to trace as 'c:\newcontrolfile.txt'
create new control file from above.
--------------E-FINAL SOLUTION---------------------------------------
shutdown immediate;
add into init.ora _allow_resetlogs_corruption=true
startup mount;
sql>recover database until cancel using backup controlfile;
Specify log: {
CANCEL
Alter database open resetlogs
===============================================================
Thursday, May 21, 2009
RMAN Catalog Registration Configuration
RMAN Catalog Registration Configuration
STEPS
-----------------
1) SET CATALOG for RMAN user
create user rman identified by rman;
grant recovery_catalog_owner to rman;
2) CREATE CATALOG and REGISTER DATABASE
set oracle_sid=sns5
rman catalog rman/rman@sns0910srv
RMAN>create catalog;
RMAN>register database;
3) VERIFY REGISTRATION
RMAN>report schema;
exit
4) MANUAL BACKUP REGISTRATION
rman target rman/rman@sns0910srv nocatalog;
RMAN>backup current controlfile;
rman target rman/rman@sns0910srv catalog rman/rman@sns0910srv;
RMAN>catalog backuppiece 'c:\backup\rman\sns0910\C-...';
------------
ERRORS IN ABOVE PROCESS
ORACLE error from recovery catalog database: ORA-01950: no privileges o
ace 'SYSTEM'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog
---------
Solution
tablespace ---USR
Quota on USR unlimited
-----------
RMAN> create catalog;
recovery catalog created
-----------
Error
RMAN> register database;
RMAN-00571: ==========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ==========================================================
RMAN-03002: failure of register command at 06/23/2009 15:50:47
RMAN-06171: not connected to target database
---------------
Solution
c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv
==================
FINAL STEPS
-------
1) SET CATALOG for RMAN user
create user rman identified by rman
Temporary tablespace temp
Default tablespace usr
Quota unlimited on usr;
grant recovery_catalog_owner to rman;
2) CREATE CATALOG and REGISTER DATABASE
set oracle_sid=sns5
c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv
RMAN>create catalog;
RMAN>register database;
RMAN>exit
3) VERIFY REGISTRATION
RMAN>report schema;
exit
--------------------------
Configure RMAN
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
target database Password: oracle
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\backup\rman\sns0809\%F';
RMAN>SHOW ALL;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:\backup\rman\sns0809\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSNS5.ORA'; # default
RMAN BACKUP
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
target database Password: oracle
RMAN> BACKUP DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
RMAN RECOVERY
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
RMAN> RESTORE CONTROLFILE FROM 'E:\backup\rman\sns0809\C-1560435174-20081003-00'
SQL> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS
Restoring Default RMAN Configuration Settings:
CONFIGURE BACKUP OPTIMIZATION CLEAR;
ONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
Configuring the Default Device Type for Backups
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
Configuring the Default Backup Type for Disk Backups
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed
Configuring Compressed Backupsets as Default for Tape or Disk
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
Configuring Disk Devices and Channels
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';
Configuring Tape Devices and Channels
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
Configuring Control File and Server Parameter File Autobackup
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
Configuring the Control File Autobackup Format
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE deviceSpecifier TO 'string';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO 'ora_home/oradata/cf_%F';
CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE sbt CLEAR;
Overriding the Configured Control File Autobackup Format
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'controlfile_%F';
RMAN> BACKUP AS COPY DATABASE;
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F.bck';
BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;
}
Configuring a Recovery Window-Based Retention Policy
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Configuring a Redundancy-Based Retention Policy
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Showing the Current Retention Policy
RMAN> SHOW RETENTION POLICY;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Disabling the Retention Policy
CONFIGURE RETENTION POLICY TO NONE;
------------------------------------------------
Important Points
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
%F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:
XXXXXXXXX – database id
YYYYMMDD – day, when backed up
NN – change number during day, starts with 00, and represented in hexadecimal
RMAN increments "1" to the last number string (sequence) for each autobackup that is performed in a day.
-------------
Run bplist as the root user on the client host.
# bplist -t 4 -l -R / > bp.list.out
# cat bp.list.out
----------
RMAN Reports
# Show all backup details
list backup;
# Show items that beed 7 days worth of archivelogs to recover completely
report need backup days = 7 database;
# Show/Delete items not needed for recovery
report obsolete;
delete obsolete;
# Show/Delete items not needed for point-in-time
# recovery within the last week
report obsolete recovery window of 7 days;
delete obsolete recovery window of 7 days;
# Show/Delete items with more than 2 newer copies available
report obsolete redundancy = 2 device type disk;
delete obsolete redundancy = 2 device type disk;
# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';
Specifying the Location of RMAN Output
By default, RMAN writes command output to standard output. To redirect output to a log file, enter the LOG parameter on the command line when starting RMAN, as in the following example:
% rman LOG /tmp/rman.log
-------------------
Different RMAN Backup
Back up the database, and then the control file:
(which contains a record of the backup)
RMAN> BACKUP DATABASE;
RMAN> BACKUP CURRENT CONTROLFILE;
Backup datafiles:
RMAN> BACKUP AS BACKUPSET DATAFILE
'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trgt/tools01.dbf';
Backup all datafiles in the database:
RMAN> BACKUP AS COPY DATABASE;
Backup archive logs:
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-28' AND 'SYSDATE-7';
Backup tablespace:
RMAN> BACKUP TABLESPACE system, users, tools;
Backup controlfile:
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
Backup parameter file:
RMAN> BACKUP SPFILE;
Backup everything:
RMAN> BACKUP BACKUPSET ALL;
Create a consistent backup and keep the backup for 1 year:
(exempt from the retention policy)
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;
Backup Validation confirms that a backup could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.
Example:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
----------------------------------------------
Restoring the Oracle SPFILE
To restore the spfile, you still need to start the database, but you can do so with a minimal database parameter file.
Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initsns5.ora
Once the instance is started, you can start RMAN and restore the SPFILE as seen in this example:
RMAN>restore spfile from autobackup;
RMAN INCOMPLETE RECOVERY
Performing Incomplete Recovery
RMAN allows you to perform recovery of the whole database to a specified non-current time, SCN, or log sequence number. This
type of recovery is called incomplete recovery. Because you must open RESETLOGS after performing incomplete recovery, you
have to recover all datafiles. You cannot recover some datafiles before the RESETLOGS and others after the RESETLOGS. The
database must be closed to perform database point-in-time recovery.
run {
shutdown immediate;
startup mount;
set until time 'May 15 2009 09:00:00';
# set until scn 1000; # alternatively, you can specify SCN
# set until sequence 9923; # alternatively, you can specify log sequence number
restore database;
recover database;
alter database open resetlogs;
}
RMAN Disaster Recovery
C:>rman catalog=rman/rman@sns5 target=sys/password@sns5live
startup nomount;
restore controlfile;
alter database mount;
--------
SQL> SELECT archivelog_change#-1 FROM v$database;
run {
set until scn 1048438;
restore database;
recover database;
alter database open resetlogs;
}
------
sql "ALTER TABLESPACE temp ADD
TEMPFILE ''C:\Oracle\oradata\sns5live\temp01.dbf''
SIZE 100M
AUTOEXTEND ON NEXT 64K";
---------------
list incarnation;
reset database to incarnation x;
----------------
# Show all backup details
list backup;
------------------
RMAN point in time recovery (PITR)
Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly
recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the
tablespaces and other objects in the database.
run{
set until time "to_date('05/28/09 15:00:00' , 'mm/dd/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;}
---------------------
Unregister database from recovery catalog
1)nregistering a Database That is Unique in Catalog
RMAN> CONNECT TARGET /
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> LIST DB_UNIQUE_NAME ALL;
2) Unregistering a Database That is Not Unique in Catalog
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 28014364;
RMAN> UNREGISTER DATABASE;
3)Unregistering a Standby Database
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 1627367554;
RMAN> UNREGISTER DB_UNIQUE_NAME sns0901test;
STEPS
-----------------
1) SET CATALOG for RMAN user
create user rman identified by rman;
grant recovery_catalog_owner to rman;
2) CREATE CATALOG and REGISTER DATABASE
set oracle_sid=sns5
rman catalog rman/rman@sns0910srv
RMAN>create catalog;
RMAN>register database;
3) VERIFY REGISTRATION
RMAN>report schema;
exit
4) MANUAL BACKUP REGISTRATION
rman target rman/rman@sns0910srv nocatalog;
RMAN>backup current controlfile;
rman target rman/rman@sns0910srv catalog rman/rman@sns0910srv;
RMAN>catalog backuppiece 'c:\backup\rman\sns0910\C-...';
------------
ERRORS IN ABOVE PROCESS
ORACLE error from recovery catalog database: ORA-01950: no privileges o
ace 'SYSTEM'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog
---------
Solution
tablespace ---USR
Quota on USR unlimited
-----------
RMAN> create catalog;
recovery catalog created
-----------
Error
RMAN> register database;
RMAN-00571: ==========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ==========================================================
RMAN-03002: failure of register command at 06/23/2009 15:50:47
RMAN-06171: not connected to target database
---------------
Solution
c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv
==================
FINAL STEPS
-------
1) SET CATALOG for RMAN user
create user rman identified by rman
Temporary tablespace temp
Default tablespace usr
Quota unlimited on usr;
grant recovery_catalog_owner to rman;
2) CREATE CATALOG and REGISTER DATABASE
set oracle_sid=sns5
c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv
RMAN>create catalog;
RMAN>register database;
RMAN>exit
3) VERIFY REGISTRATION
RMAN>report schema;
exit
--------------------------
Configure RMAN
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
target database Password: oracle
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\backup\rman\sns0809\%F';
RMAN>SHOW ALL;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:\backup\rman\sns0809\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSNS5.ORA'; # default
RMAN BACKUP
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
target database Password: oracle
RMAN> BACKUP DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
RMAN RECOVERY
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
C:\>SET ORACLE_SID=SNS5
C:\>RMAN TARGET SYS@SNS0910SRV
RMAN> RESTORE CONTROLFILE FROM 'E:\backup\rman\sns0809\C-1560435174-20081003-00'
SQL> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS
Restoring Default RMAN Configuration Settings:
CONFIGURE BACKUP OPTIMIZATION CLEAR;
ONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
Configuring the Default Device Type for Backups
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
Configuring the Default Backup Type for Disk Backups
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed
Configuring Compressed Backupsets as Default for Tape or Disk
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
Configuring Disk Devices and Channels
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';
Configuring Tape Devices and Channels
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
Configuring Control File and Server Parameter File Autobackup
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
Configuring the Control File Autobackup Format
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE deviceSpecifier TO 'string';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO 'ora_home/oradata/cf_%F';
CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE sbt CLEAR;
Overriding the Configured Control File Autobackup Format
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'controlfile_%F';
RMAN> BACKUP AS COPY DATABASE;
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F.bck';
BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;
}
Configuring a Recovery Window-Based Retention Policy
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Configuring a Redundancy-Based Retention Policy
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Showing the Current Retention Policy
RMAN> SHOW RETENTION POLICY;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Disabling the Retention Policy
CONFIGURE RETENTION POLICY TO NONE;
------------------------------------------------
Important Points
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';
%F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:
XXXXXXXXX – database id
YYYYMMDD – day, when backed up
NN – change number during day, starts with 00, and represented in hexadecimal
RMAN increments "1" to the last number string (sequence) for each autobackup that is performed in a day.
-------------
Run bplist as the root user on the client host.
# bplist -t 4 -l -R / > bp.list.out
# cat bp.list.out
----------
RMAN Reports
# Show all backup details
list backup;
# Show items that beed 7 days worth of archivelogs to recover completely
report need backup days = 7 database;
# Show/Delete items not needed for recovery
report obsolete;
delete obsolete;
# Show/Delete items not needed for point-in-time
# recovery within the last week
report obsolete recovery window of 7 days;
delete obsolete recovery window of 7 days;
# Show/Delete items with more than 2 newer copies available
report obsolete redundancy = 2 device type disk;
delete obsolete redundancy = 2 device type disk;
# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';
Specifying the Location of RMAN Output
By default, RMAN writes command output to standard output. To redirect output to a log file, enter the LOG parameter on the command line when starting RMAN, as in the following example:
% rman LOG /tmp/rman.log
-------------------
Different RMAN Backup
Back up the database, and then the control file:
(which contains a record of the backup)
RMAN> BACKUP DATABASE;
RMAN> BACKUP CURRENT CONTROLFILE;
Backup datafiles:
RMAN> BACKUP AS BACKUPSET DATAFILE
'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trgt/tools01.dbf';
Backup all datafiles in the database:
RMAN> BACKUP AS COPY DATABASE;
Backup archive logs:
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-28' AND 'SYSDATE-7';
Backup tablespace:
RMAN> BACKUP TABLESPACE system, users, tools;
Backup controlfile:
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
Backup parameter file:
RMAN> BACKUP SPFILE;
Backup everything:
RMAN> BACKUP BACKUPSET ALL;
Create a consistent backup and keep the backup for 1 year:
(exempt from the retention policy)
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;
Backup Validation confirms that a backup could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.
Example:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
----------------------------------------------
Restoring the Oracle SPFILE
To restore the spfile, you still need to start the database, but you can do so with a minimal database parameter file.
Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initsns5.ora
Once the instance is started, you can start RMAN and restore the SPFILE as seen in this example:
RMAN>restore spfile from autobackup;
RMAN INCOMPLETE RECOVERY
Performing Incomplete Recovery
RMAN allows you to perform recovery of the whole database to a specified non-current time, SCN, or log sequence number. This
type of recovery is called incomplete recovery. Because you must open RESETLOGS after performing incomplete recovery, you
have to recover all datafiles. You cannot recover some datafiles before the RESETLOGS and others after the RESETLOGS. The
database must be closed to perform database point-in-time recovery.
run {
shutdown immediate;
startup mount;
set until time 'May 15 2009 09:00:00';
# set until scn 1000; # alternatively, you can specify SCN
# set until sequence 9923; # alternatively, you can specify log sequence number
restore database;
recover database;
alter database open resetlogs;
}
RMAN Disaster Recovery
C:>rman catalog=rman/rman@sns5 target=sys/password@sns5live
startup nomount;
restore controlfile;
alter database mount;
--------
SQL> SELECT archivelog_change#-1 FROM v$database;
run {
set until scn 1048438;
restore database;
recover database;
alter database open resetlogs;
}
------
sql "ALTER TABLESPACE temp ADD
TEMPFILE ''C:\Oracle\oradata\sns5live\temp01.dbf''
SIZE 100M
AUTOEXTEND ON NEXT 64K";
---------------
list incarnation;
reset database to incarnation x;
----------------
# Show all backup details
list backup;
------------------
RMAN point in time recovery (PITR)
Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly
recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the
tablespaces and other objects in the database.
run{
set until time "to_date('05/28/09 15:00:00' , 'mm/dd/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;}
---------------------
Unregister database from recovery catalog
1)nregistering a Database That is Unique in Catalog
RMAN> CONNECT TARGET /
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> LIST DB_UNIQUE_NAME ALL;
2) Unregistering a Database That is Not Unique in Catalog
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 28014364;
RMAN> UNREGISTER DATABASE;
3)Unregistering a Standby Database
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 1627367554;
RMAN> UNREGISTER DB_UNIQUE_NAME sns0901test;
Subscribe to:
Posts (Atom)