谢太数据库: backup and recovery

Showing posts with label backup and recovery. Show all posts

Monday, February 21, 2011

Using dNFS clonedb for testing - 使用dNFS搭建测试数据库

Using dNFS clonedb for testing
使用dNFS搭建测试数据库

1. 配置dNFS

1.1. 挂载NFS
编辑/etc/fstab，如：

192.168.1.1:/dbak/xxods /home/oracle/xxodsnew    nfs     rw,bg,hard,intr,nolock,tcp,rsize=32768,wsize=32768,nocto,retry=480  0       0

1.2. 启用nfsodm库
将原odm库改名，并建为nfsodm库的符号连接，如：

ln -s libnfsodm11.so libodm11.so

重启数据库，告警日志显示"Oracle instance running with ODM"，说明dNFS已启用

...
  pga_aggregate_target     = 1G
  dg_broker_start          = FALSE
  diagnostic_dest          = "/opt/oracle"
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Fri Dec 31 18:13:43 2010
...

1.3. 设置oradism可执行程序的属性
设置oradism可执行程序的属性setuid，执行

root.sh -silent

oradism可执行程序的属性变为：

-rwsr-x--- 1 root   oinstall     68432 Sep  4 22:21 oradism

如不设置，告警日志中将可能会报错"Direct NFS: please check that oradism is setuid"，如：

...
space available in the underlying filesystem or ASM diskgroup.
Fri Dec 31 18:19:19 2010
alter database backup controlfile to '/home/oracle/xxodsnew/control_bak.ctl'
Direct NFS: please check that oradism is setuid
Fri Dec 31 18:20:19 2010
Direct NFS: please check that oradism is setuid
Fri Dec 31 18:21:19 2010
Direct NFS: please check that oradism is setuid
Fri Dec 31 18:22:19 2010
Direct NFS: please check that oradism is setuid
Fri Dec 31 18:23:20 2010
Completed: alter database backup controlfile to '/home/oracle/xxodsnew/control_bak.ctl'
Fri Dec 31 18:23:51 2010
Starting background process SMCO
Fri Dec 31 18:23:51 2010
SMCO started with pid=28, OS id=18071
...

1.4. 检查视图
v$dnfs_servers: dnfs服务器
v$dnfs_channels: dnfs网络路径
v$dnfs_files: dnfs文件
v$dnfs_stats: dnfs状态

如：

SQL> SQL> select * from v$dnfs_servers;

        ID
----------
SVRNAME
--------------------------------------------------------------------------------
DIRNAME
--------------------------------------------------------------------------------
   MNTPORT    NFSPORT      WTMAX      RTMAX
---------- ---------- ---------- ----------
         1
192.168.1.1
/dbak/xxods
       721       2049          0          0

外部链接：
Direct NFS: FAQ [ID 954425.1]
Step by Step - Configure Direct NFS Client (DNFS) on Linux [ID 762374.1]
3.2.3 Deciding to Use Direct NFS for Data Files

Mount Options for Oracle files when used with NAS devices [ID 359515.1]

2. 在dnfs上搭建测试库

使用了dnfs写时拷贝技术（copy-on-write），测试库只写入修改的数据块，从而减少了其占用空间。

主要步骤如下：
2.1. 生产机导出nfs目录，试机挂载nfs目录
2.2. 创建生产库数据文件备份，用rman image复制，操作系统复制，或存储快照。数据文件备份存放在nfs目录上。
2.2. 生产库控制文件建跟踪备份
2.4. 测试库修改控制文件，将数据文件路径改为nfs备份目录。修改参数文件一些相应参数
2.5. nomount方式启动测试库，建控制文件
2.6. 执行dbms_dnfs.clonedb_renamefile存储过程，将数据文件备份改名到测试目录下。实际上没有真的改名，备份是只读的，不会有任何改动
2.8. 恢复并打开测试数据库

外部链接：
Oracle Database 11g Direct NFS Clonedb Feature – Part I.
Oracle Database 11g Direct NFS Clonedb Feature – Part I (and a half).
Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2)
Clone your dNFS Production Database for Testing [ID 1210656.1]

NOTE 1210656.1 from MOS:

Clone your dNFS Production Database for Testing [ID 1210656.1]

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

Modified 17-FEB-2011 Type ANNOUNCEMENT Status PUBLISHED

In this Document
What is being announced?
What do you need to do?

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

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Oracle RDBMS
dNFS
What is being announced?
Cloning a production database is an often needed procedure in customer environments to develop and test new application patches. When new OS releases, Storage software, Application version has to be installed in a production environment, it calls for a thorough testing using the production data. Today this is achieved by making a copy of the production datafiles in a test environment. In addition to the test environment, copies of the production database also made in development environment where application developers are creating their new applications and testing them. All of these require huge amount of storage space to be allocated and managed.

Database cloning process involves copying the entire production database files into the new environment where the development or testing takes place. In addition when customers have multiple test environments, the files need to be copied into each of these test environments. dNFS provides a new feature called clonedb which allows test databases to be cloned instantaneously. dNFS clonedb feature allows customers to set up their test database environment without really copying the production files into the new database environment. The files in the test database are created based on copy-on-write technology, so that only the blocks that are updated in the test database are ever written to the disk. This type of thin-provisioning reduces the amount of storage required for testing and development purposes. In addition to the reduced storage requirement, since the datafiles in the new test environment are not copied, setting up a test database is very quick and instantaneous.

Database cloning in a dNFS environment can be made from a rman full backup/image copy of all the relevant datafiles or from a storage snapshot. The database backup will be used as the backing store for the datafiles in the test database environment. Customers often take or required to take a full backup of their database and these files can be used to create the dnfs clonedb. By making the backup piece as the backing store the production data files are not touched by the test instances and the test instances don't compete for the same resources that the production database is using.
What do you need to do?
Following the few simple steps described below, one can clone a production database in a matter of few minutes. For the below example let us assume that your production database PROD1 is located at ORACLE_HOME=/u01/prod1/oracle. The rman backup of this production database is taken at RMAN_BACKUP_DIR=/u02/oracle/backup/prod1. The user is trying to clone a test database1 at ORACLE_HOME=/u03/test1/oracle and test database2 at ORACLE_HOME=/u03/test2/oracle

1.On your production database take a full rman hot/cold backup. If it is a hot backup make sure your production database is in archivelog mode and all the necessary archivelogs are saved and accessible to the new test database environment.
?To take a hot backup first put the database in hot backup mode.
alter database begin backup;
<Do a rman image copy/OS copy/Storage snapshot for each datafile>
alter database end backup;
?You can also run "backup as copy database format" rman command and get a copy of all your datafiles
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Aug 18 10:25:35 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN-06568: connected to target database: PROD1 (DBID=1985124264, not open)
RMAN-06008: connected to recovery catalog database
RMAN> backup as copy database format '/u02/oracle/backup/prod1/%U' ;
RMAN-03090: Starting backup at AUG 18 2010 10:25:37
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=110 device type=DISK
RMAN-08580: channel ORA_DISK_1: starting datafile copy
RMAN-08522: input datafile file number=00001 name=/u01/prod1/oracle/dbs/tbs_01.f
RMAN-08586: output file name=/u02/oracle/backup/prod1/data_D-PROD1_I-1985124264_TS-SYSTEM_FNO-1_01lll1d6 tag=TAG20100818T102541 RECID=1 STAMP=727352775
RMAN-08581: channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

The above command creates a backup of all your datafiles in the location
"/u02/oracle/backup/prod1/"
2.Generate backup control file script from your production database by connecting with sysdba privileges and executing 'alter database backup controlfile to trace'. This will generate a trace file in your user_dump_dest directory which contains the create control file command.
3.Generate the testdb_create.sql using the script in the trace file.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
STARTUP NOMOUNT PFILE='/u03/test1/oracle/dbs/testdb1.ora'
CREATE CONTROLFILE REUSE SET DATABASE "testdb1" RESETLOGS ARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXINSTANCES 1
MAXLOGHISTORY 908 LOGFILE
GROUP 1 '/u03/test1/oracle/dbs/t_1alog.f' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/u03/test1/oracle/dbs/t_2alog.f' SIZE 100M BLOCKSIZE 512
DATAFILE
' /u02/oracle/backup/prod1/data_D-PROD1_I-1985124264_TS-SYSTEM_FNO-1_01lll1d6',...CHARACTER SET WE8DEC? ;

make sure you change the name of the database to the test db name and the log directories are pointing to the new testdb environment and use the image copy of the datafiles.
4.Copy spfile from your production db to the testdb environment and change the relevant parameters. In your testdb environment run the testdb_create.sql to create testdb control file. Execute dbms_dnfs.clonedb_renamefile(backup_file_name, new_data_filename) on each datafile in your test database.

SQL> declare
2 begin
3 dbms_dnfs.clonedb_renamefile('/u02/oracle/backup/prod1/data_D-LV1_I-1985124264_TS-SYSTEM_FNO-1_01lll1d6' , 'test1_tbs_01.f');
4 end;
5.PL/SQL procedure successfully completed.
Do this for each datafile in your testdb environment.
6.If you created your test clone from a hotbackup do a recover database recover database using backup controlfile until cancel;
The above command will ask for the archivelogs for the period when the backup was taken and input those file names.
SQL> recover database using backup controlfile until cancel ;
7.Open the database and start running your tests

