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

Physical hot backup: backup when the database is open, but archiving must be enabled, because only when archiving is enabled, logs can be used to restore the database to the latest; physical hot backup can back up a single data file, tablespace and the entire database; physical hot backup can be divided into ordinary tablespace (non-critical data files) backup recovery and system tablespace (critical data files) backup and recovery.

System tablespace: refers to system, sysaux, undo tablespace

1: hot backup and simulated fault recovery of ordinary tablespaces (method 1: restore shutdown)

1. (premise) Archiving must be enabled for physical hot backup. Check:

SYS@ORA11GR2 > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 13

Next log sequence to archive 15

Current log sequence 15

SYS@ORA11GR2 >

-- make sure that the quick recovery area is set up:

SYS@ORA11GR2 > show parameter recover

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/FRA/

Db_recovery_file_dest_size big integer 3G

Db_unrecoverable_scn_tracking boolean TRUE

Recovery_parallelism integer 0

SYS@ORA11GR2 >

two。 View the absolute path of backup files (important)

SYS@ORA11GR2 > select name from v$controlfile

NAME

/ u01/app/oracle/oradata/ORA11GR2/control01.ctl

/ u01/app/oracle/oradata/ORA11GR2/control02.ctl

/ u01/app/FRA/control03.ctl

SYS@ORA11GR2 > select name from v$datafile

NAME

/ u01/app/oracle/oradata/ORA11GR2/system01.dbf

/ u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

/ u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

/ u01/app/oracle/oradata/ORA11GR2/users01.dbf

/ u01/app/oracle/oradata/ORA11GR2/example01.dbf

/ u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

/ u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

SYS@ORA11GR2 > select member from v$logfile

MEMBER

/ u01/app/oracle/oradata/ORA11GR2/redo03.log

/ u01/app/oracle/oradata/ORA11GR2/redo02.log

/ u01/app/oracle/oradata/ORA11GR2/redo01.log

/ u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/ u01/app/FRA/redo01_b.log

/ u01/app/FRA/redo02_b.log

/ u01/app/FRA/redo03_b.log

9 rows selected.

3. Simulated backup tablespace TS_ORA11GR2

-- check the tablespace and corresponding files of the database first

SYS@ORA11GR2 > select tablespace_name,file_name from dba_data_files

TABLESPACE FILE_NAME

USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

-- start tablespace TS_ORA11GR2 backup: (no library closed, that is, hot backup)

SYS@ORA11GR2 > alter tablespace TS_ORA11GR2 begin backup

Tablespace altered.

4. Back to the operating system layer copy tablespace TS_ORA11GR2

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $cp ts_ora11gr2_01.dbf / home/oracle

[oracle@wang ORA11GR2] $

[oracle@wang ~] $ls

Ts_ora11gr2_01.dbf

[oracle@wang ~] $

5. End tablespace TS_ORA11GR2 backup

SYS@ORA11GR2 > alter tablespace TS_ORA11GR2 end backup

Tablespace altered.

Verify that you are in a backup:

SYS@ORA11GR2 > select * from v$backup

FILE# STATUS CHANGE# TIME

--

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 1543727 27-SEP-16

7 NOT ACTIVE 0

(indicates that the backup has been completed, finished)

7 rows selected.

SYS@ORA11GR2 > select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1

FILE_ID TABLESPACE FILE_NAME

-

1 SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

2 SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

3 UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

4 USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

5 EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

6 TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

7 UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

6. Delete the data file under the TS_ORA11GR2 table space by operation:

[oracle@wang ORA11GR2] $rm ts_ora11gr2_01.dbf

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

7. Consistency shuts down the database:

SYS@ORA11GR2 > shutdow immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2 >

8. Open the database (ts_ora11gr2_01.dbf not found)

SYS@ORA11GR2 > startup

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2256832 bytes

Variable Size 457179200 bytes

Database Buffers 268435456 bytes

Redo Buffers 2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6-see DBWR trace file

ORA-01110: data file 6:'/ u01 *

9. Perform a restore operation:

-- cp the backed-up ts_ora11gr2_01.dbf file to / u01/app/oracle/oradata/ORA11GR2/:

[oracle@wang ~] $pwd

/ home/oracle

[oracle@wang ~] $

[oracle@wang ~] $ls

Ts_ora11gr2_01.dbf

[oracle@wang ~] $

[oracle@wang ~] $cp ts_ora11gr2_01.dbf / u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ~] $

[oracle@wang ORA11GR2] $ls ts_ora11gr2_01.dbf

Ts_ora11gr2_01.dbf

-- restore tablespaces:

SYS@ORA11GR2 > select status from v$instance

STATUS

-

MOUNTED

SYS@ORA11GR2 > recover tablespace TS_ORA11GR2

Media recovery complete.

(the action of recover is to apply logs to restore the instance to the most recent point in time.)

-- start the database with OPEN status:

