欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

catalogdatabase管理:

程序员文章站 2022-05-31 16:35:27
...

转载请注明出处。 一.catalog 库: 官方文档:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcatdb.htm#BRADV89642 参考:http://blog.csdn.net/rlhua/article/details/13169205 二.建立catalog 数据库: 1.在要建立catalog database的数据库上

转载请注明出处。
一.catalog 库:
官方文档:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcatdb.htm#BRADV89642
参考:http://blog.csdn.net/rlhua/article/details/13169205 catalogdatabase管理:
二.建立catalog 数据库:
1.在要建立catalog database的数据库上操作 (库名:EMREP) ①.创建表空间 SQL> create tablespace rc_data datafile "/u01/app/oracle/oradata/EMREP/rc_data01.dbf' size 100m;
Tablespace created.
SQL> select file#,name from v$datafile;
FILE# NAME ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/EMREP/system01.dbf 2 /u01/app/oracle/oradata/EMREP/sysaux01.dbf 3 /u01/app/oracle/oradata/EMREP/undotbs01.dbf 4 /u01/app/oracle/oradata/EMREP/users01.dbf 5 /u01/app/oracle/oradata/EMREP/rc_data01.dbf ②.创建用户 并授权 SQL> create user rc_admin identified by rc_admin default tablespace rc_data;
User created. #授权 SQL> grant connect,resource to rc_admin;
Grant succeeded.
SQL> select * from dba_roles where role like '%RECOVERY%';
ROLE PASSWORD AUTHENTICAT ------------------------------ -------- ----------- RECOVERY_CATALOG_OWNER NO NONE
#必须授予 RECOVERY_CATALOG_OWNER 这个角色 SQL> grant RECOVERY_CATALOG_OWNER to rc_admin;
Grant succeeded.
③.创建密码文件 SQL> alter user sys identified by oracle;
User altered.
[oracle@gc2 dbs]$ orapwd file=orapwEMREP password=oracle entries=3 force=y

④.启动监听:
lsnrctl start
2.在主库(目标库)上操作 (库名:PROD)
①.远程连接rman [oracle@localhost dbs]$ rman catalog rc_admin/rc_admin@emrep
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 10 18:43:24 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database ②.在catalog库上 创建恢复目录。
RMAN> create catalog;
recovery catalog created
RMAN> quit ③.注册 同时连上目标库和catalog库 才能进行注册 [oracle@localhost dbs]$ rman target sys/oracle@prod catalog rc_admin/rc_admin@emrep
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 10 18:46:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=239015079) connected to recovery catalog database #开始注册: 将元数据同步到catalog库中的表里。 注册完成后,就可以在catalog database查询到相关信息。 RMAN> register database;
database registered in recovery catalog starting full resync of recovery catalog full resync complete
3.从恢复目录(recovery catalog)中注销目标数据库:(在目标库上操作)

[oracle@localhost dbs]$ rman target sys/oracle@prod catalog rc_admin/rc_admin@emrep