SQL> alter database open resetlogs ;

At this point your testdb will be available for use and you will be able to read and write to the database. The testdb datafiles will not have allocated any storage for the datafiles. As you start running your test work load, inserts on the datafiles will allocate space on demand.

The above same procedure can be repeated for multiple test database instances using the same backup files. If you have a storage level snapshot taken on the datafile, the rman backup file names can be replaced with the storage snapshot names.

Since all the testdb clones are using the rman backup or the storage snapshot as the backend storage, availability of these files is critical for testdb to run. If the backup files become unavailable, you will see errors in the testdb database.

When it is time to destroy the testdb environment, all files in the test environment can be deleted without any impact on the production or backup environment. As this clonedb feature uses the backup piece as the backing storage, there is no pressure on the I/O subsystem that is servicing the production database. DBA's don't have to hunt for storage equivalent to the size of production database to set up a testdb environment. The testdb storage space usage grows at the speed at which the data is modified.

Work-in-progress - A perl script to automate the above steps is in progress. Using this script one will be able to clone a database by issuing a single command after setting few environment variables.

3. dnfs性能测试

外部链接：
New DNFS Performance Results
Monitoring Direct NFS with Oracle 11g and Solaris… pealing back the layers of the onion.
Direct NFS vs Kernel NFS bake-off with Oracle 11g and Solaris… and the winner is
Kernel NFS fights back… Oracle throughput matches Direct NFS with latest Solaris improvements

-fin-

Wednesday, September 23, 2009

introducing maatkit - parallel dump

introducing maatkit - parallel dump
maatkit介绍 - 并行导出

Maatkit是一组为MySQL提供的命令行工具集, 是由Percona公司开发的开源软件
该公司同时还开发了XtraDB存储引擎, XtraBackup热备工具, MySQL增强补丁版本Percona

包括:

mk-archiver 将表数据清除或归档到另外的表或文件
mk-audit 分析MySQL的配置,概要,操作, 生成报表
mk-checksum-filter mk-table-checksum的过滤器
mk-deadlock-logger 记录InnoDB的死锁信息
mk-duplicate-key-checker 查找重复或冗余的外键和索引
mk-fifo-split 将一个文件拆分为多个部分, 输出到FIFO管道(有用吗?)
mk-find 按指定规则查找表名, 然后执行操作
mk-heartbeat 监视数据库之间复制的延迟
mk-log-player 拆分并重演慢速查询日志
mk-parallel-dump 多线程导出
mk-parallel-restore 多线程导入
mk-profile-compact 压缩mk-query-profiler输出
mk-query-digest 分析日志
mk-query-profiler 查询性能分析工具
mk-show-grants 显示用户权限
mk-slave-delay 实现备库与主库之间一定的延时
mk-slave-find 查找/显示出备库的树型层次结构
mk-slave-move 在层次结构中移动备库(什么玩意?)
mk-slave-prefetch 在备库上运行SELECT查询语句, 使数据预读取到内存中
mk-slave-restart 监测备库发生的错误并重启
mk-table-checksum 快速检测两个表的数据是否相同. 可以用来检测备库和主库的数据一致性
mk-table-sync 发现并修复不同服务器上的两个表之间的数据差异
mk-upgrade 比较2个数据库中语句的运行结果
mk-visual-explain 以树形显示执行计划

1. 安装
从http://maatkit.googlecode.com/处下载RPM包进行安装

yum -y install perl-TermReadKey.x86_64
rpm -Uvh http://maatkit.googlecode.com/files/maatkit-4623-1.noarch.rpm

依赖以下安装包

# rpm -q --requires maatkit
/usr/bin/env
perl(DBD::mysql) >= 1.0
perl(DBI)
perl(DBI) >= 1.13
perl(Data::Dumper)
perl(Digest::MD5)
perl(English)
perl(Exporter)
perl(File::Basename)
perl(File::Find)
perl(File::Spec)
perl(File::Temp)
perl(Getopt::Long)
perl(IO::File)
perl(List::Util)
perl(POSIX)
perl(Socket)
perl(Term::ReadKey) >= 2.10
perl(Time::HiRes)
perl(Time::Local)
perl(constant)
perl(sigtrap)
perl(strict)
perl(warnings)
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1

2. 并行导出mk-parallel-dump和导入mk-parallel-restore
使用方法见:
mk-parallel-dump - Dump sets of MySQL tables in parallel.
mk-parallel-restore - Load files into MySQL in parallel.

1) 生成测试表
生成一个3百万行记录的测试表

mysql --socket=/var/lib/mysql/data_3306/mysql.sock

set autocommit=0;
drop database if exists dbtest;
create database dbtest;
use dbtest;
drop table if exists t1;
create table t1 (
  id int(9) not null auto_increment,
  name varchar(20) not null,
  age int(3) not null,
  notes varchar(100),
  primary key (id),
  index ind_t1_name (name)
);
truncate table t1;
insert into t1 (name, age, notes)
select conv(floor(rand() * 99999999999999), 10, 36), floor(1+rand()*(100-1)), md5(rand())
  from information_schema.COLUMNS a
       , information_schema.COLUMNS b
       , information_schema.COLUMNS c
 limit 3000000;
commit;

大小300多M

mysql> insert into t1 (name, age, notes)
    -> select conv(floor(rand() * 99999999999999), 10, 36), floor(1+rand()*(100-1)), md5(rand())
    ->   from information_schema.COLUMNS a
    ->        , information_schema.COLUMNS b
    ->        , information_schema.COLUMNS c
    ->  limit 3000000;
Query OK, 3000000 rows affected (3 min 30.61 sec)
Records: 3000000  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> show table status like 't1';
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| Name | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment              |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| t1   | InnoDB |      10 | Compact    | 3000249 |             76 |   228294656 |               0 |     86654976 |         0 |        6000000 | 2009-09-23 05:26:34 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 4096 kB |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
1 row in set (0.08 sec)

mysql> system ls -l /var/lib/mysql/data_3306/dbtest
total 319820
-rw-rw---- 1 mysql mysql        61 Sep 23 04:17 db.opt
-rw-rw---- 1 mysql mysql      8646 Sep 23 05:26 t1.frm
-rw-rw---- 1 mysql mysql 327155712 Sep 23 05:30 t1.ibd
mysql>

2) mk-parallel-dump和mysqldump分别导出该表作比较
mysqldump导出表

# mkdir -p $HOME/backup
# cd $HOME/backup && rm -rf *
# time mysqldump --socket=/var/lib/mysql/data_3306/mysql.sock --opt dbtest t1 >dbback-dbtest-t1.sql

real    0m11.316s
user    0m2.348s
sys     0m0.472s
# ls -l dbback-dbtest-t1.sql
-rw-r--r-- 1 root root 179090589 Sep 23 05:31 dbback-dbtest-t1.sql

mk-parallel-dump导出表, 导出文件放在目录$HOME/backup/pdump1下, 不压缩, 不记录binlog的位置

# time mk-parallel-dump --socket=/var/lib/mysql/data_3306/mysql.sock --base-dir=$HOME/backup/pdump1 --no-gzip --nobin-log-position --tables="dbtest.t1"
     default:              1 tables,     1 chunks,     1 successes,  0 failures,  11.40 wall-clock time,  11.31 dump time

real    0m11.608s
user    0m2.176s
sys     0m0.556s
# find pdump1 -ls
7162198    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:33 pdump1
7162199    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:33 pdump1/default
7162200    4 drwxr-xr-x   2 root     root         4096 Sep 23 05:33 pdump1/default/dbtest
7162201 175076 -rw-r--r--   1 root     root     179096039 Sep 23 05:33 pdump1/default/dbtest/t1.000000.sql

mk-parallel-dump导出表, 增加每一百万条(近似值)导出一个文件

# time mk-parallel-dump --socket=/var/lib/mysql/data_3306/mysql.sock --base-dir=$HOME/backup/pdump2 --no-gzip --nobin-log-position --tables="dbtest.t1" --chunk-size=1000000
     default:              1 tables,     4 chunks,     4 successes,  0 failures,  10.36 wall-clock time,  15.92 dump time

real    0m10.509s
user    0m2.580s
sys     0m0.560s
# find pdump2 -ls
7162202    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:33 pdump2
7162203    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:33 pdump2/default
7162204    4 drwxr-xr-x   2 root     root         4096 Sep 23 05:33 pdump2/default/dbtest
7162205    4 -rw-r--r--   1 root     root          101 Sep 23 05:33 pdump2/default/dbtest/t1.chunks
7162208 58544 -rw-r--r--   1 root     root     59879876 Sep 23 05:33 pdump2/default/dbtest/t1.000001.sql
7162206   16 -rw-r--r--   1 root     root        16365 Sep 23 05:33 pdump2/default/dbtest/t1.000003.sql
7162209 58000 -rw-r--r--   1 root     root     59324125 Sep 23 05:33 pdump2/default/dbtest/t1.000000.sql
7162207 58544 -rw-r--r--   1 root     root     59880064 Sep 23 05:33 pdump2/default/dbtest/t1.000002.sql
# cat pdump2/default/dbtest/t1.chunks
`id` < 1999835
`id` >= 1999835 AND `id` < 3999669
`id` >= 3999669 AND `id` < 5999503
`id` >= 5999503

