Saturday, May 21, 2011
Transport Database
Steps on Source Platform
Step-1: check compatibility
declare
b boolean;
begin
b:= dbms_tdb.check_db('Linux IA (32-bit)');
if b then dbms_output.put_line('YES your database can be transported on LINUX Platform');
else dbms_output.put_line('NO your database can not be transported on LINUX Platform');
end if;
end;
SQL> /
PL/SQL procedure successfully completed.
Step-2: shut immediate
Step-1: startup mount
Step-1: alter database open read only;
Step-1: create folder to hold converted data
Step-1: start RMAN
Step-1:
RMAN>
RMAN>
RMAN>
RMAN> CONVERT DATABASE transport script 'crdb.sql' new database 'newdb' TO PLATFORM 'Linux IA (32-bit)' FORMAT='C:\u01\app\oracle\product\10.2.0\db_transport/%U';
Starting convert at 23-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
External table SH.SALES_TRANSACTIONS_EXT found in the database
External table SYS.EXT_TABLE_CSV found in the database
Directory SYS.TRANS_RMAN_DIR found in the database
Directory SYS.TRANS_DIR found in the database
Directory SYS.DIR1 found in the database
Directory SYS.EXPDR found in the database
Directory SYS.SCOTT_DIR found in the database
Directory SYS.SYS_DIR found in the database
Directory SYS.DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYST
EM01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-SYSTEM_FNO-1_0BM2RLF6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSA
UX01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-SYSAUX_FNO-3_0CM2RLG0
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAM
PLE01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-EXAMPLE_FNO-5_0DM2RLGF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDO
TBS01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-UNDOTBS1_FNO-2_0EM2RLGN
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USER
S01.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-USERS_FNO-4_0FM2RLGU
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSP.
DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-TSP_FNO-6_0GM2RLH2
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TRAN
S_TSP.DBF
converted datafile=C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\DATA_D-ORCL_I-1
268521292_TS-TRANS_TSP_FNO-7_0HM2RLH3
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\CRDB.SQL on the tar
get platform to create database
Edit init.ora file C:\U01\APP\ORACLE\PRODUCT\10.2.0\DB_TRANSPORT\INIT_00M2RLF6_1
_0.ORA. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat
form
To change the internal database identifier, use DBNEWID Utility
Finished backup at 23-JAN-11
RMAN>
Steps on Target Platform
C:\>
C:\>
C:\>oradim -new -sid newdb
Instance created.
C:\>
C:\>
C:\>
C:\>
C:\>set oracle_sid=newdb
C:\>
C:\>
C:\>
C:\>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 23 19:39:38 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL>
SQL> create spfile from pfile;
File created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 285214228 bytes
Database Buffers 440401920 bytes
Redo Buffers 7094272 bytes
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log01.log' SIZE
50M,
9 GROUP 2 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log02.log' SIZE
50M,
10 GROUP 3 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\log03.log' SIZE
50M
11 DATAFILE
12 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\SYSTEM.dbf',
13 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\UNDOTBS1.dbf',
14 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\SYSAUX.dbf',
15 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\USERS.dbf',
16 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\EXAMPLE.dbf',
17 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\TSP.dbf',
18 'C:\u01\app\oracle\product\10.2.0\oradata\newdb\TRANS_TSP.dbf'
19 CHARACTER SET WE8MSWIN1252
20 ;
Control file created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 297797140 bytes
Database Buffers 427819008 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @C:\u01\app\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
2 package STANDARD AUTHID CURRENT_USER is -- careful on this line; SED edit occurs!
3
4 /********** Types and subtypes, do not reorder **********/
5 type BOOLEAN is (FALSE, TRUE);
6
7 type DATE is DATE_BASE;
8
9 type NUMBER is NUMBER_BASE;
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 301991444 bytes
Database Buffers 423624704 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL> @C:\u01\app\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select name from v$database;
NAME
---------
NEWDB
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL01.CTL
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL02.CTL
C:\U01\APP\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\CONTROL03.CTL
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
Data Recovery Advisor(DRA)
Oracle Database 11g - Data Recovery Advisor(DRA) is a new tool in Oracle database 11g. It is an integrated solution from Oracle Corporation.It is the primary tool used in preventing database failure.
What is a database failure?
Database failure is the corruption/loss of entire data files. data files are the physical location in which information is stored. Their loss causes database failure.
Data Recovery Advisor as it is popularly called DRA has the following uses :
1) It detects and analyzes database failures in a proactive fashion
2) It determines optimal repair strategy
3) It performs repair optionally
4) It verifies the success after repair
How do we access Data Recovery Advisor/DRA?
We can access the data recovery advisor/DRA using the following methods :
1) RMAN (Recovery Manager) client
2) Oracle Enterprise Manager (OEM)
What is MTTR? How is Data recovery advisor used to enhance database reliability:
MTTR stands for Mean Time To REcovery. Data recovery advisor(DRA) is used toreduce MTTR. It is an automated data repair tool. It is used to enhance database reliability.
------------- Practicle Demo (Windows Based) ----------------------------
C:\>set oracle_sid=orcl
C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 18 11:16:09 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
7 rows selected.
C:\>rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:17:24 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1278854133)
RMAN> backup database plus archivelog;
Starting backup at 18-MAY-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=751214999
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 18-MAY-11
Starting backup at 18-MAY-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_ANNNN_TAG20110518T111944_6X6V08QG_.BKP tag=TAG2011
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-MAY-11
Starting Control File and SPFILE Autobackup at 18-MAY-11
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2011_05_18\O1_MF_S_751461586_6X6V0C7T_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAY-11
RMAN> quit
Recovery Manager complete.
SQL>
SQL>
SQL> alter system archive log current;
System altered.
SQL>
SQL>
SQL>
Now rename any datafile for failure
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 318768552 bytes
Database Buffers 452984832 bytes
Redo Buffers 5259264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF'
SQL>
SQL>
SQL>
C:\>
C:\>
C:\>
C:\>rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:22:53 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1278854133, not open)
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-11 One or more non-system datafiles are missing
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-11 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
RMAN>
RMAN>
RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
Do you really want to execute the above repair (enter YES or NO)? y <---- Here it will ask for your choice
executing repair script
Starting restore at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518
_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518T111816_6X6TX
20110518T111816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-MAY-11
Starting recover at 18-MAY-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-MAY-11
repair failure complete
Do you want to open the database (enter YES or NO)? y <---- Here it will ask for your choice
database opened
RMAN>
RMAN>
RMAN> change failure 1082 priority low;
no failures found that match specification
RMAN>
RMAN>
RMAN>
RMAN> list failure;
no failures found that match specification
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> validate database;
Starting validate at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
input datafile file number=00007 name=C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12511 88326 1585054
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 59446
Index 0 12286
Other 0 4077
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 17836 71711 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 13936
Index 0 10200
Other 0 29708
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 12802 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 12799
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 247 640 1059228
File Name: C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 46
Other 0 256
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1689 12803 1065300
File Name: C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4544
Index 0 1261
Other 0 5306
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1427089
File Name: C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 121 128 1448699
File Name: C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 598
Finished validate at 18-MAY-11
RMAN>
What is a database failure?
Database failure is the corruption/loss of entire data files. data files are the physical location in which information is stored. Their loss causes database failure.
Data Recovery Advisor as it is popularly called DRA has the following uses :
1) It detects and analyzes database failures in a proactive fashion
2) It determines optimal repair strategy
3) It performs repair optionally
4) It verifies the success after repair
How do we access Data Recovery Advisor/DRA?
We can access the data recovery advisor/DRA using the following methods :
1) RMAN (Recovery Manager) client
2) Oracle Enterprise Manager (OEM)
What is MTTR? How is Data recovery advisor used to enhance database reliability:
MTTR stands for Mean Time To REcovery. Data recovery advisor(DRA) is used toreduce MTTR. It is an automated data repair tool. It is used to enhance database reliability.
------------- Practicle Demo (Windows Based) ----------------------------
C:\>set oracle_sid=orcl
C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 18 11:16:09 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
7 rows selected.
C:\>rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:17:24 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1278854133)
RMAN> backup database plus archivelog;
Starting backup at 18-MAY-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=751214999
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 18-MAY-11
Starting backup at 18-MAY-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_ANNNN_TAG20110518T111944_6X6V08QG_.BKP tag=TAG2011
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-MAY-11
Starting Control File and SPFILE Autobackup at 18-MAY-11
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2011_05_18\O1_MF_S_751461586_6X6V0C7T_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAY-11
RMAN> quit
Recovery Manager complete.
SQL>
SQL>
SQL> alter system archive log current;
System altered.
SQL>
SQL>
SQL>
Now rename any datafile for failure
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 318768552 bytes
Database Buffers 452984832 bytes
Redo Buffers 5259264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF'
SQL>
SQL>
SQL>
C:\>
C:\>
C:\>
C:\>rman target/
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:22:53 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1278854133, not open)
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-11 One or more non-system datafiles are missing
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-11 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
RMAN>
RMAN>
RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
Do you really want to execute the above repair (enter YES or NO)? y <---- Here it will ask for your choice
executing repair script
Starting restore at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518
_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518T111816_6X6TX
20110518T111816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-MAY-11
Starting recover at 18-MAY-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-MAY-11
repair failure complete
Do you want to open the database (enter YES or NO)? y <---- Here it will ask for your choice
database opened
RMAN>
RMAN>
RMAN> change failure 1082 priority low;
no failures found that match specification
RMAN>
RMAN>
RMAN>
RMAN> list failure;
no failures found that match specification
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> validate database;
Starting validate at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
input datafile file number=00007 name=C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12511 88326 1585054
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 59446
Index 0 12286
Other 0 4077
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 17836 71711 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 13936
Index 0 10200
Other 0 29708
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 12802 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 12799
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 247 640 1059228
File Name: C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 46
Other 0 256
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1689 12803 1065300
File Name: C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4544
Index 0 1261
Other 0 5306
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1427089
File Name: C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 121 128 1448699
File Name: C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 598
Finished validate at 18-MAY-11
RMAN>
Thursday, May 5, 2011
Archival Backup in 11g
1. New in Oracle 11g is a feature that permits you to create a backup that will not be affected by the normal retention policy defined for backups taken using RMAN.
2. Such backups once created may be exempted from the normal backup and recovery strategy.
3. These backups may be created on separate backup media other than the flash recovery area to be retained for a long time.
4. These kinds of backups may require to be maintained for business or legal requirements. They will not be displayed when you view the obsolete backups based on your retention policy.
5. This backup is "all-inclusive" and every datafile of the database, control file, server parameter file needed for restore and recovery is backed up to a single disk or tape device.
6. It also automatically generates an archive redo log backup to ensue that the database backup can be recovered to a consistent state.
7. Typically these backups are done to tape.
8. An archival backup are created with the BACKUP…KEEP command.
9. The KEEP option indicates that the backupset should be exempt from the retention policy either forever (KEEP FOREVER clause) or for a specified time period (KEEP UNTIL TIME).
10. The KEEP FOREVER clause requires the use of a recovery catalog database.
The RMAN syntax
An example:
RMAN>BACKUP DATABASE FORMAT '/%U.bkp' KEEP UNTIL TIME 'sysdate + 365';
Modify The archival backup settings by CHANGE Command
The CHANGE... NOKEEP may be used to remove the KEEP attributes of a backup, making it subject to the backup retention policy.
The CHANGE…KEEP command may be used to change a consistent backup into an archival backup.
RMAN> CHANGE BACKUP TAG 'full_orcl_backup' KEEP FOREVER;
Archival Backups using Enterprise Manager (OEM):
Database Control Home Page
- Availability tab
- Schedule Backup
- Schedule Customized Backup (Wizard)
- Setting Page
- Override Current Settings Button
- Policy
- Over Retention Policy Section
- You can select to keep a backup for a specified number of days.
Thanks,
2. Such backups once created may be exempted from the normal backup and recovery strategy.
3. These backups may be created on separate backup media other than the flash recovery area to be retained for a long time.
4. These kinds of backups may require to be maintained for business or legal requirements. They will not be displayed when you view the obsolete backups based on your retention policy.
5. This backup is "all-inclusive" and every datafile of the database, control file, server parameter file needed for restore and recovery is backed up to a single disk or tape device.
6. It also automatically generates an archive redo log backup to ensue that the database backup can be recovered to a consistent state.
7. Typically these backups are done to tape.
8. An archival backup are created with the BACKUP…KEEP command.
9. The KEEP option indicates that the backupset should be exempt from the retention policy either forever (KEEP FOREVER clause) or for a specified time period (KEEP UNTIL TIME).
10. The KEEP FOREVER clause requires the use of a recovery catalog database.
The RMAN syntax
KEEP {FOREVER| UNTIL TIME ‘SYSDATE +n’}
RESTORE POINT restore_point_name
where the restore point is a user-defined name associated with an SCN of the database corresponding to the time of the
creation of the restore point. The restore point would act as a label for the SCN to which the backup must be recovered to
be made consistent. The restore point option is not allowed with the CHANGE…KEEP command described later.
RMAN> BACKUP TAG FORBKP DATABASE KEEP FOREVER;
Modify The archival backup settings by CHANGE Command
The CHANGE…KEEP command may be used to change a consistent backup into an archival backup.
RMAN> CHANGE BACKUP TAG 'full_orcl_backup' KEEP FOREVER;
Archival Backups using Enterprise Manager (OEM):
Database Control Home Page
- Availability tab
- Schedule Backup
- Schedule Customized Backup (Wizard)
- Setting Page
- Override Current Settings Button
- Policy
- Over Retention Policy Section
- You can select to keep a backup for a specified number of days.
Thursday, April 28, 2011
Oracle Local Registry in Oracle 11g
Why OLR is Required
Before we get into this, we should see some of the improvements in Oracle 11gR2 RAC infrastructure. Until 11gR2, the CRS resources namely the OCR components and the voting disks were maintained in RAW or shared file systems. With the new 11gR2, the Oracle clusterware related files can be maintained in Oracle ASM (Automatic Storage Management). A feature that was introduced with Oracle 10g DB release. This ability to host OCR and Voting disks in ASM poses an interesting situation.
In order for the cluster resources to be up, the ASM needs to be up. If ASM needs to be up, the clusterware components should be functional. By having all the CRS and cluster resource information stored in OCR, this contradicting situation cannot be resolved unless somehow the cluster specific components detail is separately maintained from other resources/services.
As a solution, Oracle has come up with a new approach; the Oracle Local Registry. The Oracle Local registry maintains the node specific information and gets created with Oracle Clusterware installation of OCR. Since this maintains node specific resources, the clusterware components (crs,css,ctss,evm,gip, and asm) can be made available, with ASM being made available, this makes the OCR and voting disks access possible which eventually opens up the various cluster resources and components.
Without OLR, the clusterware resources will not start which in turn will not start the dependent components.
Note:
Oracle Clusterware includes two important components that manage configuration and node membership: Oracle Cluster Registry (OCR), which also includes the local component Oracle Local Registry (OLR), and voting disks.
1. OCR manages Oracle Clusterware and Oracle RAC database configuration information
2. OLR resides on every node in the cluster and manages Oracle Clusterware configuration information for each particular node
3. Voting disks manage information about node membership. Each voting disk must be accessible by all nodes in the cluster for nodes to be of the cluster
4. You can store OCR and voting disks on Oracle Automatic Storage Management (Oracle ASM), or a certified cluster file system.
Important Points for OLR
1. OCR contains information about all Oracle resources in the cluster.
2. OLR is a registry similar to OCR located on each node in a cluster, but contains information specific to each node. It contains manageability information about Oracle Clusterware, including dependencies between various services. Oracle High Availability Services uses this information. OLR is located on local storage on each node in a cluster. Its default location is in the path Grid_home/cdata/host_name.olr, where Grid_home is the Oracle Grid Infrastructure home, and host_name is the host name of the node.
3. The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.
4. GRID_HOME/cdata/localhost/$host_name.olr
e.g /u01/app/oracle/product/11.2.0/grid/cdata/localhost/node1.olr
even we can also check the location of this file from
/etc/oracle/olr.loc
5. OLR is automatically installed when we install Oracle Grid Infrastructure S/W
6. The OLR is managed by adding the –local option to the OCRCHECK, OCRDUMP, and OCRCONFIG commands as shown in the slide.
e.g $ ocrcheck –local
$ ocrdump –local –stdout
$ ocrconfig –local -manualbackup
7. You can define the backup location of the OLR using the ocrconfig –local –backuploc file_name command.
8. The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.
9. You should create a new OLR backup each time you modify your Oracle Restart configuration.
Thanks,
Before we get into this, we should see some of the improvements in Oracle 11gR2 RAC infrastructure. Until 11gR2, the CRS resources namely the OCR components and the voting disks were maintained in RAW or shared file systems. With the new 11gR2, the Oracle clusterware related files can be maintained in Oracle ASM (Automatic Storage Management). A feature that was introduced with Oracle 10g DB release. This ability to host OCR and Voting disks in ASM poses an interesting situation.
In order for the cluster resources to be up, the ASM needs to be up. If ASM needs to be up, the clusterware components should be functional. By having all the CRS and cluster resource information stored in OCR, this contradicting situation cannot be resolved unless somehow the cluster specific components detail is separately maintained from other resources/services.
As a solution, Oracle has come up with a new approach; the Oracle Local Registry. The Oracle Local registry maintains the node specific information and gets created with Oracle Clusterware installation of OCR. Since this maintains node specific resources, the clusterware components (crs,css,ctss,evm,gip, and asm) can be made available, with ASM being made available, this makes the OCR and voting disks access possible which eventually opens up the various cluster resources and components.
Without OLR, the clusterware resources will not start which in turn will not start the dependent components.
Note:
Oracle Clusterware includes two important components that manage configuration and node membership: Oracle Cluster Registry (OCR), which also includes the local component Oracle Local Registry (OLR), and voting disks.
1. OCR manages Oracle Clusterware and Oracle RAC database configuration information
2. OLR resides on every node in the cluster and manages Oracle Clusterware configuration information for each particular node
3. Voting disks manage information about node membership. Each voting disk must be accessible by all nodes in the cluster for nodes to be of the cluster
4. You can store OCR and voting disks on Oracle Automatic Storage Management (Oracle ASM), or a certified cluster file system.
Important Points for OLR
1. OCR contains information about all Oracle resources in the cluster.
2. OLR is a registry similar to OCR located on each node in a cluster, but contains information specific to each node. It contains manageability information about Oracle Clusterware, including dependencies between various services. Oracle High Availability Services uses this information. OLR is located on local storage on each node in a cluster. Its default location is in the path Grid_home/cdata/host_name.olr, where Grid_home is the Oracle Grid Infrastructure home, and host_name is the host name of the node.
3. The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.
4. GRID_HOME/cdata/localhost/$host_name.olr
e.g /u01/app/oracle/product/11.2.0/grid/cdata/localhost/node1.olr
even we can also check the location of this file from
/etc/oracle/olr.loc
5. OLR is automatically installed when we install Oracle Grid Infrastructure S/W
6. The OLR is managed by adding the –local option to the OCRCHECK, OCRDUMP, and OCRCONFIG commands as shown in the slide.
e.g $ ocrcheck –local
$ ocrdump –local –stdout
$ ocrconfig –local -manualbackup
7. You can define the backup location of the OLR using the ocrconfig –local –backuploc file_name command.
8. The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.
9. You should create a new OLR backup each time you modify your Oracle Restart configuration.
Thanks,
Subscribe to:
Posts (Atom)