Remember your dreams
Remember your dreams
Friday, December 3, 2010
Restore database to different host.
Oracle 11gR2. 11.2.0.1.0
Linux RH5
NetBackup 6.5.5
You may want to restore your production database to a different host, for example, to the development host database server, for testing purposes. This allows you to test your current backups and rehearse the necessary steps required in the event of a real emergency or hardware failure on the production host. In the event that you cannot rebuild the production environment in a reasonable time frame whether waiting on parts or technical expertise, this may be the answer to getting your production back up and running in hours compared to days.
Regardless of your reasons, it is a great method of testing backups and your restoration capabilities.
* RECOMMENDATION *
Rehearse, document, and practice your ability to recover the database on a regular basis. This should be part of your regular monthly tasks. When disaster strikes you will be glad you have routinely rehearsed these procedures.
I am using a development environment identical to my production environment (I know, nice eh?). Identical versions of Oracle are on both severs and my production specific directories already exist on my development server.
If your production directory structure does not exist on the development server then restore those folders from a backup of the production server. You can create them, but it is much easier to just restore them.
* IMPORTANT *
Be sure to delete any prod archive log folders on the dev server that were restored from the production server. This is important because you do not want this information cataloged during the restore (it will prevent you from finding the desired backup sets to restore). Our assumption is a total failure of the production sever so the only archive logs we want have been included in our last backup.
In this example I will restore my production instance, prod, to the development database, dev, server. I will shutdown the dev instance although you can opt to modify your memory parameters (modifying Oracle memory parameters to run both prod and dev on the same server).
-- On the development server. --
1. Recreate your production directory structure.
2. Delete any production archive log file directories.
3. Create a prod password file.\
4. Shut down emctl for dev.
% emctl stop dbconsole
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
Shutdown the DEV instance
% sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 3 08:38:12 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Linux RH5
NetBackup 6.5.5
You may want to restore your production database to a different host, for example, to the development host database server, for testing purposes. This allows you to test your current backups and rehearse the necessary steps required in the event of a real emergency or hardware failure on the production host. In the event that you cannot rebuild the production environment in a reasonable time frame whether waiting on parts or technical expertise, this may be the answer to getting your production back up and running in hours compared to days.
Regardless of your reasons, it is a great method of testing backups and your restoration capabilities.
* RECOMMENDATION *
Rehearse, document, and practice your ability to recover the database on a regular basis. This should be part of your regular monthly tasks. When disaster strikes you will be glad you have routinely rehearsed these procedures.
I am using a development environment identical to my production environment (I know, nice eh?). Identical versions of Oracle are on both severs and my production specific directories already exist on my development server.
If your production directory structure does not exist on the development server then restore those folders from a backup of the production server. You can create them, but it is much easier to just restore them.
* IMPORTANT *
Be sure to delete any prod archive log folders on the dev server that were restored from the production server. This is important because you do not want this information cataloged during the restore (it will prevent you from finding the desired backup sets to restore). Our assumption is a total failure of the production sever so the only archive logs we want have been included in our last backup.
In this example I will restore my production instance, prod, to the development database, dev, server. I will shutdown the dev instance although you can opt to modify your memory parameters (modifying Oracle memory parameters to run both prod and dev on the same server).
-- On the development server. --
1. Recreate your production directory structure.
2. Delete any production archive log file directories.
3. Create a prod password file.\
4. Shut down emctl for dev.
% emctl stop dbconsole
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
Shutdown the DEV instance
% sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 3 08:38:12 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Thursday, October 14, 2010
ORA-24247: network access denied by access control list (ACL) - Enabling UTL_MAIL
I first came across this issue when migrating from version 10 to 11gR2. It continues to rear its ugly head even as I migrate to version 12.2.0.1 causing my my email alert package to no longer work.
I use a custom mail package to send email to users and alerts to the DBA.
Executing returns the following error:
ORA-24247: network access denied by access control list (ACL).
What is this ACL business all about? It all started as a new feature in 11g and now just a feature in 12c. Oracle likes to call it Fine-Grain Access to External Network Services.
I am going to focus on my need to get the utl_mail.send procedure working again. This is how to resolve the issue and more on enabling UTL_MAIL.
This original information I used to resolve this came from the 11gR2 documentation, but as I recently discovered still holds true in 12.2.0.1. For the 11gR2 documentation, including writing a custom mail package of your own, look to the following Oracle documentation.
Oracle® Database Security Guide
11g Release 2 (11.2)
Part Number E16543-02
If this link does not work for you,
Google for "Verifying Security Access with Auditing"
Search for "Install and Configure the UTL_MAIL PL/SQL Package" within the document.
You may see an error like the following:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2
or
ERROR at line 1:
ORA-24247: network access denied by
access control list (ACL)
Let's start by testing the utl_mail.send procedure.
SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message');
end;
/
utl_mail.send(
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
For starters the utl_mail package is not installed.
UTL_MAIL is not installed in by default 11g and 12c.
You must install it manually.
Install and Configure the UTL_MAIL PL/SQL Package
% sqlplus / as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
If are not going to be sys then be sure to grant execute to utl_mail package to the user you are going to be executing the mail send procedure as. In this example I'll use HOWARD and the user HOWARD will own the custom mail package. Replace user HOWARD with your desired user.
GRANT EXECUTE ON SYS.UTL_MAIL TO HOWARD;
Now determine your smtp outgoing mail server. Once you have this you need to configure the database parameter set smtp_out_server.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server = smtp server:port scope=both;
As in
SQL> alter system set smtp_out_server = 'smtp-server.com:25’ scope=both;
System altered.
Now you need to create and configure the ACL (Access Control List).
Configuring the ACL (Access Control List) File for Network Services.
Before you can use PL/SQL network utility packages such as UTL_MAIL, you must configure an access control list (ACL) file that enables fine-grained access to external network services. To configure an access control list for the e-mail alert.
Start by creating the ACL
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Enables mail to be sent',
principal => 'HOWARD',is_grant => true,
privilege => 'connect');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Assign the access control list to the outgoing SMTP network host for your e-mail server.
SQL> begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_mail.xml',
host => 'your smtp-server.com',
lower_port => 25);
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Finally grant permission to use it,
SQL> begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_mail.xml',
principal => 'HOWARD',
is_grant => TRUE,
privilege => 'connect');
end;
/
Let's try the utl_mail send package again and see if it works.
SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message from HOWARD');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Looks like we are back in business and I did receive an email.
To Drop the ACL and start over issue the drop ACL command:
SQL> begin
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl=>'utl_mail.xml');
end;
/
SQL> commit;
Commit complete.
Here are some useful queries you can use to verify the information on your database:
SELECT host,lower_port,upper_port,acl,DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'HOWARD', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE FROM dba_network_acls
WHERE host IN (SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('your smtp server.com')))
ORDER BY DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;
SELECT acl,principal,privilege,is_grant,TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
select host, lower_port, upper_port, acl,DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,null,'connect') GRANTED from dba_network_acls;
I use a custom mail package to send email to users and alerts to the DBA.
Executing returns the following error:
ORA-24247: network access denied by access control list (ACL).
What is this ACL business all about? It all started as a new feature in 11g and now just a feature in 12c. Oracle likes to call it Fine-Grain Access to External Network Services.
I am going to focus on my need to get the utl_mail.send procedure working again. This is how to resolve the issue and more on enabling UTL_MAIL.
This original information I used to resolve this came from the 11gR2 documentation, but as I recently discovered still holds true in 12.2.0.1. For the 11gR2 documentation, including writing a custom mail package of your own, look to the following Oracle documentation.
Oracle® Database Security Guide
11g Release 2 (11.2)
Part Number E16543-02
If this link does not work for you,
Google for "Verifying Security Access with Auditing"
Search for "Install and Configure the UTL_MAIL PL/SQL Package" within the document.
You may see an error like the following:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2
or
ERROR at line 1:
ORA-24247: network access denied by
access control list (ACL)
Let's start by testing the utl_mail.send procedure.
SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message');
end;
/
utl_mail.send(
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
For starters the utl_mail package is not installed.
UTL_MAIL is not installed in by default 11g and 12c.
You must install it manually.
Install and Configure the UTL_MAIL PL/SQL Package
% sqlplus / as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
If are not going to be sys then be sure to grant execute to utl_mail package to the user you are going to be executing the mail send procedure as. In this example I'll use HOWARD and the user HOWARD will own the custom mail package. Replace user HOWARD with your desired user.
GRANT EXECUTE ON SYS.UTL_MAIL TO HOWARD;
Now determine your smtp outgoing mail server. Once you have this you need to configure the database parameter set smtp_out_server.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server = smtp server:port scope=both;
As in
SQL> alter system set smtp_out_server = 'smtp-server.com:25’ scope=both;
System altered.
Now you need to create and configure the ACL (Access Control List).
Configuring the ACL (Access Control List) File for Network Services.
Before you can use PL/SQL network utility packages such as UTL_MAIL, you must configure an access control list (ACL) file that enables fine-grained access to external network services. To configure an access control list for the e-mail alert.
Start by creating the ACL
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Enables mail to be sent',
principal => 'HOWARD',is_grant => true,
privilege => 'connect');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Assign the access control list to the outgoing SMTP network host for your e-mail server.
SQL> begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_mail.xml',
host => 'your smtp-server.com',
lower_port => 25);
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Finally grant permission to use it,
SQL> begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_mail.xml',
principal => 'HOWARD',
is_grant => TRUE,
privilege => 'connect');
end;
/
Let's try the utl_mail send package again and see if it works.
SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message from HOWARD');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Looks like we are back in business and I did receive an email.
To Drop the ACL and start over issue the drop ACL command:
SQL> begin
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl=>'utl_mail.xml');
end;
/
SQL> commit;
Commit complete.
Here are some useful queries you can use to verify the information on your database:
SELECT host,lower_port,upper_port,acl,DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'HOWARD', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE FROM dba_network_acls
WHERE host IN (SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('your smtp server.com')))
ORDER BY DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;
SELECT acl,principal,privilege,is_grant,TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
select host, lower_port, upper_port, acl,DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,null,'connect') GRANTED from dba_network_acls;
Friday, July 30, 2010
Oracle Disaster Recovery Test.
I will intentionally destroy my DEV instance in order to provide an example of restoring and recovering the database using Oracle RMAN an and RMAN Catalog.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
Create a new pfile and backup the spfile just for kicks
SQL> create pfile from spfile;
mv spfiledev.ora spfiledev.old.ora
cd /u01/oradata/dev
# ls
control01.ctl
sysaux01.dbf
system01.dbf
#pwd
/u01/oradata/dev
# rm *
#cd /u02/oradata/dev/
# ls
control02.ctl
redo01a.rdo
redo02a.rdo
redo03a.rdo
#pwd
/u02/oradata/dev
# rm *
#cd /u03/oradata/dev/
# ls
control03.ctl
redo01b.rdo
redo02b.rdo
redo03b.rdo
#pwd
/u03/oradata/dev
# rm *
# cd /u04/oradata/dev/
# ls
undotbs01.dbf
#pwd
/u04/oradata/dev
# rm *
-- Login to sqlplus
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/u01/oradata/dev/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/u01/oradata/dev/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:45:59 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
ORA-00205: error in identifying control file, check alert log for more info
SQL> shutdown abort;
ORACLE instance shut down.
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:58:42 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
-- login to rman and the rman catalog
#rman target / rcvcat_login/rcvcat_passwd@rcvcat
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 29 10:00:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV (not mounted)
connected to recovery catalog database
RMAN> list incarnation of database dev;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
198582917 198582918 DEV 3800958841 CURRENT 1 25-JUN-10
RMAN> set dbid 3800958841
executing command: SET DBID
database name is "DEV" and DBID is 3800958841
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_1: AUTOBACKUP found: c-3800958841-20100729-01
channel ORA_SBT_TAPE_2: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring spfile from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 29-JUL-10
RMAN>
RMAN> SHUTDOWN IMMEDIATE;
Oracle instance shut down
RMAN> STARTUP FORCE NOMOUNT;
Oracle instance started
RMAN> run {
2> RESTORE CONTROLFILE FROM AUTOBACKUP;
3> ALTER DATABASE MOUNT;
4> }
Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring control file from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/dev/control01.ctl
output file name=/u02/oradata/dev/control02.ctl
output file name=/u03/oradata/dev/control03.ctl
Finished restore at 29-JUL-10
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
-- Restore to the last archive log file backed up.
RMAN> list backup of archivelog from scn=1;
List of Backup Sets
===================
List of Archived Logs in backup set 208615940
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 40 5138860 27-JUL-10 5214805 28-JUL-10
RMAN> RUN {
2> SET UNTIL SEQUENCE 40 THREAD 1;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
6> }
executing command: SET until clause
Starting restore at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to /u01/oradata/dev/system01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u06/oradata/dev/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece bk_62_1_725106021
channel ORA_SBT_TAPE_2: starting datafile backup set restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_2: restoring datafile 00002 to /u01/oradata/dev/sysaux01.dbf
channel ORA_SBT_TAPE_2: restoring datafile 00003 to /u04/oradata/dev/undotbs01.dbf
channel ORA_SBT_TAPE_2: reading from backup piece bk_61_1_725106021
channel ORA_SBT_TAPE_1: piece handle=bk_62_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
channel ORA_SBT_TAPE_2: piece handle=bk_61_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:02:05
Finished restore at 30-JUL-10
Starting recover at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
starting media recovery
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_SBT_TAPE_1: reading from backup piece al_69_1_725533318
channel ORA_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=38
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=39
channel ORA_SBT_TAPE_2: reading from backup piece al_70_1_725533318
channel ORA_SBT_TAPE_2: piece handle=al_70_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:45
channel ORA_SBT_TAPE_1: piece handle=al_69_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:15
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc thread=1 sequence=36
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc RECID=36 STAMP=725704495
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc thread=1 sequence=37
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc RECID=37 STAMP=725704496
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc thread=1 sequence=38
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc RECID=35 STAMP=725704470
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc thread=1 sequence=39
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc RECID=34 STAMP=725704469
media recovery complete, elapsed time: 00:00:14
Finished recover at 30-JUL-10
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>exit
-- log into sqlplus and check the database status.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
Create a new pfile and backup the spfile just for kicks
SQL> create pfile from spfile;
mv spfiledev.ora spfiledev.old.ora
cd /u01/oradata/dev
# ls
control01.ctl
sysaux01.dbf
system01.dbf
#pwd
/u01/oradata/dev
# rm *
#cd /u02/oradata/dev/
# ls
control02.ctl
redo01a.rdo
redo02a.rdo
redo03a.rdo
#pwd
/u02/oradata/dev
# rm *
#cd /u03/oradata/dev/
# ls
control03.ctl
redo01b.rdo
redo02b.rdo
redo03b.rdo
#pwd
/u03/oradata/dev
# rm *
# cd /u04/oradata/dev/
# ls
undotbs01.dbf
#pwd
/u04/oradata/dev
# rm *
-- Login to sqlplus
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/u01/oradata/dev/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/u01/oradata/dev/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:45:59 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
ORA-00205: error in identifying control file, check alert log for more info
SQL> shutdown abort;
ORACLE instance shut down.
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:58:42 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
-- login to rman and the rman catalog
#rman target / rcvcat_login/rcvcat_passwd@rcvcat
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 29 10:00:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV (not mounted)
connected to recovery catalog database
RMAN> list incarnation of database dev;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
198582917 198582918 DEV 3800958841 CURRENT 1 25-JUN-10
RMAN> set dbid 3800958841
executing command: SET DBID
database name is "DEV" and DBID is 3800958841
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_1: AUTOBACKUP found: c-3800958841-20100729-01
channel ORA_SBT_TAPE_2: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring spfile from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 29-JUL-10
RMAN>
RMAN> SHUTDOWN IMMEDIATE;
Oracle instance shut down
RMAN> STARTUP FORCE NOMOUNT;
Oracle instance started
RMAN> run {
2> RESTORE CONTROLFILE FROM AUTOBACKUP;
3> ALTER DATABASE MOUNT;
4> }
Starting restore at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: looking for AUTOBACKUP on day: 20100729
channel ORA_SBT_TAPE_2: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_1: skipped, AUTOBACKUP already found
recovery area destination: /app/oracle/flash_recovery_area
database name (or database unique name) used for search: DEV
channel ORA_DISK_2: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20100729
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_SBT_TAPE_1: restoring control file from AUTOBACKUP c-3800958841-20100729-01
channel ORA_SBT_TAPE_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/dev/control01.ctl
output file name=/u02/oradata/dev/control02.ctl
output file name=/u03/oradata/dev/control03.ctl
Finished restore at 29-JUL-10
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
-- Restore to the last archive log file backed up.
RMAN> list backup of archivelog from scn=1;
List of Backup Sets
===================
List of Archived Logs in backup set 208615940
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 40 5138860 27-JUL-10 5214805 28-JUL-10
RMAN> RUN {
2> SET UNTIL SEQUENCE 40 THREAD 1;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
6> }
executing command: SET until clause
Starting restore at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to /u01/oradata/dev/system01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u06/oradata/dev/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece bk_62_1_725106021
channel ORA_SBT_TAPE_2: starting datafile backup set restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_2: restoring datafile 00002 to /u01/oradata/dev/sysaux01.dbf
channel ORA_SBT_TAPE_2: restoring datafile 00003 to /u04/oradata/dev/undotbs01.dbf
channel ORA_SBT_TAPE_2: reading from backup piece bk_61_1_725106021
channel ORA_SBT_TAPE_1: piece handle=bk_62_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
channel ORA_SBT_TAPE_2: piece handle=bk_61_1_725106021 tag=HOT_DB_BK_LEVEL0
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:02:05
Finished restore at 30-JUL-10
Starting recover at 30-JUL-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
starting media recovery
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_SBT_TAPE_1: reading from backup piece al_69_1_725533318
channel ORA_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=38
channel ORA_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=39
channel ORA_SBT_TAPE_2: reading from backup piece al_70_1_725533318
channel ORA_SBT_TAPE_2: piece handle=al_70_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:45
channel ORA_SBT_TAPE_1: piece handle=al_69_1_725533318 tag=TAG20100728T090157
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:15
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc thread=1 sequence=36
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_36_655w8sr0_.arc RECID=36 STAMP=725704495
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc thread=1 sequence=37
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_37_655w8sqg_.arc RECID=37 STAMP=725704496
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc thread=1 sequence=38
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_38_655w7xmk_.arc RECID=35 STAMP=725704470
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc thread=1 sequence=39
channel default: deleting archived log(s)
archived log file name=/app/oracle/flash_recovery_area/DEV/archivelog/2010_07_30/o1_mf_1_39_655w7xm0_.arc RECID=34 STAMP=725704469
media recovery complete, elapsed time: 00:00:14
Finished recover at 30-JUL-10
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>exit
-- log into sqlplus and check the database status.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
Recovering the database after a disaster
Using RMAN and RMAN recovery catalog.
These steps will restore and recovery the database after losing some or all of the critical database components. i.e. controlfiles, spfile, system.dbf, etc…
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
Once you have determined you need to restore the database login to sqlplus and issue the shutdown abort command.
SQL> shutdown abort
Restore the server files (Oracle Base and Home directories) from a backup if necessary.
Restore the Oracle directories.
Connect to rman and rman catalog.
# rman target / catalog rcvcat_login/rcvcat_passwd@rcvcat;
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 27 15:46:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database
set the DBID
see setting the DBID thread
RMAN> SET DBID 3800958841
executing command: SET DBID
database name is "HRPROD" and DBID is 3800958841
Startup the database in nomount
RMAN> startup nomount;
Oracle instance started
In order to restore the spfile the current spfile cannot be intact. You cannot restore over a current spfile. Either rename it or delete it if it currently exists before executing the 'restore spfile from autobackup' command.
Restore the SPFILE
RMAN> restore spfile from autobackup;
Starting restore at ...
.
.
.
Shutdown the database.
RMAN> shutdown immediate;
Oracle instance shut down
Restart the instance with the restored server parameter file.
RMAN> startup force nomount;
Determine the last archive log sequence backed up or available on disk.
Since I am using a RAID 5 configuration, if I lose more than one disk at a time I will lose everything, including all archive logs files on disk. I have an output from the last backup in my email that I can reference, but this information can also be obtained from your RMAN catalog.
RMAN> list backup of archivelog from scn=1;
List of Archived Logs in backup set 205213164
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 4221565 20-JUL-10 4421777 22-JUL-10
Some other useful RMAN commands are the following as well:
RMAN> list backup;
RMAN> list backup of database completed after '19-JUL-10';
Now that you know the Archive Log Seq you want to restore to, you can issue the following commands in the RUN block.
RMAN> RUN
{
# Restore autobackup of the control file.
# This example assumes that you have
# accepted the default format
# for the autobackup name.
RESTORE CONTROLFILE FROM AUTOBACKUP;
# The set until command is used in case
# the database structure has changed in
# the most recent backups, and you want to
# recover to that point-in-time.
# In this way RMAN restores the database
# to the same structure that the
# database had at the specified time.
ALTER DATABASE MOUNT;
SET UNTIL SEQUENCE 34 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Starting restore at ...
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=98 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=114 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
.
.
.
Finished restore at ...
Starting recover at ...
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1
starting media recovery
.
.
.
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Hopefully all your restores are tests and if you are ever faced with a production restore it all goes well.
A DBA can do everything else in the world wrong -- EVERYTHING. We can fix that, we can resolve those issues. But if they mess up backup and recovery – forget about it. You are hosed.
~ Unknown author and DBA
These steps will restore and recovery the database after losing some or all of the critical database components. i.e. controlfiles, spfile, system.dbf, etc…
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
Once you have determined you need to restore the database login to sqlplus and issue the shutdown abort command.
SQL> shutdown abort
Restore the server files (Oracle Base and Home directories) from a backup if necessary.
Restore the Oracle directories.
Connect to rman and rman catalog.
# rman target / catalog rcvcat_login/rcvcat_passwd@rcvcat;
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 27 15:46:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database
set the DBID
see setting the DBID thread
RMAN> SET DBID 3800958841
executing command: SET DBID
database name is "HRPROD" and DBID is 3800958841
Startup the database in nomount
RMAN> startup nomount;
Oracle instance started
In order to restore the spfile the current spfile cannot be intact. You cannot restore over a current spfile. Either rename it or delete it if it currently exists before executing the 'restore spfile from autobackup' command.
Restore the SPFILE
RMAN> restore spfile from autobackup;
Starting restore at ...
.
.
.
Shutdown the database.
RMAN> shutdown immediate;
Oracle instance shut down
Restart the instance with the restored server parameter file.
RMAN> startup force nomount;
Determine the last archive log sequence backed up or available on disk.
Since I am using a RAID 5 configuration, if I lose more than one disk at a time I will lose everything, including all archive logs files on disk. I have an output from the last backup in my email that I can reference, but this information can also be obtained from your RMAN catalog.
RMAN> list backup of archivelog from scn=1;
List of Archived Logs in backup set 205213164
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 4221565 20-JUL-10 4421777 22-JUL-10
Some other useful RMAN commands are the following as well:
RMAN> list backup;
RMAN> list backup of database completed after '19-JUL-10';
Now that you know the Archive Log Seq you want to restore to, you can issue the following commands in the RUN block.
RMAN> RUN
{
# Restore autobackup of the control file.
# This example assumes that you have
# accepted the default format
# for the autobackup name.
RESTORE CONTROLFILE FROM AUTOBACKUP;
# The set until command is used in case
# the database structure has changed in
# the most recent backups, and you want to
# recover to that point-in-time.
# In this way RMAN restores the database
# to the same structure that the
# database had at the specified time.
ALTER DATABASE MOUNT;
SET UNTIL SEQUENCE 34 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Starting restore at ...
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=98 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=114 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
.
.
.
Finished restore at ...
Starting recover at ...
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1
starting media recovery
.
.
.
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Hopefully all your restores are tests and if you are ever faced with a production restore it all goes well.
A DBA can do everything else in the world wrong -- EVERYTHING. We can fix that, we can resolve those issues. But if they mess up backup and recovery – forget about it. You are hosed.
~ Unknown author and DBA
Wednesday, July 28, 2010
Using RMAN Catalog to get the DBID for your database for recovery
Please use RMAN and RMAN Recovery Catalog for all of your database backups. If you are not, stop what you are doing and FIX IT! You’ll be glad you did when (not if) you need to restore your database from a catastrophic failure (yes, I know this from experience).
If you need to restore your database including your spfile and control file, you’ll need to know the DBID of the database. You should have a record the DBID before disaster strikes, but in the event you don't there's still hope, and if you are using an RMAN catalog, that hope is just a few commands away. My backup process (RMAN including catalog, and NetBackup) includes an email sent to me upon completion, so I always have important database information in my email archives. If you have an RMAN catalog (and you should) you can get easily get the DBID of your database.
-- In this example the database is down.
-- From your target start RMAN and connect to the catalog.
First restore your file system if necessary. We want our init.ora to start the database.
#rman target / catalog rman_user/rman_user_passwd@RCVCAT
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 28 09:25:27 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database
-- The DBID is listed in RED below with the following RMAN command:
RMAN> list incarnation of database HRPROD;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
191122317 198582218 HRPROD 3800958841 CURRENT 1 25-JUN-10
-- Set your DBID while connected to RMAN
RMAN> SET DBID 3800958841;
executing command: SET DBID
database name is "DEV" and DBID is 3800958841
-- You can now proceed with the SPFILE restore, restore controlfile from autobackup, and restore database.
RMAN> startup nomount pfile='$ORACLE_HOME/dbs/inithrprod.ora';
Oracle instance started
RMAN>restore spfile from autobackup;
If you need to restore your database including your spfile and control file, you’ll need to know the DBID of the database. You should have a record the DBID before disaster strikes, but in the event you don't there's still hope, and if you are using an RMAN catalog, that hope is just a few commands away. My backup process (RMAN including catalog, and NetBackup) includes an email sent to me upon completion, so I always have important database information in my email archives. If you have an RMAN catalog (and you should) you can get easily get the DBID of your database.
-- In this example the database is down.
-- From your target start RMAN and connect to the catalog.
First restore your file system if necessary. We want our init.ora to start the database.
#rman target / catalog rman_user/rman_user_passwd@RCVCAT
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 28 09:25:27 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database
-- The DBID is listed in RED below with the following RMAN command:
RMAN> list incarnation of database HRPROD;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
191122317 198582218 HRPROD 3800958841 CURRENT 1 25-JUN-10
-- Set your DBID while connected to RMAN
RMAN> SET DBID 3800958841;
executing command: SET DBID
database name is "DEV" and DBID is 3800958841
-- You can now proceed with the SPFILE restore, restore controlfile from autobackup, and restore database.
RMAN> startup nomount pfile='$ORACLE_HOME/dbs/inithrprod.ora';
Oracle instance started
RMAN>restore spfile from autobackup;
Friday, July 9, 2010
Oracle 11gR2, RMAN, NetBackup, and the Media Layer (MML).
Oracle 11gR2 (11.2.0.1). This is a step-by-step approach to configuring Oracle 11gR2 with RMAN and a NetBackup Media Layer. This is a basic example of my configuration and not a in-depth discussion of RMAN and NetBackup MML. But it is enough to get started.
Verify your NetBackup installation. For 11gR2 you need a minimum of NBU 6.5.4 Master and Client.
If you are using version 6.5.4 you need to apply the Symantec EEB fix.
Details:
Symantec Bug ID: 1862252
Version: NetBackup_6.5.4
Installation Location: Client
Fixes Included:
(ET1940073) RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
There is a different EEB if you are on NBU 6.5.5 and I believe NBU 7.0 has resolved the issue with Oracle 11gR2.
Be sure to shutdown the database instance and then run oracle_link.
Run oracle_link (as oracle owner) in found in the netbackup/bin directory.
This will link $ORACLE_HOME/lib/libobk.so to NBU libobk.so64
libobk.so -> /usr/openv/netbackup/bin/libobk.so64
Update the bp.conf file on the client to reflect your client, master, and media servers
SERVER =
EMMSERVER=
MEDIA_SERVER=
CLIENT_NAME=
Look in the following netbackup directory for a sample script. Copy this script to the location you want to keep your NBU scripts.
netbackup/ext/db_ext/oracle/samples/rman
I have modified this script greatly over time and for many different backup types. This is just a example of the minimum updates required to use it. Even if you choose to not use a script a NetBackup Policy is mandatory. Create an Oracle Policy even if you do not use this script as a template.
Using an RMAN catalog? I highly recommend it.
I am starting with the script hot_database_backup.sh and will copy it to $ORACLE_HOME/scripts/rman and update it for my environment.
Update the permissions on the script so you can edit it.
scripts/rman% chmod 775 hot_database_backup.sh
I will modify the following lines.
ORACLE_HOME=your Oracle home
ORACLE_SID=your SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=/
Add the following for the catalog connection
CATALOG_CONNECT_STR=xxx/xxx@xxx (catalog login password and SID)
Now update the RMAN variable prior to the run command
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append <<>
Finally I add a line to email the results to me.
The command is all one line. Your host must already be configured to send mail.
cat $RMAN_LOG_FILE | mailx -s "${ORACLE_SID} on `uname -n` $BACKUP_TYPE $LOGMSG
- EXIT Status = $RSTAT" youremail@company.com
Before configuring RMAN, create a backup policy in NetBackup. This is a specific Oracle Policy for this particular client. The existence of a Policy is mandatory even if you run a command for the command line or call an RMAN script.
These are the minimum RMAN parameter configuration I recommend.
Start RMAN and connect to a target database and a recovery catalog
rman target / catalog catlogin/password@catalog_sid
RMAN> list incarnation;
RMAN> Register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
Run the RMAN SHOW command.
RMAN> SHOW ALL;
RMAN> SHOW RETENTION POLICY;
RMAN> SHOW RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now test a backup to your MML
RMAN> backup spfile;
You can view the Activity Monitor on your NetBackup Administrative Console GUI for activity of the newly created policy.
Example of the output you should see on the server.
RMAN> backup spfile;
Starting backup at 09-JUL-10
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=228 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=254 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 09-JUL-10
channel ORA_SBT_TAPE_1: finished piece 1 at 09-JUL-10
piece handle=01lic0o5_1_1 tag=TAG20100709T143028 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:35
Finished backup at 09-JUL-10
Starting Control File and SPFILE Autobackup at 09-JUL-10
piece handle=c-134461749-20100709-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 09-JUL-10
Verify your NetBackup installation. For 11gR2 you need a minimum of NBU 6.5.4 Master and Client.
If you are using version 6.5.4 you need to apply the Symantec EEB fix.
Details:
Symantec Bug ID: 1862252
Version: NetBackup_6.5.4
Installation Location: Client
Fixes Included:
(ET1940073) RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
There is a different EEB if you are on NBU 6.5.5 and I believe NBU 7.0 has resolved the issue with Oracle 11gR2.
Be sure to shutdown the database instance and then run oracle_link.
Run oracle_link (as oracle owner) in found in the netbackup/bin directory.
This will link $ORACLE_HOME/lib/libobk.so to NBU libobk.so64
libobk.so -> /usr/openv/netbackup/bin/libobk.so64
Update the bp.conf file on the client to reflect your client, master, and media servers
SERVER =
EMMSERVER=
MEDIA_SERVER=
CLIENT_NAME=
Look in the following netbackup directory for a sample script. Copy this script to the location you want to keep your NBU scripts.
netbackup/ext/db_ext/oracle/samples/rman
I have modified this script greatly over time and for many different backup types. This is just a example of the minimum updates required to use it. Even if you choose to not use a script a NetBackup Policy is mandatory. Create an Oracle Policy even if you do not use this script as a template.
Using an RMAN catalog? I highly recommend it.
I am starting with the script hot_database_backup.sh and will copy it to $ORACLE_HOME/scripts/rman and update it for my environment.
Update the permissions on the script so you can edit it.
scripts/rman% chmod 775 hot_database_backup.sh
I will modify the following lines.
ORACLE_HOME=your Oracle home
ORACLE_SID=your SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=/
Add the following for the catalog connection
CATALOG_CONNECT_STR=xxx/xxx@xxx (catalog login password and SID)
Now update the RMAN variable prior to the run command
$RMAN target $TARGET_CONNECT_STR catalog $CATALOG_CONNECT_STR msgno append <<>
Finally I add a line to email the results to me.
The command is all one line. Your host must already be configured to send mail.
cat $RMAN_LOG_FILE | mailx -s "${ORACLE_SID} on `uname -n` $BACKUP_TYPE $LOGMSG
- EXIT Status = $RSTAT" youremail@company.com
Before configuring RMAN, create a backup policy in NetBackup. This is a specific Oracle Policy for this particular client. The existence of a Policy is mandatory even if you run a command for the command line or call an RMAN script.
These are the minimum RMAN parameter configuration I recommend.
Start RMAN and connect to a target database and a recovery catalog
rman target / catalog catlogin/password@catalog_sid
RMAN> list incarnation;
RMAN> Register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
Run the RMAN SHOW command.
RMAN> SHOW ALL;
RMAN> SHOW RETENTION POLICY;
RMAN> SHOW RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now test a backup to your MML
RMAN> backup spfile;
You can view the Activity Monitor on your NetBackup Administrative Console GUI for activity of the newly created policy.
Example of the output you should see on the server.
RMAN> backup spfile;
Starting backup at 09-JUL-10
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=228 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=254 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 09-JUL-10
channel ORA_SBT_TAPE_1: finished piece 1 at 09-JUL-10
piece handle=01lic0o5_1_1 tag=TAG20100709T143028 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:35
Finished backup at 09-JUL-10
Starting Control File and SPFILE Autobackup at 09-JUL-10
piece handle=c-134461749-20100709-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 09-JUL-10
Subscribe to:
Posts (Atom)
Proactive Oracle DBA
This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...
