RMAN Catalog创建、配置和管理

Alfred Zhao

发布于 2019-05-24 20:24:57

6050

发布于 2019-05-24 20:24:57

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。

环境：RHEL6.4 + Oracle 11.2.0.4

一、创建数据库catdb

1.1 官档的建库脚本示例
1.2 根据我实际环境修改如下项
1.3 创建必要目录并赋予权限
1.4 执行脚本建库

二、配置catdb为catalog数据库

2.1 创建表空间
2.2 创建用户
2.3 创建CATALOG
2.4 目标数据库注册到CATALOG

三、管理catalog数据库

3.1 创建和管理VPC
3.2 创建和管理存储脚本
3.3 删除恢复目录
3.4 更新恢复目录

四、Reference

一、创建数据库catdb

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 -> oracle
system_password -> oracle
US7ASCII -> ZHS16GBK
mynewdb -> catdb
/u01/logs/my -> /u01/redologs/a
/u02/logs/my -> /u01/redologs/b
/u01/app/oracle/oradata -> /u01/oradata03

可以vi批量替换,比如:%s#/u01/logs/my#/u01/redologs/a#g替换所有/u01/logs/my为/u01/redologs/a。修改完如下: vi /home/oracle/create_db.sql

CREATE DATABASE catdb
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/redologs/a/redo01a.log','/u01/redologs/b/redo01b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/u01/redologs/a/redo02a.log','/u01/redologs/b/redo02b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/u01/redologs/a/redo03a.log','/u01/redologs/b/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET ZHS16GBK
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/oradata03/catdb/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/oradata03/catdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/oradata03/catdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oradata03/catdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/oradata03/catdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

1.3 创建必要目录并赋予权限

#redologs和datafiles
mkdir -p /u01/redologs/a && mkdir -p /u01/redologs/b && mkdir -p /u01/oradata03/catdb
chown oracle:oinstall /u01/redologs/a /u01/redologs/b /u01/oradata03/catdb
#controlfiles
mkdir -p /u01/system/catdb && mkdir -p /u02/system/catdb && mkdir -p /u03/system/catdb  
chown oracle:oinstall /u01/system/catdb /u02/system/catdb /u03/system/catdb

1.4 执行脚本建库

1.4.1 声明ORACLE_SID变量

export ORACLE_SID=catdb

1.4.2 编辑初始化参数文件

vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

DB_NAME=catdb
CONTROL_FILES=('/u01/system/catdb/control01.ctl','/u02/system/catdb/control02.ctl','/u03/system/catdb/control03.ctl')
MEMORY_TARGET=1G

1.4.3 创建服务器参数文件并启动实例

创建服务器参数文件,以服务器参数文件启动数据库

SQL> create spfile from pfile;
SQL> startup nomount

1.4.4 运行建库脚本建库

SQL> @/home/oracle/create_db.sql

Database created.

然后执行以下脚本:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

1.4.5 配置tnsnames.ora

CATDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = JY-DB02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = catdb)
    )
  )

二、配置catdb为catalog数据库

2.1 创建表空间

CREATE TABLESPACE TOOLS DATAFILE '/u01/oradata03/catdb/tools.dbf' SIZE 15M REUSE AUTOEXTEND ON NEXT 15M MAXSIZE 1500M;

2.2 创建用户

CREATE USER rman IDENTIFIED BY rman
  TEMPORARY TABLESPACE tempts1
  DEFAULT TABLESPACE tools 
  QUOTA UNLIMITED ON tools;

GRANT RECOVERY_CATALOG_OWNER TO rman;

2.3 创建CATALOG

$ rman catalog rman/rman
RMAN> CREATE CATALOG;

2.4 目标数据库注册到CATALOG

在目标数据库执行:

rman TARGET / CATALOG rman@catdb
REGISTER DATABASE;

三、管理catalog数据库

3.1 创建和管理VPC(virtual private catalogs)

3.1.1 创建表空间，用户，赋权

create tablespace vpcusers datafile '/u01/oradata03/catdb/vpcusers01.dbf' size 30M autoextend on maxsize 300M;
CREATE USER vpc1 IDENTIFIED BY vpc1 DEFAULT TABLESPACE vpcusers QUOTA UNLIMITED ON vpcusers;
GRANT recovery_catalog_owner TO vpc1;

