Shulou(Shulou.com)06/01 Report--

Please indicate the source of the reprint: http://blog.csdn.net/guoyjoe/article/details/30839817

# preparation before recovery

1. Make a 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

Host cp / u01/app/oracle/oradata/PROD/sysaux01.dbf / backup

Alter tablespace SYSAUX end backup

Tablespace altered.

Sys@PROD > alter tablespace SYSTEM begin backup

Host cp / u01/app/oracle/oradata/PROD/system01.dbf / backup

Alter tablespace SYSTEM end backup

Alter tablespace TP1 begin backup

Host cp / u01/app/oracle/oradata/PROD/tp01.dbf / backup

Alter tablespace TP1 end backup

Alter tablespace UNDOTBS begin backup

Host cp / u01/app/oracle/oradata/PROD/undotbs01.dbf / backup

Alter tablespace UNDOTBS end backup

Alter tablespace USERS begin backup

Host cp / u01/app/oracle/oradata/PROD/users01.dbf / backup

Alter tablespace USERS end backup

2. Switch between logs

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. Missing parameter file, control file, redo log file

[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*

# start recovery

1. Create a parameter file

[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. Start the instance

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.

3. Start restoring data files

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. Check the database character set

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 revision #, DEFAULT_PERMANENT_TABLESPACE USERS$Name of default permanent tablespace, GLOBAL_DB_NAME PROD Global database name, NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters, NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set, NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception, NLS_LENGTH_SEMANTICS BYTE NLS length semantics, NLS_COMP BINARY NLS comparison, NLS_DUAL_CURRENCY Dual currency symbol NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format, NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format, NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Timestamp format, NLS_TIME_FORMAT HH.MI.SSXFF AM Time format, NLS_SORT BINARY Linguistic definition, NLS_DATE_LANGUAGE AMERICAN Date language, NLS_DATE_FORMAT DD-MON-RR Date format, NLS_CALENDAR GREGORIAN Calendar system, NLS_CHARACTERSET ZHS16GBK Character set, NLS_NUMERIC_CHARACTERS Numeric characters NLS_ISO_CURRENCY AMERICA ISO currency, NLS_CURRENCY Local currency, NLS_TERRITORY AMERICA Territory, NLS_LANGUAGE AMERICAN Language, DEFAULT_TBS_TYPE SMALLFILE Default tablespace type, DST_SECONDARY_TT_VERSION 0'Version of secondary timezone data file, DST_PRIMARY_TT_VERSION 14%Version of primary timezone data file, DST_UPGRADE_STATE NONE&State of Day Light Saving Time Upgrade, DBTIMEZONE + 08:00 DB timezone, TDE_MASTER_KEY_ID, Flashback Timestamp TimeZone GMT "Flashback timestamp created in GMT, DEFAULT_EDITION ORA$BASE$Name of the database default edition DEFAULT_PERMANENT_TABLESPACE SYSTEM$Name of default permanent tablespace, DEFAULT_TEMP_TABLESPACE TEMPTS$Name of default temporary tablespace, DICT.BASE 2 dictionary base tables version #

5. Create a control file sys@PROD > CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 32 3 MAXLOGMEMBERS 2 4 MAXDATAFILES 32 25 MAXINSTANCES 1 6 MAXLOGHISTORY 449 7 LOGFILE 8 GROUP 1'/ u01 GROUP SIZE SIZE 50m, 9 oradata 2'/ u01 GROUP 10 GROUP 3'/ u01 plaza SIZE redo03.log' oradat 50M 11 DATAFILE 12'/ u01aphort oradataPRODSystem01.dbfills, 13'/ u01plicatableoradataplaza PRODUniverse sysaux01.dbfills, 14'/ u01aphorsoradataandPRODLeundotbs01.dbfbrands, 15'/ u01 oradataoradataPRODUsers01.dbf` 16'/ u01 CHARACTER SET ZHS16GBK App amp oraclemax oradata app01.dbf' 17 Control file created.

6. Registration file log sys@PROD > select count (*) from vested archived log; COUNT (*)-0 sys@PROD > alter database register physical logfile'/ arch/1_134_842976958.dbf'; Database altered. Sys@PROD > alter database register physical logfile'/ arch/1_135_842976958.dbf'; Database altered. Sys@PROD > alter database register physical logfile'/ arch/1_136_842976958.dbf'; Database altered. Sys@PROD > alter database register physical logfile'/ arch/1_137_842976958.dbf'; Database altered. Sys@PROD > alter database register physical logfile'/ arch/1_138_842976958.dbf'; Database altered. Sys@PROD > select count (*) from vested archived log; COUNT (*)-5

7. Check whether the checkpoint of the data file header is consistent with that of the control file

Sys@PROD > select file#,checkpoint_change# from vault datafile; FILE# CHECKPOINT_CHANGE#-- 1 1658759 2 1658739 3 1658788 4 1658802 5 1658774 sys@PROD > select file#,checkpoint_change# from v$datafile_header FILE# CHECKPOINT_CHANGE#-1 1658759 2 1658739 3 1658788 4 1658802 5 1658774

8. Incomplete recovery at the beginning

Sys@PROD > recover database using backup controlfile until cancel ORA-00279: change 1658739 generated at 06 generated at 2014 06:01:29 needed for thread 1 ORA-00289: suggestion: / arch/1_134_842976958.dbf ORA-00280: change 1658739 for thread 1 is in sequence # 134 Specify log: {= suggested | filename | AUTO | CANCEL} ORA-00279: change 1658838 generated at 06 ORA-00289 2014 06:02:46 needed for thread 1 ORA-00289: suggestion: / arch/1_135_842976958.dbf ORA-00280: change 1658838 for thread 1 is in sequence # 135ORA-00278: log file'/ arch/1_134_842976958.dbf' no longer needed for this recovery Specify log: {= suggested | filename | AUTO | CANCEL} ORA-00279: change 1658841 generated at 06 needed for thread 08 ORA-00289: suggestion: / arch/1_136_842976958.dbf ORA-00280: change 1658841 for thread 1 is in sequence # 136 ORA-00278: log file'/ arch/1_135_842976958.dbf' no longer needed for this recovery Specify log : {= suggested | filename | AUTO | CANCEL} ORA-00279: change 1658844 generated at 06 change 08 needed for thread 2014 06:02:50 needed for thread 1 ORA-00289: suggestion: / arch/1_137_842976958.dbf ORA-00280: change 1658844 for thread 1 is in sequence # 137 ORA-00278: log file'/ arch/1_136_842976958.dbf' no longer needed for this recovery Specify log: {= suggested | filename | AUTO | CANCEL} ORA-00279: change 1658856 generated at 06Charpy 2014 06:03 : 17 needed for thread 1 ORA-00289: suggestion: / arch/1_138_842976958.dbf ORA-00280: change 1658856 for thread 1 is in sequence # 138ORA-00278: log file'/ arch/1_137_842976958.dbf' no longer needed for this recovery Specify log: {= suggested | filename | AUTO | CANCEL} ORA-00279: change 1658859 generated at 06 Universe 08 needed for thread 2014 06:03:21 needed for thread 1 ORA-00289: suggestion: / arch/1_139_842976958.dbf ORA-00280: change 1658859 for thread 1 is in sequence # 139 ORA-00278: log file'/ arch/1_138_842976958.dbf' no longer needed for this recovery Specify log: {= suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled.

9. Check again whether the checkpoint of the data file header is consistent with that of the control file

Sys@PROD > select file#,checkpoint_change# from vault datafile; FILE# CHECKPOINT_CHANGE#-- 1 1658859 2 1658859 3 1658859 4 1658859 5 1658859 sys@PROD > select file#,checkpoint_change# from v$datafile_header FILE# CHECKPOINT_CHANGE#-1 1658859 2 1658859 3 1658859 4 1658859 5 1658859

10. Open the database with resetlogs

Sys@PROD > alter database open resetlogs; Database altered.

# recovery completed

Gyj@PROD > select * from gyj_test1

ID NAME

1 guoyJ

2 BBBBB

3 CCCCC

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.