Creating a Recovery Catalog

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

---

RMAN can be used either with or without a recovery catalog. A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. Generally, an experienced DBA would suggest that the Enterprise Manager instance schema and RMAN catalog schema be placed in the same utility database on a server separate from the main servers. The RMAN schema generally only requires 15 megabyte per year per database backed up.

The RMAN schema owner is created in the RMAN database using the following steps:

• 1. Start SQL*Plus and connect as a user with administrator privileges to the database containing the recovery catalog. For example, enter:

CONNECT SYS/oracle@catdb AS SYSDBA

• 2. Create a user and schema for the recovery catalog. For example, enter:

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

• 3. Grant the recovery_catalog_owner role to the user. This role provides all of the privileges required to maintain and query the recovery catalog:

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Once the owner user is created, the RMAN recovery catalog schema can be added:

• 1. Connect to the database that contains the catalog owner. For example, using the RMAN user from the above example, enter the following from the operating system command line.  The use of the CATALOG keyword tells Oracle this database contains the repository:

% rman CATALOG rman/cat@catdb

• 2. It is also possible to connect from the RMAN utility prompt:

% rman

RMAN> CONNECT CATALOG rman/cat@catdb

• 3. Now, the CREATE CATALOG command can be run to create the catalog. The creation of the catalog may take several minutes. If the catalog tablespace is this user's default tablespace, the command would look like the following:

CREATE CATALOG;

While the RMAN catalog can be created and used from either a 9i or 10g database, the Enterprise Manager Grid Control database must be a 9i database.  This is true at least for release 1, although this may change with future releases.

Each database that the catalog will track must be registered.

• Registering a Database with RMAN

The following process can be used to register a database with RMAN:

• 1. Make sure the recovery catalog database is open.

• 2. Connect RMAN to both the target database and recovery catalog database. For example, with a catalog database of RMANDB and user RMAN, owner of the catalog schema, and the target database, AULT1, which is the database to be backed up, database user SYS would issue:

% rman TARGET sys/oracle@ault1 CATALOG rman/cat@rmandb

• 3. Once connected, if the target database is not mounted, it should be opened or mounted:

RMAN> STARTUP;

--or--

RMAN> STARTUP MOUNT;

• 4. If this target database has not been registered, it should be registered it in the connected recovery catalog:

RMAN> REGISTER DATABASE;

The database can now be operated on using the RMAN utility.

• Example RMAN Operations

The following is an example of the command line connection to a RAC environment, assuming the RAC instances are AULT1 and AULT2:

$ rman TARGET SYS/kr87m@ault2 CATALOG rman/cat@rmandb

The connection string, in this case AULT2, can only apply to a single instance, so the entry in the tnsnames.ora for the AULT2 connection would be:

ault2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (LOAD_BALANCE = OFF)
    (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = aultlinux2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ault)
      (INSTANCE_NAME = ault2)
    )

• If the instances use archive logs, RAC requires that a channel connection be specified for each instance that will resolve to only one instance. For example, using the AULT1 and AULT2 instances from the previous example:

CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE DEVICE TYPE TO sbt PARALLELISM 2;
CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT = 'SYS/kr87m@ault1';
CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT = 'SYS/kr87m@ault2';

• This configuration only has to be specified once for a RAC environment. It should be changed only if nodes are added or removed from the RAC configuration. For this reason, it is known as a persistent configuration, and it need never be changed for the life of the RAC system. This configuration requires that each of the specified nodes be open, the database is operational, or closed, the database shutdown. If one specified instance is not in the same state as the others, the backup will fail.

RMAN is also aware of the node affinity of the various database files. The node with the greatest access will be used to backup those datafiles that the instance has greatest affinity for. Node affinity can, however, be overridden with manual commands, as follows:

BACKUP
            #Channel 1 gets datafiles 1,2,3
            (DATAFILE 1,2,3 CHANNEL ORA_SBT_TAPE_1)
            #Channel 2 gets datafiles 4,5,6,7
            (DATAFILE 4,5,6,7 CHANNEL ORA_SBT_TAPE2)

The nodes chosen to backup an Oracle RAC cluster must have the ability to see all of the files that require backup. For example:

BACKUP DATABASE PLUS ARCHIVELOG;

The specified nodes must have access to all archive logs generated by all instances. This could entail some special considerations when configuring the Oracle RAC environment.

The essential steps for using RMAN in Oracle RAC are:

* Configure the snapshot control file location.

* Configure the control file autobackup feature.

* Configure the archiving scheme.

* Change the archivemode of the database, although this is optional.

* Monitor the archiver process.

The following section will show how the snapshot control file location is configured.

定制log组的数量和大小

