CNDBA社区
RMAN Catalog备份及恢复
参考官方链接：https://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmcatdb.htm#BRADV8015
CATALOG与NOCATALOG区别，参考群主大大文章链接：https://blog.csdn.net/tianlesoftware/article/details/5641763
Catalog恢复目录(即catalog方式)支持如下的命令
{CREATE|UPGRADE|DROP} CATALOG
{CREATE|DELETE|REPLACE|PRINT} SCRIPT.
LIST INCARNATION
REGISTER DATABASE
REPORT SCHEMA AT TIME
RESET DATABASE
RESYNC CATALOG
1、Resync命令
Resync可以同步数据库控制文件与恢复目录之间的相关备份信息，在实际情况下，rman一般可以自动同步(即使用Catalog恢复目录不管是backup备份还是restore恢复都会自动同步)
在如下情况下需要同步
·数据库物理结构的改变
·数据文件增加或者是改变大小
·表空间删除
·回滚段的创建与删除
·nocatalog与catalog结合使用时，需要先nocatalog备份，再catalog恢复目录备份，这样就不用手动同步（备份顺序反过来就需要手动同步）
2、Reset 命令
目标数据库不完全恢复resetlogs 之后，需要重新设置恢复目录。Reset命令就用来重新设置恢复目录
目录 总结说明一、创建数据库catadb 1.1 官档建库脚本1.2 修改建库脚本1.3 创建目录并授权1.4 脚本建库1.4.1 声明ORACLE_SID1.4.2 编辑初始化参数文件1.4.3 创建服务器参数文件并启动实例1.4.4 运行脚本建库1.4.5 配置监听以及Tnsnames.ora二、配置catadb为catalog数据库2.1、创建表空间2.2、创建用户2.3、创建CATALOG2.4、目标数据库注册CATALOG三、管理CATALOG数据库3.1、创建和管理VPC3.2、创建和管理存储脚本3.2.1、创建存储脚本3.2.2 存储脚本管理四、脚本备份执行五、恢复验证
总结说明
1、CATALOG恢复目录数据库是可以备份恢复管理多个数据库的，即 一个 CATALOG恢复目录数据库 可以注册多个数据库，而注册多个 Target 数据库以后，可以分给不同的虚拟用户目录进行管理，即VPC< virtual private catalog >虚拟用户目录
2、当前实验环境是一个CATALOG恢复目录，注册管理多个Target，并使用VPC虚拟用户进行分开管理
总结：
1、NOCATALOG 备份信息存在在数据库控制文件，受参数control_file__record_keep_time影响，设置备份信息保存时间，到规定时间就自动清除以前的备份信息，可以理解为备份过期，所以，nocatalog时利用controlfile存放备份信息，建议将Oracle参数文件中的CONTROL_FILE_RECORD_KEEP_TIME值加大（缺省为7天），保证几天的恢复量,必须大于RMAN 保留策略RECOVERY WINDOW OF 7 days，所以，对于控制文件的备份很重要，这也是为什么backup database 最好加上include current controlfile原因
2、CATALOG单独存储备份信息，为此，我们只要确保CATALOG恢复目录数据库正常，也就相当于间接备份数据库控制文件
3、备份CATALOG数据库：因为CATALOG数据库包含了所有的备份信息，所以该数据库本身也是需要通过某种方法进行备份，但因为该数据库很小（一年内可能才增加十几二十兆），所以既可以对它进行冷备份，也可以进行逻辑的输出（EXPORT）
4、对于DBA管理多个数据库时，备份恢复的管理，CATALOG不妨是个比较好的管理方式
一、创建数据库catadb
其实关于创建数据库，可以不使用该方法创建，只是想熟悉下手工建库过程，直接DBCA创建数据库catalogdb即可，直接可以省略很多麻烦...
1.1 官档建库脚本
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
1.2 修改建库脚本
sys_password -> marvinn --更换SYS用户密码
system_password -> marvinn --更换SYSTEM用户密码
US7ASCII -> AL32UTF8
mynewdb -> catadb
/u01/logs/my -> /u01/redo/
/u02/logs/my -> /u01/redo/
/u01/app/oracle/oradata -> /u01/oradata
可以vi批量替换,比如:%s#/u01/logs/my#/u01/redologs/a#g替换所有/u01/logs/my为/u01/redo/
修改完如下:
vi /u01/create_db.sql
注意：数据库库名不能超过8字符，否则报错，起库报错:
ORA-01127: database name ‘catalogdb’ exceeds size limit of 8 characters
CREATE DATABASE catadb
USER SYS IDENTIFIED BY marvinn
USER SYSTEM IDENTIFIED BY marvinn
LOGFILE GROUP 1 ('/u01/redo/redo01a.log','/u01/redo/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/redo/redo02a.log','/u01/redo/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/redo/redo03a.log','/u01/redo/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oradata/catalogdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/oradata/catalogdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/oradata/catalogdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oradata/catalogdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oradata/catalogdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
1.3 创建目录并授权
创建建库脚本中的指定目录…并授权
#redologs和datafiles
mkdir -p /u01/redo/ && mkdir -p /u01/oradata/catalogdb
chown oracle:oinstall /u01/redo/ /u01/oradata/catalogdb
#controlfiles
mkdir -p /u01/oradata/controlfile/
chown oracle:oinstall /u01/oradata/controlfile/
1.4 脚本建库
1.4.1 声明ORACLE_SID
export ORACLE_SID=catadb
1.4.2 编辑初始化参数文件
查看物理内存大小，当前物理内存4G左右
oracle@catalog:/home/oracle>free -m
total used free shared buffers cached
Mem: 3832 283 3549 0 90 103
-/+ buffers/cache: 90 3742
Swap: 4031 0 4031
oracle@catalog:/home/oracle>
编辑初始化参数文案进
vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
编辑添加如下：
DB_NAME=catadb
CONTROL_FILES=('/u01/oradata/controlfile/control01.ctl','/u01/oradata/control02.ctl')
MEMORY_TARGET=2G
注意：
1、MEMORY_TARGET 参数值不能大于系统物理内存大小，否则创建SPFILE会报错误
2、参数文件编辑，可以参照已有正常数据库的PFILE，进行编辑修改，再创建SPFILE，也是可行的
1.4.3 创建服务器参数文件并启动实例
创建服务器参数文件,以服务器参数文件启动数据库
oracle@adam:/u01/oradata>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 28 18:26:46 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system
SQL>
1.初始化参数MEMORY_TARGET或MEMORY_MAX_TARGET不能大于共享内存(/dev/shm),为了解决这个问题，可以增大/dev/shm
[root@adam ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_adam-lv_root
35G 13G 21G 38% /
tmpfs 1.0G 0 1.0G 0% /dev/shm
/dev/vda1 477M 28M 425M 7% /boot
shmfs 2.0G 0 2.0G 0% /dev/shm
2.为了确保操作系统重启之后能生效，需要修改/etc/fstab文件
vi /etc/fstab
增加shm挂载大小
tmpfs /dev/shm tmpfs defaults,size=2g 0 0
重新mount,使之生效
[root@adam ~]# mount -o remount /dev/shm
重新起库nomount状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 616563592 bytes
Database Buffers 444596224 bytes
Redo Buffers 5517312 bytes
1.4.4 运行脚本建库
SQL> @/u01/create_db.sql
NATIONAL CHARACTER SET AL16UTF16
*
ERROR at line 12:
ORA-12701: CREATE DATABASE character set is not known
报错：之前参考MOS文章：ORA-12701 When Creating a Database (文档 ID 1058400.6) export ORA_NLS10=$ORACLE_HOME/nls/data 未能解决，再次查看建库脚本 发现数据库字符集写错
CHARACTER SET AL32UTFB
NATIONAL CHARACTER SET AL16UTF16
修改成
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
注意：若是建库失败，需要清理之前已经创建好的文件，比如：控制文件，日志文件等，重新建库，否则报错无法创建
SQL> @/u01/create_db.sql
CREATE DATABASE catadb
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-00301: error in adding log file '/u01/redo/redo01a.log' - file cannot be
created
ORA-27038: created file already exists
Additional information: 1
Process ID: 9636
Session ID: 16 Serial number: 1
清理完毕，重新建库
oracle@adam:/u01/redo>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 29 09:48:57 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 616563592 bytes
Database Buffers 444596224 bytes
Redo Buffers 5517312 bytes
SQL> @/u01/create_db.sql ---建库需要一段时间....耐心等待，期间可以查看Alert告警日志，显示建库过程
Database created.
然后执行以下脚本:
这两个脚本，用于系统数据字典，包，相关组件以及Type等对象的重建
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
这个脚本SYSTEM用户下生成PRODUCT_USER_PROFILE表，用于设置普通用户登录SQLPLUS的权限控制，若不执行该脚本，普通用户连接SQLPLUS登录，会一直报错：User Profile information not loaded类似的相关错误
conn system/marvinn
@?/sqlplus/admin/pupbld.sql
1.4.5 配置监听以及Tnsnames.ora
oracle@catalog:/home/oracle>cd /u01/oracle/12.2.0/network/admin/
编辑添加Listener.ora文件
oracle@catalog:/u01/oracle/12.2.0/network/admin>cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=172.16.10.110)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SERVICE_NAME=catadb)
(SID_NAME= catadb)
(ORACLE_HOME=/u01/oracle/12.2.0/)
(PROGRAM = extproc)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
编辑添加Tnsnames.ora文件
oracle@catalog:/u01/oracle/12.2.0/network/admin>vi tnsnames.ora
CATADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = catadb)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.53)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.115)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
注意：目标端(即生产数据库，需要catalog备份的数据库)数据库Tnsnames.ora参数文件，需要配置CATADB(CATALOG恢复目录数据库)数据库的TNS配置，简单一句话，相互配置TNS参数
目标端(生产数据库端)添加,有多少个生产数据库就需要添加多少个，因为CATALOG恢复目录数据库可以备份对应多个生产数据库
ORCL目标数据库：
vi tnsnames.ora
编辑添加
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.53)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
CATADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = catadb)
)
)
TEST目标数据库：
vi tnsnames.ora
编辑添加
CATADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = catadb)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.115)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
二、配置catadb为catalog数据库
2.1、创建表空间
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE ---建库脚本跑完，可以看到catadb数据库已是可读写OPEN状态
SQL> CREATE TABLESPACE catadb DATAFILE '/u01/oradata/catalogdb/catalog.dbf' SIZE 15M AUTOEXTEND ON NEXT 15M;
Tablespace created.
2.2、创建用户
SQL> CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE tempts1 --取决于参数文件SPFILE中临时表空间名
DEFAULT TABLESPACE catadb;
User created.
SQL> grant connect,resource to rman;
Grant succeeded.
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
Grant succeeded.
2.3、创建CATALOG
oracle@catalog:/u01/oracle/12.2.0/network/admin>rman catalog rman/rman
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 07:33:01 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> create catalog;
recovery catalog created
2.4、目标数据库注册CATALOG
注意：如果目标数据库（生产数据库)和CATADB恢复目录数据库，TNS配置并未相互配置写入到各自的tnsnames.ora文件中，否则报错，是无法进行访问的
CATADB恢复目录数据库上操作
1、注册目标端ORCL到CATALOG
oracle@catalog:/u01/oracle/12.2.0/network/admin>rman target sys/yunq111@orcl catalog rman/rman@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 07:55:20 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1487384610)
connected to recovery catalog database
RMAN> REGISTER DATABASE; --注册DB到CATALOG恢复目录数据库CATADB
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
2、注册目标端TEST到CATALOG
oracle@catalog:/u01/oracle/12.2.0/network/admin>rman target sys/yunq111@test catalog rman/rman@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 08:33:56 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2260953507)
connected to recovery catalog database
RMAN> create catalog; ---再次创建CATALOG，则报错CATALOG已经存在，说明，一个CATALOG，可以注册管理多个Target,无需再次创建
recovery catalog already exists
RMAN> REGISTER DATABASE; --注册DB到CATALOG恢复目录数据库CATADB
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
三、管理CATALOG数据库
3.1、创建和管理VPC
针对一个CATALOG注册多个Target,为便于管理，可以创建对应的VPC(虚拟用户目录)进行分开管理
3.1.1、创建表空间、用户以及授权
创建管理目标端ORCL VPC用户：
SQL> create tablespace vpcuser1 datafile '/u01/oradata/catalogdb/vpcuser1.dbf' size 30M autoextend on next 30m;
Tablespace created.
SQL> CREATE USER vpc_orcl IDENTIFIED BY vpc_orcl DEFAULT TABLESPACE vpcuser1 QUOTA UNLIMITED ON vpcuser1;
User created.
SQL>GRANT recovery_catalog_owner TO vpc_orcl;
Grant succeeded.
创建管理目标端TEST VPC用户：
SQL> create tablespace vpcuser2 datafile '/u01/oradata/catalogdb/vpcuser2.dbf' size 30M autoextend on next 30m;
Tablespace created.
SQL> CREATE USER vpc_test IDENTIFIED BY vpc_test DEFAULT TABLESPACE vpcuser2 QUOTA UNLIMITED ON vpcuser2;
User created.
SQL> GRANT recovery_catalog_owner TO vpc_test;
Grant succeeded
3.1.2、恢复目录所有者rman用户登录授权虚拟用户VPC注册数据库权限以及虚拟用户访问某个数据库的权限
oracle@catalog:/u01/oracle/12.2.0/network/admin>rman catalog rman/rman@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 08:54:28 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> GRANT REGISTER DATABASE TO vpc_orcl; --注册数据库权限
Grant succeeded.
RMAN> GRANT REGISTER DATABASE TO vpc_test; --注册数据库权限
Grant succeeded.
RMAN> GRANT CATALOG FOR DATABASE orcl TO vpc_orcl; --虚拟用户vpc_orcl访问orcl库的权限
Grant succeeded
RMAN> GRANT CATALOG FOR DATABASE test TO vpc_test;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, integer, single-quoted-string"
RMAN-01007: at line 1 column 28 file: standard input
RMAN> GRANT CATALOG FOR DATABASE "test" TO vpc_test; --虚拟用户vpc_test访问test库的权限
Grant succeeded.
3.1.3、VPC虚拟用户登录创建CATALOG
VPC虚拟用户vpc_orcl登录：
oracle@catalog:/u01/>rman catalog vpc_orcl/vpc_orcl@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 08:56:42 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE VIRTUAL CATALOG;
found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN
RMAN> exit;
VPC虚拟用户vpc_test登录:
oracle@catalog:/u01/>rman catalog vpc_test/vpc_test@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 31 08:59:50 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE VIRTUAL CATALOG;
found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN
RMAN> exit;
注意：如果是10.2版本或更早,需要SQLPLUS下执行存储过程创建
PS:
1、登录SQLPLUS用户是对应的虚拟用户VPC，这样才会在其对应的虚拟用户下创建虚拟专用恢复目录
2、base_catalog_owner，该值对应的是CATALOG恢复目录所有者（即首次创建的恢复用户拥有者，当前环境是rman用户），而不是虚拟用户VPC
示例如下：sqlplus vpc_orcl/vpc_orcl@catadb
SQL>SQL> EXECUTE rman.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
语法如下：
SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
3.2、创建和管理存储脚本
rman的备份恢复命令写成脚本并保存在恢复目录内，恢复目录内的脚本可用性及通用性高于基于文件系统的脚本，客户端只要能够登录到RMAN恢复目录，则这些脚本可用，尤其对于global脚本，可以被任意注册的数据库调度
脚本的分类：
local 本地存储脚本: 在rman连接的目标数据库下创建的脚本，此类脚本仅仅适用于当前目标数据库。即是针对特定的数据库创建的rman脚本
global 全局存储脚本 : 能够在恢复目录注册的任意目标数据库中执行
3.2.1、创建存储脚本
1、创建备份存放路径
Target 目标端ORCL：
mkdir -p /u01/bakcup
mkdir -p /u01/bakcup/full_db/orcl
mkdir -p /u01/bakcup/leveldb_1/orcl
mkdir -p /u01/bakcup/leveldb_2/orcl
mkdir -p /u01/bakcup/archivelog/orcl
Target 目标端TEST：
mkdir -p /u01/bakcup
mkdir -p /u01/bakcup/full_db/test
mkdir -p /u01/bakcup/leveldb_1/test
mkdir -p /u01/bakcup/leveldb_2/test
mkdir -p /u01/bakcup/archivelog/test
2、RMAN任意连接某个目标数据库以及恢复目录(catadb操作)
注意：考虑，后续方便管理查看，以及变更，故采用本地存储脚本方式备份
Target 目标端数据库ORCL： 虚拟专用用户VPC_ORCL连接
oracle@catalog:/u01>rman target sys/yunq111@orcl catalog vpc_orcl/vpc_orcl
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 1 02:17:29 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1487384610)
connected to recovery catalog database
创建0级全库备份
RMAN> CREATE SCRIPT local_fulldb_orcl
COMMENT 'A script for full backup to be used with current Target database'
{
ALLOCATE CHANNEL D1 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D2 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D3 TYPE DISK MAXPIECESIZE=100M;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE TAG='ORCL_LEVEL_0' INCLUDE CURRENT CONTROLFILE
FORMAT '/u01/bakcup/full_db/orcl/fulldb_%d_%T_%s_%p'
PLUS ARCHIVELOG FORMAT '/u01/bakcup/archivelog/orcl/arch_%d_%T_%s_%p'
DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE recovery window of 7 days; --保证7天的可恢复量
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}
created script local_fulldb_orcl
注意：0级全库备份，使用CATALOG恢复目录，
创建1级增量:
RMAN>CREATE SCRIPT local_level1_orcl
COMMENT 'A script for Incremental level 1 backup to be used with current Target database'
{
ALLOCATE CHANNEL D1 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D2 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D3 TYPE DISK MAXPIECESIZE=100M;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT expired archivelog ALL;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE TAG='ORCL_LEVEL_1' INCLUDE CURRENT CONTROLFILE
FORMAT '/u01/bakcup/leveldb_1/orcl/leveldb1_%d_%T_%s_%p'
PLUS ARCHIVELOG FORMAT '/u01/bakcup/archivelog/orcl/leveldb1_%d_%T_%s_%p'
DELETE ALL INPUT;
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}
created script local_level1_orcl
创建2级增量：
RMAN>CREATE SCRIPT local_level2_orcl
COMMENT 'A script for Incremental level 2 backup to be used with current Target database'
{
ALLOCATE CHANNEL D1 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D2 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D3 TYPE DISK MAXPIECESIZE=100M;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT expired archivelog ALL;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 2 DATABASE TAG='ORCL_LEVEL_2' INCLUDE CURRENT CONTROLFILE
FORMAT '/u01/bakcup/leveldb_2/orcl/leveldb2_%d_%T_%s_%p'
PLUS ARCHIVELOG FORMAT '/u01/bakcup/archivelog/orcl/leveldb2_%d_%T_%s_%p'
DELETE ALL INPUT;
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}
created script local_level2_orcl
修改存储脚本：REPLACE命令，增加备份参数文件SPFILE
RMAN>REPLACE SCRIPT local_fulldb_orcl
COMMENT 'A script for full backup to be used with current Target database'
{
ALLOCATE CHANNEL D1 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D2 TYPE DISK MAXPIECESIZE=100M;
ALLOCATE CHANNEL D3 TYPE DISK MAXPIECESIZE=100M;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE TAG='ORCL_LEVEL_0' INCLUDE CURRENT CONTROLFILE
FORMAT '/u01/bakcup/full_db/orcl/fulldb_%d_%T_%s_%p'
PLUS ARCHIVELOG FORMAT '/u01/bakcup/archivelog/orcl/arch_%d_%T_%s_%p'
DELETE ALL INPUT;
BACKUP SPFILE FORMAT '/u01/bakcup/full_db/orcl/spfile_%d_%T_%s_%p';
DELETE NOPROMPT OBSOLETE recovery window of 7 days;
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}
replaced script local_fulldb_orcl
至此....当前Target目标数据库ORCL存储脚本创建完毕
另外一个Target目标数据库TEST过程省略，只需要修改备份路径,存储脚本名，TAG以及RMAN链接的目标Target数据库(这点尤为重要)即可...另外关于，全局存储脚本的大同小异省略
链接Target DB TEST串：$ rman target sys/yunq111@test catalog rman/rman@catadb
注意：
使用EXECUTE SCRIPT, DELETE SCRIPT ,PRINT SCRIPT等命令时，如果一个本地的脚本未找到，则寻找全局的脚本来代替。
因此应当注意命名规范，尽可能的不使用重名的脚本名，当DELETE SCRIPT 找不到本地脚本，而找到同样名字的全局脚本，则全局
脚本被删除
---------------------------------------------------------------------------------------------------------------------------当前环境未使用的脚本创建语法以及管理更新，删除脚本语法
--------------------------------------------------------------------------------------------------------------------------
全局存储脚本语法：
注意：创建全局存储脚本 不能使用虚拟用户VPC登录，需要使用CATALOG恢复目录用户所有者登录，即当前用户是rman用户登陆创建，至于链接哪个目标端可随意
CREATE GLOBAL SCRIPT global_full_backup
COMMENT 'A script for full backup to be used with any database' --COMMENT注释
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
注意：
Rman提供plus archivelog选项，极大的简化了Rman备份的操作。
当你定义plus archivelog语句时，RMAN执行如下步骤的操作：
1。运行一个ALTER SYSTEM ARCHIVELOG CURRENT命令
2。运行BACKUP ARCHIVELOG ALL命令。注意如果目标端备份优化被启用，RMAN只会备份未备份过的日志
3。备份BACKUP命令中定义的文件
4。运行ALTER SYSTEM ARCHIVELOG CURRENT命令
5。备份所有的剩下的归档日志
也可以从系统文本文件创建存储脚本，语法如下：(当前环境省略...)
--从文本文件创建脚本
[oracle@oradb bk]$ cat backup_ctl_spfile --注意文本文件必须以"{" 开头，以"}" 结尾
{
allocate channel ch1 device type disk;
backup current controlfile
tag='ctl_spfile'
format '/u01/oracle/bk/rmbk/ctl_spfile_%d_%U';
release channel ch1;
}
RMAN> create script ctl_spfile_bk from file '/u01/oracle/bk/rmbk/backup_ctl_spfile';
script commands will be loaded from file /u01/oracle/bk/rmbk/backup_ctl_spfile
created script ctl_spfile_bk
----------------------------------------------------------------------------------------------------------------
3.2.2 存储脚本管理
RMAN恢复目录连接任意一个目标Target数据库,使用如下管理命令:如：$rman target sys/yunq111@test catalog rman/rman@catadb
查看当前恢复目录内的脚本
list [global | all] script names 参数ALL，表明查看恢复目录内所有的存储脚本，不管是本地还是全局
RMAN> list script names; --查看本地存储脚本
List of Stored Scripts in Recovery Catalog
Scripts of Target Database TEST
Script Name
Description
-----------------------------------------------------------------------
local_fulldb_test
A script for full backup to be used with current Target database
local_level1_test
A script for Incremental level 1 backup to be used with current Target database
local_level2_test
A script for Incremental level 2 backup to be used with current Target database
RMAN> list all script names; --查看所有存储脚本
List of Stored Scripts in Recovery Catalog
Scripts of Target Database ORCL
Script Name
Description
-----------------------------------------------------------------------
local_fulldb_orcl
A script for full backup to be used with current Target database
local_level1_orcl
A script for Incremental level 1 backup to be used with current Target database
local_level2_orcl
A script for Incremental level 2 backup to be used with current Target database
Scripts of Target Database TEST
Script Name
Description
-----------------------------------------------------------------------
local_fulldb_test
A script for full backup to be used with current Target database
local_level1_test
A script for Incremental level 1 backup to be used with current Target database
local_level2_test
A script for Incremental level 2 backup to be used with current Target database
查看脚本的内容
print [global] script script_name
print [global] script script_name to file '<dir>' --将RMAN存储脚本转换到文件系统可读文件
RMAN> print script ctl_spfile_bk;
RMAN> print script ctl_spfile_bk to file '/u01/oracle/bk/rmbk/backup_ctl_spfile2';
更新脚本
replace [global] script scrip_name {....}
replace [global] script script_name from file '<dir>' --从文件更新脚本
RMAN> replace global script global_full_backup
2> {
3> backup as compressed backupset database plus archivelog
4> tag='whole_db_bk';
5> delete obsolete;
6> }
replaced global script global_full_backup
执行脚本
方法：RUN { EXECUTE [global] SCRIPT script_name; }
从rman客户端直接执行恢复目录内的脚本
$rman target sys/redhat@orcl catalog rman/rman@asmdb script 'script_name';
RMAN> run { execute global script global_full_backup;}
executing global script: global_full_backup
删除脚本
delete [global] script script_name
RMAN> delete script ctl_spfile_bk;
deleted script: ctl_spfile_bk
删除恢复目录 --该操作不需要用CATALOG才用来操作
链接CATALOG，执行删除(两次确认)
$RMAN catalog rman/rman@catadb
RMAN>DROP CATALOG;
RMAN>DROP CATALOG;
更新恢复目录
注意：
比如RMAN恢复目录版本是10g，而目标端升级到了11g版本的RMAN客户端，则需要升级更新恢复目录本地包和模式。
如果你10gR1版本之前创建的恢复目录, 并且RECOVERY_CATALOG_OWNER角色不包含CREATE TYPE权限,那么需要赋予CREATE TYPE的权限给恢复目录所有者。
SQL>GRANT CREATE TYPE TO rman;
升级更新CATLOG（两次确认)
RMAN>upgrade catalog;
RMAN>upgrade catalog;
四、脚本备份执行
备份Target ORCL:
0级：
$rman target sys/yunq111@orcl catalog vpc_orcl/vpc_orcl@catadb script 'local_fulldb_orcl'
创建测试数据
SQL> create table marvin(id number,name varchar2(10));
Table created.
SQL> insert into marvin values(1,'dd');
1 row created.
SQL> commit;
Commit complete.
1级：
$rman target sys/yunq111@orcl catalog vpc_orcl/vpc_orcl@catadb script 'local_level1_orcl' --2级备份就不测试了
备份Target TEST:
0级：
$rman target sys/yunq111@test catalog vpc_test/vpc_test@catadb script 'local_fulldb_test'
创建测试数据
SQL> create table marvin(id number,name varchar2(10));
Table created.
SQL> insert into marvin values(1,'dd');
1 row created.
SQL> commit;
Commit complete.
2级：
$rman target sys/yunq111@test catalog vpc_test/vpc_test@catadb script 'local_level2_test'
再插入测试数据：
SQL> insert into marvin values(1,'ss');
1 row created.
SQL> insert into marvin values(1,'nn');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from marvin;
COUNT(*)
----------
3
1级：
$rman target sys/yunq111@test catalog vpc_test/vpc_test@catadb script 'local_level1_test'
五、恢复验证
Target 目标数据库TEST:
查看表Marvin位于哪个表空间
SQL> select table_name,tablespace_name from dba_tables where table_name='MARVIN';
TABLE_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
MARVIN
SYSTEM
查看SYSTEM表空间位置，系统层面手动备份下
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
/u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf
SYSTEM
oracle@test:/home/oracle>cp /u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf /u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf.bak
手动删除SYSTEM表空间
oracle@test:/home/oracle>rm -rf /u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf
oracle@test:/home/oracle>
重启数据库，报错
SQL> startup force;
ORACLE instance started.
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf'
恢复过程(Target 目标端操作)
oracle@test:/home/oracle>rman target / catalog vpc_test/vpc_test@catadb
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 2 12:19:20 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup mount; --从这里可以看出，根本不用像NOCATALOG还得NOMOUNT状态，恢复控制文件，在MOUNT，再恢复数据文件，因为CATALOG，相当于控制文件备份了 （当然这里没有删除控制文件测试，有兴趣可以自己去试）
Oracle instance started
database mounted
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
RMAN> restore tablespace system;
Starting restore at 2018-06-02 12:20:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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 00001 to /u01/oracle/oradata/TEST/datafile/o1_mf_system_f36tmgd6_.dbf
channel ORA_DISK_1: reading from backup piece /u01/bakcup/full_db/test/fulldb_TEST_20180601_17_1
channel ORA_DISK_1: piece handle=/u01/bakcup/full_db/test/fulldb_TEST_20180601_17_1 tag=TEST_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece /u01/bakcup/full_db/test/fulldb_TEST_20180601_17_2
channel ORA_DISK_1: piece handle=/u01/bakcup/full_db/test/fulldb_TEST_20180601_17_2 tag=TEST_LEVEL_0
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: reading from backup piece /u01/bakcup/full_db/test/fulldb_TEST_20180601_17_3
channel ORA_DISK_1: piece handle=/u01/bakcup/full_db/test/fulldb_TEST_20180601_17_3 tag=TEST_LEVEL_0
channel ORA_DISK_1: restored backup piece 3
channel ORA_DISK_1: restore complete, elapsed time: 00:00:34
Finished restore at 2018-06-02 12:20:39
starting full resync of recovery catalog
full resync complete
RMAN> recover tablespace system;
Starting recover at 2018-06-02 12:21:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/TEST/datafile/o1_mf_system_fk46qoqx_.dbf
channel ORA_DISK_1: reading from backup piece /u01/bakcup/leveldb_1/test/leveldb1_TEST_20180601_33_1
channel ORA_DISK_1: piece handle=/u01/bakcup/leveldb_1/test/leveldb1_TEST_20180601_33_1 tag=TEST_LEVEL_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 314 is already on disk as file /u01/oracle/11.2.0/dbs/arch/1_314_962807076.dbf
archived log for thread 1 with sequence 315 is already on disk as file /u01/oracle/11.2.0/dbs/arch/1_315_962807076.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=313
channel ORA_DISK_1: reading from backup piece /u01/bakcup/archivelog/test/leveldb1_TEST_20180601_37_1
channel ORA_DISK_1: piece handle=/u01/bakcup/archivelog/test/leveldb1_TEST_20180601_37_1 tag=TAG20180601T145743
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/oracle/11.2.0/dbs/arch/1_313_962807076.dbf thread=1 sequence=313
media recovery complete, elapsed time: 00:00:08
Finished recover at 2018-06-02 12:21:40 --说明是完全恢复
RMAN> alter database open; --数据库正常打开
database opened
RMAN>
版权声明：本文为博主原创文章，未经博主允许不得转载。