.chunks文件记录了分块规则

mk-parallel-dump导出表, 增加启动4个线程同时导出(不指定则默认为2个线程)

# time mk-parallel-dump --socket=/var/lib/mysql/data_3306/mysql.sock --base-dir=$HOME/backup/pdump3 --no-gzip --nobin-log-position --tables="dbtest.t1" --chunk-size=1000000 --threads=4
     default:              1 tables,     4 chunks,     4 successes,  0 failures,   9.37 wall-clock time,  25.29 dump time

real    0m9.529s
user    0m2.572s
sys     0m0.516s
# find pdump3 -ls
7359077    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:34 pdump3
7359078    4 drwxr-xr-x   3 root     root         4096 Sep 23 05:34 pdump3/default
7359079    4 drwxr-xr-x   2 root     root         4096 Sep 23 05:34 pdump3/default/dbtest
7359080    4 -rw-r--r--   1 root     root          101 Sep 23 05:34 pdump3/default/dbtest/t1.chunks
7359084 58544 -rw-r--r--   1 root     root     59879876 Sep 23 05:34 pdump3/default/dbtest/t1.000001.sql
7359081   16 -rw-r--r--   1 root     root        16365 Sep 23 05:34 pdump3/default/dbtest/t1.000003.sql
7359083 58000 -rw-r--r--   1 root     root     59324125 Sep 23 05:34 pdump3/default/dbtest/t1.000000.sql
7359082 58544 -rw-r--r--   1 root     root     59880064 Sep 23 05:34 pdump3/default/dbtest/t1.000002.sql

导出速度差不多, 无显著差异, 因为这是个单CPU的系统, 只导出一个表, 分块和多线程可能还会带来额外的开销.
如果是多核多CPU系统导出多个表, mk-parallel-dump应该会更快些.

3) 比较mk-parallel-dump和mysqldump导入
mysql导入

# time mysql --socket=/var/lib/mysql/data_3306/mysql.sock dbtest <dbback-dbtest-t1.sql

real    3m16.760s
user    0m1.672s
sys     0m0.156s

mk-parallel-restore导入

# time mk-parallel-restore --socket=/var/lib/mysql/data_3306/mysql.sock $HOME/backup/pdump1
    1 tables,     1 files,     1 successes,  0 failures, 199.75 wall-clock time, 199.75 load time

real    3m19.910s
user    0m0.232s
sys     0m0.136s

mk-parallel-restore导入多个文件

# mysql --socket=/var/lib/mysql/data_3306/mysql.sock -e "drop table dbtest.t1;"
# time mk-parallel-restore --socket=/var/lib/mysql/data_3306/mysql.sock $HOME/backup/pdump2
    1 tables,     4 files,     1 successes,  0 failures, 196.55 wall-clock time, 196.54 load time

real    3m16.653s
user    0m0.268s
sys     0m0.148s

mk-parallel-restore导入多个文件, 启4个线程

# mysql --socket=/var/lib/mysql/data_3306/mysql.sock -e "drop table dbtest.t1;"
# time mk-parallel-restore --socket=/var/lib/mysql/data_3306/mysql.sock $HOME/backup/pdump3 --threads=4
    1 tables,     4 files,     1 successes,  0 failures, 194.19 wall-clock time, 194.19 load time

real    3m14.606s
user    0m0.204s
sys     0m0.164s

速度也都差不多

总体感觉很一般啊, 以后再试试其它工具. 关于数据库复制, 日志/语句分析等工具可能还比较有用.

外部链接:
Tools for MySQL - Maatkit makes MySQL - easier to manage.
maatkit - A toolkit that provides advanced functionality for MySQL
mysql-parallel-dump test

-fin-

Saturday, May 30, 2009

recover from corrupted undo tablespace 恢复损坏的撤销表空间

recover from corrupted undo tablespace
恢复损坏的撤销表空间

讲述了通过设置 _offline_rollback_segments / _corrupted_rollback_segments 隐含参数, 恢复损坏的undo表空间的例子

因在netapp存储上误操作, 清除了文件锁, 导致数据库意外终止

Thu Mar  5 23:05:20 2009
KCF: write/open error block=0x724 online=1
     file=2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 32768'
Automatic datafile offline due to write error on
file 2: /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
KCF: write/open error block=0x2b31 online=1
     file=3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar  5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Automatic datafile offline due to write error on
file 3: /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
Thu Mar  5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar  5 23:05:21 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar  5 23:05:22 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
KCF: write/open error block=0x7671 online=1
     file=1 /home/oracle/app/oracle/oradata/rcat/system01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar  5 23:05:23 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_dbw0_25055.trc:
