Oracle 備份與恢複學習筆記（12）

第十二章： Catalog Database 目錄庫

1、catalog database 的功能

1）集中存放rman的資料庫（備份的中繼資料），并且可以和target database 的controlfile同步

2）存放rman的備份腳本

2、catalog database 的配置

1）需要建立一個單獨的database

02:43:21 SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string

db_name string catdb

db_unique_name string catdb

global_names boolean FALSE

instance_name string catdb

lock_name_space string

log_file_name_convert string

service_names string catdb

02:43:30 SQL>

2）建立存放rman 中繼資料的tablespace

02:44:12 SQL> create tablespace cattbs

02:44:24 2 datafile '/u01/app/oracle/oradata/catdb/cattbs01.dbf' size 100m;

Tablespace created.

3)建立user，用于管理rman，并授權

02:45:10 SQL> create user rman identified by rman default tablespace cattbs;

User created.

02:45:33 SQL> grant connect ,resource ,recovery_catalog_owner to rman;

Grant succeeded.

4）啟動listener ，連結catalog database

[oracle@work admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-AUG-2011 02:48:01

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=work)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 19-AUG-2011 02:47:31

Uptime 0 days 0 hr. 0 min. 29 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=work)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "prod" has 1 instance(s).

Instance "prod", status READY, has 1 handler(s) for this service...

Service "prodXDB" has 1 instance(s).

Service "prod_XPT" has 1 instance(s).

The command completed successfully

[oracle@work admin]$ rman catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 02:49:05 2011

connected to recovery catalog database

RMAN> create catalog; // 建立catalog 的對象

recovery catalog created

RMAN>

--------建立以下對象，存儲rman 中繼資料

02:45:54 SQL> conn rman/rman

Connected.

02:50:16 SQL>

02:50:16 SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

NODE TABLE

DB TABLE

CONF TABLE

DBINC TABLE

CKP TABLE

TS TABLE

TSATT TABLE

DF TABLE

DFATT TABLE

TF TABLE

TFATT TABLE

OFFR TABLE

RR TABLE

RT TABLE

ORL TABLE

RLH TABLE

AL TABLE

BS TABLE

BP TABLE

BCF TABLE

CCF TABLE

XCF TABLE

BSF TABLE

BDF TABLE

CDF TABLE

XDF TABLE

BRL TABLE

BCB TABLE

CCB TABLE

SCR TABLE

SCRL TABLE

CONFIG TABLE

XAL TABLE

RSR TABLE

FB TABLE

RC_DATABASE VIEW

RC_DATABASE_INCARNATION VIEW

RC_RESYNC VIEW

RC_CHECKPOINT VIEW

RC_TABLESPACE VIEW

RC_DATAFILE VIEW

RC_TEMPFILE VIEW

RC_REDO_THREAD VIEW

RC_REDO_LOG VIEW

RC_LOG_HISTORY VIEW

RC_ARCHIVED_LOG VIEW

RC_BACKUP_SET VIEW

RC_BACKUP_PIECE VIEW

RC_BACKUP_DATAFILE VIEW

RC_BACKUP_CONTROLFILE VIEW

RC_BACKUP_SPFILE VIEW

RC_DATAFILE_COPY VIEW

RC_CONTROLFILE_COPY VIEW

RC_BACKUP_REDOLOG VIEW

RC_BACKUP_CORRUPTION VIEW

RC_COPY_CORRUPTION VIEW

RC_OFFLINE_RANGE VIEW

RC_STORED_SCRIPT VIEW

RC_STORED_SCRIPT_LINE VIEW

RC_PROXY_DATAFILE VIEW

RC_PROXY_CONTROLFILE VIEW

RC_RMAN_CONFIGURATION VIEW

RC_DATABASE_BLOCK_CORRUPTION VIEW

RC_PROXY_ARCHIVEDLOG VIEW

RC_RMAN_STATUS VIEW

ROUT TABLE

RC_RMAN_OUTPUT VIEW

RCVER TABLE

RC_BACKUP_FILES VIEW

RC_RMAN_BACKUP_SUBJOB_DETAILS VIEW

RC_RMAN_BACKUP_JOB_DETAILS VIEW

RC_BACKUP_SET_DETAILS VIEW

RC_BACKUP_PIECE_DETAILS VIEW

RC_BACKUP_COPY_DETAILS VIEW

RC_PROXY_COPY_DETAILS VIEW

RC_PROXY_ARCHIVELOG_DETAILS VIEW

