This is one of a few posts surrounding the installation of a single instance ASM database using grid 11.2.0.2 and database 11.2.0.2
The installations were carried out on the new Oracle Linux 6.2
Be aware that software versions up until 11.2.0.2 and BELOW are not certified to run 11.2.0.2 database and grid.
Therefore, there are funnies – but with a few changes here and there, they can be circumnavigated.
More to come…
Friday, June 8, 2012
Installing Oracle Grid Infrastructure - Summary
Thursday, February 2, 2012
ORA-00600 in PL/SQL after patch 11.2.0.3
I recently patched a standard edition 11g UAT database from version 11.2.0.2 to 11.2.0.3
The following week I had a look at the alert log. I was greeted with ORA-00600 messages.
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [78402], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [78402]
Later on that day the users complained about their sessions dying when executing a certain package call from the web front end.
On metalink I found a bug note resembling the error which effects versions of Oracle < 11.2, starting at version 10.2.0.3.
Bug 6068126
I opened up an SR with Oracle, shortly after they responded with a reference to Bug 13612575 (comes with a test case to reproduce the error)
It seems as if the bug was reintroduced in 11.2.0.3, though it comes in a different form, the conditions of its occurrence remain.
Both of these bugs surround the poor handling of REF CURSORs. Read the solutions in either of the bug notes to work around the problem.
The workaround suggested by the note was implement, and since then the alert log has been clear of errors.
Cheers
The following week I had a look at the alert log. I was greeted with ORA-00600 messages.
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [78402], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [78402]
Later on that day the users complained about their sessions dying when executing a certain package call from the web front end.
On metalink I found a bug note resembling the error which effects versions of Oracle < 11.2, starting at version 10.2.0.3.
Bug 6068126
I opened up an SR with Oracle, shortly after they responded with a reference to Bug 13612575 (comes with a test case to reproduce the error)
It seems as if the bug was reintroduced in 11.2.0.3, though it comes in a different form, the conditions of its occurrence remain.
Both of these bugs surround the poor handling of REF CURSORs. Read the solutions in either of the bug notes to work around the problem.
The workaround suggested by the note was implement, and since then the alert log has been clear of errors.
Cheers
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrwP3pvVGRrfww3huMy9z579hJ58V-1uidX4k4JyRrQxCFy7IEoUNiSy231JEEQAnErPPBGTmLX9swN2XDY46tbpbu8eUaZb6x9-IhdxmOq6D0Hlj7bZyaKDcqavMqxVabyUwFgbEWjNDB/s200/2043-fail-camera.jpg)
Wednesday, January 11, 2012
Flashback Version and Flashback Transaction Query
Flashback technology offers us the chance to traverse the undo tablespace for detailed version information of data changes within the database.
Flashback version is particularly useful for investigating complex applications covering a labyrinth of subroutines and conditions.
Below is a basic example of its use.
--PREP WORK
drop table ray.employee;
create table ray.employee (
id number,
name varchar2(60),
position varchar2(20)
);
insert into ray.employee VALUES (1,'TOPPER HARLEY','TOP GUN');
commit;
set lines 1000
select * from ray.employee;
ID NAME POSITION
---------- ------------------------------------------------------------ --------------------
1 TOPPER HARLEY TOP GUN
update ray.employee set name = 'SPUD WESTERN';
commit;
update ray.employee set name = 'JOHN MCWHEELY';
commit;
update ray.employee set name = 'DUKE SPENCER', position='PILOT';
commit;
delete from ray.employee;
commit;
--View the record versions
set lines 1000
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20
select TO_CHAR(VERSIONS_STARTTIME,'DD-MON-YY HH24:MI:SS') "start",
TO_CHAR(VERSIONS_ENDTIME,'DD-MON-YY HH24:MI:SS') "end",
versions_operation,
versions_xid,
t.*
from ray.employee versions between scn minvalue and maxvalue t
where id = 1;
start end VE VERSIONS_XID ID NAME POSITION
-------------------- -------------------- -- ---------------- ---- -------------------- --------------------
12-JAN-12 09:16:22 D 0900180032010000 1 DUKE SPENCER PILOT
12-JAN-12 09:16:13 12-JAN-12 09:16:22 U 080013002F010000 1 DUKE SPENCER PILOT
12-JAN-12 09:16:13 12-JAN-12 09:16:13 U 07001C0038010000 1 JOHN MCWHEELY TOP GUN
12-JAN-12 09:15:58 12-JAN-12 09:16:13 U 0600160035010000 1 SPUD WESTERN TOP GUN
12-JAN-12 09:15:58 1 TOPPER HARLEY TOP GUN
--Above, the start time and scn of the record version are given to use as well as the data of that version. We also see the type of transaction which generated the version (U/D). We can look as far back as the undo tablespace size and retention period allow us to.
--Using FLASHBACK QUERY with VERSION
--For more transaction related information, we can use FLASHBACK_TRANSACTION_QUERY view to see details about the transaction.
The privilege FLASHBACK ANY TABLE must be given to the person doing the selecting.
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN LOGON_USER FORMAT a10
COLUMN UNDO_SQL FORMAT a10
COLUMN TABLE_NAME FORMAT a10
COLUMN TABLE_OWNER FORMAT a10
COLUMN XID FORMAT a20
COLUMN ROWID FORMAT a20
COLUMN OPERATION FORMAT a20
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20
select *
from flashback_transaction_query where xid in (
select VERSIONS_XID
from ray.employee versions between scn minvalue and maxvalue r
where versions_operation = 'U');
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNE ROW_ID UNDO_SQL
-------------------- ---------- --------- ---------- --------- ---------- ------------ -------------------- ---------- ---------- ------------------- ----------
0600160035010000 1354577 12-JAN-12 1354589 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
0600160035010000 1354577 12-JAN-12 1354589 12-JAN-12 SYS 2 BEGIN
07001C0038010000 1354589 12-JAN-12 1354597 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
07001C0038010000 1354589 12-JAN-12 1354597 12-JAN-12 SYS 2 BEGIN
080013002F010000 1354597 12-JAN-12 1354600 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
080013002F010000 1354597 12-JAN-12 1354600 12-JAN-12 SYS 2 BEGIN
Flashback version is particularly useful for investigating complex applications covering a labyrinth of subroutines and conditions.
Below is a basic example of its use.
--PREP WORK
drop table ray.employee;
create table ray.employee (
id number,
name varchar2(60),
position varchar2(20)
);
insert into ray.employee VALUES (1,'TOPPER HARLEY','TOP GUN');
commit;
set lines 1000
select * from ray.employee;
ID NAME POSITION
---------- ------------------------------------------------------------ --------------------
1 TOPPER HARLEY TOP GUN
update ray.employee set name = 'SPUD WESTERN';
commit;
update ray.employee set name = 'JOHN MCWHEELY';
commit;
update ray.employee set name = 'DUKE SPENCER', position='PILOT';
commit;
delete from ray.employee;
commit;
--View the record versions
set lines 1000
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20
select TO_CHAR(VERSIONS_STARTTIME,'DD-MON-YY HH24:MI:SS') "start",
TO_CHAR(VERSIONS_ENDTIME,'DD-MON-YY HH24:MI:SS') "end",
versions_operation,
versions_xid,
t.*
from ray.employee versions between scn minvalue and maxvalue t
where id = 1;
start end VE VERSIONS_XID ID NAME POSITION
-------------------- -------------------- -- ---------------- ---- -------------------- --------------------
12-JAN-12 09:16:22 D 0900180032010000 1 DUKE SPENCER PILOT
12-JAN-12 09:16:13 12-JAN-12 09:16:22 U 080013002F010000 1 DUKE SPENCER PILOT
12-JAN-12 09:16:13 12-JAN-12 09:16:13 U 07001C0038010000 1 JOHN MCWHEELY TOP GUN
12-JAN-12 09:15:58 12-JAN-12 09:16:13 U 0600160035010000 1 SPUD WESTERN TOP GUN
12-JAN-12 09:15:58 1 TOPPER HARLEY TOP GUN
--Above, the start time and scn of the record version are given to use as well as the data of that version. We also see the type of transaction which generated the version (U/D). We can look as far back as the undo tablespace size and retention period allow us to.
--Using FLASHBACK QUERY with VERSION
--For more transaction related information, we can use FLASHBACK_TRANSACTION_QUERY view to see details about the transaction.
The privilege FLASHBACK ANY TABLE must be given to the person doing the selecting.
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN LOGON_USER FORMAT a10
COLUMN UNDO_SQL FORMAT a10
COLUMN TABLE_NAME FORMAT a10
COLUMN TABLE_OWNER FORMAT a10
COLUMN XID FORMAT a20
COLUMN ROWID FORMAT a20
COLUMN OPERATION FORMAT a20
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20
select *
from flashback_transaction_query where xid in (
select VERSIONS_XID
from ray.employee versions between scn minvalue and maxvalue r
where versions_operation = 'U');
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNE ROW_ID UNDO_SQL
-------------------- ---------- --------- ---------- --------- ---------- ------------ -------------------- ---------- ---------- ------------------- ----------
0600160035010000 1354577 12-JAN-12 1354589 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
0600160035010000 1354577 12-JAN-12 1354589 12-JAN-12 SYS 2 BEGIN
07001C0038010000 1354589 12-JAN-12 1354597 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
07001C0038010000 1354589 12-JAN-12 1354597 12-JAN-12 SYS 2 BEGIN
080013002F010000 1354597 12-JAN-12 1354600 12-JAN-12 SYS 1 UNKNOWN EMPLOYEE
080013002F010000 1354597 12-JAN-12 1354600 12-JAN-12 SYS 2 BEGIN
Saturday, January 7, 2012
Database Recovery #6 - Duplication/Cloning from RMAN backup
The steps below show how to perform a BACKUP BASED database duplication (not an active
duplication) The TARGET database is the source database you draw the backup pieces from, the AUXILIARY database is where you will restore to and recover up until.
A backup must be available and must be accessible from the auxiliary database via Oracle .Net
The server on which RMAN is being operated MUST have access the backup pieces. If the AUXILIARY db is going to reside on a different machine, the machine must have the backup location mapped from the machine on which the TARGET resides.
This guide was used with a TARGET instance in archivelog mode.
Its not necessary to have both instance using the same app tree but the TARGET and AUXILIARY must share the exact same application tree setup (versions, patches).
Compare with both trees using $ORACLE_HOME/OPatch/opatch lsinventory
--First backup the target database
. oraenv
orcl
rman target=/
backup database plus archivelog delete input;
--Pick a time, SCN or archivelog number to duplicate AUXILIARY database up until. Do this by looking
--at the catalog of your TARGET DATABASE to determine what is possible.
list backup;
list archivelog all;
--You want to make sure the TIME,SCN or SEQ you pick coincides with the backed up archivelogs you have available in the TARGET instance. RMAN will tell you flat out if you
--pick a value which is not possible to recover to.
--In the guide below, I took a backup a few hours earlier and backed up archivelog instead.
--PREPARE THE AUXILIARY DATABASE environemnt.
--Add new database to /etc/oratab : I use the SID - DUP
vi /etc/oratab
dup:/u01/app/oracle/product/11.1.0/db_1
--Add the new database to the listener.ora file
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = dup)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = dup)
)
)
--Add the new database to the tnsnames.ora file
DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SID = dup)
(SERVER = DEDICATED)
)
)
--Give the AUXILIARY db an init.ora file by duplication the target database init file and changing the values.
--Change the control_files parameter
--Change the core_dump_dest name
--Change the db_name
--Change the dispatchers name
--Add the convert file name parameters
--Change the log_archive_dest_1 location
----DB_FILE_NAME_CONVERT
----LOG_FILE_NAME_CONVERT
[oracle@vmorcl ~]$ cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initdup.ora
[oracle@vmorcl ~]$ vi $ORACLE_HOME/dbs/initdup.ora
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/dup/control01.ctl','/u01/app/oracle/oradata/dup/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/dup/cdump'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain='localdomain'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_name='dup'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10000000000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_dest_1='LOCATION=/redologs/stage2'
*.log_buffer=10485760
*.memory_max_target=512M
*.memory_target=512M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'
*.processes=150
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=2304K
*.sga_max_size=372M# internally adjusted
*.sga_target=372M
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTS'
--Create the datafile folders
mkdir /u01/app/oracle/oradata/dup/
--Create the diagnostic folders
mkdir /u01/app/oracle/admin/dup/dup
mkdir /u01/app/oracle/admin/dup/adump
mkdir /u01/app/oracle/admin/dup/bdump
mkdir /u01/app/oracle/admin/dup/cdump
--Reload the listener.
lsnrctl reload
--Duplicate password file
cp /u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.1.0/db_1/dbs/orapwdup
--Startup the duplicate database with nomount
. oraenv
dup
sqlplus /nolog
conn / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initdup.ora';
exit;
--Connect to the target and auxiliary databases
rman target=sys/oracle@orcl auxiliary=sys/oracle@dup
--Duplicate the database
--You must have a full backup (cold or warm), and you must specify an until sequence,time or scn.
--Below I use up until past the last sequence number in the archivelog backup
run {
DUPLICATE TARGET DATABASE TO dup;
SET UNTIL SEQUENCE 1;
}
--Provided all went well, your instance should be up and running.
duplication) The TARGET database is the source database you draw the backup pieces from, the AUXILIARY database is where you will restore to and recover up until.
A backup must be available and must be accessible from the auxiliary database via Oracle .Net
The server on which RMAN is being operated MUST have access the backup pieces. If the AUXILIARY db is going to reside on a different machine, the machine must have the backup location mapped from the machine on which the TARGET resides.
This guide was used with a TARGET instance in archivelog mode.
Its not necessary to have both instance using the same app tree but the TARGET and AUXILIARY must share the exact same application tree setup (versions, patches).
Compare with both trees using $ORACLE_HOME/OPatch/opatch lsinventory
--First backup the target database
. oraenv
orcl
rman target=/
backup database plus archivelog delete input;
--Pick a time, SCN or archivelog number to duplicate AUXILIARY database up until. Do this by looking
--at the catalog of your TARGET DATABASE to determine what is possible.
list backup;
list archivelog all;
--You want to make sure the TIME,SCN or SEQ you pick coincides with the backed up archivelogs you have available in the TARGET instance. RMAN will tell you flat out if you
--pick a value which is not possible to recover to.
--In the guide below, I took a backup a few hours earlier and backed up archivelog instead.
--PREPARE THE AUXILIARY DATABASE environemnt.
--Add new database to /etc/oratab : I use the SID - DUP
vi /etc/oratab
dup:/u01/app/oracle/product/11.1.0/db_1
--Add the new database to the listener.ora file
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = dup)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = dup)
)
)
--Add the new database to the tnsnames.ora file
DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SID = dup)
(SERVER = DEDICATED)
)
)
--Give the AUXILIARY db an init.ora file by duplication the target database init file and changing the values.
--Change the control_files parameter
--Change the core_dump_dest name
--Change the db_name
--Change the dispatchers name
--Add the convert file name parameters
--Change the log_archive_dest_1 location
----DB_FILE_NAME_CONVERT
----LOG_FILE_NAME_CONVERT
[oracle@vmorcl ~]$ cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initdup.ora
[oracle@vmorcl ~]$ vi $ORACLE_HOME/dbs/initdup.ora
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/dup/control01.ctl','/u01/app/oracle/oradata/dup/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/dup/cdump'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain='localdomain'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_name='dup'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10000000000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_dest_1='LOCATION=/redologs/stage2'
*.log_buffer=10485760
*.memory_max_target=512M
*.memory_target=512M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'
*.processes=150
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=2304K
*.sga_max_size=372M# internally adjusted
*.sga_target=372M
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTS'
--Create the datafile folders
mkdir /u01/app/oracle/oradata/dup/
--Create the diagnostic folders
mkdir /u01/app/oracle/admin/dup/dup
mkdir /u01/app/oracle/admin/dup/adump
mkdir /u01/app/oracle/admin/dup/bdump
mkdir /u01/app/oracle/admin/dup/cdump
--Reload the listener.
lsnrctl reload
--Duplicate password file
cp /u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.1.0/db_1/dbs/orapwdup
--Startup the duplicate database with nomount
. oraenv
dup
sqlplus /nolog
conn / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initdup.ora';
exit;
--Connect to the target and auxiliary databases
rman target=sys/oracle@orcl auxiliary=sys/oracle@dup
--Duplicate the database
--You must have a full backup (cold or warm), and you must specify an until sequence,time or scn.
--Below I use up until past the last sequence number in the archivelog backup
run {
DUPLICATE TARGET DATABASE TO dup;
SET UNTIL SEQUENCE 1;
}
--Provided all went well, your instance should be up and running.
New Version of Putty
Your favourite terminal application – putty – has been upgraded. It seems to use the same registry as your regular putty, so there is no need to reconfigure. Just run it and use your saved configurations.
Transparency and automatic reconnect after coming out of standby are some of the new features.
Have a look at:
Thursday, December 22, 2011
Database Recovery #5 - RMAN Recover everything
--Restoring a database from scratch.
--These steps remove the entire instance, and the restore and recover from backup pieces outside the FRA. Please be careful
with the remove script.
--Prep database cleanout
sqlplus /nolog
conn / as sysdba
!rm /tmp/drop_orcl.sql
spool /tmp/drop_orcl.sql
select '!rm '||value from v$parameter where name = 'spfile';
select '!rm '||replace(value,'spfile','init') from v$parameter where name = 'spfile';
select '!rm '||name from v$controlfile;
select '!rm '||file_name from dba_data_files;
select '!rm '||member from v$logfile;
select '!rm -fr '||value||'/ORCL' from v$parameter where name = 'db_recovery_file_dest';
spool off
exit
--Take a backup and note the DBID of the database you are going to use to restore the instance.
[oracle@vmorcl backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 20:29:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1296288227)
--Set controlfile autobackup ON
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
--Clear out backups and backup the database, make sure SPFILE is backed up
delete noprompt backup;
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;
--List and copy the backup pieces from the recovery area to another location
list backup;
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6ygq1p_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_nnndf_MASTER_DB_7h6ygrg9_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6yh7t6_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_22/o1_mf_s_770589705_7h6yh9gp_.bkp /u01/backup/';
--Check the backups were moved
host 'ls -rtl /u01/backup';
exit
--Once files are in the location, clear out the instance using the spool commands from the prep, and kill the instance.
--MAKE SURE YOU HAVE THE DBID BEFORE FIRING THIS OFF!
sqlplus / as sysdba @/tmp/drop_orcl.sql
--This should throw an error if the instance has be removed.create table sys.test as select * from v$instance;
--shutdown
shutdown abort;
exit;
--Right, the database is GONE. Time to restore and recover.--set DBID
rman target=/
set DBID=1296288227
--You have no spfile or initfile, the only way to startup is using the default init.ora file supplied by oracle
startup nomount force;
--Restore the spfile from the autobackup piece.run {
restore spfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
shutdown abort;
--Startup using the restored spfile
startup nomount;
--Restore the control file from the same autobackup piece
run {
restore controlfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
--Mount the database with the new found control files.
alter database mount;
--Add the rest of the backup pieces to the catalog
catalog start with '/u01/backup/';
--Restore the database to its former glory.
--Get the last sequence number.
list backup of archivelog all;
--Restore the database. Set the UNTIL SEQUENCE clause to the highest sequence + 1
run {
set until sequence 3;
restore database;
recover database;
}
--Be a hero - open the database with reset logs since we used a backup control file.
alter database open resetlogs;
--Once the database is open, it is important to take a NEW backup!
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;
--Done. To handle the password file, either create a new one, or copy the one from the original hosts app tree.
--These steps remove the entire instance, and the restore and recover from backup pieces outside the FRA. Please be careful
with the remove script.
--Prep database cleanout
sqlplus /nolog
conn / as sysdba
!rm /tmp/drop_orcl.sql
spool /tmp/drop_orcl.sql
select '!rm '||value from v$parameter where name = 'spfile';
select '!rm '||replace(value,'spfile','init') from v$parameter where name = 'spfile';
select '!rm '||name from v$controlfile;
select '!rm '||file_name from dba_data_files;
select '!rm '||member from v$logfile;
select '!rm -fr '||value||'/ORCL' from v$parameter where name = 'db_recovery_file_dest';
spool off
exit
--Take a backup and note the DBID of the database you are going to use to restore the instance.
[oracle@vmorcl backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 20:29:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1296288227)
--Set controlfile autobackup ON
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
--Clear out backups and backup the database, make sure SPFILE is backed up
delete noprompt backup;
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;
--List and copy the backup pieces from the recovery area to another location
list backup;
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6ygq1p_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_nnndf_MASTER_DB_7h6ygrg9_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6yh7t6_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_22/o1_mf_s_770589705_7h6yh9gp_.bkp /u01/backup/';
--Check the backups were moved
host 'ls -rtl /u01/backup';
exit
--Once files are in the location, clear out the instance using the spool commands from the prep, and kill the instance.
--MAKE SURE YOU HAVE THE DBID BEFORE FIRING THIS OFF!
sqlplus / as sysdba @/tmp/drop_orcl.sql
--This should throw an error if the instance has be removed.create table sys.test as select * from v$instance;
--shutdown
shutdown abort;
exit;
--Right, the database is GONE. Time to restore and recover.--set DBID
rman target=/
set DBID=1296288227
--You have no spfile or initfile, the only way to startup is using the default init.ora file supplied by oracle
startup nomount force;
--Restore the spfile from the autobackup piece.run {
restore spfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
shutdown abort;
--Startup using the restored spfile
startup nomount;
--Restore the control file from the same autobackup piece
run {
restore controlfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
--Mount the database with the new found control files.
alter database mount;
--Add the rest of the backup pieces to the catalog
catalog start with '/u01/backup/';
--Restore the database to its former glory.
--Get the last sequence number.
list backup of archivelog all;
--Restore the database. Set the UNTIL SEQUENCE clause to the highest sequence + 1
run {
set until sequence 3;
restore database;
recover database;
}
--Be a hero - open the database with reset logs since we used a backup control file.
alter database open resetlogs;
--Once the database is open, it is important to take a NEW backup!
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;
--Done. To handle the password file, either create a new one, or copy the one from the original hosts app tree.
Tuesday, December 20, 2011
Database Recovery #4 - RMAN Recover control file without FLASH_RECOVERY_AREA
--Open rman
rman target=/
--Ensure autobackup is set to on
configure controlfile autobackup on;
--Take note of the DBID, delete backups, make a new backup of the datbase and logs.
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;
--Ensure you have a copy of the controlfile
list backup of controlfile;
exit;
--Move autobackup off to the a new location OUT OF THE instance's FLASH_RECOVERY_AREA
--%F is not just a filename format. It means FOLDERS as well. /FRA/{%F} = /FRA/{ORCL/AUTOBACKUP/YYYYMMDD}
mv /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_20/o1_mf_s_770381286_7h0ly6sk_.bkp /u01/backup/ORCL/autobackup/2011_12_20
--Connect to instance and remove the control files from the database
sqlplus /nolog
conn / as sysdba
spool /tmp/delete_controlfile.sql
select '!rm '||name from v$controlfile;
spool off
--Drop controlfiles
@/tmp/delete_controlfile.sql
--Shutdown abort
shutdown abort;
exit;
--Reconnect to RMAN to recover the database
rman target /
--Startup database in NOMOUNT state and set the DBID (DBID is used when no FRA is available)
startup nomount;
set dbid=1296288227
--Restore the controlfile from the relocated autobackup piece from the new FRA location
restore controlfile from autobackup db_recovery_file_dest='/u01/backup';
--Mount, recover and open resetlogs the instance.
alter database mount;
recover database;
alter database open resetlogs;
--Delete backups from previous incarnation
delete noprompt backup;
--Take a new backup
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;
rman target=/
--Ensure autobackup is set to on
configure controlfile autobackup on;
--Take note of the DBID, delete backups, make a new backup of the datbase and logs.
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;
--Ensure you have a copy of the controlfile
list backup of controlfile;
exit;
--Move autobackup off to the a new location OUT OF THE instance's FLASH_RECOVERY_AREA
--%F is not just a filename format. It means FOLDERS as well. /FRA/{%F} = /FRA/{ORCL/AUTOBACKUP/YYYYMMDD}
mv /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_20/o1_mf_s_770381286_7h0ly6sk_.bkp /u01/backup/ORCL/autobackup/2011_12_20
--Connect to instance and remove the control files from the database
sqlplus /nolog
conn / as sysdba
spool /tmp/delete_controlfile.sql
select '!rm '||name from v$controlfile;
spool off
--Drop controlfiles
@/tmp/delete_controlfile.sql
--Shutdown abort
shutdown abort;
exit;
--Reconnect to RMAN to recover the database
rman target /
--Startup database in NOMOUNT state and set the DBID (DBID is used when no FRA is available)
startup nomount;
set dbid=1296288227
--Restore the controlfile from the relocated autobackup piece from the new FRA location
restore controlfile from autobackup db_recovery_file_dest='/u01/backup';
--Mount, recover and open resetlogs the instance.
alter database mount;
recover database;
alter database open resetlogs;
--Delete backups from previous incarnation
delete noprompt backup;
--Take a new backup
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;
Subscribe to:
Posts (Atom)