ORA-01243: system tablespace file suffered media failure
ORA-01114: IO error writing block to file 1 (block # 30321)
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/rcat/system01.dbf'
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192
DBW0: terminating instance due to error 1243
Instance terminated by DBW0, pid = 25055

重启后报错

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'

和

ORA-376 encountered when generating server alert SMG-3600

告警日志:

Sat May 30 10:40:04 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 500
LICENSE_SESSIONS_WARNING = 150
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  resource_limit           = TRUE
  license_max_sessions     = 500
  license_sessions_warning = 150
  __shared_pool_size       = 306184192
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  filesystemio_options     = setall
  sga_target               = 524288000
  control_files            = /home/oracle/app/oracle/oradata/rcat/control01.ctl, /home/oracle/app/oracle/oradata/rcat/control02.ctl, /home/oracle/app/oracle/oradata/rcat/control03.ctl
  control_file_record_keep_time= 30
  db_block_size            = 8192
  __db_cache_size          = 201326592
  compatible               = 10.2.0.4.0
  log_archive_dest_1       = LOCATION=/home/oracle/app/oracle/oradata/rcat/archive
  log_archive_dest_state_1 = ENABLE
  log_archive_format       = %t_%s_%r.arc
  archive_lag_target       = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /home/oracle/app/oracle/oradata/rcat/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = TRUE
  db_domain                =
  session_cached_cursors   = 200
  utl_file_dir             = /home/oracle/app/oracle/oradata/rcat/utl
  job_queue_processes      = 10
  background_dump_dest     = /home/oracle/app/oracle/admin/rcat/bdump
  user_dump_dest           = /home/oracle/app/oracle/admin/rcat/udump
  core_dump_dest           = /home/oracle/app/oracle/admin/rcat/cdump
  audit_file_dest          = /home/oracle/app/oracle/admin/rcat/adump
  audit_trail              = DB_EXTENDED
  db_name                  = rcat
  open_cursors             = 3000
  pga_aggregate_target     = 209715200
  aq_tm_processes          = 1
PMON started with pid=2, OS id=12880
PSP0 started with pid=3, OS id=12882
MMAN started with pid=4, OS id=12884
DBW0 started with pid=5, OS id=12886
LGWR started with pid=6, OS id=12888
CKPT started with pid=7, OS id=12890
SMON started with pid=8, OS id=12892
RECO started with pid=9, OS id=12894
CJQ0 started with pid=10, OS id=12896
MMON started with pid=11, OS id=12898
MMNL started with pid=12, OS id=12900
Sat May 30 10:40:05 2009
ALTER DATABASE   MOUNT
Sat May 30 10:40:09 2009
Setting recovery target incarnation to 1
Sat May 30 10:40:09 2009
Successful mount of redo thread 1, with mount id 460376325
Sat May 30 10:40:09 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sat May 30 10:40:09 2009
ALTER DATABASE OPEN
Sat May 30 10:40:09 2009
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Sat May 30 10:40:10 2009
Started redo scan
Sat May 30 10:40:10 2009
Completed redo scan
 21 redo blocks read, 3 data blocks need recovery
Sat May 30 10:40:10 2009
Started redo application at
 Thread 1: logseq 7202, block 231, scn 6268440
Sat May 30 10:40:10 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 7202 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/rcat/redo02.rdo
Sat May 30 10:40:10 2009
Completed redo application
Sat May 30 10:40:10 2009
Completed crash recovery at
 Thread 1: logseq 7202, block 252, scn 6288463
 3 data blocks read, 3 data blocks written, 21 redo blocks read
Sat May 30 10:40:10 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=12913
Sat May 30 10:40:10 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=12915
Sat May 30 10:40:10 2009
Thread 1 advanced to log sequence 7203 (thread open)
Thread 1 opened at log sequence 7203
  Current log# 3 seq# 7203 mem# 0: /home/oracle/app/oracle/oradata/rcat/redo03.rdo
Successful open of redo thread 1
Sat May 30 10:40:10 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat May 30 10:40:10 2009
ARC0: Becoming the heartbeat ARCH
Sat May 30 10:40:10 2009
SMON: enabling cache recovery
Sat May 30 10:40:10 2009
Successfully onlined Undo Tablespace 1.
Sat May 30 10:40:10 2009
SMON: enabling tx recovery
Sat May 30 10:40:10 2009
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
replication_dependency_tracking turned off (no async multimaster replication found)
Sat May 30 10:40:11 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Starting background process QMNC
QMNC started with pid=19, OS id=12917
Sat May 30 10:40:12 2009
Completed: ALTER DATABASE OPEN
Sat May 30 10:40:13 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ORA-376 encountered when generating server alert SMG-3600
Sat May 30 10:45:13 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 10:50:14 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 10:55:15 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 11:00:08 2009
MMNL absent for 1203 secs; Foregrounds taking over
Sat May 30 11:00:09 2009
MMNL absent for 1203 secs; Foregrounds taking over
MMNL absent for 1203 secs; Foregrounds taking over
MMNL absent for 1203 secs; Foregrounds taking over
Sat May 30 11:00:17 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'

跟踪文件1:

/home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/10.2
System name:    Linux
Node name:      DEV-DB-2
Release:        2.6.9-34.ELsmp
Version:        #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine:        x86_64
Instance name: rcat
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 12892, image: oracle@DEV-DB-2 (SMON)

*** SERVICE NAME:() 2009-05-30 10:40:10.958
*** SESSION ID:(1649.1) 2009-05-30 10:40:10.958
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
*** 2009-05-30 10:40:11.610
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
*** 2009-05-30 10:45:13.135
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
...
...

跟踪文件2:

/home/oracle/app/oracle/admin/rcat/bdump/rcat_mmon_12898.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/10.2
System name:    Linux
Node name:      DEV-DB-2
Release:        2.6.9-34.ELsmp
Version:        #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine:        x86_64
Instance name: rcat
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 12898, image: oracle@DEV-DB-2 (MMON)

*** SERVICE NAME:(SYS$BACKGROUND) 2009-05-30 10:40:13.257
*** SESSION ID:(1646.1) 2009-05-30 10:40:13.257
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
  *** SQLSTR: total-len=328, dump-len=240,
      STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
*** kewrwdbi_1: Error=13509 encountered during run_once
keaInitAdvCache: failed, err=604
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.post_instance_up308: SQLCODE -13917,ORA-13917: Posting system
 alert with reason_id 135 failed with code [5] [post_error]
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:41:11.684
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:42:11.742
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:43:11.803
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:44:11.862
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:45:08.910
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
  *** SQLSTR: total-len=328, dump-len=240,
      STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
...
...

undo和sysaux表空间都需要恢复

SQL> set pages 50000 line 130
SQL> col name for a60
SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                OWNER  STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM                         SYSTEM                         SYS    ONLINE
_SYSSMU1$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU4$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU5$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU6$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU7$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU8$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU9$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU10$                     UNDOTBS1                       PUBLIC NEEDS RECOVERY

11 rows selected.

SQL>

设置 _offline_rollback_segments 参数, 重启

alter system set "_offline_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;
alter system set undo_management=manual scope=spfile;
shutdown immediate
startup

_offline_rollback_segments 和 _corrupted_rollback_segments 隐含参数的讲解见dsi401 Chapter 6 - Page 20
----begin----
When opening a database, any rollback segments listed in _offline or _corrupted parameters:
* Arenot scanned, and any active transactions are neither marked as dead nor rolled back
* Appear offline in dba_rollback_segs(undo$)
* Cannot be acuired by the instance for new transactions
...

If an open ITL is found to be associated with an _offline segment, the segment is read to find the transaction status
* If committed, the block is cleaned out
* If active and you want to read the block, a CR copy is constructed using undo from the segment
* If active and you want to lock the row, undesirable behavior may result
...

If an open ITL is found to be associated with a _corrupted segment, the segment is not read to find the transaction status
* It is as though the rollback segment had been dropped; the transaction is assumed to be committed and delayed block cleanout is performed
* If the transaction was not committed, logical corruption will occur

Most important, the Oracle server does not read the segment in this case. It is as if the segment has been dropped. This is the most important difference between _offline and _corrupted.
...
----end----

删除原来的撤销段, 恢复撤销表空间和系统辅助表空间

drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU3$";
drop rollback segment "_SYSSMU4$";
drop rollback segment "_SYSSMU5$";
drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU7$";
drop rollback segment "_SYSSMU8$";
drop rollback segment "_SYSSMU9$";
drop rollback segment "_SYSSMU10$";
alter database recover automatic datafile 2,3;
alter tablespace undotbs1 online;
alter tablespace sysaux online;

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                OWNER  STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM                         SYSTEM                         SYS    ONLINE
_SYSSMU1$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU4$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU5$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU6$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU7$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU8$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU9$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU10$                     UNDOTBS1                       PUBLIC NEEDS RECOVERY

11 rows selected.

SQL> drop rollback segment "_SYSSMU1$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU2$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU3$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU4$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU5$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU6$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU7$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU8$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU9$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU10$";

Rollback segment dropped.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> alter database recover automatic datafile 2,3;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           OFFLINE
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            OFFLINE
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> alter tablespace undotbs1 online;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL>

日志:

Sat May 30 13:39:26 2009
Completed: ALTER DATABASE OPEN
Sat May 30 13:39:26 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ORA-376 encountered when generating server alert SMG-3600
Sat May 30 13:40:35 2009
drop rollback segment "_SYSSMU1$"
Sat May 30 13:40:35 2009
Completed: drop rollback segment "_SYSSMU1$"
Sat May 30 13:41:16 2009
drop rollback segment "_SYSSMU2$"
Sat May 30 13:41:16 2009
Completed: drop rollback segment "_SYSSMU2$"
Sat May 30 13:41:18 2009
drop rollback segment "_SYSSMU3$"
Completed: drop rollback segment "_SYSSMU3$"
Sat May 30 13:41:21 2009
drop rollback segment "_SYSSMU4$"
Completed: drop rollback segment "_SYSSMU4$"
Sat May 30 13:41:25 2009
drop rollback segment "_SYSSMU5$"
Completed: drop rollback segment "_SYSSMU5$"
Sat May 30 13:41:28 2009
drop rollback segment "_SYSSMU6$"
Sat May 30 13:41:28 2009
Completed: drop rollback segment "_SYSSMU6$"
Sat May 30 13:41:30 2009
drop rollback segment "_SYSSMU7$"
Completed: drop rollback segment "_SYSSMU7$"
Sat May 30 13:41:36 2009
drop rollback segment "_SYSSMU8$"
Completed: drop rollback segment "_SYSSMU8$"
Sat May 30 13:41:38 2009
drop rollback segment "_SYSSMU9$"
Sat May 30 13:41:38 2009
Completed: drop rollback segment "_SYSSMU9$"
Sat May 30 13:41:40 2009
drop rollback segment "_SYSSMU10$"
Completed: drop rollback segment "_SYSSMU10$"
Sat May 30 13:43:12 2009
alter database recover automatic datafile 2,3
Sat May 30 13:43:12 2009
Media Recovery Start
 parallel recovery started with 3 processes
Sat May 30 13:43:13 2009
Media Recovery Log /home/oracle/app/oracle/oradata/rcat/archive/1_7192_663322987.arc
Sat May 30 13:43:13 2009
Media Recovery Complete (rcat)
Completed: alter database recover automatic datafile 2,3
Sat May 30 13:44:04 2009
alter tablespace undotbs1 online
Sat May 30 13:44:04 2009
Completed: alter tablespace undotbs1 online
Sat May 30 13:44:07 2009
alter tablespace sysaux online
Completed: alter tablespace sysaux online

恢复参数,重启

alter system reset "_offline_rollback_segments" scope=spfile sid='*';
alter system set undo_management=auto scope=spfile;
shutdown immediate
startup

至此, 恢复完成.

-fin-

Monday, May 4, 2009

flashback dropped tablespace 用闪回数据库恢复删除的表空间

flashback dropped tablespace
用闪回数据库恢复删除的表空间

10g OCP 教程上说不能恢复删除的表空间
"You cannot use Flashback Database in the following situations:
The control file has been restored or re-created.
A tablespace has been dropped.
A data file has been shrunk."

但是文档5.3.1 Limitations of Flashback Database 和 Prerequisites of Flashback Database 都没说有这个限制
11g文档 Table 2-7 How FLASHBACK DATABASE Responds to Datafile Status Changes 还提到 "Adds the datafile to the control file, but marks it as offline and does not flash it back. You can then restore and recover the datafile to the same time or SCN."

特测试一下

1. 打开闪回数据库功能

shutdown immediate
startup mount exclusive
show parameter db_flashback_retention_target
alter database flashback on;
select flashback_on from v$database;

2. 创建表空间并录入测试数据

create tablespace ts_a datafile '/home/oracle/app/oracle/oradata/test/ts_a.dbf' size 10m;
create table t_a (a int) tablespace ts_a;
insert into t_a values (1);
commit;
select current_scn from v$database;
insert into t_a values (2);
commit;
select current_scn from v$database;

SQL> create tablespace ts_a datafile '/home/oracle/app/oracle/oradata/test/ts_a.dbf' size 10m;

Tablespace created.

SQL> create table t_a (a int) tablespace ts_a;

Table created.

SQL> insert into t_a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    5166171

SQL> insert into t_a values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    5166180

SQL>

3. 删除表空间

drop tablespace ts_a including contents;
select current_scn from v$database;

SQL> drop tablespace ts_a including contents;

Tablespace dropped.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    5166218

SQL>

4. 闪回数据库

shutdown immediate
startup mount
select current_scn from v$database;
flashback database to scn &scn;

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2085320 bytes
Variable Size             603983416 bytes
Database Buffers         1526726656 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
          0

SQL> flashback database to scn &scn;
Enter value for scn: 5166171
old   1: flashback database to scn &scn
new   1: flashback database to scn 5166171
flashback database to scn 5166171
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 7 will be lost if RESETLOGS is done
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007'


SQL>

这步报错了, 说不认识数据文件名, 要改成正确的

日志:

...
Mon May  4 08:18:34 2009
flashback database to scn 5166171
Mon May  4 08:18:35 2009
Flashback Restore Start
Flashback: created tablespace #8: 'TS_A' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00007' for tablespace #8 in the controlfile.
Filename was:
'/home/oracle/app/oracle/oradata/test/ts_a.dbf' when dropped.
File will have to be restored from a backup and recovered.
Flashback: deleted datafile #7 in tablespace #8 from control file.
Flashback: dropped tablespace #8: 'TS_A' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
 parallel recovery started with 3 processes
Mon May  4 08:18:36 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/test/redo03.rdo
  Mem# 1: /home/oracle/app/oracle/oradata/test/redo03b.rdo
Mon May  4 08:18:36 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo
  Mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo
Flashback recovery: Added file #7 to control file as OFFLINE and 'UNNAMED00007'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'/home/oracle/app/oracle/oradata/test/ts_a.dbf'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Mon May  4 08:18:36 2009
Incomplete Recovery applied until change 5166172
Flashback Media Recovery Complete
ORA-38795 signalled during: flashback database to scn 5166171...

5. 按前面提示, 将数据文件改名

select * from v$datafile;
select * from v$tablespace;
alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf';
select * from v$datafile;

SQL> select * from v$datafile;

     FILE# CREATION_CHANGE# CREATION_TIME             TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ---------------- ------------------ ---------- ---------- ------- ---------- ------------------ ------------------
UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME          OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME
--------------------- ------------------ ------------ ------------------ --------------- -------------- ------------------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
----------------------------------------------------------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TI
------------------- ------------------
...
                  7          5166051 04-MAY-09                   8          7 RECOVER READ WRITE            5166051 04-MAY-09
                    0                                                                  0              0
         0          0     10485760       8192
/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007
         0    4294967295
UNKNOWN
                  0


7 rows selected.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
...
         8 TS_A                           YES NO  YES

9 rows selected.

SQL> alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf';

Database altered.

SQL> select * from v$datafile;

     FILE# CREATION_CHANGE# CREATION_TIME             TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ---------------- ------------------ ---------- ---------- ------- ---------- ------------------ ------------------
UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME          OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME
--------------------- ------------------ ------------ ------------------ --------------- -------------- ------------------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
----------------------------------------------------------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TI
------------------- ------------------
...
         7          5166051 04-MAY-09                   8          7 RECOVER READ WRITE            5166051 04-MAY-09
                    0                                                                  0              0
  10485760       1280     10485760       8192
/home/oracle/app/oracle/oradata/test/ts_a.dbf
         0          8192
UNKNOWN
                  0


7 rows selected.

SQL>

日志:

Mon May  4 08:21:46 2009
alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf'
Mon May  4 08:21:46 2009
Completed: alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf'

6. 重新闪回数据库

flashback database to scn &scn;

SQL> flashback database to scn &scn;
Enter value for scn: 5166171
old   1: flashback database to scn &scn
new   1: flashback database to scn 5166171

Flashback complete.

SQL>

日志:

Mon May  4 08:22:24 2009
flashback database to scn 5166171
Mon May  4 08:22:25 2009
Flashback Restore Start
Deleted file /home/oracle/app/oracle/oradata/test/ts_a.dbf
Flashback: deleted datafile #7 in tablespace #8 from control file.
Flashback: dropped tablespace #8: 'TS_A' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
 parallel recovery started with 3 processes
Mon May  4 08:22:25 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/test/redo03.rdo
  Mem# 1: /home/oracle/app/oracle/oradata/test/redo03b.rdo
Mon May  4 08:22:26 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo
  Mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo
Recovery created file /home/oracle/app/oracle/oradata/test/ts_a.dbf
Successfully added datafile 7 to media recovery
Datafile #7: '/home/oracle/app/oracle/oradata/test/ts_a.dbf'
Mon May  4 08:22:26 2009
Incomplete Recovery applied until change 5166172
Flashback Media Recovery Complete
Completed: flashback database to scn 5166171

7. 打开数据库, 查询
alter database open read only;
alter database open resetlogs;
select current_scn from v$database;
select * from t_a;

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode


SQL> alter database open resetlogs;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    5166406

SQL> select * from t_a;

         A
----------
         1

SQL>

可以恢复

日志:

Mon May  4 08:33:36 2009
alter database open read only
Mon May  4 08:33:36 2009
ORA-16006 signalled during: alter database open read only...
Mon May  4 08:35:41 2009
alter database open resetlogs
Mon May  4 08:35:42 2009
RESETLOGS after incomplete recovery UNTIL CHANGE 5166172
Resetting resetlogs activation ID 1984070862 (0x764284ce)
Mon May  4 08:35:53 2009
Setting recovery target incarnation to 4
Mon May  4 08:35:53 2009
Assigning activation ID 1984060563 (0x76425c93)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=20301
Mon May  4 08:35:53 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=16, OS id=20303
Mon May  4 08:35:53 2009
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo
  Current log# 1 seq# 1 mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo
Successful open of redo thread 1
Mon May  4 08:35:53 2009
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Mon May  4 08:35:53 2009
ARC1: Becoming the heartbeat ARCH
Mon May  4 08:35:53 2009
SMON: enabling cache recovery
Mon May  4 08:35:54 2009
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Mon May  4 08:35:54 2009
SMON: enabling tx recovery
Mon May  4 08:35:54 2009
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=17, OS id=20305
Mon May  4 08:35:56 2009
LOGSTDBY: Validating controlfile with logical metadata
Mon May  4 08:35:56 2009
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

8. 其它
如果删除表空间同时删除了数据文件(including contents and datafiles), 显然用闪回数据库是无法恢复的

外部链接:
FLASHBACK DATABASE(10gR2)
FLASHBACK DATABASE(11gR1)

-fin-

Monday, March 16, 2009

tar over ssh 通过ssh进行tar备份

tar over ssh
tar+ssh备份

1. 备份远程服务器上的文件到本地
如:

mkdir -p $HOME/dev-rpt-2
ssh oracle@dev-rpt-2 'tar -C $HOME -zcf - tmp' >$HOME/dev-rpt-2/oracle_dev-rpt-2_tmp.tar.gz

并解开:

ssh oracle@dev-rpt-2 'tar -C $HOME -zcf - tmp' |tar -C $HOME/dev-rpt-2 -zxvf -

2. 恢复远程备份文件到本地

mkdir -p $HOME/tmp2
ssh root@dev-db-1 'cat $HOME/dev-rpt-2/oracle_dev-rpt-2_tmp.tar.gz' | \
tar -C $HOME/tmp2 -zxvf -

3. 备份本地文件到远程服务器上

tar -C $HOME -zcf - tmp2 | \
ssh root@dev-db-1 'mkdir -p $HOME/dev-rpt-2 && cat >$HOME/dev-rpt-2/oracle_dev-rpt-2_tmp2.tar.gz'

并解压:

tar -C $HOME -zcf - tmp2 | \
ssh root@dev-db-1 'mkdir -p $HOME/dev-rpt-2 && tar -C $HOME/dev-rpt-2 -zxvf -'

4. 恢复本地备份文件到远程服务器上

cat $HOME/dev-rpt-2/oracle_dev-rpt-2_tmp2.tar.gz | \
ssh oracle@dev-rpt-2 'mkdir $HOME/tmp3 && tar -C $HOME/tmp3 -zxvf -'

-fin-

Wednesday, March 4, 2009

creating standby database with image copies 使用映像拷贝创建备用数据库

creating standby database with image copies
使用复制文件备份方式创建备用数据库

1.
主库停远程归档

sqlplus /nolog <<EOF
conn / as sysdba
alter system set log_archive_dest_2='';
alter system set log_archive_dest_state_2='enable';
exit
EOF

2.
删除备库

sqlplus /nolog <<EOF
conn / as sysdba
shutdown immediate
exit
EOF

cd /home/oracle/app/oracle/oradata
rm -rf test_s.bak
mv test_s test_s.bak
mkdir -p test_s/{archive,flash_recovery_area,utlfile}

3. NFS共享目录
备库共享数据文件目录, 让主库服务器访问, 主库备份到这个目录

备库开启NFS

su - root
service nfs start
exportfs -iv -o rw,no_root_squash dev-rpt-2:/home/oracle/app/oracle/oradata/test_s

[root@DEV-pxy-1 ~]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]
[root@DEV-pxy-1 ~]# exportfs -iv -o rw,no_root_squash dev-rpt-2:/home/oracle/app/oracle/oradata/test_s
exporting DEV-RPT-2.s3lab.mot.com:/home/oracle/app/oracle/oradata/test_s
[root@DEV-pxy-1 ~]#