RC_BACKUP_DATAFILE_DETAILS VIEW

RC_BACKUP_CONTROLFILE_DETAILS VIEW

RC_BACKUP_ARCHIVELOG_DETAILS VIEW

RC_BACKUP_SPFILE_DETAILS VIEW

RC_BACKUP_SET_SUMMARY VIEW

RC_BACKUP_DATAFILE_SUMMARY VIEW

RC_BACKUP_CONTROLFILE_SUMMARY VIEW

RC_BACKUP_ARCHIVELOG_SUMMARY VIEW

RC_BACKUP_SPFILE_SUMMARY VIEW

RC_BACKUP_COPY_SUMMARY VIEW

RC_PROXY_COPY_SUMMARY VIEW

RC_PROXY_ARCHIVELOG_SUMMARY VIEW

RC_UNUSABLE_BACKUPFILE_DETAILS VIEW

RC_RMAN_BACKUP_TYPE VIEW

90 rows selected.

02:50:19 SQL>

--------注冊目标庫（将目标庫controlfile的rman 中繼資料同步到 catalog database）

[oracle@work admin]$ rman target / catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 19 02:52:19 2011

connected to target database: PROD (DBID=170319990)

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

---------------檢視注冊資訊

02:50:19 SQL> desc rc_database;

Name Null? Type

----------------------------------------------------------------- -------- --------------------------------------------

DB_KEY NOT NULL NUMBER

DBINC_KEY NUMBER

DBID NOT NULL NUMBER

NAME NOT NULL VARCHAR2(8)

RESETLOGS_CHANGE# NOT NULL NUMBER

RESETLOGS_TIME NOT NULL DATE

02:54:28 SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS

---------- ---------- ---------- -------- ----------------- ---------

1 2 170319990 PROD 1452590 19-AUG-11

02:54:34 SQL>

----------利用catalog database存放rman 腳本

1）建立腳本（replace 是修改已經存在的腳本）

RMAN> create script users_bak {

2> backup datafile '/u01/app/oracle/oradata/prod/users01.dbf' format '/disk1/rman/prod/users_%s.bak';

3> }

created script users_bak

2）檢視腳本資訊

RMAN> print script users_bak;

printing stored script: users_bak

{backup datafile '/u01/app/oracle/oradata/prod/users01.dbf' format '/disk1/rman/prod/users_%s.bak';

}

------通過catalog database 檢視

03:02:45 SQL> col SCRIPT_NAME for a30

03:02:54 SQL> col SCRIPT_COMMENT for a50

03:03:01 SQL>

1* select * from RC_STORED_SCRIPT

DB_KEY DB_NAME SCRIPT_NAME SCRIPT_COMMENT

---------- -------- ------------------------------ --------------------------------------------------

1 PROD users_bak

03:03:23 SQL> col text for a50

03:03:30 SQL>

1* select * from RC_STORED_SCRIPT_LINE

DB_KEY SCRIPT_NAME LINE TEXT

---------- ------------------------------ ---------- --------------------------------------------------

1 users_bak 1 {backup datafile '/u01/app/oracle/oradata/prod/us

ers01.dbf' format '/disk1/rman/prod/users_%s.bak';

1 users_bak 2 }

3）運作腳本

RMAN> run { execute script users_bak;}

4）删除腳本

RMAN> delete script users_bak;

deleted script: users_bak

驗證catalog的恢複功能：

1、建立catalog庫，并注冊target database和同步catalog db

-----建立資料庫的備份

[oracle@rh4 ~]$ rman target / catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 19 11:43:13 2012

connected to target database: PROD (DBID=187338998)

RMAN> backup database format '/disk1/rman/prod/cold_bak/%d_%s.bak';

Starting backup at 19-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbf

input datafile fno=00006 name=/u01/app/oracle/oradata/prod/lxtbs1.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/prod/undotbs01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

channel ORA_DISK_1: starting piece 1 at 19-FEB-12

channel ORA_DISK_1: finished piece 1 at 19-FEB-12

piece handle=/disk1/rman/prod/cold_bak/PROD_71.bak tag=TAG20120219T114346 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 19-FEB-12

Starting Control File and SPFILE Autobackup at 19-FEB-12

piece handle=/u01/app/oracle/flash_recovery_area/PROD/autobackup/2012_02_19/o1_mf_s_775655094_7n0w1qr5_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 19-FEB-12

RMAN> list backup;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

389 Full 597.17M DISK 00:01:01 19-FEB-12