SYS@ORA11GR2 > alter database open

Database altered.

Done!

The backend log records of similar operations are as follows:

Alert.txt

Second: hot backup and simulated fault recovery of ordinary tablespaces (method 2: online recovery of offline-recover-online)

1. Check whether the hot backup is in archive mode:

SYS@ORA11GR2 > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 22

Next log sequence to archive 24

Current log sequence 24

SYS@ORA11GR2 >

two。 View the absolute path to the backup file:

SYS@ORA11GR2 > select member from v$logfile

MEMBER

-

/ u01/app/oracle/oradata/ORA11GR2/redo03.log

/ u01/app/oracle/oradata/ORA11GR2/redo02.log

/ u01/app/oracle/oradata/ORA11GR2/redo01.log

/ u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo03_a.log

6 rows selected.

SYS@ORA11GR2 > select name from v$controlfile

NAME

-

/ u01/app/oracle/oradata/ORA11GR2/control01.ctl

/ u01/app/oracle/oradata/ORA11GR2/control02.ctl

SYS@ORA11GR2 > select tablespace_name,file_name from dba_data_files

TABLESPACE_NAME FILE_NAME

USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

SYS@ORA11GR2 > select file_id, tablespace_name,file_name from dba_data_files order by 1

FILE_ID TABLESPACE_NAME FILE_NAME

--

1SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

2SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

3UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

4USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

5EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

6TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

7UNDOTBS2 u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

SYS@ORA11GR2

3. Back up the data file users01.dbf:

-- keep the database in a hot standby state: (no library is closed)

SYS@ORA11GR2 > alter tablespace users begin backup

Tablespace altered.

SYS@ORA11GR2 >

-- go back to the copy file in the operating system layer and go to the backup directory:

[oracle@wang ORA11GR2] $ls users01.dbf

Users01.dbf

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $cp users01.dbf / home/oracle/

[oracle@wang ORA11GR2] $

[oracle@wang ~] $ls

Users01.dbf

[oracle@wang ~] $pwd

/ home/oracle

[oracle@wang ~] $

View backup status:

SYS@ORA11GR2 > select * from v$backup

FILE# STATUS CHANGE# TIME

--

1 NOT ACTIVE 1548675 2016-09-27 16:53:00

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 ACTIVE 1585418 2016-09-27 21:57:23

5 NOT ACTIVE 0

6 NOT ACTIVE 1543727 2016-09-27 16:11:12

7 NOT ACTIVE 0

(indicates that the backup action is not over yet)

7 rows selected.

SYS@ORA11GR2 > select file_id,tablespace_name,file_name from dba_data_files order by 1

FILE_ID TABLESPACE_NAME FILE_NAME

--

1SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

2SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

3UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

4USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

5EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

6TS_ORA11GR2 / 01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

7UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

End the backup:

SYS@ORA11GR2 > alter tablespace users end backup

Tablespace altered.

View backup status:

SYS@ORA11GR2 > select * from v$backup

FILE# STATUS CHANGE# TIME

--

1 NOT ACTIVE 1548675 2016-09-27 16:53:00

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 1585418 2016-09-27 21:57:23

5 NOT ACTIVE 0

6 NOT ACTIVE 1543727 2016-09-27 16:11:12

7 NOT ACTIVE 0

7 rows selected.

4. Delete the user01.dbf file in the original directory:

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $rm users01.dbf

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $ls user01.dbf

Ls: user01.dbf: No such file or directory

[oracle@wang ORA11GR2] $

5. Restore the data file:

-- offline the data file:

SYS@ORA11GR2 > alter databasedatafile'/ u01 offline App offline ORA11GR2

Database altered.

-copy the backup files in the backup directory back to the original directory:

[oracle@wang ~] $pwd

/ home/oracle

[oracle@wang ~] $cp users01.dbf / u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $ls users01.dbf

Users01.dbf

[oracle@wang ORA11GR2] $

-- Media recovery using the recover command

SYS@ORA11GR2 > recover datafile4

Media recovery complete.

SYS@ORA11GR2 >

-- modify the tablespace to the online state (or you can put the data file in the online state)

SYS@ORA11GR2 > alter tablespace users online

Tablespace altered.

Recovery is successful!

Alert.txt

Three: hot standby and simulated fault recovery of system tablespace

Restore database can only choose to turn off restore, because the system tablespace can only online

1. (premise) Archiving must be enabled for physical hot backup. Check:

SYS@ORA11GR2 > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 13

Next log sequence to archive 15

Current log sequence 15

SYS@ORA11GR2 >

two。 View the absolute path of backup files (important)

SYS@ORA11GR2 > select name from v$controlfile

NAME

/ u01/app/oracle/oradata/ORA11GR2/control01.ctl

/ u01/app/oracle/oradata/ORA11GR2/control02.ctl

/ u01/app/FRA/control03.ctl