3.1.2 恢复目录所有者RMAN登录赋予vpc1用户注册数据库权限

$ rman catalog rman/rman@catdb
RMAN> GRANT REGISTER DATABASE TO vpc1;

3.1.3 虚拟专用目录RMAN登录创建虚拟CATALOG

RMAN> CONNECT CATALOG vpc1@catdb;
RMAN>  CREATE VIRTUAL CATALOG;

如果是10.2版本或更早,需要SQL下执行存储过程创建：

SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;

3.1.4 恢复目录所有者RMAN登录赋予vpc1用户已注册数据库的访问权限

$ rman catalog rman/rman@catdb
RMAN> GRANT CATALOG FOR DATABASE jingyu TO vpc1;

3.2 创建和管理存储脚本

首先连接到目标数据库和恢复目录。

3.2.1 创建全局脚本

CREATE GLOBAL SCRIPT global_full_backup 
{     
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
}

你可以创建脚本时同时加上描述性的注释,比如：

CREATE GLOBAL SCRIPT global_full_backup 
COMMENT 'use only with ARCHIVELOG mode databases'
{     
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
}

3.2.2 创建本地脚本

CREATE SCRIPT full_backup 
{     
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
}

创建脚本还可以从已有脚本文本文件获取：

CREATE SCRIPT full_backup 
  FROM FILE '/tmp/my_script_file.txt';

3.2.3 替换存储脚本

REPLACE GLOBAL SCRIPT global_full_backup 
COMMENT 'A script for full backup to be used with any database'
{
  BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
}

3.2.4 执行存储脚本

RUN 
{
  EXECUTE GLOBAL SCRIPT global_full_backup;
}

RUN
{ 
  EXECUTE SCRIPT global_full_backup; 
}

如果没有本地脚本是global_full_backup这个名字，那么上面两个命令效果一样，如果有，那么第二个命令则会优先执行本地脚本。

3.2.5 创建和管理动态存储脚本

登录RMAN

rman TARGET / CATALOG rman@catdb USING arc_backup bck0906 FY06Q3

创建动态存储脚本：

CREATE SCRIPT quarterly { 
  ALLOCATE CHANNEL c1
    DEVICE TYPE sbt
    PARMS 'ENV=(OB_MEDIA_FAMILY=&1)';
  BACKUP
    TAG &2
    FORMAT '/disk2/bck/&1%U.bck'
    KEEP FOREVER
    RESTORE POINT &3
    DATABASE;
}

替换成符合我实际环境可执行的：

Replace SCRIPT quarterly { 
  ALLOCATE CHANNEL c1
    DEVICE TYPE disk;
  BACKUP
    TAG '&2'
    FORMAT '/u01/jingyu/&1%U.bck'
    KEEP FOREVER
    RESTORE POINT '&3'
    DATABASE;
}

执行：

RUN
{ 
  EXECUTE SCRIPT quarterly 
    USING arc_backup
          bck1206
          FY06Q4;
}

3.2.6 打印存储脚本

打印存储脚本：

PRINT SCRIPT full_backup;

打印内容发送到脚本：

PRINT SCRIPT full_backup 
  TO FILE '/tmp/my_script_file.txt';

3.3 删除恢复目录

连接到catalog，执行删除（两次确认）。

RMAN> drop catalog;
RMAN> drop catalog;

3.4 更新恢复目录

比如RMAN恢复目录版本是10g，那么11g版本的RMAN客户端需要升级更新本地包和模式。如果你10gR1版本之前创建的恢复目录, 并且RECOVERY_CATALOG_OWNER角色不包含CREATE TYPE权限,那么需要赋予CREATE TYPE的权限给恢复目录所有者。

SQL> GRANT CREATE TYPE TO rman;

升级catalog（两次确认）：

RMAN> upgrade catalog;
RMAN> upgrade catalog;

四、Reference

Oracle® Database Backup and Recovery User's Guide 11g Release 2 (11.2)
OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2015-10-09 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度