no_root_squash保留root权限
首先要启portmap服务, 否则启nfs服务报错

Starting NFS services:  [  OK  ]
Starting NFS quotas: Cannot register service: RPC: Unable to receive; errno = Connection refused
rpc.rquotad: unable to register (RQUOTAPROG, RQUOTAVERS, udp).
[FAILED]
Starting NFS daemon: 
...

主库挂载NFS目录

su - root
mkdir -p /mnt/standby-db-oradata
mount -v -t nfs -o rw dev-pxy-1:/home/oracle/app/oracle/oradata/test_s /mnt/standby-db-oradata

[root@DEV-RPT-2 mnt]# mount -v -t nfs -o rw dev-pxy-1:/home/oracle/app/oracle/oradata/test_s /mnt/standby-db-oradata
dev-pxy-1:/home/oracle/app/oracle/oradata/test_s on /mnt/standby-db-oradata type nfs (rw,addr=192.168.12.51)
[root@DEV-RPT-2 mnt]# ls -l /mnt/standby-db-oradata
total 12
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 archive
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 flash_recovery_area
drwxr-x---  2 oracle oinstall 4096 Mar  4 02:53 utlfile
[root@DEV-RPT-2 mnt]#

挂载nfs最好加上oracle的建议参数 hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac
(noac用于RAC环境)
比如: rw,bg,hard,rsize=32768,wsize=32768,nfsvers=3,proto=tcp,nointr,timeo=600
否则RMAN报错ORA-27054

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 03/04/2009 17:29:27
ORA-19504: failed to create file "/mnt/other/df_data01.dbf"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Add ...