SYS@ORA11GR2 > select name from v$datafile

NAME

/ u01/app/oracle/oradata/ORA11GR2/system01.dbf

/ u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

/ u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

/ u01/app/oracle/oradata/ORA11GR2/users01.dbf

/ u01/app/oracle/oradata/ORA11GR2/example01.dbf

/ u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

/ u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

SYS@ORA11GR2 > select member from v$logfile

MEMBER

/ u01/app/oracle/oradata/ORA11GR2/redo03.log

/ u01/app/oracle/oradata/ORA11GR2/redo02.log

/ u01/app/oracle/oradata/ORA11GR2/redo01.log

/ u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/ u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/ u01/app/FRA/redo01_b.log

/ u01/app/FRA/redo02_b.log

/ u01/app/FRA/redo03_b.log

9 rows selected.

3. View the tablespaces and corresponding files of the database

SYS@ORA11GR2 > select tablespace_name,file_name from dba_data_files

TABLESPACE FILE_NAME

USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

4. Back up the system tablespace:

SYS@ORA11GR2 > alter tablespace SYSTEM begin backup

Tablespace altered.

SYS@ORA11GR2 >

-- View backup status:

SYS@ORA11GR2 > select * from v$backup

FILE# STATUS CHANGE# TIME

--

1 ACTIVE 1548675 27-SEP-16

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 1543727 27-SEP-16

7 NOT ACTIVE 0

7 rows selected.

SYS@ORA11GR2 > select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1

FILE_ID TABLESPACE FILE_NAME

-

1 SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

2 SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

3 UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

4 USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

5 EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

6 TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

7 UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

5. Go back to the operating system layer copy tablespace system:

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $cp system01.dbf / home/oracle/

[oracle@wang ORA11GR2] $cd

[oracle@wang ~] $pwd

/ home/oracle

[oracle@wang ~] $ls system01.dbf

System01.dbf

[oracle@wang ~] $

6. End the backup:

SYS@ORA11GR2 > alter tablespace system end backup

Tablespace altered.

-- View backup status:

SYS@ORA11GR2 > select * from v$backup

FILE# STATUS CHANGE# TIME

--

1 NOT ACTIVE 1548675 27-SEP-16

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 1543727 27-SEP-16

7 NOT ACTIVE 0

7 rows selected.

SYS@ORA11GR2 > select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1

FILE_ID TABLESPACE FILE_NAME

-

1 SYSTEM / u01/app/oracle/oradata/ORA11GR2/system01.dbf

2 SYSAUX / u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

3 UNDOTBS1 / u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

4 USERS / u01/app/oracle/oradata/ORA11GR2/users01.dbf

5 EXAMPLE / u01/app/oracle/oradata/ORA11GR2/example01.dbf

6 TS_ORA11GR2 / u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf

7 UNDOTBS2 / u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf

7 rows selected.

SYS@ORA11GR2 >

8. Operation to delete data files under system tablespace

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $ls system01.dbf

System01.dbf

[oracle@wang ORA11GR2] $rm system01.dbf

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $ls system01.dbf

Ls: system01.dbf: No such file or directory

[oracle@wang ORA11GR2] $

-- close the library: (simulate failure)

SYS@ORA11GR2 > shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

9. Start the database: (show failure)

SYS@ORA11GR2 > startup

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2256832 bytes

Variable Size 457179200 bytes

Database Buffers 268435456 bytes

Redo Buffers 2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ u01 *

9. Restore tablespaces on offline

SQL > select status from v$instance

STATUS

-

MOUNTED

SYS@ORA11GR2 > alter tablespace system offline

Alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

(system tablespaces such as system cannot be restored under offline, but must be restored after closing the database.)

SYS@ORA11GR2 > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2 >

-- copy the backed-up system01.dbf to / u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ~] $pwd

/ home/oracle

[oracle@wang ~] $ls

System01.dbf ts_ora11gr2_01.dbf

[oracle@wang ~] $

[oracle@wang ~] $cp system01.dbf / u01/app/oracle/oradata/ORA11GR2/

Verify:

[oracle@wang ORA11GR2] $pwd

/ u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2] $

[oracle@wang ORA11GR2] $ls system01.dbf

System01.dbf

[oracle@wang ORA11GR2] $

-- start the database again

SYS@ORA11GR2 > startup

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2256832 bytes

Variable Size 457179200 bytes

Database Buffers 268435456 bytes

Redo Buffers 2842624 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/ u01 *

-- restore tablespace system; (hot backup enables archiving, so you can recover)

SYS@ORA11GR2 > recover tablespace system

Media recovery complete.

SYS@ORA11GR2 > select status from v$instance

STATUS

-

MOUNTED

SYS@ORA11GR2 > alter database open

Database altered.

SYS@ORA11GR2 > select status from v$instance

STATUS

-

OPEN

Done!

Alert.txt

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.