ORACLE数据库RMAN备份问题、_工具

RMAN> show channel ;

db_unique_name 为 REPO 的数据库的 RMAN 配置参数为:

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/fast_recovery_area/REPO/c2/%U';

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u01/app/oracle/fast_recovery_area/REPO/c1/%U';

backup database 后：

[oracle@prod c2]$ ll /u01/app/oracle/fast_recovery_area/REPO/c1/

总计 711092

-rw-r----- 1 oracle oinstall 727441408 01-12 04:26 05otrqks_1_1

[oracle@prod c2]$ ll /u01/app/oracle/fast_recovery_area/REPO/c2/

总计 760036

-rw-r----- 1 oracle oinstall 767508480 01-12 04:25 06otrqks_1_1

-rw-r----- 1 oracle oinstall 9895936 01-12 04:25 07otrqma_1_1

-rw-r----- 1 oracle oinstall 98304 01-12 04:25 08otrqmd_1_1

我这边两个目录都有备份文件，最好将你的备份backup database 输出拿出来看一下。

1、切换服务器归档模式，如果已经是归档模式可跳过此步：

%sqlplus /nolog (启动sqlplus)

SQL> conn / as sysdba (以DBA身份连接数据库)

SQL> shutdown immediate; (立即关闭数据库)

SQL> startup mount (启动实例并加载数据库，但不打开)

SQL> alter database archivelog; (更改数据库为归档模式)

SQL> alter database open; (打开数据库)

SQL> alter system archive log start; (启用自动归档)

SQL> exit (退出) 2、连接：

rman target=sys/comeon@orcl; (启动恢复管理器) 3、基本设置：

RMAN> configure default device type to disk; (设置默认的备份设备为磁盘)

RMAN> configure device type disk parallelism 2; (设置备份的并行级别，通道数)

RMAN> configure channel 1 device type disk fromat '/backup1/backup_%U'; (设置备份的文件格式，只适用于磁盘设备)

RMAN> configure channel 2 device type disk fromat '/backup2/backup_%U'; (设置备份的文件格式，只适用于磁盘设备)

RMAN> configure controlfile autobackup on; (打开控制文件与服务器参数文件的自动备份)

RMAN> configure controlfile autobackup format for device type disk to '/backup1/ctl_%F'; (设置控制文件与服务器参数文件自动备份的文件格式) 4、查看所有设置：

RMAN> show all 5、查看数据库方案报表：

RMAN> report schema; 6、备份全库：

RMAN> backup database plus archivelog delete input; (备份全库及控制文件、服务器参数文件与所有归档的重做日志，并删除旧的归档日志) 7、备份表空间：

RMAN> backup tablespace system plus archivelog delete input; (备份指定表空间及归档的重做日志，并删除旧的归档日志) 8、备份归档日志：

RMAN> backup archivelog all delete input; 9、复制数据文件：

RMAN> copy datafile 1 to '/oracle/dbs/systemcopy'; 10、查看备份和文件复本：

RMAN> list backup; 11、验证备份：

RMAN> validate backupset 3; 12、从自动备份中恢复服务器参数文件：

RMAN> shutdown immediate; (立即关闭数据库)

RMAN> startup nomount; (启动实例)

RMAN> restore spfile to pfile '/backup1/mydbora' from autobackup; (从自动备份中恢复服务器参数文件) 13、从自动备份中恢复控制文件：

RMAN> shutdown immediate; (立即关闭数据库)

RMAN> startup nomount; (启动实例)

RMAN> restore controlfile to '/backup1' from autobackup; (从自动备份中恢复控制文件) 13、恢复和复原全数据库：

RMAN> shutdown immediate; (立即关闭数据库)

RMAN> exit (退出)

%mv /oracle/dbs/tbs_12f /oracle/dbs/tbs_12bak (将数据文件重命名)

%mv /oracle/dbs/tbs_13f /oracle/dbs/tbs_13bak (将数据文件重命名)

%mv /oracle/dbs/tbs_14f /oracle/dbs/tbs_14bak (将数据文件重命名)

%mv /oracle/dbs/tbs_15f /oracle/dbs/tbs_15bak (将数据文件重命名)

%rman target=rman/rman@mydb (启动恢复管理器)