如何取消NFS共享:
备库端先umount nfs目录, 主库端再用exportfs -u ... 取消共享, 最后停止nfs等服务

4. 查询数据库信息

查询系统变更号, 数据文件名等

sqlplus -s /nolog <<'EOF'
conn / as sysdba
set pages 9999 line 140
select current_scn from v$database;
col ts_name for a20
col df_name for a70
select df.file# df_no,ts.name ts_name,df.name df_name
  from v$datafile df, v$tablespace ts
 where df.ts# = ts.ts#
 order by df.file#;
select value from v$parameter where name='log_archive_format';
select * from (select resetlogs_id from v$archived_log order by completion_time desc) where rownum=1;
exit
EOF

[oracle@DEV-RPT-2 ~]$ sqlplus -s /nolog <<'EOF'
> conn / as sysdba
> set pages 9999 line 140
> select current_scn from v$database;
> col ts_name for a20
> col df_name for a70
> select df.file# df_no,ts.name ts_name,df.name df_name
>   from v$datafile df, v$tablespace ts
>  where df.ts# = ts.ts#
>  order by df.file#;
> select value from v$parameter where name='log_archive_format';
> select * from (select resetlogs_id from v$archived_log order by completion_time desc) where rownum=1;
> exit
> EOF

CURRENT_SCN
-----------
    2391414


     DF_NO TS_NAME              DF_NAME
---------- -------------------- ----------------------------------------------------------------------
         1 SYSTEM               /home/oracle/app/oracle/oradata/test/system01.dbf
         2 UNDOTBS1             /home/oracle/app/oracle/oradata/test/undotbs01.dbf
         3 SYSAUX               /home/oracle/app/oracle/oradata/test/sysaux01.dbf
         4 USERS                /home/oracle/app/oracle/oradata/test/users01.dbf
         5 TS_TEST              /home/oracle/app/oracle/oradata/test/ts_test01.dbf


VALUE
--------------------------------------------------------------------------------------------------------------------------------------------
%t_%s_%r.arc


RESETLOGS_ID
------------
   676622925

[oracle@DEV-RPT-2 ~]$

5.
删除主库原来的备份, 以免干扰

rman target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
list backupset;
delete noprompt backupset;
list copy;
delete noprompt copy of controlfile;
delete noprompt copy of datafile 1,2,3,4,5;
delete noprompt copy of archivelog like '/mnt/standby-db-oradata/archive%';
exit

6.
创建IMAGE COPY备份

主库上运行

rman target /
run {
 allocate channel c1 type disk rate 5M;
 copy datafile 1 to '/mnt/standby-db-oradata/system01.dbf';
 backup as copy datafile 2 format '/mnt/standby-db-oradata/undotbs01.dbf';
 backup as copy datafile 3 format '/mnt/standby-db-oradata/sysaux01.dbf';
 backup as copy datafile 4 format '/mnt/standby-db-oradata/users01.dbf';
 backup as copy datafile 5 format '/mnt/standby-db-oradata/ts_test01.dbf';
 sql 'alter system archive log current';
 backup as copy archivelog from scn=2391414 format '/mnt/standby-db-oradata/archive/%h_%e_676622925.arc';
 backup as copy current controlfile for standby format '/mnt/standby-db-oradata/control01.ctl';
 backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control02.ctl';
 backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control03.ctl';
 release channel c1;
}
list copy;
exit

用 allocate channel ... rate ...限制读取的速率

10g中 backup as copy 取代了copy 命令, 这里用copy是为了避免备份system表空间时自动备份控制文件
copy允许覆盖同名文件, 不报错
backup as copy不允许同名文件, 会报错ORA-27038: created file already exists

归档日志可以不用backup as copy备份, 用操作系统命令拷贝到备库也行

用backup as copy controlfilecopy .. 复制2份控制文件备份, 也可以操作系统命令拷贝

[oracle@DEV-RPT-2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:09:45 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1973292924)

RMAN>

Recovery Manager complete.
[oracle@DEV-RPT-2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:10:38 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1973292924)

