Simulation of rm-rf *. dbf recovery in cold backup

Simulation of rm-rf *. dbf recovery in cold backup

Backup recovery has always been a major concern for all relational databases. The following describes the Cold Standby database, simulates and destroys data files to restore the database, and involves other related content.
[Oracle @ localhost ~] $ Cat/etc/RedHat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

SQL> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
First introduce the cold backup (full offline backup), you need to shut down the database, paste and copy it, back up first:
SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.

[Oracle @ localhost orcl3939] $ cp *. dbf/home/oracle/beifeng
[Oracle @ localhost orcl3939] $ cp *. log/home/oracle/beifeng
[Oracle @ localhost orcl3939] $ cp *. ctl/home/oracle/beifeng
You only need to back up one control file, because it is an image file.
Is it easy!
This method is suitable for archivelog and noarchivelog, which involves several types of files:
Logfile: v $ log v $ logfile
Controlfile: v $ controlfile
Datafile: dba_data_files
Temp files: dba_temp_files
The temporary file is not the backup object, because the backup file can be understood as the virtual memory of the database. For example, in linux, if the memory is small, swap partitions can be allocated, the function is to exchange cache data, which is only a choice for insufficient memory.
About archivelog and noarchivelog, almost all of them are archivelog in the production environment:
SQL> archive log list;
Database Log mode non-archive Mode
Disable automatic archiving
Archiving end point USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 424
Current Log sequence 426

In this case, my exercise library is not in archive mode, so we start the archive mode:
SQL> startup mount;
The ORACLE routine has been started.
SQL> alter database archivelog;
The database has been changed.

SQL> select log_mode from v $ database;

LOG_MODE
------------
ARCHIVELOG

At this time, the database is already in archive mode.
For the storage location of the archive file, see the parameters:

SQL> show parameter db_recover
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string/u01/app/oracle/flash_recovery
_ Area
Db_recovery_file_dest_size large integer 3852 M

The first parameter is the storage location, and the second parameter is the size of the space.
The flash_recovery _ area directory is 10 Gb, which is convenient for managing files such as archives.
We can modify db_recovery_file_dest:
SQL> alter system set db_recovery_file_dest = '';
The system has been changed.
SQL> show parameter db_recover
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string
Db_recovery_file_dest_size large integer 3852 M

At this time, the directory for storing the archive file is back to 10 GB:
SQL> show parameter log_archive_dest
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string
Log_archive_dest_11 string
Log_archive_dest_12 string
Log_archive_dest_13 string
Log_archive_dest_14 string
Log_archive_dest_15 string
Log_archive_dest_16 string
Log_archive_dest_17 string
Log_archive_dest_18 string
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest_19 string
Log_archive_dest_2 string
Log_archive_dest_20 string
Log_archive_dest_21 string
Log_archive_dest_22 string
Log_archive_dest_23 string
Log_archive_dest_24 string
Log_archive_dest_25 string
Log_archive_dest_26 string
Log_archive_dest_27 string
Log_archive_dest_28 string
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest_29 string
Log_archive_dest_3 string
Log_archive_dest_30 string
Log_archive_dest_31 string
Log_archive_dest_4 string
Log_archive_dest_5 string
Log_archive_dest_6 string
Log_archive_dest_7 string
Log_archive_dest_8 string
Log_archive_dest_9 string

You can use the specified directory location, but this is not conducive to management.

In brief, SCN: System change number | System Commit Number is more accurate than the first one.

SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
0
SQL> alter database open;
The database has been changed.
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232872
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232878
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232879
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232905
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232915
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
7232917
SCN is a clock mechanism of oracle, which increases with time. Each database has a global SCN and maintains Database Consistency through SCN oracle. SCN everywhere, resetlogs scn, checkpoint scn .......
Unless the database is re-built, it will never be 0, and 0 appears above, because the database has not been opened yet.
At this time, we are in the sysY User:
SQL> show user;
USER is "SYS"

SQL> create table tt (id number, scn number );
The table has been created.

SQL> insert into tt values (1, dbms_flashback.get_system_change_number );
One row has been created.
SQL> insert into tt values (2, dbms_flashback.get_system_change_number );
One row has been created.
SQL> commit;
Submitted.
SQL> select * from tt;
ID SCN
--------------------
1 7235265
2 7235294

By searching for tt, We can roughly estimate the time when we insert data.
View v $ log:
SQL> select group #, status, archived, sequence #, first_change #, next_change # from v $ log;
GROUP # status arc sequence # FIRST_CHANGE # NEXT_CHANGE #
----------------------------------------------------------------
1 INACTIVE: YES 424 7179754 7189656
2 INACTIVE: YES 425 7189656 7227701
3 current no 426 7227701 2.8147E + 14