RMAN> unregister database;
从恢复目录中注销某一数据库时,恢复目录中的所有RMAN 资料档案库记录都会丢失。你可以重新注册这个数据库。此时,该数据库的恢复目录记录取决于再次注册时控制文件的内容。 通常,只有不再需要使用数据库的恢复目录时或数据库不再存在时,才会注销目标数据库。
三.RMAN存储脚本:
1.存放脚本:(在生产库上执行)
catalogdatabase管理:
RMAN> create script cold_bak{ 2> shutdown immediate; 3> startup mount; 4> allocate channel c1 type disk; 5> allocate channel c2 type disk; 6> backup database format "/home/oracle/rman_bak/cold_bak/%d_%s_%p.bak'; 7> alter database open; 8> }
created script cold_bak
RMAN> create script hot_bak{ 2> sql 'alter system switch logfile'; 3> allocate channel c1 type disk; 4> allocate channel c2 type disk; 5> backup database format '/home/oracle/rman_bak/hot_bak/%d_%s_%p_hot.bak' 6> include current controlfile plus archivelog delete input 7> filesperset 3; 8> sql 'alter system switch logfile'; 9> }
created script hot_bak
RMAN> create script recover_database{ 2> startup force mount; 3> allocate channel c1 type disk; 4> allocate channel c2 type disk; 5> restore database; 6> recover database; 7> alter database open; 8> }
created script recover_database
RMAN> create script system_bak{ 2> backup tablespace system format '/home/oracle/rman_bak/hot_bak/system_%s.bak'; 3> }
created script system_bak
2.在catalog database 上查看脚本信息:
基本名称 SQL> select SCRIPT_NAME,SCRIPT_COMMENT from rc_stored_script;
SCRIPT_NAME SCRIPT_COMMENT -------------------- -------------------------------------------------- cold_bak hot_bak recover_database system_bak 脚本内容: SQL> col line for a20 SQL> col script_name for a10 SQL> col script_name for a20 SQL> col text for a50 SQL> select script_name,text from rc_stored_script_line;
SCRIPT_NAME TEXT -------------------- -------------------------------------------------- cold_bak { cold_bak shutdown immediate; cold_bak startup mount; cold_bak allocate channel c1 type disk; cold_bak allocate channel c2 type disk; cold_bak backup database format '/home/oracle/rman_bak/cold _bak/%d_%s_%p.bak';
cold_bak alter database open; cold_bak } hot_bak {
SCRIPT_NAME TEXT -------------------- -------------------------------------------------- hot_bak sql 'alter system switch logfile'; hot_bak allocate channel c1 type disk; hot_bak allocate channel c2 type disk; hot_bak backup database format '/home/oracle/rman_bak/hot_ bak/%d_%s_%p_hot.bak'
hot_bak include current controlfile plus archivelog delete input
hot_bak filesperset 3; hot_bak sql 'alter system switch logfile';
SCRIPT_NAME TEXT -------------------- -------------------------------------------------- hot_bak } recover_database { recover_database startup force mount; recover_database allocate channel c1 type disk; recover_database allocate channel c2 type disk; recover_database restore database; recover_database recover database; recover_database alter database open; recover_database } system_bak { system_bak backup tablespace system format '/home/oracle/rman
SCRIPT_NAME TEXT -------------------- -------------------------------------------------- _bak/hot_bak/system_%s.bak';
system_bak }
3.在生产库上查看、执行、修改、删除脚本:
#查看脚本 RMAN> print script system_bak;
printing stored script: system_bak { backup tablespace system format '/home/oracle/rman_bak/hot_bak/system_%s.bak'; } #执行脚本 RMAN> run{execute script system_bak;}
#修改脚本 RMAN> replace script system_bak{backup tablespace users format '/home/oracle/rman_bak/hot_bak/users_%s.bak';} #删除脚本 RMAN> delete script system_bak;


四.将其他备份文件列入目录:
1.将控制文件、数据文件、归档重做日志文件和备份片段列入目录 如果磁盘上有其它控制文件副本、数据文件副本、备份片段或归档重做日志文件,则可使用CATALOG命令将其列入恢复目录。如果某些备份因时间太早已从控制文件中清除,则可将这些备份列入目录,以便RMAN 在执行还原操作期间使用它们。
以下是将控制文件、数据文件、归档重做日志文件和备份片段列入目录的示例:
RMAN> CATALOG CONTROLFILECOPY '/disk1/controlfile_bkup/2009_01_01/control01.ctl'; RMAN> CATALOG DATAFILECOPY '/disk1/datafile_bkup/2009_01_01/users01.dbf'; RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.log', '/disk1/arch_logs/archive1_732.log'; RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';
2.可以将当前启用的快速恢复区中的所有文件列入目录,如下所示: RMAN> CATALOG RECOVERY AREA NOPROMPT;
3.START WITH选项 使用START WITH选项可将在指定目录树中找到的所有文件列入目录。提供表示目录的前缀以及用于查找的可能的文件前缀。不能使用通配符,仅提供一个前缀。
指定目录和子目录中找到的所有类型的备份文件都被列入目录。假定在/tmp/arch_logs目录中有多个备份文件。下列命令将所有文件列入目录: RMAN> CATALOG START WITH '/tmp/arch_logs/';

For example, assume that a group of directories /disk1/backups, /disk1/backups-year2003, /disk1/backupsets, and /disk1/backupsets/test and so on, all contain backup files. The following command catalogs all files in all of these directories, because /disk1/backups is a prefix for the paths for all of these directories:

CATALOG START WITH '/disk1/backups';

To catalog only backups in the /disk1/backups directory, the correct command would be as follows:

CATALOG START WITH '/disk1/backups/';

五.重新同步恢复目录(Resynchronizing the Recovery Catalog)
RMAN 执行重新同步时,会对恢复目录和目标数据库的当前控制文件或备份/备用控制文件进行比较,然后用缺失或更改的信息来更新恢复目录。 1.重新同步有两种类型:部分和完全。 对于部分重新同步,RMAN 会比较控制文件和恢复目录,并使用与备份、归档重做日志、数据文件副本等有关的任何元数据更新恢复目录。但rman不能同步关于数据库物理方案的元数据。 对于完全重新同步,RMAN 先创建控制文件快照,该快照只是控制文件的临时副本。它使用快照与恢复目录进行比较。它比较和更新部分重新同步处理的所有数据,还包括所有数据库结构更改。例如,完全重新同步包括数据库方案更改或新表空间。 注:数据库方案包括数据文件、重做日志文件、归档日志文件、还原段的名称和位置以及在控制文件中找到的其它信息。
如果只对控制文件中由CONTROL_FILE_RECORD_KEEP_TIME控制的记录进行更改,则使用部分重新同步即可。否则,需使用完全重新同步。也可以通过发出RESYNC CATALOG命令执行完全重新同步。
2.手动重新同步恢复目录: 可在下列情况下手动重新同步恢复目录: 发出RMAN 命令以执行部分重新同步时恢复目录不可用 因为在发生重做日志切换或归档了重做日志时恢复目录未自动进行更新,所以执行不常执行的目标数据库备份 对目标数据库的物理结构进行任何更改后 RMAN> RESYNC CATALOG;

六.备份 恢复目录(recovery catalog)
1.恢复目录是一个Oracle DB,因此与任何数据库一样,需要进行备份。Oracle 建议使用RMAN 进行备份,当然,应将控制文件而不是恢复目录用作RMAN 资料档案库。千万不要将包含数据库的RMAN 资料档案库的恢复目录存储在与目标数据库相同的数据库中,或与目标数据库相同的磁盘上。只有独立于要保护的数据时,恢复目录才有效。 配置控制文件自动备份,以便每次备份包含恢复目录时,都备份控制文件。任何时候在目标数据库中创建备份时,都可随后备份恢复目录。这样做可保护最新备份记录。 2.下面概要介绍了如何针对recovery catalog配置备份和恢复环境: 在ARCHIVELOG模式下运行恢复目录。 将保留策略的REDUNDANCY设置为大于1 的值。 将恢复目录备份到磁盘和磁带。 要创建备份,请使用BACKUP DATABASE PLUS ARCHIVELOG命令。 使用控制文件(NOCATALOG) 而不是其它恢复目录作为RMAN 资料档案库。 将控制文件自动备份配置为ON。
七.重新创建无法恢复的recovery catalog

如果恢复目录数据库已丢失或已损坏,并且通过正常Oracle 恢复过程无法恢复恢复目录数据库时,就必须重新创建恢复目录。 可以使用以下命令重新填充恢复目录的部分内容: ? RESYNC CATALOG:通过此命令,使用来自目标数据库控制文件或控制文件副本的任何RMAN 资料档案库信息更新恢复目录。 请注意,来自因时间太早而从控制文件中清除的控制文件记录的元数据会丢失。 ? CATALOG START WITH...:使用此命令可重新将所有可用备份列入目录。
八.升级和删除 recovery catalog
1.升级恢复目录 如果使用的恢复目录版本低于RMAN 客户机需要的版本,则必须通过执行UPGRADE CATALOG命令来升级它。 ? 要安装新的恢复目录方案,恢复目录用户必须具有CREATE TYPE权限。 ? 必须连接到目录数据库,而且目录数据库必须处于打开状态。不必连接到目标数据库。 ? 必须再次输入UPGRADE命令来确认升级。如果恢复目录的版本已高于RMAN 执行文件所需的版本,你将收到错误消息。但是,如果恢复目录是最新版本,RMAN 将允许此命令运行,所以可根据需要重新创建程序包。 ? RMAN 会在消息日志中显示升级期间生成的所有错误消息。
1.1.确定恢复目录方案的版本: #使用恢复目录方案的拥有者连接数据库 [oracle@master ~]$ sqlplus rman/rman #查询版本,如果返回多行记录,最高版本是当前恢复目录的把版本。 SQL> select * from rcver;
VERSION ------------ 11.02.00.01
1.2.使用upgrade catalog命令:

To upgrade the recovery catalog:

    If you created the recovery catalog owner in a release before 10gR1, and if the RECOVERY_CATALOG_OWNER role did not include theCREATE TYPE privilege, then grant it.

    For example, start SQL*Plus and connect to the recovery catalog database with administrator privileges. You can then execute the following GRANT statement:

    SQL> GRANT CREATE TYPE TO rman;
    SQL> EXIT;
    

    Start RMAN and connect RMAN to the recovery catalog database.

    Run the UPGRADE CATALOG command:

    RMAN> UPGRADE CATALOG;
    
    recovery catalog owner is rman 
    enter UPGRADE CATALOG command again to confirm catalog upgrade 
    

    Run the UPDATE CATALOG command again to confirm:

    RMAN> UPGRADE CATALOG;
    
    recovery catalog upgraded to version 11.01.00
    DBMS_RCVMAN package upgraded to version 11.01.00
    DBMS_RCVCAT package upgraded to version 11.01.00

    来源: >

    2.删除恢复目录 如果不再需要保留恢复目录,可以使用DROP CATALOG命令从表空间中删除恢复目录方案。删除目录时,会删除目录中注册的所有目标数据库的备份恢复目录记录。 ? 只能在RMAN 提示符下执行此命令。 ? 必须通过CATALOG命令行选项或CONNECT CATALOG命令连接到恢复目录数据库。 目录数据库必须处于打开状态。不必连接到目标数据库。 ? 请输入命令两次,确认要删除方案。
    九. IMPORT CATALOG
    1.使用import catalog 命令用于合并recovery catalog方案到其他的并recovery catalog方案中,这个命令在以下情况使用: 当有不同版本的数据库有多个recovery catalog 方案时,合并所有的方案到一个方案里而不会丢失备份元数据。
    移动recovery catalog 从一个数据库到另外一个数据库。
    2.使用import catalog的前提: 源恢复目录方案版本必须等于当前执行命令的rman的版本。
    3.导入恢复目录: 导入恢复目录到另一个恢复目录时,只需要源目录和目标目录保持连接性。 源恢复目录:导出恢复目录。 目标恢复目录: 接收源恢复目录的恢复目录。

    例:To import a recovery catalog:

      Start RMAN and connect as CATALOG to the destination recovery catalog schema. For example:

      % rman
      RMAN> CONNECT CATALOG 111cat@destdb;
      

      Import the source recovery catalog schema, specifying the connection string for the source catalog.

      For example, enter the following command to import the catalog owned by 102cat on database srcdb:

      IMPORT CATALOG 102cat@srcdb;
      

      A variation is to import metadata for a subset of the target databases registered in the source catalog. You can specify the databases by DBID or database name, as shown in the following examples:

      IMPORT CATALOG 102cat@srcdb DBID=1423241, 1423242;
      IMPORT CATALOG 102cat@srcdb DB_NAME=prod3, prod4;
      

      Optionally, connect to a target database to check that the metadata was successfully imported. For example, the following commands connect to database prod1 as TARGET and list all backups for this database:

      LIST BACKUP;



      4.import catalog语法:
      IMPORT CATALOG [DBID = [, ,…]] [DB_NAME=[, 是源恢复目录连接字符串。源恢复目录方案的版本必须等于RMAN 可执行文件的当前版本。如果需要,将源目录升级到当前RMAN 本版。 DBID:你可以指定数据库ID 的列表,数据库ID 的元数据应从源目录方案导入。未指定列表时,RMAN 将所有数据库ID 的元数据从源目录方案合并到目标目录方案中。如果已在恢复目录方案中注册了合并元数据的数据库,RMAN 就会发出错误消息。
      DB_NAME:可以指定应导入其元数据的数据库的名称列表。如果数据库名称不明确,RMAN 就会发出错误消息。 NO UNREGISTER:默认情况下,导入的数据库ID 在成功导入后从源恢复目录方案中注销。使用NO UNREGISTER选项,可以强制RMAN 将导入的数据库ID 保留在源目录方案中。
      5.有关使用的其它详细资料 ? 确保没有目标数据库既在源目录方案中注册,又在目标目录方案中注册。如果某个目标数据库在这两个方案中都注册了,请将此数据库从源目录中注销,然后重试导入。 ? 如果导入过程中操作失败,则将回退导入。从不存在部分导入的情况。 ? 源目录方案和目标目录方案中的存储脚本名称发生冲突时,RMAN 会重命名源目录方案的存储脚本。
      十. virtual private catalog
      1.使用此功能可以合并RMAN 资料档案库并保持职责分离,这是一项基本安全要求。 RMAN 目录已被增强,可用于创建数据库组和用户组的虚拟专用RMAN 目录。该目录的所有者将创建基本目录并为虚拟目录的所有者授予RECOVERY_CATALOG_OWNER权限。目录的所有者可为虚拟目录的所有者授予访问已注册数据库的权限或REGISTER权限。然后,虚拟目录所有者可连接到特定目标的目录,或者注册一个目标数据库。完成配置后,VPC 所有者可像使用标准基本目录一样使用虚拟专用目录。 作为目录所有者,你可以访问该目录中所有已注册的数据库信息。你可以列出使用SQL*Plus 命令注册的所有数据库: SELECT DISTINCT db_name FROM DBINC; 作为虚拟目录所有者,你只能查看授予了你访问权限的数据库。 注:如果目录所有者未被授予对目标数据库的SYSDBA或SYSOPER权限,则无法执行大多数RMAN 操作。

      2.创建一个虚拟私有目录步骤:

      The basic steps for creating a virtual private catalog are as follows:

        Create the database user who will own the virtual private catalog (if this user does not exist) and grant this user access privileges.

        This task is described in "Creating and Granting Privileges to a Virtual Private Catalog Owner".

        Create the virtual private catalog.

        This task is described in "Creating a Virtual Private Catalog".


        来源: >

        Creating and Granting Privileges to a Virtual Private Catalog Owner

        This section assumes that you created the base recovery catalog.

        Assume that the following databases are registered in the base recovery catalog: prod1, prod2, and prod3. The database user who owns the base recovery catalog is catowner. You want to create database user vpc1 and grant this user access privileges only to prod1 and prod2. By default, a virtual private catalog owner has no access to the base recovery catalog.

        To create and grant privileges to a virtual private catalog owner:

        Start SQL*Plus and connect to the recovery catalog database with administrator privileges.

        If the user that will own the virtual private catalog does not exist, then create the user.

        For example, if you want to create database user vpc1 to own the catalog, then you could execute the following command (replacing password with a user-defined password):

        SQL> CREATE USER vpc1 IDENTIFIED BY password
          2  DEFAULT TABLESPACE vpcusers
          3  QUOTA UNLIMITED ON vpcusers;
        

        Note:

        Create a password that is secure. See Oracle Database Security Guide for more information.

        Grant the RECOVERY_CATALOG_OWNER role to the database user that will own the virtual private catalog, and then exit SQL*Plus.

        The following example grants the role to user vpc1:

        SQL> GRANT recovery_catalog_owner TO vpc1;
        SQL> EXIT;
        

        Start RMAN and connect to the recovery catalog database as the base recovery catalog owner (not the virtual private catalog owner).

        The following example connects to the base recovery catalog as catowner:

        % rman
        RMAN> CONNECT CATALOG catowner@catdb;
        
        recovery catalog database Password: password
        connected to recovery catalog database
        

        Grant desired privileges to the virtual private catalog owner.

        The following example gives user vpc1 access to the metadata for prod1 and prod2 (but not prod3):

        RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1;
        RMAN> GRANT CATALOG FOR DATABASE prod2 TO vpc1;
        

        You can also use a DBID rather than a database name. The virtual private catalog user does not have access to the metadata for any other databases registered in the recovery catalog.

        You can also grant the user the ability to register new target databases in the recovery catalog. For example:

        RMAN> GRANT REGISTER DATABASE TO vpc1;
        

        Creating a Virtual Private Catalog

        This section assumes that the virtual private catalog owner has been given the RECOVERY_CATALOG_OWNER database role. Also, the base recovery catalog owner used the GRANT command to give the virtual private catalog owner access to metadata in the base recovery catalog.

        To create a virtual private catalog:

        Start RMAN and connect to the recovery catalog database as the virtual private catalog owner (not the base recovery catalog owner).

        The following example connects to the recovery catalog as vpc1:

        % rman
        RMAN> CONNECT CATALOG vpc1@catdb;
        

        Create the virtual private catalog.

        The following command creates the virtual private catalog:

        RMAN> CREATE VIRTUAL CATALOG;
        

        If you intend to use a 10.2 or earlier release of RMAN with this virtual private catalog, then execute the following PL/SQL procedure (wherebase_catalog_owner is the database user who owns the base recovery catalog):

        SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
        

        Revoking Privileges from a Virtual Private Catalog Owner

        This section assumes that you have created a virtual private catalog.

        Assume that two databases are registered in the base recovery catalog: prod1 and prod2. As owner of the base recovery catalog, you have granted the vpc1user access privileges to prod1. You have also granted this user the right to register databases in his virtual private catalog. Now you want to revoke privileges from vpc1.

        To revoke privileges from a virtual private catalog owner:

        Start RMAN and connect to the recovery catalog database as the recovery catalog owner (not the virtual private catalog owner).

        The following example connects to the recovery catalog as catowner:

        % rman
        RMAN> CONNECT CATALOG catowner@catdb;
        

        Revoke specified privileges from the virtual private catalog owner.

        The following command revokes access to the metadata for prod1 from virtual private catalog owner vpc1:

        REVOKE CATALOG FOR DATABASE prod1 FROM vpc1;
        

        You can also specify a DBID rather than a database name. The catalog vpc1 retains all other granted catalog privileges.

        You can also revoke the privilege to register new target databases in the recovery catalog. For example:

        REVOKE REGISTER DATABASE FROM vpc1;
        

        Dropping a Virtual Private Catalog

        This section assumes that you have created a virtual private catalog and now want to drop it. When you drop a virtual private catalog, you do not remove the base recovery catalog itself, but only drop the synonyms and views that refer to the base recovery catalog.

        To drop a virtual private catalog:

        Start RMAN and connect to the recovery catalog database as the virtual private catalog owner (not the base recovery catalog owner).

        The following example connects to the recovery catalog as user vpc1:

        % rman
        RMAN> CONNECT CATALOG vpc1@catdb;
        

        Drop the catalog.

        If you are using an Oracle Database 11g or later RMAN executable, then drop the virtual private catalog with the DROP CATALOG command:

        RMAN> DROP CATALOG;
        

        If you are using an Oracle Database 10g or earlier RMAN executable, then you cannot use the DROP CATALOG command. Instead, connect SQL*Plus to the catalog database as the virtual private catalog user, then execute the following PL/SQL procedure (where base_catalog_owner is the database user who owns the base recovery catalog):

        SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.DELETE_VIRTUAL_CATALOG;

        来源: >