高分求使用rman备份和恢复oracle数据库的 *** 作实例,本人初学,求高手指教_sql

1 创建一个名为rman的数据库,可以使用dbca工具,根据向导提示建立即可，该数据库用于备份信息的管理

2 在rman数据库中创建rman用户(密码暂设为rman)，用于访问rman数据库，并赋予权限

export ORACLE_SID=rman

sqlplus / as sysdba

create user rman identified by rman default tablespace users quota unlimited on users

grant create type,recovery_catalog_owner to rman

exit

3 使用rman用户登录到rman数据库，建立catalog编录

rman catalog rman/rman@rman

create catalog

exit

4 使用rman命令连接要备份的数据库，例如orcl，进行注册

export ORACLE_SID=orcl

rman target / catalog rman/rman@rman

这样就可以通过rman命令进行orcl数据库的备份管理了。

常见rman命令有：

rman target / catalog rman/rman@rman

list backup列出所有备份

report obsolete 列出过期备份

report need backup days=3列出最近三天没有备份的文件

backup database备份数据库

backup tablespace tbsname备份表空间tbsname

restore database 恢复数据库

一、RMAN 备份的内容

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

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

二、测试环境

OS：CentOS release 6.4 (Final)

Database：Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 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 12.1.0.2.0 - 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/12.1.0.2/dbs/initscp.ora'

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/initscp.ora' from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01'

OR：

RMAN>restore spfile to '$ORACLE_HOME/dbs/spfilescp.ora' 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 initscp.ora

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='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/scp/control01.ctl','/u01/app/oracle/fast_recovery_area/scp/control02.ctl'

*.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/initscp.ora'

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/control01.ctl

output file name=/home/OracleArch/fast_recovery_area/control02.ctl

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/12.1.0.2/dbs/snapcf_scp.f'# 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