RMAN> startup pfile=/oracle/admin/mydb/pfile/initmydbora (指定初始化参数文件启动数据库)

RMAN> restore database; (还原数据库)

RMAN> recover database; (恢复数据库)

RMAN> alter database open; (打开数据库) 14、恢复和复原表空间：

RMAN> sql 'alter tablespace users offline immediate'; (将表空间脱机)

RMAN> exit (退出恢复管理器)

%mv /oracle/dbs/users01dbf /oracle/dbs/users01bak (将表空间重命名)

%rman target=rman/rman@mydb (启动恢复管理器)

RMAN> restore tablespace users; (还原表空间)

RMAN> recover tablespace users; (恢复表空间)

RMAN> sql 'alter tablespace users online'; (将表空间联机)

Oracle数据库中rman备份脚本非常实用

查询字符集

SQL> select from nls_database_parameters;

NLS_CHARACTERSET

AL32UTF8

备份时字符集很重要，不然会出乱码

一、0级备份（全备）红色是要修改的，根据个人的存放位置。

[oracle@oracle122 script]$ cat db_bak0rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc0_%d_%s_%p_%u_%T';

backup incremental level 0 tag='level 0' database skip inaccessible filesperset 10 plus archivelog filesperset 20;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

delete noprompt archivelog until time 'sysdate-30';

0级运行脚本

[oracle@oracle122 script]$ cat exec_0_levelsh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/1120/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china"AL32UTF8

/u01/app/oracle/product/1120/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak0rman log /home/oracle/rmanbak/script/log/rman_$DATElog append

二、1级备份（增量）

[oracle@oracle122 script]$ cat db_bak1rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc1_%d_%s_%p_%u_%T';

backup incremental level 1 tag='level 1' database skip inaccessible filesperset 10 plus archivelog filesperset 20;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

delete noprompt archivelog until time 'sysdate-30';

1级运行脚本

[oracle@oracle122 script]$ cat exec_1_levelsh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/1120/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china"AL32UTF8

/u01/app/oracle/product/1120/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak1rman log /home/oracle/rmanbak/script/log/rman_$DATElog append

三、2级备份（从0级追加到现在的备份）

[oracle@oracle122 script]$ cat db_bak2rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc2_%d_%s_%p_%u_%T';

backup incremental level 2 tag='level 2' database skip inaccessible filesperset 10 plus archivelog filesperset 20 delete all input;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

2级运行脚本

[oracle@oracle122 script]$ cat exec_2_levelsh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/1120/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china"AL32UTF8

/u01/app/oracle/product/1120/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak2rman log /home/oracle/rmanbak/script/log/rman_$DATElog append

定时备份星期天为0级全备，星期三为2级备份，星期一，星期二，星期四，星期五，星期六为1级增量备份晚上11点开始备份

[oracle@oracle122 script]$ crontab -l

23 0 /home/oracle/rmanbak/script/exec_0_levelsh

23 1 /home/oracle/rmanbak/script/exec_1_levelsh

23 2 /home/oracle/rmanbak/script/exec_1_levelsh

23 3 /home/oracle/rmanbak/script/exec_2_levelsh

23 4 /home/oracle/rmanbak/script/exec_1_levelsh

23 5 /home/oracle/rmanbak/script/exec_1_levelsh

23 6 /home/oracle/rmanbak/script/exec_1_levelsh

一、RMAN 备份的内容

RMAN做数据库全备时包含了数据文件、归档日志、控制文件和参数文件和备份日志，如下：

arch_20160223_08qukp2t_1_1 arch_20160223_0bqukp92_1_1 ctl_c-3234695588-20160223-01 rmanbak-20160223-0852log scp_20160223_09qukp2u_1_1 scp_20160223_0aqukp2u_1_1

二、测试环境

OS：CentOS release 64 (Final)

Database：Oracle Database 12c Enterprise Edition Release 121020 - 64bit Production

原平台与当前平台环境一致，但是oracle数据库目录结构不一致。

三、开始恢复

1、确认原数据库的DBID（通过RMAN的备份日志，或者通过RMAN备份的控制文件名来识别），同时确认一下原数据库的实例名；

2、将RMAN备份的内容拷贝到目标数据库上；

3、设置好环境变量：

