oracle11g新建实例,oracle11gRMAN的catalog的创建和使用

oracle11gRMAN的catalog的创建和使用

1. 创建恢复目录

一般分为三大步骤

创建存放恢复目录的数据库(或使用已存在的数据库)

创建恢复目录的的所有者

创建恢复目录

1.1 创建数据库用于存储恢复目录schema及恢复目录数据

[root@zaibei-db ~]# su - oracle

[oracle@zaibei-db ~]$ env | grep ORA

ORACLE_SID=racdb_st

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@zaibei-db ~]$ export DISPLAY=192.168.3.77:0.0

[oracle@zaibei-db ~]$ export ORACLE_SID=catalog

[oracle@zaibei-db ~]$ dbca

使用dbca创建catalog数据库

[oracle@zaibei-db ~]$ sqlplus /nolog

SQL> conn / as sysdba;

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

catalog

SQL> CREATE TABLESPACE tbs_catalog DATAFILE

'/u01/app/oracle/oradata/CATALOG/datafile/tbs_catalog01.dbf' SIZE

1G AUTOEXTEND ON NEXT 100M;

SQL> CREATE USER rman_cata IDENTIFIED BY 123456 TEMPORARY

TABLESPACE temp DEFAULT TABLESPACE tbs_catalog QUOTA UNLIMITED ON

tbs_catalog;

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman_cata;

SQL> conn rman_cata/123456;

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

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

CREATE SESSION

ALTER SESSION

CREATE TABLE

CREATE CLUSTER

CREATE SYNONYM

CREATE VIEW

CREATE SEQUENCE

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

11 rows selected.

SQL> SELECT * FROM session_roles;

ROLE

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

RECOVERY_CATALOG_OWNER

修改文件，添加如下内容，并测试连接

[oracle@zaibei-db ~]$ vi

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

RMAN_CATA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = zaibei-db)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = catalog)

)

)

[oracle@zaibei-db ~]$ env | grep ORA

ORACLE_SID=catalog

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@zaibei-db ~]$ sqlplus /nolog

SQL> conn system/oracle@rman_cata;

RAC节点1添加TNS，并测试连接

[oracle@jinhu-db01 ~]$ vi

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

RMAN_CATA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = zaibei-db)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = catalog)

)

)

[oracle@jinhu-db01 ~]$ sqlplus system/oracle@rman_cata

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 11:58:12 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

RMAN> CONNECT CATALOG rman_cata/123456@rman_cata;

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> EXIT;

注册数据库

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl catalog

rman_cata/123456@rman_cata

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 12:01:32 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

取消注册数据库

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl catalog

rman_cata/123456@rman_cata

RMAN> UNREGISTER DATABASE;

删除恢复目录

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl

RMAN> CONNECT CATALOG rman_cata/123456@rman_cata;

RMAN> DROP CATALOG;

DG中的物理备库注册到CATALOG时报错，无法注册

[oracle@zaibei-db ~]$ env | grep ORA

ORACLE_SID=racdb_st

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@zaibei-db ~]$ rman target sys/oracle@dg_psby_1

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 12:28:13 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

RMAN> CONNECT CATALOG rman_cata/123456@rman_cata;

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog already exists

RMAN> REGISTER DATABASE;

RMAN-00571:

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

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

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

RMAN-00571:

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

RMAN-03002: failure of register command at 04/21/2018

12:28:48

RMAN-01005: Mounted control file type must be CURRENT to

register the database

1.2 在存储恢复目录的数据库创建表空间用于存储恢复目录schema及恢复目录数据

[oracle@db01 datafile]$ sqlplus /nolog

SQL> conn / as sysdba;

SQL> CREATE TABLESPACE tbs_catalog DATAFILE

'/u01/app/oracle/oradata/CATALOG/datafile/tbs_catalog01.dbf' SIZE

1G AUTOEXTEND ON NEXT 100M;

SQL> CREATE USER rman_cata IDENTIFIED BY 123456 TEMPORARY

TABLESPACE temp DEFAULT TABLESPACE tbs_catalog QUOTA UNLIMITED ON

tbs_catalog;

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman_cata;

SQL> conn rman_cata/123456;

Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE

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

CREATE SESSION

ALTER SESSION

CREATE TABLE

CREATE CLUSTER

CREATE SYNONYM

CREATE VIEW

CREATE SEQUENCE

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

11 rows selected.

SQL> SELECT * FROM session_roles;

ROLE

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

