同时丢失参数文件、控制文件及redologfile的不完全恢复（有数据文件的热备和归档）(一)

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/30839817

?##########恢复前的准备工作

   select
    'alter tablespace '||tablespace_name|| ' begin backup;' ||chr(10)||
    'host cp '||file_name||' /backup' ||chr(10)||
    'alter tablespace '||tablespace_name|| ' end backup;'
   from dba_data_files order by tablespace_name;

sys@PROD> alter tablespace SYSAUX begin backup;

2、日志做切换

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> conn gyj/gyj
Connected.
gyj@PROD> select * from gyj_test1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 guoyJ
         2 BBBBB

gyj@PROD> insert into gyj_test1 values(3,'CCCCC');

1 row created.

gyj@PROD> commit;

Commit complete.

gyj@PROD> alter system switch logfile;

System altered.

gyj@PROD> alter system switch logfile;

System altered.

3、丢失参数文件，控制文件，redo日志文件

[oracle@jfdb dbs]$ rm  -rf spfile.ora
[oracle@jfdb dbs]$ rm -rf spfilePROD.ora
[oracle@jfdb dbs]$ rm -rf initPROD.ora 
[oracle@jfdb PROD]$ rm -rf control0*
[oracle@jfdb PROD]$ rm -rf redo0* 

###########开始恢复
[oracle@jfdb trace]$ cat alert_PROD.log vi /tmp/pfile.ora processes = 150 sga_max_size = 900M sga_target = 900M control_files = "/u01/app/oracle/oradata/PROD/control01.ctl" control_files = "/u01/app/oracle/oradata/PROD/control02.ctl" _controlfile_update_check= "OFF" db_block_size = 8192 log_archive_dest_1 = "location=/arch" undo_tablespace = "UNDOTBS" _in_memory_undo = FALSE service_names = "PROD,crm,oa" local_listener = "(DESCRIPTION= (ADDRESS_LIST= (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1521)) (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1522))))" db_name = "PROD" pga_aggregate_target = 200M

2、启动实例
sys@PROD> startup pfile='/tmp/pfile.ora' nomount; ORACLE instance started. Total System Global Area 939495424 bytes Fixed Size 2233960 bytes Variable Size 251660696 bytes Database Buffers 679477248 bytes Redo Buffers 6123520 bytes sys@PROD> create spfile from pfile='/tmp/pfile.ora'; File created.

oracle@jfdb arch]$ cd /backup
[oracle@jfdb backup]$ ll
total 1664052
-rw-r-----. 1 oracle oinstall 340795392 Jun  8 06:01 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 system01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 tp01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Jun  8 06:01 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Jun  8 06:02 users01.dbf
[oracle@jfdb backup]$ cp * -rf /u01/app/oracle/oradata/PROD

4、查数据库字符集?

select distinct dbms_rowid.rowid_block_number(rowid) from props$;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 801
[oracle@jfdb PROD]$ dd if=system01.dbf of=guoyJoe bs=8192 skip=801 count=1

1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000159113 s, 51.5 MB/s
[oracle@jfdb PROD]$ strings guoyJoe
NO_USERID_VERIFIER_SALT 0438054C4F979EC5A5F74990346F5327,
WORKLOAD_REPLAY_MODE
bPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress,
WORKLOAD_CAPTURE_MODE
/CAPTURE implies workload capture is in progress,
EXPORT_VIEWS_VERSION
Export views r