根据 统计事务量 来确定 日志组数
下列语句可以 统计事务量
select to_char(first_time,'YYYY-MM-DD HH24') v_time, count(*）from v$log_history
group by to_char(first_time, 'YYYY-MM-DD HH24') order by v_time desc

看一小时 切换 几个log 一小时 切换 不能多余5次，否则影响性能

加大日志文件 也可以 ， 但是当库坏，会丢的数据多些

redo 的 组多， 是为 避免 redo 一圈 使用完，要覆盖时， 却发现不能覆盖，要覆盖的还没归档
redo 的 大小：为了解决 checkpoint 数量过多 checkpoint 数量 每小时 5次， 越多越影响性能

++++++++++++++++++++++
关于影响性能说明：
1、
日志切换太频繁，那么就会引起数据写很频繁，那么就会影响性能
（日志切换就会产生checkpoint,产生了checkpoint那么就会写数据）

2、
checkpoint是把内存中的脏数据写到磁盘
checkpoint是一个数据库事件，它将已修改的数据从高速缓存刷新到磁盘，并更新控制文件和数据文件
如果checkpoint越大(即log_checkpoint_interval值)，一次写磁盘的数据

也会越多。另外，内存中的脏数据也会越多，而且这些脏数据部分占用了内存空间，其他可用的空间就会更少。
还有，checkpoint越大，数据库意外停止后的启动时，需要恢复的过程也会越长。
++++++++++++++++++++++

checkpoint ： date block 从 memeber --> disk 同步

redo 的 越大， checkpoint 数量越少（当 redo切换时，会做一次 checkpoint）

select a.member,a.group#,b.thread#,b.bytes,b.members,b.status from v$logfile a,v$log b where a.group#=b.group# order by a.group#;

6 rows selected.

如果看到将要当前使用日志组的下一组状态为active,那说明可能发生要切

换日志了但是日志还没归档完毕（active说明正在归档），所以有必要增

加一组日志来避免这种情况
也可以增大日志文件，但是增大日志文件会增大数据库的风险

获取更新的行数

一:在pl/sql下面,你可以用sql%rowcount;

举例：

1。 SQL> set serveroutput on
SQL> begin
2 update testa set salary=2600 where first_name='ccc' and rownum<2;
3 dbms_output.put_line(to_char(SQL%ROWCOUNT));
4 END;
5 /
1
PL/SQL 过程已成功完成。

举例
SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update t5 set deptno=90 where deptno=30;
  3  dbms_output.put_line(to_char(sql%rowcount));
  4* end;
SQL> /
8

PL/SQL procedure successfully completed.

SQL> rollback
  2  ;

Rollback complete.

oracle 的 copy 命令

在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy 命令便是其中之一。SQL*Plus Copy 命令通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。
在实际运行环境中若能恰当地选择使用SQL*Plus Copy 命令可以有效地提高数据复制的性能。
下面将简要介绍SQL*Plus Copy 命令使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用Copy 命令的方案参考。
1.语法及使用说明
1.1 语法
下面我们来看一下SQL*Copy 命令的语法及使用说明。
在解释SQL*Plus Copy 命令的语法之前，我们必须要明确SQL*Plus Copy 命令不是一个方法或是函数，也不是一个SQL语句，它是一个命令(command)，当然这个命令必须在SQL*Plus里运行。

中国网管联盟bitsCN.com

SQL*Plus Copy 命令的语法：
网管联盟bitsCN_com

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

我们分部分来解释一下：

• COPY – 这个不太需要解释，主命令，声明要执行COPY操作

From Database – 源数据库
To Database – 目标数据库
此处注意花括号中有三种可选的写法(以”|”隔开)，如果源数据表和目标数据表在同一个Schema中，则可以只写From Database，也可以只写To Database，当然还可以是第三种写法，把From Database和To Database写全。但如果源数据表和目标数据表不在同一个Schema中，则必须用第三种写法，即把From Database和To Database都写全
From Database和To Database的格式是一样的：USERID/PASSWORD@SID，这个大家都应该很熟悉了。 网管网www_bitscn_com

{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的方式，下面分别解释一下：

• Append – 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。

• Create – 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。

• Insert – 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。

• Replace – 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。

• destination_table – 目标表的名字

• [(column, column, column, ...)] – 可以指定目标表中列的名字，如果不指定，则自动使用Query中的列名。

• USING query – 查询语句，交流的数据来自这儿。

1.2 使用范例
下面我们通过几个例子来看一下SQL*Plus Copy 命令的使用：

1.2.1 在同一个服务器的同一个Schema中复制数据：
同时指定From database和To database
SQL> copy from scott/tiger@orcl to scott/tiger@orcl create dept1 using select * from dept;
只指定From Database
SQL> copy from scott/tiger@orcl create dept2 using select * from dept; 中国网管联盟bitsCN.com
只指定To Database
SQL> copy to scott/tiger@orcl create dept3 using select * from dept;

1.2.2 在同一个服务器的不同Schema中复制数据：
这种情况下必须同时指定From Database和To Database
SQL> copy from scott/tiger@orcl to u1/u1@orcl create dept using select * from dept;
注意这种情况下，using select * from dept 中并不需要使用scott.demp的形式。

1.2.3 在不同的服务器间复制数据：
SQL> conn u1/u1@sunserve
已连接。
SQL> copy from scott/tiger@orcl to u1/u1@sunserve create dept using select * from dept;
2.4 性能实验结果
实验数据：
记录数：5,082,500
数据量：504M
实验结果

方案---------------------执行时间(秒) ---------Undo(M) ------Redo(M)
Copy command ------------- 520.51--------------0 ----------- 592
Insert into…select …----   631.64 ------------345 ---------- 1720

网管论坛bbs_bitsCN_com

Create Table…------------ 244.79 -------------0 ------------ 515

2.总结

Create Table…as select…是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。
Insert into … select … 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较简单，适合于处理少量的数据，若要处理大量的数据，不推荐使用这种方案。
Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更高一些，生成更少的Redo信息，不生成Undo信息，所以在执行大量的数据追加时，推荐使用Copy Command命令。

监听由动态改成静态

2.若不是unknow 状态则用以下方法修改

a)    sys连接到库 SQL> show parameter names 得到global_names和instrance_name(sid_name)的值。
b)    打开listener.ora 加入 如：

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /export/home/11.2.0/grid/11.2.0)
   (PROGRAM = extproc)

