只有数据文件的备份如何恢复一例

foway

只有数据文件的恢复过程
Created by foway in uplooking.com 2008: 欢迎转载,但请保留本行说明,谢谢!

1.建立密码文件,参数文件,数据存放目录,日志存放目录.
2.指定ORACLE_SID,然后通过dbms_backup_restore从备份中读出数据文件
3.建立控制文件
4.加日志参数后resetlogs生成日志文件
5.全备数据库测试交易是否存在,并全备

我系统环境所有的文件如下:

[oracle@foway dbs]$ pwd
/opt/oracle/10g/dbs
[oracle@foway dbs]$ ls
initdw.ora  init.ora
[oracle@foway dbs]$ls /opt/oracle/oradata
已没有任何资料
[oracle@foway dbs]$ls /opt/oracle/admin
已没有任何资料
[oracle@foway dbs] ls /opt/oracle/flash_recovery_area/
ORCL
[oracle@foway dbs]ls /opt/oracle/flash_recovery_area/ORCL/backupset/
2008_07_18
[oracle@foway dbs]ls /opt/oracle/flash_recovery_area/ORCL/backupset/2008_07_18/
o1_mf_nnndf_TAG20080718T203240_482fkb0k_.bkp

在我这台电脑上, ,系统中所有的ORCL相关的数据文件,参数文件,日志文件,控制文件全部不在了, 没有其他可用的备份文件了,只有rman备份的数据文件在,这可怎么办呢?

没办法只能恢复了,可怎么恢复呢?

数据库启动的3个要素:数据文件[datafile  and  logfile ],参数文件,控制文件.
目前可一个都没有.所以我们必须想办法解决这3个要素所需要的资料了.

1.        先准备任意一个实例如ORCL的必须的目录,密码文件,参数文件:
目录:
[oracle@foway dbs]mkdir /opt/oracle/oradata/orcl
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/adump –p
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/cdump
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/bdump
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/udump
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/dpdump
[oracle@foway dbs]mkdir /opt/oracle/admin/orcl/pfile
密码文件:
[oracle@foway dbs] orapwd file=/opt/oracle/10g/dbs/orapworcl password=uplooking entries=5

参数文件:
[oracle@foway dbs]cd $ORACLE_HOME/dbs
[oracle@foway dbs]vi initorcl.ora
#/////////////////////内容////////////////
orcl.__db_cache_size=75497472
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=71303168
orcl.__streams_pool_size=8388608
*.audit_file_dest='/opt/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/orcl/control01.ctl','/opt/oracle/oradata/orcl/control02.ctl','/opt/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/orcl/udump'
#/////////////////////内容结束////////////////

2.        [oracle@foway dbs]export ORACLE_SID=orcl
[oracle@foway dbs]sql /nolog
SQL> conn /as sysdba
由于在刚才建立了参数文件所以我们能启动到nomount状态
SQL> startup force nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
下面是使用dbms_backup_restore提取数据文件了;
SQL> !ls /opt/oracle/oradata/orcl/
看到没有任何文件的,如果提取成功则有数据文件的 好我们开始……..
SQL>DECLARE
2devtype varchar2(256);
3done boolean;
4BEGIN
5devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6sys.dbms_backup_restore.restoreSetDatafile;
7sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/orcl/data1.dbf');
8sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/orcl/data2.dbf');
9sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/orcl/data3.dbf');
10sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/orcl/data4.dbf');
11sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/orcl/data5.dbf');
12sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>6,toname=>'/opt/oracle/oradata/orcl/dat6.dbf');
13sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/flash_recovery_area/ORCL/backupset/2008_07_18/o1_mf_nnndf_TAG20080718T203240_482fls2h_.bkp',params=>null);
14 sys.dbms_backup_restore.deviceDeallocate;
15 end;
16/
DECLARE
*
ERROR at line 1:
ORA-19583: conversation terminated due to error
ORA-19870: error reading backup piece
/opt/oracle/flash_recovery_area/ORCL/backupset/2015_02_15/o1_mf_nnndf_TAG2015021
5T031100_bfz7g53v_.bkp
ORA-19615: some files not found in backup set
ORA-19613: datafile 5 not found in backup set
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5149
ORA-06512: at line 15
说明数据库中可能只有4个datafile,那我们就按照4个来读取吧
SQL>DECLARE
2devtype varchar2(256);
3done boolean;
4BEGIN
5devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6sys.dbms_backup_restore.restoreSetDatafile;
7sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/orcl/data1.dbf');
8sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/orcl/data2.dbf');
9sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/orcl/data3.dbf');
10sys.dbms_backup_restore.restoreDatalfileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/orcl/data4.dbf');
11sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/flash_recovery_area/ORCL/backupset/2008_07_18/o1_mf_nnndf_TAG20080718T203240_482fls2h_.bkp',params=>null);
12 sys.dbms_backup_restore.deviceDeallocate;
13 end;
14/

PL/SQL procedure successfully completed.
SQL> !ls /opt/oracle/oradata/orcl/
data1.dbf  data2.dbf  data3.dbf  data4.dbf
恭喜已经有了数据文件了.
3.        有了数据文件就可以建立控制文件了.
SQL>startup force nomount;
SQL> CREATE CONTROLFILE set DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
2MAXLOGFILES 16
3MAXLOGMEMBERS 3
4MAXDATAFILES 100
5MAXINSTANCES 8
6MAXLOGHISTORY 292
7LOGFILE
8GROUP 1 '/opt/oracle/oradata/orcl/redo01.log'  SIZE 50M,
9GROUP 2 '/opt/oracle/oradata/orcl/redo02.log'  SIZE 50M,
10GROUP 3 '/opt/oracle/oradata/orcl/redo03.log'  SIZE 50M
11DATAFILE
12'/opt/oracle/oradata/orcl/data1.dbf',
13'/opt/oracle/oradata/orcl/data2.dbf’,
14'/opt/oracle/oradata/orcl/data3.dbf',
15'/opt/oracle/oradata/orcl/data4.dbf
16CHARACTER SET WE8ISO8859P1
17;

ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              88082676 bytes
Database Buffers           75497472 bytes
Redo Buffers                2973696 bytes

Control file created.
SQL> !ls /opt/oracle/oradata/orcl/
control01.ctl  control03.ctl  data2.dbf  data4.dbf
control02.ctl  data1.dbf     data3.dbf
好到这里已经按照我们的参数文件建立好控制文件了,下面是生成日志文件了.
SQL> alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              88082676 bytes
Database Buffers           75497472 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database open resetlogs;
SQL> !ls /opt/oracle/oradata/orcl/
control01.ctl  control03.ctl  data2.dbf  data4.dbf   redo02.log
control02.ctl  data1.dbf      data3.dbf  redo01.log  redo03.log

好了到这里,非常高兴了,你已经成功的从只有数据文件的备份中恢复了参数文件,控制文件,所有数据文件,日志文件了.

通常到这里还需要你全备数据库的哈,别忘了!

managergh

  支持

nicsky

好贴。。。。。。。。。

huzi1986

强烈支持

weigershi

先收藏了,以后估计用得到.

dingning239

楼主的方法非常好，最近我刚刚遇到和你一样的问题，也是通过dbms_backup_restore来提取文件搞定的

togoever

ding

glkgdj

友情支持，不过提个问题，如果不是用RMAN备份的，用VERITAS dp一类东西的备份出来的备份文件呢？这个方法是不行吧

renxiao2003

写的不错，LZ是自己亲手动手实践过的吧。

riceshell

好东西我是不会错过的，谢谢