BP Key: 390 Status: AVAILABLE Compressed: NO Tag: TAG20120219T114346

Piece Name: /disk1/rman/prod/cold_bak/PROD_71.bak

List of Datafiles in backup set 389

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/system01.dbf

2 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/undotbs01.dbf

3 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/sysaux01.dbf

4 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/users01.dbf

5 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/example01.dbf

6 Full 634042 19-FEB-12 /u01/app/oracle/oradata/prod/lxtbs1.dbf

405 Full 7.27M DISK 00:00:02 19-FEB-12

BP Key: 412 Status: AVAILABLE Compressed: NO Tag: TAG20120219T114454

Piece Name: /u01/app/oracle/flash_recovery_area/PROD/autobackup/2012_02_19/o1_mf_s_775655094_7n0w1qr5_.bkp

Control File Included: Ckp SCN: 634074 Ckp time: 19-FEB-12

SPFILE Included: Modification time: 19-FEB-12

RMAN> exit

Recovery Manager complete.

2、模拟target database 被破壞，所有的controlfile丢失，重建控制檔案

10:53:47 SQL> select name from v$controlfile;

NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/prod/control01.ctl

/u01/app/oracle/oradata/prod/control02.ctl

/u01/app/oracle/oradata/prod/control03.ctl

11:47:36 SQL> alter database backup controlfile to trace;

Database altered.

11:47:52 SQL> !

[oracle@rh4 ~]$ ls -lt /u01/app/oracle/admin/prod/udump/

total 556

-rw-r----- 1 oracle oinstall 7759 Feb 19 11:47 prod_ora_7588.trc

-rw-r----- 1 oracle oinstall 736 Feb 19 11:44 prod_ora_12464.trc

-rw-r----- 1 oracle oinstall 708 Feb 19 11:31 prod_ora_12034.trc

-rw-r----- 1 oracle oinstall 737 Feb 19 11:28 prod_ora_11849.trc

-rw-r----- 1 oracle oinstall 17460 Feb 19 10:53 prod_ora_7566.trc

-rw-r----- 1 oracle oinstall 714 Feb 19 10:51 prod_ora_7563.trc

[oracle@rh4 ~]$ more /u01/app/oracle/admin/prod/udump/prod_ora_7588.trc

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/prod/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oracle/oradata/prod/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oracle/oradata/prod/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/prod/system01.dbf',

'/u01/app/oracle/oradata/prod/undotbs01.dbf',

'/u01/app/oracle/oradata/prod/sysaux01.dbf',

'/u01/app/oracle/oradata/prod/users01.dbf',

'/u01/app/oracle/oradata/prod/example01.dbf',

'/u01/app/oracle/oradata/prod/lxtbs1.dbf'

CHARACTER SET ZHS16GBK

;

sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 19 11:49:23 2012

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

11:49:23 SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

11:50:01 SQL> !

[oracle@rh4 ~]$ rm /u01/app/oracle/oradata/prod/*.ctl

[oracle@rh4 ~]$ !sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 19 11:50:26 2012

Connected to an idle instance.

11:50:26 SQL> startup

ORACLE instance started.

Total System Global Area 184549376 bytes

Fixed Size 1218412 bytes

Variable Size 71305364 bytes

Database Buffers 109051904 bytes

Redo Buffers 2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

11:50:57 20 ;

Control file created.

11:50:59 SQL> select status from v$instance;

STATUS

------------

MOUNTED

11:51:20 SQL> alter database open;

11:51:32 SQL> select name from v$datafile;

/u01/app/oracle/oradata/prod/system01.dbf

/u01/app/oracle/oradata/prod/undotbs01.dbf

/u01/app/oracle/oradata/prod/sysaux01.dbf

/u01/app/oracle/oradata/prod/users01.dbf

/u01/app/oracle/oradata/prod/example01.dbf

/u01/app/oracle/oradata/prod/lxtbs1.dbf

6 rows selected.

11:51:43 SQL>

3、新的控制檔案中的rman 備份的中繼資料丢失，無法檢視到備份資訊

[oracle@rh4 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 19 11:51:53 2012

using target database control file instead of recovery catalog

4、連接配接到catalog db 庫，恢複rman的中繼資料

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 19 11:52:06 2012

Oracle 備份與恢複學習筆記（12）

繼續閱讀

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

面試的三重境界

性能測試（并發負載壓力）測試分析－簡要篇

世界500強常用的管理方法和工具

賦予使用者dba權限--解釋以上：

一個四年java程式員的年終總結

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List