--(ENVS = EXTPROC_DLLS = /xxx/ArcSDE/bst_shapelib.so")

    )
(SID_DESC =
  (SID_NAME = ORCL)
  (ORACLE_HOME = /export/home/11.2.0/grid/11.2.0)
  (GLOBAL_DBNAME=ORCL.EXAMPLE.COM)
  )
)

注意:

1.其中xxx代表oracle安装目录 。
2.这里的global_dbname=ORCL.EXAMPLE.COM ，SID_NAME=ORCL
这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致

[oracle@luck~]$ echo $ORACLE_SID  得到ORCL

c) 打开 tnsnames.ora 加入

jingtai=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LUCKY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL.EXAMPLE.COM)
    )
  )

EXTPROC_CONNECTION_DATA = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

     )

    (CONNECT_DATA =

       (SID = PLSExtProc)

       (PRESENTATION = RO)

    )

)
注意：

1.  这里的服务名为 ORCL.EXAMPLE.COM 而不是通常的 SID的ORCL，因为在 listener.ora 中已经注册了 ORCL.EXAMPLE.COM ,lsnrctl 启动时会监听 ORCL.EXAMPLE.COM ，并对

应到 SID_NAME=ORCL 上。
2.  HOST为对应的目标主机的主机名。
d) 停止、启动监听和服务
全部配置完成最后，重启数据库实例和监听

sqlplus / as sysdba 登录时报 TNS的错误

操作系统windows，oracle11g在服务端，以sys登陆不加服务名，登陆报错；如果加上服务名登陆正确。重启操作系统，重启db，现在在服务端无论用什么用户登陆只要不加服务名都错误；连接时加上服务名就正确。到底为什么呢？

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 1月 31 08:46:08 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/oracle@vassp as sysdba已连接。
SQL> exit从Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production中断开

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 1月 31 08:46:24 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect /as sysdba
ERROR:ORA-12560: TNS: 协议适配器错误

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

平台：windows+oracle9.2.0.1.0

SQL> select * from v$version;

BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

我的系统开始运行正常，系统只有一个数据库test1,为了自己做测试，又建了第两个数据库test2,这样当启动test1的服务后，当登录总是报如下错误：

ERROR:
ORA-12560: TNS: 协议适配器错误

自己尝试了几次没有搞定，发现登录时加上服务名可以正常登录，也就随它去了，但这个问题也依然在自己脑中．

经过测试发现，

１．确认sqlnet.ora文件中设置为操作系统验证

　　SQLNET.AUTHENTICATION_SERVICES= (NTS)

2．查看windows对应的服务是否开启

打开services查看，服务名oracleservice<SID>的运行状态，该服务的启动与关闭也可通过CML（命令行 COMMAND LINE）进行操作．

SQL> $net start oracleserviceTEST2The OracleServiceTEST2 service is starting.
The OracleServiceTEST2 service was started successfully.

SQL> $net stop oracleserviceTEST2The OracleServiceTEST2 service is stopping...
The OracleServiceTEST2 service was stopped successfully.

