Oracle 11g 采用冷备异机恢复数据库

           今日做一个实验，验证一下主库采用冷备的方式进行异机恢复数据库都需要哪些文件

####主库进行停机操场#####

[oracle@ora11] /home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 24 23:40:14 2015

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

Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             645925928 bytes
Database Buffers          188743680 bytes
Redo Buffers                2355200 bytes
Database mounted.
Database opened.
SQL> shutdown abort             
ORACLE instance shut down.
SQL> exit

####首先拷贝主库的控制文件和数据文件到另外一台主机####

   ---控制文件

[oracle@ora11] /u01/app/datafile/XULQ/controlfile> scp o1_mf_bmqj051y_.ctl 192.168.56.13:/data/xulq
   ----数据文件

[oracle@ora11] /u01/app/datafile/XULQ/datafile> scp *  192.168.56.13:/data/xulq
oracle@192.168.56.13's password: 
ggusr01.dbf                                                                                                                          100%  100MB  10.0MB/s   00:10    
o1_mf_sysaux_bmqj0q5k_.dbf                                                                                               100%  600MB   9.4MB/s   01:04    
o1_mf_system_bmqj0bgb_.dbf                                                                                                           100% 1390MB  11.8MB/s   01:58    
o1_mf_temp_bmqj1fql_.tmp                                                                                                             100%   59MB  11.8MB/s   00:05    
o1_mf_undotbs1_bmqj1b4j_.dbf                                                                                                         100% 3625MB   8.9MB/s   06:47    
o1_mf_users_bmqj1x8s_.dbf                                                                                                            100%   39MB   2.8MB/s   00:14    
user02.dbf                                          

   ----参数文件

   scp initxulq.ora             --------------到异机 

###异机修改参数文件

---主要修改控制文件路径

xulq.__db_cache_size=318767104
xulq.__java_pool_size=4194304
xulq.__large_pool_size=8388608
xulq.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
xulq.__pga_aggregate_target=293601280
xulq.__sga_target=549453824
xulq.__shared_io_pool_size=0
xulq.__shared_pool_size=209715200
xulq.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/xulq/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/xulq/o1_mf_bmqj051y_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/xulq'
*.db_domain=''
*.db_file_name_convert='/u01/app/datafile/XULQ/datafile','/data/xulq/'   ----该参数暂时不好用，就讲数据文件放在和原来一样的位置。
*.db_flashback_retention_target=1440
*.db_name='xulq'
*.db_recovery_file_dest_size=2147483648
#*.db_recovery_file_dest='/data/flash'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xulqXDB)'
*.log_archive_dest_1='location=/u01/arch'
*.memory_target=842006528
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

####启动数据库#######

[oracle@ora12 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 25 08:11:18 2015

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             645925928 bytes
Database Buffers          188743680 bytes
Redo Buffers                2355200 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'/u01/app/datafile/XULQ/onlinelog/o1_mf_2_bmqj05k7_.log'   ---这里提示需要拷贝在线日志文件。
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 

SQL> exit

####试一试重建控制文件####

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "XULQ" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 665
LOGFILE
  GROUP 1 '/u01/app/datafile/XULQ/onlinelog/o1_mf_1_bmqj05hk_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/datafile/XULQ/onlinelog/o1_mf_2_bmqj05k7_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/datafile/XULQ/onlinelog/o1_mf_3_bmqj0779_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/datafile/XULQ/datafile/o1_mf_system_bmqj0bgb_.dbf',
  '/u01/app/datafile/XULQ/datafile/o1_mf_sysaux_bmqj0q5k_.dbf',
  '/u01/app/datafile/XULQ/datafile/o1_mf_undotbs1_bmqj1b4j_.dbf',
  '/u01/app/datafile/XULQ/datafile/o1_mf_users_bmqj1x8s_.dbf',
  '/u01/app/datafile/XULQ/datafile/user02.dbf',
  '/u01/app/datafile/XULQ/datafile/ggusr01.dbf'
CHARACTER SET AL32UTF8;

Control file created.

SQL> exit

###试着打开数据库

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 25 08:33:05 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:

'/u01/app/datafile/XULQ/datafile/o1_mf_system_bmqj0bgb_.dbf'

####使用原来的控制文件并且拷贝原在线日志文件#####

[oracle@ora11] /u01/app/datafile/XULQ/onlinelog> scp * 192.168.56.13:/u01/app/datafile/XULQ/onlinelog
oracle@192.168.56.13's password: 
o1_mf_1_bmqj05hk_.log                                                                                                                100%   50MB  25.0MB/s   00:02    
o1_mf_2_bmqj05k7_.log                                                                                                                100%   50MB   8.3MB/s   00:06    
o1_mf_3_bmqj0779_.log                                                                                                                100%   50MB  12.5MB/s   00:04  

###再次启动数据库###

SQL> alter database open;


Database altered.

总结:

个人感觉最好的冷备方法是:拷贝:数据文件，控制文件，在线redo文件。