手工创建CATADB数据库，备份、配置和使用

今天因为工作原因，需要部署一个rman catalog库，对另一个进行备份，正好复习下手工建库。
全部过程记录如下：

一、手工创建【catadb】数据库

1.1创建密码文件orapwcatadb

[oracle@db1 dbs]$ orapwd file=orapwcatadb password=oracle entries=30
[oracle@db1 dbs]$ ll
total 40
-rw-rw----. 1 oracle oinstall 1544 sep 18 10:44 hc_oradb3.dat
-rw-r--r--. 1 oracle oinstall 2851 may 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 sep 15 14:42 lkoradb3
-rw-r-----. 1 oracle oinstall 5120 sep 20 10:32 orapwcatadb
-rw-r-----. 1 oracle oinstall 1536 sep 15 14:45 orapworadb3
-rw-r-----. 1 oracle oinstall 2560 sep 18 10:45 spfileoradb3.ora

1.2生成初始化参数文件并修改

[oracle@db1 dbs]$ cat init.ora |grep -v ^$ |grep -v ^# >initcatadb.ora
[oracle@db1 dbs]$ cat initcatadb.ora 
db_name='catadb'
memory_target=1g
processes = 150
audit_file_dest='$oracle_base/admin/catadb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='$oracle_base/fast_recovery_area'
db_recovery_file_dest_size=2g
diagnostic_dest='$oracle_base'
dispatchers='(protocol=tcp) (service=catadbxdb)'
open_cursors=300 
remote_login_passwordfile='exclusive'
undo_tablespace='undotbs'
control_files = '/u01/app/oracle/oradata/catadb/ora_control1.ctl', '/u01/app/oracle/oradata/catadb/ora_control2.ctl'
compatible ='11.2.0'

1.3 创建相关目录

[oracle@db1 dbs]$ mkdir -p $oracle_base/admin/catadb/adump
[oracle@db1 dbs]$ mkdir -p $oracle_base/fast_recovery_area
[oracle@db1 dbs]$ mkdir -p /u01/app/oracle/oradata/catadb/

1.4 参照官方文档脚本创建建库sql脚本

[oracle@db1 dbs]$ cat crdb.sql 
create database catadb
   user sys identified by oracle
   user system identified by oracle
   logfile group 1 ('/u01/app/oracle/oradata/catadb/redo01a.log','/u01/app/oracle/oradata/catadb/redo01b.log') size 100m blocksize 512,
           group 2 ('/u01/app/oracle/oradata/catadb/redo02a.log','/u01/app/oracle/oradata/catadb/redo02b.log') size 100m blocksize 512,
           group 3 ('/u01/app/oracle/oradata/catadb/redo03a.log','/u01/app/oracle/oradata/catadb/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/app/oracle/oradata/catadb/system01.dbf' size 325m reuse
   sysaux datafile '/u01/app/oracle/oradata/catadb/sysaux01.dbf' size 325m reuse
   default tablespace users
      datafile '/u01/app/oracle/oradata/catadb/users01.dbf'
      size 500m reuse autoextend on maxsize unlimited
   default temporary tablespace temp
      tempfile '/u01/app/oracle/oradata/catadb/temp01.dbf'
      size 20m reuse
   undo tablespace undotbs
      datafile '/u01/app/oracle/oradata/catadb/undotbs01.dbf'
      size 200m reuse autoextend on maxsize unlimited;

1.5创建spfile并启动实例，开始创建数据库

[oracle@db1 ~]$export oracle_sid=catadb
[oracle@db1 ~]$sqlplus / as sysdba
sql*plus: release 11.2.0.4.0 production on wed sep 20 10:42:56 2017
copyright (c) 1982, 2013, oracle.  all rights reserved.
connected to an idle instance.
sys@catadb> create spfile from pfile;
file created.

sys@catadb> startup nomount;
oracle instance started.
total system global area 1068937216 bytes
fixed size          2260088 bytes
variable size         671089544 bytes
database buffers      390070272 bytes
redo buffers            5517312 bytes

sys@catadb> @crdb.sql
database created.

1.6执行手工建库后脚本

[oracle@db1 dbs]$ cat 1.sql

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

sys@catadb> @1.sql
doc>######################################################################
doc>######################################################################
doc>    the following statement will cause an "ora-01722: invalid number"
doc>    error and terminate the sqlplus session if the user is not sys.
doc>    disconnect and reconnect with as sysdba.
doc>######################################################################
doc>######################################################################
doc>#
no rows selected
session altered.
timestamp
------------------------------------------------------------
comp_timestamp catalg_bgn 2017-09-20 10:50:16 2458017 39016
package created.
package body created.
grant succeeded.
synonym created.
grant succeeded.
table created.
......
......
pl/sql procedure successfully completed.

1.7 确认建库成功

system@catadb> rem =========================================================================
system@catadb> rem end utlrp.sql
system@catadb> rem ===========================================================================

system@catadb> select * from v$version;

banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
pl/sql release 11.2.0.4.0 - production
core    11.2.0.4.0  production
tns for linux: version 11.2.0.4.0 - production
nlsrtl version 11.2.0.4.0 - production

5 rows selected.

system@catadb> select open_mode from v$database;

open_mode
--------------------
read write

1 row selected.

二、创建catalog恢复目录

2.1 修改tnsnames.ora，添加catadb

[oracle@db1 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@db1 admin]$ cat tnsnames.ora 
# tnsnames.ora network configuration file: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# generated by oracle configuration tools.

oradb3 =
  (description =
    (address = (protocol = tcp)(host = db1.us.oracle.com)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = oradb3)
    )
  )