注：WINNT中，在sqlplus中运行dos命令时，命令前加＂$＂符号

3．查看本地环境变量ORACLE_SID,ORACLE_HOME是否正确设置

C:Documents and SettingsAdministrator> set
ALLUSERSPROFILE=C:Documents and SettingsAll Users
APPDATA=C:Documents and SettingsAdministratorApplication Data
ClusterLog=C:WINDOWSClustercluster.log
CommonProgramFiles=C:Program FilesCommon Files
COMPUTERNAME=LDY
ComSpec=C:WINDOWSsystem32cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=Documents and SettingsAdministrator
JSERV=F:oracleora92/Apache/Jserv/conf
LOGONSERVER=LDY
NUMBER_OF_PROCESSORS=1
oracle_home=F:oracleora92
oracle_sid=TEST1OS=Windows_NT．．．．

4．如果没有设置环境变量，请查看注册表中，HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOME0中的ORACLE_SID,ORACLE_HOME项是否正确设置，如果oracle只有一个数据库，只需注册表中正确设置即可，无需设置环境变量．当然，如果像有多个数据库，需动态设置该环境变量

C:Documents and SettingsAdministrator>set oracle_home=

C:Documents and SettingsAdministrator>set oracle_sid=

C:Documents and SettingsAdministrator>set
ALLUSERSPROFILE=C:Documents and SettingsAll Users
APPDATA=C:Documents and SettingsAdministratorApplication Data
ClusterLog=C:WINDOWSClustercluster.log
CommonProgramFiles=C:Program FilesCommon Files
COMPUTERNAME=LDY
ComSpec=C:WINDOWSsystem32cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=Documents and SettingsAdministrator
JSERV=F:oracleora92/Apache/Jserv/conf
LOGONSERVER=LDY
NUMBER_OF_PROCESSORS=1
OS=Windows_NT．．．无此变量

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 19:56:47 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba已连接。

5．如果有多个数据库，若想默认连接某个数据库，可不管当前注册表的设置，手动设置环境变量ORACLE_SID即可请看实验：

当前注册表ORACLE_SID=TEST1，

C:Documents and SettingsAdministrator>set oracle_sid=

?/P>

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 20:39:41 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

?/P>

SQL> $net stop oracleserviceTEST2

The OracleServiceTEST2 service is stopping...

The OracleServiceTEST2 service was stopped successfully.

?/P>

SQL> $net stop oracleserviceTEST1

The OracleServiceTEST1 service is stopping........

The OracleServiceTEST1 service could not be stopped.

?/P>

SQL> $net start oracleserviceTEST1

The OracleServiceTEST1 service is starting.

The OracleServiceTEST1 service was started successfully.

?/P>

SQL> conn / as sysdba

已连接到空闲例程。

--注：当有注册设置时，即使没有设置环境变量，此时系统可按注册表设置进行TEST1的登录

SQL> $net stop oracleserviceTEST1

The OracleServiceTEST1 service is stopping...

The OracleServiceTEST1 service was stopped successfully.

?/P>

SQL> conn / as sysdba

ERROR:

ORA-12560: TNS: 协议适配器错误

警告: 您不再连接到 ORACLE。

--注：当相关WINNT服务ORACLESERVICETEST1没有开启，用户将无法正常连接TEST1

SQL> $net start oracleserviceTEST1

The OracleServiceTEST1 service is starting.

The OracleServiceTEST1 service was started successfully.

?/P>

SQL> conn / as sysdba

已连接到空闲例程。

?/P>

SQL> exit

已断开连接

?/P>

C:Documents and SettingsAdministrator>set oracle_sid=TEST2

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 20:45:29 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

?/P>

SQL> conn / as sysdba

ERROR:

ORA-12560: TNS: 协议适配器错误

--注：环境变量的设置影响用户登录数据库的选择，此时设置为TEST2，而NT服务ORACLESERVICEQTEST2没有开启，所以显示登录错误

SQL> $NET START ORACLESERVICEQTEST2

The OracleServiceTEST2 service is starting.

The OracleServiceTEST2 service was started successfully.

?/P>

SQL> CONN / AS SYSDBA

已连接到空闲例程。

部分摘自：http://oracle114.itpub.net/post/21719/259479

另外今天我也遇到这个错误提示

查看了一下listener 是启动的，并且注册表里是有oracle_sid=oratest的，只是今天我们网络不通，因为数据库在我本地机器上，所以开始并不认为是tnsnames.ora里的host=192.168.5.195的问题，但是又有所怀疑，所以把 ip 地址改成了 我机器的名字，保存以后 sqlplus aaa/aa233@oratest 就没问题了：）所以当网络不通的时候还是用自己机器名字的好