ITPub博客

首页 > 数据库 > Oracle > 【备份恢复】从备份恢复数据库

【备份恢复】从备份恢复数据库

原创 Oracle 作者:GM_DBA 时间:2015-01-20 13:28:13 0 删除 编辑


准备工作

[root@edbjr2p1 ~]# su - oracle
[oracle@edbjr2p1 ~]$ export ORACLE_SID=PROD
[oracle@edbjr2p1 ~]$ cd /u01/arch
      
    备份数据库,备份脚本如下

backup.cmd

run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
crosscheck backup;
crosscheck archivelog all;
delete force noprompt expired backup;
delete force noprompt expired archivelog all;
allocate channel c1 device type disk connect sys/oracle@PROD;
sql 'alter system archive log current';
backup as compressed backupset database format '/u01/arch/full_%d_%s_%p_%T' plus archivelog delete all input format '/u01/arch/arch_%d_%s_%p_%T';
backup current controlfile format '/u01/arch/ctl_%d_%s_%p_%T';
backup spfile format ‘/u01/arch/spfile_%d_%T.bak’;
release channel c1;
}

backup.sh

rman target / nocatalog cmdfile=/u01/backup.cmd log=/u01/backup-`date +%Y%m%d`.log


一、查看备份
[oracle@edbjr2p1 arch]$ ls -lrt
total 69508
-rw-r----- 1 oracle oinstall    13312 Jan  9 11:19 arch_PROD_39_1_20150109
-rw-r----- 1 oracle oinstall 62971904 Jan  9 11:20 full_PROD_40_1_20150109
-rw-r----- 1 oracle oinstall  1114112 Jan  9 11:20 full_PROD_41_1_20150109
-rw-r----- 1 oracle oinstall     3072 Jan  9 11:20 arch_PROD_42_1_20150109
-rw-r----- 1 oracle oinstall  6881280 Jan  9 11:20 ctl_PROD_43_1_20150109
-rw-r----- 1 oracle oinstall    98304 Jan  9 11:20 spfile_PROD_44_1_20150109
[oracle@edbjr2p1 arch]$ 
[oracle@edbjr2p1 arch]$ 
[oracle@edbjr2p1 arch]$ 
[oracle@edbjr2p1 arch]$ rm -rf $ORACLE_HOME/dbs/*PROD.ora
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:21:02 2015


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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

二、删除数据库,尝试启动。
[oracle@edbjr2p1 arch]$ rm -rf /u01/app/oracle/oradata/PROD/disk*/*
[oracle@edbjr2p1 arch]$ set -o vi
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:22:23 2015

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

Connected to an idle instance.

SYS@PROD>startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD.ora'
SYS@PROD>exit
Disconnected

三、还原参数文件
[oracle@edbjr2p1 arch]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 9 11:22:31 2015

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

connected to target database (not started)

RMAN> restore spfile from '/u01/arch/spfile_PROD_44_1_20150109';

Starting restore at 09-JAN-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/09/2015 11:22:46
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> restore spfile from '/u01/arch/spfile_PROD_44_1_20150109';

Starting restore at 09-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/arch/spfile_PROD_44_1_20150109
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-JAN-15

RMAN> shutdown abort

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     524288000 bytes

Fixed Size                     1220384 bytes
Variable Size                155189472 bytes
Database Buffers             364904448 bytes
Redo Buffers                   2973696 bytes

RMAN> exit

Recovery Manager complete.
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:23:25 2015

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SYS@PROD>show parameter com


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
compatible                           string     10.2.0
max_commit_propagation_delay         integer     0
nls_comp                             string
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_v2_compatibility               boolean     FALSE
SYS@PROD>exit
Disconnected from Oracle Database 10g Enterprise Edition  Data Mining Scoring Engine options
[oracle@edbjr2p1 arch]$ ls -lrt
total 69508
-rw-r----- 1 oracle oinstall    13312 Jan  9 11:19 arch_PROD_39_1_20150109
-rw-r----- 1 oracle oinstall 62971904 Jan  9 11:20 full_PROD_40_1_20150109
-rw-r----- 1 oracle oinstall  1114112 Jan  9 11:20 full_PROD_41_1_20150109
-rw-r----- 1 oracle oinstall     3072 Jan  9 11:20 arch_PROD_42_1_20150109
-rw-r----- 1 oracle oinstall  6881280 Jan  9 11:20 ctl_PROD_43_1_20150109
-rw-r----- 1 oracle oinstall    98304 Jan  9 11:20 spfile_PROD_44_1_20150109

四、恢复控制文件


[oracle@edbjr2p1 arch]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 9 11:23:41 2015

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

connected to target database: PROD (not mounted)

RMAN> restore controlfile from '/u01/arch/ctl_PROD_43_1_20150109';

Starting restore at 09-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/PROD/disk1/control01.ctl
output filename=/u01/app/oracle/oradata/PROD/disk2/control02.ctl
output filename=/u01/app/oracle/oradata/PROD/disk3/control03.ctl
Finished restore at 09-JAN-15

RMAN> alter database mount;        

database mounted
released channel: ORA_DISK_1


五、恢复数据库
RMAN> restore database;

Starting restore at 09-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=320 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/PROD/disk1/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk1/USERS.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk1/DSS.dbf
channel ORA_DISK_1: reading from backup piece /u01/arch/full_PROD_40_1_20150109
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/arch/full_PROD_40_1_20150109 tag=TAG20150109T112000
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09-JAN-15

RMAN> recover database;

Starting recover at 09-JAN-15
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=36
channel ORA_DISK_1: reading from backup piece /u01/arch/arch_PROD_42_1_20150109
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/arch/arch_PROD_42_1_20150109 tag=TAG20150109T112017
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/arch/1_36_865882219.dbf thread=1 sequence=36
unable to find archive log
archive log thread=1 sequence=37
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/09/2015 11:25:24
RMAN-06054: media recovery requesting unknown log: thread 1 seq 37 lowscn 178388

RMAN> alter databaes open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databaes
RMAN-01007: at line 1 column 7 file: standard input

RMAN> alter database open resetlogs;

database opened

RMAN> 

Recovery Manager complete.

至此数据库恢复完成
[oracle@edbjr2p1 arch]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 9 11:27:01 2015


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options


SYS@PROD>show parameter com


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
compatible                           string      10.2.0
max_commit_propagation_delay         integer     0
nls_comp                             string
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_v2_compatibility               boolean     FALSE
SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>startup
ORACLE instance started.


Total System Global Area  524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             155189472 bytes
Database Buffers          364904448 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter com


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
compatible                           string      10.2.0
max_commit_propagation_delay         integer     0
nls_comp                             string
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_v2_compatibility               boolean     FALSE
SYS@PROD>alter system set compatible='10.2.0.1.0' scope=spfile;


System altered.


SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>startup
ORACLE instance started.


Total System Global Area  524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             155189472 bytes
Database Buffers          364904448 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter com


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
compatible                           string      10.2.0.1.0
max_commit_propagation_delay         integer     0
nls_comp                             string
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_v2_compatibility               boolean     FALSE
SYS@PROD>



  简单做了个备份恢复的实验,记录一下。
  2015.1.9


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29319205/viewspace-1407979/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-20

  • 博文量
    33
  • 访问量
    192171