catadb =
  (description =
    (address = (protocol = tcp)(host = db1.us.oracle.com)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = catadb)
    )
  )

2.2 创建catalog表空间

sys@catadb> create tablespace cat_tbs datafile '/u01/app/oracle/oradata/catadb/cat_tbs01.dbf' size 100m;

tablespace created.

2.3 创建catalog管理账户并授权

sys@catadb> create user catrman identified by oracle
  2  temporary tablespace temp
  3  default tablespace cat_tbs
  4  quota unlimited on cat_tbs;

user created.

sys@catadb> grant recovery_catalog_owner,connect,resource to catrman;

grant succeeded.

2.4 进入rman创建catalog，然后升级

[oracle@db1 admin]$ rman 
recovery manager: release 11.2.0.4.0 - production on wed sep 20 11:11:41 2017
copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.

rman> connect catalog catrman/oracle
connected to recovery catalog database

rman> create catalog;
recovery catalog created

rman> upgrade catalog;
recovery catalog owner is catrman
enter upgrade catalog command again to confirm catalog upgrade

rman> upgrade catalog;
recovery catalog upgraded to version 11.02.00.04
dbms_rcvman package upgraded to version 11.02.00.04
dbms_rcvcat package upgraded to version 11.02.00.04

rman>

三、rman catalog 使用

3.1 注册目标数据库到catalog

[oracle@db1 ~]$ rman target / catalog catrman/oracle@catadb

recovery manager: release 11.2.0.4.0 - production on wed sep 20 11:14:03 2017

copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.

connected to target database: oradb3 (dbid=2731358481)
connected to recovery catalog database

rman> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

rman> 

3.2 查看当前rman备份策略

rman> show all;

rman configuration parameters for database with db_unique_name oradb3 are:
configure retention policy to redundancy 1; # default
configure backup optimization off; # default
configure default device type to disk; # default
configure controlfile autobackup off;
configure controlfile autobackup format for device type disk to '%f'; # default
configure device type disk parallelism 1 backup type to backupset; # default
configure datafile backup copies for device type disk to 1; # default
configure archivelog backup copies for device type disk to 1; # default
configure maxsetsize to unlimited; # default
configure encryption for database off; # default
configure encryption algorithm 'aes128'; # default
configure compression algorithm 'basic' as of release 'default' optimize for load true ; # default
configure archivelog deletion policy to none; # default
configure snapshot controlfile name to '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_oradb3.f'; # default

3.3 开启控制文件自动备份

rman> configure controlfile autobackup on;

old rman configuration parameters:
configure controlfile autobackup off;
new rman configuration parameters:
configure controlfile autobackup on;
new rman configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

rman> 

3.4 开启rman备份优化

rman> configure backup optimization on;

new rman configuration parameters:
configure backup optimization on;
new rman configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

rman> 

3.5 指定备份与恢复操作中的并行（parallelism）度为2，即同时开,2个通道进行备份和恢复，可以加快备份速度

rman> configure device type disk parallelism 2 backup type to backupset;

new rman configuration parameters:
configure device type disk parallelism 2 backup type to backupset;
new rman configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

rman> 

3.6 查看修改后配置策略

rman> show all;

rman configuration parameters for database with db_unique_name oradb3 are:
configure retention policy to redundancy 1; # default
configure backup optimization on;
configure default device type to disk; # default
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '%f'; # default
configure device type disk parallelism 2 backup type to backupset;
configure datafile backup copies for device type disk to 1; # default
configure archivelog backup copies for device type disk to 1; # default
configure maxsetsize to unlimited; # default
configure encryption for database off; # default
configure encryption algorithm 'aes128'; # default
configure compression algorithm 'basic' as of release 'default' optimize for load true ; # default
configure archivelog deletion policy to none; # default
configure snapshot controlfile name to '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_oradb3.f'; # default

rman> 

3.7 备份测试

[oracle@db1 ~]$ rman target / catalog catrman/oracle@catadb

recovery manager: release 11.2.0.4.0 - production on wed sep 20 13:29:46 2017

copyright (c) 1982, 2011, oracle and/or its affiliates.  all rights reserved.

connected to target database: oradb3 (dbid=2731358481)
connected to recovery catalog database

rman> backup database plus archivelog;


starting backup at 20-sep-17
current log archived
allocated channel: ora_disk_1
channel ora_disk_1: sid=10 device type=disk
allocated channel: ora_disk_2
channel ora_disk_2: sid=11 device type=disk
channel ora_disk_1: starting archived log backup set
channel ora_disk_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 recid=1 stamp=955200594
channel ora_disk_1: starting piece 1 at 20-sep-17
channel ora_disk_1: finished piece 1 at 20-sep-17
piece handle=/u01/app/oracle/fast_recovery_area/oradb3/backupset/2017_09_20/o1_mf_annnn_tag20170920t132956_dw3z6o5j_.bkp tag=tag20170920t132956 comment=none
channel ora_disk_1: backup set complete, elapsed time: 00:00:01
finished backup at 20-sep-17

starting backup at 20-sep-17
using channel ora_disk_1
using channel ora_disk_2
channel ora_disk_1: starting full datafile backup set
channel ora_disk_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oradb3/system01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/oradb3/test01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oradb3/example01.dbf
......