RECOVERY_CATALOG_OWNER

RAC节点1添加TNS，并测试连接

[oracle@jinhu-db01 ~]$ vi

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

RMAN_CATA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL =

TCP)(HOST = zaibei-db)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = catalog)

)

)

[oracle@jinhu-db01 ~]$ sqlplus system/oracle@rman_cata

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 11:58:12 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

RMAN> CONNECT CATALOG rman_cata/123456@rman_cata;

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> EXIT;

注册数据库

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl catalog

rman_cata/123456@rman_cata

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 12:01:32 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

2. 基于恢复目录的备份

1>.查看相关信息

[oracle@jinhu-db01 ~]$ rman target sys/oracle@dg_orcl catalog

rman_cata/123456@rman_cata

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr

21 12:09:16 2018

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: ORCL (DBID=1501478763)

connected to recovery catalog database

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB

ID STATUS Reset SCN Reset

Time

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

----------

1 2 ORCL 1501478763 CURRENT

1 17-APR-18

RMAN> list backup summary;

List of Backups

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

Key TY

LV S Device Type Completion Time #Pieces #Copies Compressed

Tag

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

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

410 B F A

DISK 19-APR-18 1 1 NO JINHU_FULL

411 B F A

DISK 19-APR-18 1 1 NO JINHU_FULL

412 B F A

DISK 19-APR-18 1 1 NO TAG20180419T144239

413 B 2 A

DISK 20-APR-18 1 1 NO JINHU_LEV2

414 B 2 A

DISK 20-APR-18 1 1 NO JINHU_LEV2

415 B 2 A

DISK 20-APR-18 1 1 NO JINHU_LEV2

416 B 2 A

DISK 20-APR-18 1 1 NO JINHU_LEV2

417 B A A

DISK 20-APR-18 1 1 NO TAG20180420T142546

418 B A A

DISK 20-APR-18 1 1 NO TAG20180420T142546

419 B F A

DISK 20-APR-18 1 1 NO BAK_CTLFILE

420 B F A

DISK 20-APR-18 1 1 NO SPFILE

RMAN> crosscheck copy;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 instance=oracl11 device

type=DISK

specification does not match any datafile copy in the

repository

specification does not match any control file copy in the

repository

validation succeeded for archived log

archived log file

name=+FRA/orcl/archivelog/2018_04_20/thread_1_seq_178.441.973952745

RECID=407 STAMP=973952745

validation succeeded for archived log

archived log file

name=+FRA/orcl/archivelog/2018_04_20/thread_1_seq_179.440.973957309

RECID=409 STAMP=973957314

validation succeeded for archived log

...

Crosschecked 22 objects

RMAN> list backup by file;

List of Datafile Backups

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

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies

Compressed Tag

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

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

1 413 B 2 A 1261243 20-APR-18 1 1 NO JINHU_LEV2

410 B F A

1154012 19-APR-18

1 1 NO JINHU_FULL

2 414 B 2 A 1261241 20-APR-18 1 1 NO JINHU_LEV2

410 B F A

1154012 19-APR-18

1 1 NO JINHU_FULL

3 414 B 2 A 1261241 20-APR-18 1 1 NO JINHU_LEV2

410 B F A

1154012 19-APR-18

1 1 NO JINHU_FULL

4 413 B 2 A 1261243 20-APR-18 1 1 NO JINHU_LEV2

410 B F A

1154012 19-APR-18

1 1 NO JINHU_FULL

5 414 B 2 A 1261241 20-APR-18 1 1 NO JINHU_LEV2

410 B F A

1154012 19-APR-18

1 1 NO JINHU_FULL

List of Archived Log Backups

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

Thrd Seq Low SCN Low

Time BS Key S #Pieces #Copies

Compressed Tag

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

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

1 176 1260214 20-APR-18 417 A 1 1 NO TAG20180420T142546

1 177 1260238 20-APR-18 417 A 1 1 NO TAG20180420T142546

1 178 1261598 20-APR-18 418 A 1 1 NO TAG20180420T142546

2 111 1260235 20-APR-18 417 A 1 1 NO TAG20180420T142546

2 112 1261602 20-APR-18 418 A 1 1 NO TAG20180420T142546

List of Control File Backups

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

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag

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

---

1261657 20-APR-18

419 A

1 1 NO BAK_CTLFILE

1261461 20-APR-18

416 A

1 1 NO JINHU_LEV2

1154012 19-APR-18

