rman管理4 CATALOG DB
catalog databaseの機能
1)rmanのリポジトリ(バックアップメタデータ)を集中的に格納し、target databaseのcontrolfileと同期できる
2)rmanを格納するバックアップスクリプト
catalog databaseの構成
1)個別のdatabaseを作成する必要がある
2)rmanメタデータを格納するtablespaceの確立
3)rmanを管理し、権限を与えるuserを確立する
4)listenerを起動しcatalog databaseをリンクする
--------catalog databaseでrmanスクリプトを格納する
1)rmanのリポジトリ(バックアップメタデータ)を集中的に格納し、target databaseのcontrolfileと同期できる
2)rmanを格納するバックアップスクリプト
catalog databaseの構成
1)個別のdatabaseを作成する必要がある
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
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
processor_group_name string
service_names string catdb
SQL>2)rmanメタデータを格納するtablespaceの確立
SQL> create tablespace catalogtbs datafile '/u01/app/oracle/oradata/catdb/catalogtbs01.dbf' size 100m;
Tablespace created.3)rmanを管理し、権限を与えるuserを確立する
SQL> create user rman identified by oracle default tablespace catalogtbs;
User created.
SQL>
SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.4)listenerを起動しcatalog databaseをリンクする
[oracle@ora11g ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-JUL-2015 15:12:08
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora11g)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 03-JUL-2015 15:11:18
Uptime 0 days 0 hr. 0 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "catdb" has 1 instance(s).
Instance "catdb", status READY, has 1 handler(s) for this service...
Service "catdbXDB" has 1 instance(s).
Instance "catdb", status READY, 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).
Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora11g ~]$
[oracle@ora11g admin]$ rman catalog rman/oracle@catdb
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 3 16:38:30 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> create catalog ; // catalog
recovery catalog created
-------- ， rman
SQL> conn rman/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AL TABLE
AL_V VIEW
BCB TABLE
BCB_V VIEW
BCF TABLE
BCF_V VIEW
BCR TABLE
BCR_V VIEW
BDF TABLE
BDF_V VIEW
BP TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BP_V VIEW
BRL TABLE
BRL_V VIEW
BS TABLE
BSF TABLE
BSF_V VIEW
BS_V VIEW
CCB TABLE
CCB_V VIEW
CCF TABLE
CCF_V VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CDF TABLE
CDF_V VIEW
CFS TABLE
CFS_V VIEW
CKP TABLE
CKP_V VIEW
CONF TABLE
CONFIG TABLE
CONFIG_V VIEW
CONF_V VIEW
DB TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DBINC TABLE
DBINC_V VIEW
DB_V VIEW
DF TABLE
DF_V VIEW
FB TABLE
FB_V VIEW
GRSP TABLE
GRSP_V VIEW
NODE TABLE
NODE_V VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
NRSP TABLE
NRSP_V VIEW
OFFR TABLE
OFFR_V VIEW
ORL TABLE
ORL_V VIEW
RCVER TABLE
RCVER_V VIEW
RC_ARCHIVED_LOG VIEW
RC_BACKUP_ARCHIVELOG_DETAILS VIEW
RC_BACKUP_ARCHIVELOG_SUMMARY VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_CONTROLFILE VIEW
RC_BACKUP_CONTROLFILE_DETAILS VIEW
RC_BACKUP_CONTROLFILE_SUMMARY VIEW
RC_BACKUP_COPY_DETAILS VIEW
RC_BACKUP_COPY_SUMMARY VIEW
RC_BACKUP_CORRUPTION VIEW
RC_BACKUP_DATAFILE VIEW
RC_BACKUP_DATAFILE_DETAILS VIEW
RC_BACKUP_DATAFILE_SUMMARY VIEW
RC_BACKUP_FILES VIEW
RC_BACKUP_PIECE VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_PIECE_DETAILS VIEW
RC_BACKUP_REDOLOG VIEW
RC_BACKUP_SET VIEW
RC_BACKUP_SET_DETAILS VIEW
RC_BACKUP_SET_SUMMARY VIEW
RC_BACKUP_SPFILE VIEW
RC_BACKUP_SPFILE_DETAILS VIEW
RC_BACKUP_SPFILE_SUMMARY VIEW
RC_CHECKPOINT VIEW
RC_CONTROLFILE_COPY VIEW
RC_COPY_CORRUPTION VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_DATABASE VIEW
RC_DATABASE_BLOCK_CORRUPTION VIEW
RC_DATABASE_INCARNATION VIEW
RC_DATAFILE VIEW
RC_DATAFILE_COPY VIEW
RC_LOG_HISTORY VIEW
RC_OFFLINE_RANGE VIEW
RC_PROXY_ARCHIVEDLOG VIEW
RC_PROXY_ARCHIVELOG_DETAILS VIEW
RC_PROXY_ARCHIVELOG_SUMMARY VIEW
RC_PROXY_CONTROLFILE VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_PROXY_COPY_DETAILS VIEW
RC_PROXY_COPY_SUMMARY VIEW
RC_PROXY_DATAFILE VIEW
RC_REDO_LOG VIEW
RC_REDO_THREAD VIEW
RC_RESTORE_POINT VIEW
RC_RESYNC VIEW
RC_RMAN_BACKUP_JOB_DETAILS VIEW
RC_RMAN_BACKUP_SUBJOB_DETAILS VIEW
RC_RMAN_BACKUP_TYPE VIEW
RC_RMAN_CONFIGURATION VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_RMAN_OUTPUT VIEW
RC_RMAN_STATUS VIEW
RC_SITE VIEW
RC_STORED_SCRIPT VIEW
RC_STORED_SCRIPT_LINE VIEW
RC_TABLESPACE VIEW
RC_TEMPFILE VIEW
RC_UNUSABLE_BACKUPFILE_DETAILS VIEW
RLH TABLE
RLH_V VIEW
ROUT TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ROUT_V VIEW
RR TABLE
RR_V VIEW
RSR TABLE
RSR_V VIEW
RT TABLE
RT_V VIEW
SCR TABLE
SCRL TABLE
SCRL_V VIEW
SCR_V VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SITE_DFATT TABLE
SITE_DFATT_V VIEW
SITE_TFATT TABLE
SITE_TFATT_V VIEW
TEMPRES TABLE
TF TABLE
TF_V VIEW
TS TABLE
TSATT TABLE
TSATT_V VIEW
TS_V VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
VPC_DATABASES TABLE
VPC_DATABASES_V VIEW
VPC_USERS TABLE
VPC_USERS_V VIEW
XAL TABLE
XAL_V VIEW
XCF TABLE
XCF_V VIEW
XDF TABLE
XDF_V VIEW
142 rows selected.
-------- （ controlfile rman catalog database）
[oracle@ora11g ~]$ rman target / catalog rman/oracle@catdb
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 3 16:47:19 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=293516844)
connected to recovery catalog database
RMAN> register database;
starting full resync of recovery catalog
full resync complete---------------
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
SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 293516844 PROD 1592300 01-JUL-15
SQL>--------catalog databaseでrmanスクリプトを格納する
1） （replace ）
create script prod_bk {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/ora_data/prod/full_bk_%U.dbf';
release channel c1;
release channel c2;
}
RMAN> create script prod_bk {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database format '/ora_data/prod/full_bk_%U.dbf';
5> release channel c1;
6> release channel c2;
7> }
created script prod_bk
created script prod_bk
2）
RMAN> print script prod_bk;
RMAN> print script prod_bk;
printing stored script: prod_bk
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/ora_data/prod/full_bk_%U.dbf';
release channel c1;
release channel c2;
}
------ catalog database
SQL> col SCRIPT_NAME for a30
SQL> col SCRIPT_COMMENT for a50
SQL> select * from RC_STORED_SCRIPT;
DB_KEY DB_NAME SCRIPT_NAME SCRIPT_COMMENT
---------- -------- ------------------------------ --------------------------------------------------
1 PROD prod_bk
SQL> col text for a50
SQL> select * from RC_STORED_SCRIPT_LINE
2 ;
DB_KEY SCRIPT_NAME LINE TEXT
---------- ------------------------------ ---------- --------------------------------------------------
1 prod_bk 1 {
1 prod_bk 2 allocate channel c1 type disk;
1 prod_bk 3 allocate channel c2 type disk;
1 prod_bk 4 backup database format '/ora_data/prod/full_bk_%U
.dbf';
1 prod_bk 5 release channel c1;
1 prod_bk 6 release channel c2;
1 prod_bk 7 }
7 rows selected.
3）
RMAN> run { execute script prod_bk;}
run {execute script prod_bk;}
4）
RMAN> delete script prod_bk;
deleted script: prod_bk