If you know the SCN of the inserted data and the FIRST_CHANGE # Of the current log, the log of the preceding data is stored in the current log file, that is, the serial number is 426. The above FIRST_CHANGE # indicates the scn when this group of logs is used,
Next_change # indicates the scn when the group logs are switched, that is, the FIRST_CHANGE # Of the next group logs is used #.
Analyze the status:
Current: indicates the logs currently in use. There is no doubt.
Active: the log file has been archived and written to the disk. Modifications to the data block corresponding to this part of the log have not been written to the disk. In the memory, therefore, this log file may be used after the database crash is restored.
Inactive: archiving is completed, and the log files and the modified database have been written to the disk.
How can we identify the logs generated by switching these three sets of logs cyclically,
This is the important role of the serial number:
SQL> alter system switch logfile;
The system has been changed.
SQL> select name, thread #, sequence #, first_change #, next_change # from v $ archived_log WHERE sequence #= 426;
Name thread # SEQUENCE # FIRST_CHANGE # NEXT_CHANGE #
Please wait until then ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_1__bmvr5f9j _. arc 1 426 7227701 7236412
Let's look at the name, sequence # above. You know the role of the sequence used to identify the archive file.
THREAD #: Because the database is under a single instance, a database corresponds to an instance:
So thread # can be understood as the instance number.
In RAC, the THREAD # Corresponds to the node number.
We now simulate:
SQL> insert into tt values (3, dbms_flashback.get_system_change_number
2 );
One row has been created.
SQL> commit;
Submitted.
SQL> alter system switch logfile;
The system has been changed.
SQL> insert into tt values (4, dbms_flashback.get_system_change_number
2 );
One row has been created.
SQL> COMMIT;
Submitted.
SQL> alter system switch logfile;
The system has been changed.
SQL> insert into tt values (5, dbms_flashback.get_system_change_number );
One row has been created.
SQL> COMMIT;
Submitted.
SQL> alter system switch logfile;
The system has been changed.

SQL> select * from v $ log;
GROUP # THREAD # SEQUENCE # bytes blocksize members arc status FIRST_CHANGE # FIRST_TIME NEXT_CHANGE # NEXT_TIME
------------------------------------------------------------------------------------------------------------------------------------
1 1 433 52428800 512 1 no current 7239074 month-15 2.8147E + 14
2 1 431 52428800 512 1 yes active 7239029 month-15 7239057 month-15
3 1 432 52428800 512 1 yes active 7239057 month-15 7239074 month-15

Delete the data file:
[Oracle @ localhost ~] $ Cd/u01/app/oracle/oradata/orcl3939
[Oracle @ localhost orcl3939] $ rm-rf *. dbf

Then, move the previously backed up data file to/u01/app/oracle/oradata/orcl3939. If all the data files are moved, the database can be opened and the operation data on table tt is completely lost.
[Oracle @ localhost beifeng] $ cp *. dbf/u01/app/oracle/oradata/orcl3939

SQL> startup mount;
The ORACLE routine has been started.
Total System Global Area 351522816 bytes
Fixed Size 1336484 bytes
Variable Size 297798492 bytes
Database Buffers 46137344 bytes
Redo Buffers 6250496 bytes
The database has been loaded.

In this case, the mount State is fully enabled.

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-01113: File 1 requires media recovery
ORA-01110: Data File 1: '/u01/app/oracle/oradata/orcl3939/system01.dbf'

In this case, we restore the database:
SQL> recover database;
ORA-00279: Change 7233493 (generated at 13:51:53) ORA-00289 required for thread 1:
Suggestion:
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_1__ B
Mvr5f9j _. arc
ORA-00280: Change 7233493 (for thread 1) in sequence #426
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}

Archive log:/u01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_1__ B
Mvr5f9j _. arc
The serial number is 426,
Scn: 7233493
Specify log analysis:
Suggest: indicates that the location of the archived log file is the recommended location.
Filename: indicates the location of the archived log. If you modify the location, you need to specify the location
Auto: indicates that the database is automatically restored.
Cancle: stop after this step is restored.

Next, press enter to select suggest:

ORA-00279: Change 7236412 (generated at 15:09:33) ORA-00289 required for thread 1:
Suggestion:
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_427_ B
Mvrj552 _. arc
ORA-00280: Change 7236412 (for thread 1) in sequence #427
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}

 

ORA-00279: Change 7236929 (generated at 15:15:17) ORA-00289 required for thread 1:
Suggestion:
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_428_ B
Mvrooz2 _. arc
ORA-00280: Change 7236929 (for thread 1) in sequence #428
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}

 

ORA-00279: Change 7237015 (generated at 15:18:13) ORA-00289 required for thread 1:
Suggestion:
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_429_ B
Mvs29ny _. arc
ORA-00280: Change 7237015 (for thread 1) in sequence #429
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}

 

 

If this is slow, I will show you the archive log files used in each step. Then we will use auto:
AUTO
ORA-00279: Change 7237261 (generated at 15:24:57) ORA-00289 required for thread 1:
Suggestion:
/U01/app/oracle/flash_recovery_area/ORCL3939/archivelog/2015_04_27/o1_mf_1_1__ B
Mvvf00h _. arc
ORA-00280: Change 7237261 (for thread 1) in sequence #430
Applied logs.
Media recovery is completed.

In this case, we can find the scn of the data file (all from the control file and retained after shutdown) and the scn of the Data header (the scn of the Data header is old and from the copied data file header)
To open the database, the two scn are equal:
SQL> select file #, checkpoint_change # from v $ datafile;

FILE # CHECKPOINT_CHANGE #
----------------------------
1 7259884
2 7259884
3 7259884
4 7259884
5 7259884
6 7259884
7 7259884
8 7259884
9 7259884
10 6980281
11 7259884
FILE # CHECKPOINT_CHANGE #
----------------------------
12 7259884
12 rows have been selected.

SQL> select file #, checkpoint_change # from v $ datafile_header;

FILE # CHECKPOINT_CHANGE #
----------------------------
1 7259884
2 7259884
3 7259884
4 7259884
5 7259884
6 7259884
7 7259884
8 7259884
9 7259884
10 6980281
11 7259884

FILE # CHECKPOINT_CHANGE #
----------------------------
12 7259884

12 rows have been selected.

Now that the two are equal, you can open the database.

SQL> alter database open;

The database has been changed.

SQL> select * from tt;

ID SCN
--------------------
1 7235265
2 7235294
3 7239015
4 7239050
5 7239064

All data has been recovered.