412 A

1 1 NO TAG20180419T144239

1154012 19-APR-18

411 A

1 1 NO JINHU_FULL

List of SPFILE Backups

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

Modification Time BS Key S #Pieces #Copies

Compressed Tag

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

---

20-APR-18 420 A 1 1 NO SPFILE

20-APR-18 415 A 1 1 NO JINHU_LEV2

19-APR-18 411 A 1 1 NO JINHU_FULL

RMAN> list backupset tag=JINHU_FULL;

List of Backup Sets

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

BS Key Type LV Size Device Type

Elapsed Time Completion Time

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

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

410 Full 1.06G DISK 00:00:54 19-APR-18

BP Key: 421 Status: AVAILABLE Compressed:

NO Tag: JINHU_FULL

Piece Name:

/home/oracle/rman/20180419/jinhu_full_0ft0o27k_1_1_20180419

List of Datafiles in backup set 410

File LV Type Ckp SCN Ckp Time Name

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

----

1 Full 1154012 19-APR-18

+DATA/orcl/datafile/system.268.973698037

2 Full 1154012 19-APR-18

+DATA/orcl/datafile/sysaux.269.973698047

3 Full 1154012 19-APR-18

+DATA/orcl/datafile/undotbs1.270.973698055

4 Full 1154012 19-APR-18

+DATA/orcl/datafile/undotbs2.272.973698065

5 Full 1154012 19-APR-18

+DATA/orcl/datafile/users.273.973698069

BS Key Type LV Size Device Type

Elapsed Time Completion Time

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

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

411 Full 17.80M DISK 00:00:04 19-APR-18

BP Key: 422 Status: AVAILABLE Compressed:

NO Tag: JINHU_FULL

Piece Name:

/home/oracle/rman/20180419/jinhu_full_0gt0o29c_1_1_20180419

SPFILE Included: Modification time:

19-APR-18

SPFILE db_unique_name: ORCL

Control File Included: Ckp SCN:

1154012 Ckp

time: 19-APR-18

2.全备

RMAN> run {

allocate channel ch1 device type

disk;

backup as compressed backupset

database plus archivelog delete input

format='/u01/app/oracle/bk/rmbk/Whole_%d_%U'

tag='Whole_bak';

release channel ch1;

}

3.增量备份(0级)

RMAN> run{

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup as compressed backupset

incremental level 0

database plus archivelog delete input

format='/u01/app/oracle/bk/rmbk/Inc_0_%d_%U'

tag='Inc_0';

release channel ch1;

release channel

ch2;}

4.增量备份(1级)

RMAN> run{

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup as compressed backupset

incremental level 1 database

format='/u01/app/oracle/bk/rmbk/Inc_1_%d_%U'

tag='Inc_1';

release channel ch1;

release channel ch2;}

5.累计增量备份(1级)

RMAN> run{

allocate channel ch1 device type disk;

backup as compressed backupset

incremental level 1 cumulative database

format '/u01/app/oracle/bk/rmbk/Cum_1_%d_%U'

tag='Cum_1';

release channel ch1;}

6.备份表空间

RMAN> run{

allocate channel ch1 device type disk;

backup as compressed backupset

tablespace users,example

format='/u01/app/oracle/bk/rmbk/tbs_%d_%U'

tag='tbs';}

7.备份数据文件

RMAN> run{

allocate channel ch1 device type disk;

backup as compressed backupset

datafile 3

format='/u01/app/oracle/bk/rmbk/df_%d_%U'

tag='df';

release channel ch1;}

8.备份归档日志

备份归档日志前，建议先使用crosscheck校验一下

crosscheck通常用于检查备份是否被删除，如果删除将会打上删除标签

RMAN> crosscheck archivelog all;

RMAN> sql 'alter system switch logfile';

sql

statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

9.基于SCN来备份归档日志

RMAN> run{

allocate channel ch1 device type disk;

backup as compressed backupset

archivelog from scn 848043

format='/u01/app/oracle/bk/rmbk/arc_%d_%U'

tag='arc';

release channel ch1;

}

10.镜像备份

RMAN> run{

allocate channel ch1 device type disk;

backup as copy datafile 1,4

format '/u01/app/oracle/bk/rmbk/df_%d_%U'

tag 'copybak';

release channel ch1;}

11.其它

RMAN> list backupset;

RMAN> crosscheck backupset;

RMAN> validate backupset 1322;

RMAN> change backupset 1322

!!The End!!