[oracle@dg1 ~]$ export NLS_DATA_FORMAT='YYYY-MM-DD HH24:MI:SS'

[oracle@dg1 ~]$ export ORACLE_SID=scp

[oracle@dg1 ~]$ rman target /

Recovery Manager: Release 121020 - Production on Fri May 6 22:18:52 2016

4、装载数据库并进行恢复

A、装载数据库

复制代码

RMAN> set dbid=3234695588;

executing command: SET DBID

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12102/dbs/initscpora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

复制代码

B、先恢复spfile文件（或者是pfile文件，可以修改各项参数），因为在spfile 文件中包含了控制文件的位置

RMAN> restore spfile to pfile '$ORACLE_HOME/dbs/initscpora' from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

OR：

RMAN> restore spfile to '$ORACLE_HOME/dbs/spfilescpora' from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

C、根据pfile文件中的相关参数去修改本地系统目录，或者修改这个pfile文件来匹配本地系统中的路径和目录，我们选择后者

需要修改：oracle_base、audit_file_dest、control_files、db_recovery_file_dest、db_recovery_file_dest_size、diagnostic_dest、log_archive_dest_1、

memory_target、undo_tablespace等。并在当前系统中创建好相关的目录和权限。

复制代码

[oracle@dg1 dbs]$ vim initscpora

scp__data_transfer_cache_size=0

scp__db_cache_size=822083584

scp__java_pool_size=16777216

scp__large_pool_size=33554432

scp__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

scp__pga_aggregate_target=654311424

scp__sga_target=1241513984

scp__shared_io_pool_size=50331648

scp__shared_pool_size=301989888

scp__streams_pool_size=0

audit_file_dest='/u01/app/oracle/admin/scp/adump'

audit_trail='db'

compatible='121020'

control_files='/u01/app/oracle/oradata/scp/control01ctl','/u01/app/oracle/fast_recovery_area/scp/control02ctl'

db_block_size=8192

db_domain=''

db_name='scp'

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=4560m

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=scpXDB)'

log_archive_dest_1='LOCATION=/OracleArch'

memory_target=1800m

open_cursors=300

processes=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

复制代码

D、从修改后的pfile文件启动数据库，进行控制文件的恢复

复制代码

RMAN> shutdown abort;

RMAN> startup nomount pfile='$ORACLE_HOME/dbs/initscpora';

RMAN> restore controlfile from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

Starting restore at 06-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=243 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/home/OracleData/scp/control01ctl

output file name=/home/OracleArch/fast_recovery_area/control02ctl

Finished restore at 06-MAY-16

复制代码

E、控制文件恢复后就可以mount数据库了

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

F、至此，控制文件已经恢复，数据库已经mount，所有的RMAN配置参数均已设置，您应该验证路径以确保它们适用于该主机。

复制代码

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SCP are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/RmanBackupSet/20160223/ctl_%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12102/dbs/snapcf_scpf'; # default

复制代码

G、为了能让RMAN找到恢复文件的位置，我们有两种途径可以实现：一是修改RMAN配置以符合当前备份文件所在位置，其次是将RMAN备份文件拷贝到配置文件中设定的位置（需要参考RMAN的备份日志）。在第一个方法中，为了让RMAN知道备份文件位置 /home/OracleBack/rmanbak ，我们使用catalog命令:

复制代码

RMAN> catalog start with '/home/OracleBack/rmanbak';

Starting implicit crosscheck backup at 06-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=243 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 06-MAY-16

Starting implicit crosscheck copy at 06-MAY-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 06-MAY-16

searching for all files in the recovery area

cataloging files

no files cataloged

searching for all files that match the pattern /home/OracleBack/rmanbak

List of Files Unknown to the Database

=====================================

File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1

File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1

File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1

File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01

File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1

Do you really want to catalog the above files (enter YES or NO) yes

cataloging files

cataloging done

List of Cataloged Files

=======================

File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1

File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1

File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1

File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01

File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1

复制代码

H、对备份集做交叉检查，否则还原数据库时可能会报错

复制代码

RMAN> crosscheck backup;

using channel ORA_DISK_1

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/orabackup/RmanBackupSet/20160223/arch_20160223_08qukp2t_1_1 RECID=8 STAMP=904553565