RMAN> run {
2>  allocate channel c1 type disk rate 5M;
3>  copy datafile 1 to '/mnt/standby-db-oradata/system01.dbf';
 backup as copy datafile 2 format '/mnt/standby-db-oradata/undotbs01.dbf';
4> 5>  backup as copy datafile 3 format '/mnt/standby-db-oradata/sysaux01.dbf';
6>  backup as copy datafile 4 format '/mnt/standby-db-oradata/users01.dbf';
7>  backup as copy datafile 5 format '/mnt/standby-db-oradata/ts_test01.dbf';
 sql 'alter system archive log current';
8> 9>  backup as copy archivelog from scn=2391414 format '/mnt/standby-db-oradata/archive/%h_%e_676622925.arc';
10>  backup as copy current controlfile for standby format '/mnt/standby-db-oradata/control01.ctl';
11>  backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control02.ctl';
12>  backup as copy controlfilecopy '/mnt/standby-db-oradata/control01.ctl' format '/mnt/standby-db-oradata/control03.ctl';
13>  release channel c1;
14> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=1632 devtype=DISK

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00001 name=/home/oracle/app/oracle/oradata/test/system01.dbf
output filename=/mnt/standby-db-oradata/system01.dbf tag=TAG20090304T061050 recid=36 stamp=680595110
channel c1: datafile copy complete, elapsed time: 00:01:05
channel c1: throttle time: 0:00:49
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00002 name=/home/oracle/app/oracle/oradata/test/undotbs01.dbf
output filename=/mnt/standby-db-oradata/undotbs01.dbf tag=TAG20090304T061155 recid=37 stamp=680595155
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: throttle time: 0:00:32
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00003 name=/home/oracle/app/oracle/oradata/test/sysaux01.dbf
output filename=/mnt/standby-db-oradata/sysaux01.dbf tag=TAG20090304T061241 recid=38 stamp=680595198
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: throttle time: 0:00:30
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00004 name=/home/oracle/app/oracle/oradata/test/users01.dbf
output filename=/mnt/standby-db-oradata/users01.dbf tag=TAG20090304T061326 recid=39 stamp=680595208
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: throttle time: 0:00:01
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input datafile fno=00005 name=/home/oracle/app/oracle/oradata/test/ts_test01.dbf
output filename=/mnt/standby-db-oradata/ts_test01.dbf tag=TAG20090304T061329 recid=40 stamp=680595229
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: throttle time: 0:00:16
Finished backup at 04-MAR-09

sql statement: alter system archive log current

Starting backup at 04-MAR-09
current log archived
channel c1: starting archive copy
input archive log thread=1 sequence=2081 recid=3470 stamp=680594623
output filename=/mnt/standby-db-oradata/archive/1_2081_676622925.arc recid=3478 stamp=680595237
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2079 recid=3466 stamp=680594351
output filename=/mnt/standby-db-oradata/archive/1_2079_676622925.arc recid=3479 stamp=680595239
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2083 recid=3476 stamp=680595235
output filename=/mnt/standby-db-oradata/archive/1_2083_676622925.arc recid=3480 stamp=680595241
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2080 recid=3467 stamp=680594351
output filename=/mnt/standby-db-oradata/archive/1_2080_676622925.arc recid=3481 stamp=680595243
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2082 recid=3471 stamp=680594624
output filename=/mnt/standby-db-oradata/archive/1_2082_676622925.arc recid=3482 stamp=680595245
channel c1: archivelog copy complete, elapsed time: 00:00:02
channel c1: starting archive copy
input archive log thread=1 sequence=2084 recid=3477 stamp=680595235
output filename=/mnt/standby-db-oradata/archive/1_2084_676622925.arc recid=3483 stamp=680595247
channel c1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
copying standby control file
output filename=/mnt/standby-db-oradata/control01.ctl tag=TAG20090304T061408 recid=41 stamp=680595250
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input control file copy name=/mnt/standby-db-oradata/control01.ctl
output filename=/mnt/standby-db-oradata/control02.ctl tag=TAG20090304T061408 recid=42 stamp=680595255
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: throttle time: 0:00:03
Finished backup at 04-MAR-09

Starting backup at 04-MAR-09
channel c1: starting datafile copy
input control file copy name=/mnt/standby-db-oradata/control01.ctl
output filename=/mnt/standby-db-oradata/control03.ctl tag=TAG20090304T061408 recid=43 stamp=680595263
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: throttle time: 0:00:03
Finished backup at 04-MAR-09

released channel: c1

RMAN> list copy;


List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
36      1    A 04-MAR-09       2392559    04-MAR-09       /mnt/standby-db-oradata/system01.dbf
37      2    A 04-MAR-09       2392586    04-MAR-09       /mnt/standby-db-oradata/undotbs01.dbf
38      3    A 04-MAR-09       2392609    04-MAR-09       /mnt/standby-db-oradata/sysaux01.dbf
39      4    A 04-MAR-09       2392629    04-MAR-09       /mnt/standby-db-oradata/users01.dbf
40      5    A 04-MAR-09       2392635    04-MAR-09       /mnt/standby-db-oradata/ts_test01.dbf

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
43      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control03.ctl
42      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control02.ctl
41      A 04-MAR-09       2392663    04-MAR-09       /mnt/standby-db-oradata/control01.ctl

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
3262    1    1973    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1973_676622925.arc
3264    1    1974    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1974_676622925.arc
3266    1    1975    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1975_676622925.arc
3268    1    1976    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1976_676622925.arc
3270    1    1977    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1977_676622925.arc
3272    1    1978    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1978_676622925.arc
3274    1    1979    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1979_676622925.arc
3276    1    1980    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1980_676622925.arc
3278    1    1981    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1981_676622925.arc
3280    1    1982    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1982_676622925.arc
3282    1    1983    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1983_676622925.arc
3284    1    1984    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1984_676622925.arc
3286    1    1985    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1985_676622925.arc
3288    1    1986    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1986_676622925.arc
3290    1    1987    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1987_676622925.arc
3292    1    1988    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1988_676622925.arc
3294    1    1989    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1989_676622925.arc
3296    1    1990    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1990_676622925.arc
3298    1    1991    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1991_676622925.arc
3300    1    1992    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1992_676622925.arc
3302    1    1993    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1993_676622925.arc
3304    1    1994    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1994_676622925.arc
3306    1    1995    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1995_676622925.arc
3308    1    1996    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1996_676622925.arc
3310    1    1997    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1997_676622925.arc
3312    1    1998    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1998_676622925.arc
3314    1    1999    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_1999_676622925.arc
3316    1    2000    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2000_676622925.arc
3318    1    2001    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2001_676622925.arc
3320    1    2002    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2002_676622925.arc
3322    1    2003    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2003_676622925.arc
3324    1    2004    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2004_676622925.arc
3326    1    2005    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2005_676622925.arc
3328    1    2006    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2006_676622925.arc
3330    1    2007    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2007_676622925.arc
3332    1    2008    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2008_676622925.arc
3334    1    2009    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2009_676622925.arc
3336    1    2010    A 02-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2010_676622925.arc
3338    1    2011    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2011_676622925.arc
3340    1    2012    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2012_676622925.arc
3342    1    2013    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2013_676622925.arc
3344    1    2014    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2014_676622925.arc
3346    1    2015    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2015_676622925.arc
3348    1    2016    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2016_676622925.arc
3350    1    2017    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2017_676622925.arc
3352    1    2018    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2018_676622925.arc
3354    1    2019    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2019_676622925.arc
3356    1    2020    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2020_676622925.arc
3358    1    2021    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2021_676622925.arc
3360    1    2022    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2022_676622925.arc
3362    1    2023    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2023_676622925.arc
3364    1    2024    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2024_676622925.arc
3366    1    2025    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2025_676622925.arc
3368    1    2026    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2026_676622925.arc
3370    1    2027    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2027_676622925.arc
3372    1    2028    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2028_676622925.arc
3374    1    2029    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2029_676622925.arc
3376    1    2030    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2030_676622925.arc
3378    1    2031    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2031_676622925.arc
3380    1    2032    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2032_676622925.arc
3381    1    2033    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2033_676622925.arc
3382    1    2034    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2034_676622925.arc
3383    1    2035    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2035_676622925.arc
3384    1    2036    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2036_676622925.arc
3385    1    2037    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2037_676622925.arc
3386    1    2038    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2038_676622925.arc
3387    1    2039    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2039_676622925.arc
3388    1    2040    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2040_676622925.arc
3389    1    2041    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2041_676622925.arc
3391    1    2042    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2042_676622925.arc
3393    1    2043    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2043_676622925.arc
3395    1    2044    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2044_676622925.arc
3397    1    2045    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2045_676622925.arc
3399    1    2046    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2046_676622925.arc
3401    1    2047    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2047_676622925.arc
3403    1    2048    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2048_676622925.arc
3405    1    2049    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2049_676622925.arc
3407    1    2050    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2050_676622925.arc
3409    1    2051    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2051_676622925.arc
3411    1    2052    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2052_676622925.arc
3413    1    2053    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2053_676622925.arc
3415    1    2054    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2054_676622925.arc
3417    1    2055    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2055_676622925.arc
3419    1    2056    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2056_676622925.arc
3421    1    2057    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2057_676622925.arc
3423    1    2058    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2058_676622925.arc
3425    1    2059    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2059_676622925.arc
3427    1    2060    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2060_676622925.arc
3429    1    2061    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2061_676622925.arc
3431    1    2062    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2062_676622925.arc
3433    1    2063    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2063_676622925.arc
3435    1    2064    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2064_676622925.arc
3437    1    2065    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2065_676622925.arc
3439    1    2066    A 03-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2066_676622925.arc
3441    1    2067    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2067_676622925.arc
3443    1    2068    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2068_676622925.arc
3445    1    2069    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2069_676622925.arc
3447    1    2070    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2070_676622925.arc
3449    1    2071    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2071_676622925.arc
3451    1    2072    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2072_676622925.arc
3452    1    2073    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2073_676622925.arc
3453    1    2074    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2074_676622925.arc
3457    1    2075    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2075_676622925.arc
3458    1    2076    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2076_676622925.arc
3464    1    2077    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2077_676622925.arc
3465    1    2078    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2078_676622925.arc
3479    1    2079    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2079_676622925.arc
3466    1    2079    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2079_676622925.arc
3481    1    2080    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2080_676622925.arc
3467    1    2080    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2080_676622925.arc
3478    1    2081    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2081_676622925.arc
3470    1    2081    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2081_676622925.arc
3482    1    2082    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2082_676622925.arc
3471    1    2082    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2082_676622925.arc
3480    1    2083    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2083_676622925.arc
3476    1    2083    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2083_676622925.arc
3483    1    2084    A 04-MAR-09 /mnt/standby-db-oradata/archive/1_2084_676622925.arc
3477    1    2084    A 04-MAR-09 /home/oracle/app/oracle/oradata/test/archive/1_2084_676622925.arc

RMAN> exit


Recovery Manager complete.
[oracle@DEV-RPT-2 ~]$

7. 启动备库

密码文件用原来的就行
初始化参数audit_trail不能是db了, 得改成os

启动备库到mount standby database状态

rman target /
startup mount

10g以前用

startup nomount
sql 'alter database mount standby database';

[oracle@DEV-pxy-1 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Mar 4 06:20:19 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    2147483648 bytes

Fixed Size                     2085320 bytes
Variable Size                486542904 bytes
Database Buffers            1644167168 bytes
Redo Buffers                  14688256 bytes

RMAN>

告警日志:

Wed Mar  4 06:20:39 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 500
LICENSE_SESSIONS_WARNING = 150
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  resource_limit           = TRUE
  license_max_sessions     = 500
  license_sessions_warning = 150
  __shared_pool_size       = 452984832
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  filesystemio_options     = setall
  sga_target               = 2147483648
  control_files            = /home/oracle/app/oracle/oradata/test_s/control01.ctl, /home/oracle/app/oracle/oradata/test_s/control02.ctl, /home/oracle/app/oracle/oradata/test_s/control03.ctl
  db_file_name_convert     = /home/oracle/app/oracle/oradata/test/, /home/oracle/app/oracle/oradata/test_s/
  log_file_name_convert    = /home/oracle/app/oracle/oradata/test/, /home/oracle/app/oracle/oradata/test_s/
  control_file_record_keep_time= 30
  db_block_size            = 8192
  __db_cache_size          = 1644167168
  compatible               = 10.2.0.4.0
  log_archive_dest_1       = LOCATION=/home/oracle/app/oracle/oradata/test_s/archive
  log_archive_dest_state_1 = ENABLE
  standby_archive_dest     = /home/oracle/app/oracle/oradata/test_s/archive
  log_archive_format       = %t_%s_%r.arc
  archive_lag_target       = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /home/oracle/app/oracle/oradata/test_s/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = TRUE
  db_domain                =
  instance_name            = test_s
  session_cached_cursors   = 200
  utl_file_dir             = /home/oracle/app/oracle/oradata/test_s/utlfile
  job_queue_processes      = 10
  background_dump_dest     = /home/oracle/app/oracle/admin/test_s/bdump
  user_dump_dest           = /home/oracle/app/oracle/admin/test_s/udump
  core_dump_dest           = /home/oracle/app/oracle/admin/test_s/cdump
  audit_file_dest          = /home/oracle/app/oracle/admin/test_s/adump
  audit_trail              = OS
  db_name                  = test
  db_unique_name           = test_s
  open_cursors             = 3000
  pga_aggregate_target     = 52428800
PMON started with pid=2, OS id=15607
PSP0 started with pid=3, OS id=15609
MMAN started with pid=4, OS id=15611
DBW0 started with pid=5, OS id=15613
LGWR started with pid=6, OS id=15615
CKPT started with pid=7, OS id=15617
SMON started with pid=8, OS id=15619
RECO started with pid=9, OS id=15621
CJQ0 started with pid=10, OS id=15623
MMON started with pid=11, OS id=15625
MMNL started with pid=12, OS id=15627
Wed Mar  4 06:20:41 2009
alter database mount
Wed Mar  4 06:20:45 2009
Setting recovery target incarnation to 2
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=15631
Wed Mar  4 06:20:45 2009
ARC0: Archival started
ARC1: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed Mar  4 06:20:45 2009
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
ARC1 started with pid=16, OS id=15633
ARC1: Becoming the heartbeat ARCH
ARC1: Thread not mounted
Wed Mar  4 06:20:45 2009
Successful mount of redo thread 1, with mount id 1978778425
Wed Mar  4 06:20:45 2009
Physical Standby Database mounted.
Completed: alter database mount

11. 媒体恢复备库

recover database;

RMAN> recover database;

Starting recover at 04-MAR-09
Starting implicit crosscheck backup at 04-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1642 devtype=DISK
Finished implicit crosscheck backup at 04-MAR-09

Starting implicit crosscheck copy at 04-MAR-09
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 04-MAR-09

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

starting media recovery

archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc thread=1 sequence=2083
archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc thread=1 sequence=2084
archive log filename=/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc thread=1 sequence=2085
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/04/2009 06:21:05
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
ORA-00310: archived log contains sequence 2084; sequence 2085 required
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'

RMAN>

告警日志:

Wed Mar  4 06:21:05 2009
alter database recover datafile list clear
Wed Mar  4 06:21:05 2009
Completed: alter database recover datafile list clear
Wed Mar  4 06:21:05 2009
alter database recover datafile list
 1 , 2 , 3 , 4 , 5
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5
Wed Mar  4 06:21:05 2009
alter database recover if needed
 standby start
Media Recovery Start
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
ORA-279 signalled during: alter database recover if needed
 standby start
...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc
ORA-279 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2083_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
ORA-279 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'
Wed Mar  4 06:21:05 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
Errors with log /home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc
ORA-310 signalled during: alter database recover logfile '/home/oracle/app/oracle/oradata/test_s/archive/1_2084_676622925.arc'...
Wed Mar  4 06:21:05 2009
alter database recover cancel
Wed Mar  4 06:21:07 2009
Media Recovery Canceled
Completed: alter database recover cancel

12. 主库开启远程归档

tnsping test_s
sqlplus -s /nolog <<EOF
conn / as sysdba
alter system set log_archive_dest_2='service=test_s optional reopen=60';
alter system set log_archive_dest_state_2='enable';
exit
EOF

13. 备库开启自动恢复

sqlplus /nolog <<EOF
conn / as sysdba
alter database recover managed standby database disconnect from session;
exit
EOF

告警日志:

Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 15720
RFS[1]: Identified database type as 'physical standby'
Wed Mar  4 06:22:07 2009
RFS LogMiner: Client disabled from further notification
Wed Mar  4 06:22:11 2009
alter database recover managed standby database disconnect from session
Wed Mar  4 06:22:11 2009
Attempt to start background Managed Standby Recovery process (test_s)
MRP0 started with pid=17, OS id=15724
Wed Mar  4 06:22:11 2009
MRP0: Background Managed Standby Recovery process started (test_s)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Wed Mar  4 06:22:16 2009
Waiting for all non-current ORLs to be archived...
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /home/oracle/app/oracle/oradata/test_s/redo01.rdo
Clearing online log 1 of thread 1 sequence number 2083
Wed Mar  4 06:22:16 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo01.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:17 2009
Completed: alter database recover managed standby database disconnect from session
Wed Mar  4 06:22:22 2009
Clearing online redo logfile 1 complete
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 /home/oracle/app/oracle/oradata/test_s/redo02.rdo
Clearing online log 2 of thread 1 sequence number 2084
Wed Mar  4 06:22:22 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo02.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 2 complete
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 /home/oracle/app/oracle/oradata/test_s/redo03.rdo
Clearing online log 3 of thread 1 sequence number 2085
Wed Mar  4 06:22:27 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03b.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/test_s/redo03.rdo'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Mar  4 06:22:32 2009
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 2085

14.测试打开备库

只读打开

alter database recover managed standby database cancel;
alter database open;

10g以前用

alter database open read only;

告警日志

Wed Mar  4 06:28:46 2009
alter database recover managed standby database cancel
Wed Mar  4 06:28:48 2009
MRP0: Background Media Recovery cancelled with status 16037
Wed Mar  4 06:28:48 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Wed Mar  4 06:28:49 2009
Errors in file /home/oracle/app/oracle/admin/test_s/bdump/test_s_mrp0_15724.trc:
ORA-16037: user requested cancel of managed recovery operation
Wed Mar  4 06:28:49 2009
MRP0: Background Media Recovery process shutdown (test_s)
Wed Mar  4 06:28:49 2009
Managed Standby Recovery Canceled (test_s)
Wed Mar  4 06:28:49 2009
Completed: alter database recover managed standby database cancel
Wed Mar  4 06:29:00 2009
alter database open
Wed Mar  4 06:29:00 2009
SMON: enabling cache recovery
Wed Mar  4 06:29:00 2009
Re-creating tempfile /home/oracle/app/oracle/oradata/test_s/temp01.dbf
Re-creating tempfile /home/oracle/app/oracle/oradata/test_s/ts_temp01.dbf
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
Wed Mar  4 06:29:01 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

复原

alter database recover managed standby database disconnect from session;

告警日志

Wed Mar  4 06:29:26 2009
alter database recover managed standby database disconnect from session
Wed Mar  4 06:29:26 2009
Stopping background process CJQ0
Wed Mar  4 06:29:26 2009
Stopping Job queue slave processes, flags = 27
Wed Mar  4 06:29:26 2009
Job queue slave processes stopped
Wed Mar  4 06:29:26 2009
SMON: disabling cache recovery
Wed Mar  4 06:29:26 2009
Attempt to start background Managed Standby Recovery process (test_s)
MRP0 started with pid=10, OS id=16009
Wed Mar  4 06:29:26 2009
MRP0: Background Managed Standby Recovery process started (test_s)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Wed Mar  4 06:29:31 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 2086
Wed Mar  4 06:29:32 2009
Completed: alter database recover managed standby database disconnect from session

15.
第6步备份数据文件,还有一种方式

首先转换表空间到热备份模式(hot backup mode), alter tablespace xxx begin backup, 10g可以在数据库级alter database begin backup
然后用rsync等工具复制到备库服务器上(rsync能限制传输速度,断点续传)
如:

cd /home/oracle/app/oracle/oradata/test/
rsync -azv --progress --bwlimit=1024 -e ssh ts_test01.dbf oracle@dev-pxy-1:/home/oracle/app/oracle/oradata/test_s/

最后转换成正常模式, alter tablespace xxx end backup;

如果没有切换到热备份模式, 直接就拷数据文件, 到时恢复会报错

WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
 parallel recovery started with 2 processes

每个表空间都这样备份一遍
转换表空间到热备份模式的问题是, 导致产生很多重做日志, 影响性能, 所以也不推荐在事务繁忙的生产系统上使用

外部链接:
Creating a Standby Database with Image Copies(这个要备份恢复, 太费事了)
What Happens When A Tablespace/Database Is Kept In Begin Backup Mode
What Happens During a Hot Backup
ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS

-fin-

谢太数据库

谢太的最爱

Blog Archive

Monday, February 21, 2011

Using dNFS clonedb for testing - 使用dNFS搭建测试数据库

Wednesday, September 23, 2009

introducing maatkit - parallel dump

Saturday, May 30, 2009

recover from corrupted undo tablespace 恢复损坏的撤销表空间

Monday, May 4, 2009

flashback dropped tablespace 用闪回数据库恢复删除的表空间

Monday, March 16, 2009

tar over ssh 通过ssh进行tar备份

Wednesday, March 4, 2009

creating standby database with image copies 使用映像拷贝创建备用数据库

Followers

谢太数据库

订阅谢太数据库

谢太的最爱

Blog Archive

Monday, February 21, 2011

Using dNFS clonedb for testing - 使用dNFS搭建测试数据库

Wednesday, September 23, 2009

introducing maatkit - parallel dump

Saturday, May 30, 2009

recover from corrupted undo tablespace 恢复损坏的撤销表空间

Monday, May 4, 2009

flashback dropped tablespace 用闪回数据库恢复删除的表空间

Monday, March 16, 2009

tar over ssh 通过ssh进行tar备份

Wednesday, March 4, 2009

creating standby database with image copies 使用映像拷贝创建备用数据库

Followers