Oracle training notes: Archive mode Backup Recovery

1. Set the archiving path for databases in archive Mode

1.1 query archiving status
SQL> select log_mode from v $ database;

LOG_MODE
------------
NOARCHIVELOG

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 9
Current Log sequence 11


1.2 archive Mode
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> startup mount
The ORACLE routine has been started.

Total System Global Area 251658240 bytes
Fixed Size 1248356 bytes
Variable Size 79692700 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
The database has been loaded.
SQL> alter database archivelog;
SQL> alter database open;

Set non-archive: alter database noarchivelog;

1.3 set the archiving path
SQL> show parameter log_archive_dest _

NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest_1 string
Log_archive_dest_10 string
Log_archive_dest_2 string
Log_archive_dest_3 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

SQL> show parameter log_archive_min_succeed_dest

NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_min_succeed_dest integer 1

SQL> show parameter format

NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_format string ARC % S _ % R. % T

SQL> alter system set log_archive_format = 'arc % S _ % R _ % d. % t' scope = spfile;

The system has been changed.


Set archiving path
Alter system set log_archive_dest_1 = 'location = e: \ arc1 mandatory ';
Alter system set log_archive_dest_2 = 'location = e: \ arc2 mandatory ';
Alter system set log_archive_dest_3 = 'location = e: \ arc3 optional ';

Alter system switch logfile;

Use flashback recovery zone
Alter system set log_archive_dest_1 = '';
Alter system set log_archive_dest_2 = '';
Alter system set log_archive_dest_3 = '';
Alter system set log_archive_dest_10 = 'location = USE_DB_RECOVERY_FILE_DEST ';

1.4 back up a database
Cold backup: closes the database and backs up data files, control files, and parameter files.


2. Backup
Cold backup

Hot Backup
Alter tablespace xxx begin backup;
Physical copy
Alter tablespace xxx end backup;

Alter database begin backup;
Physical copy
Alter database end backup;

Alter tablespace users begin backup;

3. Full recovery
3.1 simulate data changes and switch logs
Insert into t01 values (40 );
Commit;
Alter system switch logfile;

Insert into t01 values (50 );
Commit;
Alter system switch logfile;

Insert into t01 values (60 );
Commit;
Alter system switch logfile;

Insert into t01 values (70 );
Commit;
Alter system switch logfile;


SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.

Delete the data file users01.dbf

SQL> startup
The ORACLE routine has been started.

Total System Global Area 251658240 bytes
Fixed Size 1248356 bytes
Variable Size 79692700 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 4-see DBWR trace file
ORA-01110: data file 4: 'e: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ MYDB \ users01.dbf'

Restore the data file user01.dbf

SQL> select * from v $ recover_file;

FILE # ONLINE _ error change # TIME
-----------------------------------------------------------------------------------------------------------------
4 ONLINE 213083 month-9 month-11

SQL> select * from v $ recovery_log;

THREAD # SEQUENCE # TIME
----------------------------------
ARCHIVE_NAME
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 15 months-9 months-11
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 201%09_03 \ o%mf_%15_763hhz1g_.arc

1 16 03-9-11
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 201%09_03 \ o%mf_%16_763hhzkl_.arc


SQL> recover datafile 4;
ORA-00279: Change 213083 (generated at 13:39:09) is required for thread 1
ORA-00289: Recommendations: E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ o1_mf_000015 _ % U_.ARC
ORA-00280: Change 213083 (for thread 1) in sequence #15


Specified log: {<RET> = suggested | filename | AUTO | CANCEL}

ORA-00308: Unable to open archive log 'e: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ o%mf_%15_763hhz1g_.arc'
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/S-Error: (OS 2) the system cannot find the specified file.


SQL>
SQL> recover datafile 4;
ORA-00279: Change 213083 (generated at 13:39:09) is required for thread 1
ORA-00289: Recommendations: E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ o1_mf_000015 _ % U_.ARC
ORA-00280: Change 213083 (for thread 1) in sequence #15


Specified log: {<RET> = suggested | filename | AUTO | CANCEL}
D: \ 201715_763hhz1g_.arc
ORA-00279: Change 213171 (generated at 13:41:18) is required for thread 1
ORA-00289: Recommendations: E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ o%mf_%16 _ % U_.ARC
ORA-00280: Change 213171 (for thread 1) in sequence #16
ORA-00278: This recovery no longer requires the log file 'd: \ 10915_763hhz1g_.arc'


Specified log: {<RET> = suggested | filename | AUTO | CANCEL}
D: \ o1_mf_201716_763hhzkl_.arc
Applied logs.
Media recovery is completed.
SQL> alter database open;

The database has been changed.

3.2 restore data files online
Alter tablespace users offline;
Restore data files
Restore data files
Alter tablespace users online;

The system and undo tablespaces must be restored in the mounted state.

3.3 flashback recovery area monitoring
Select * from v $ recovery_file_dest;
Select * from v $ flash_recovery_area_usage;

3.4 Control File Corruption recovery
Back up the database in time for structural changes. (Add or delete tablespace and data files)
SQL> alter database backup controlfile to 'd: \ control. ctl ';

1) Some control files are corrupted.
Copy the existing file and open the database.

2) All control files are corrupted.
Case 1: There is a backup
Close Database
Delete all control files
Restore Control Files
Start database to mounted status
Recover the database recover database using backup controlfile;
SQL> recover database using backup controlfile;
ORA-00279: change 205641 generated at 11:52:37 needed for thread 1
ORA-00289: suggestion:
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ O1_MF_1 _

15 _ % U_.ARC
ORA-00280: change 205641 for thread 1 is in sequence #15


Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
D: \ 201715_763hhz1g_.arc
ORA-00279: change 213171 generated at 13:41:18 needed for thread 1
ORA-00289: suggestion:
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ O1_MF_1 _

16 _ % U_.ARC
ORA-00280: change 213171 for thread 1 is in sequence #16
ORA-00278: log file 'd: \ 41015_763hhz1g_.arc 'no longer needed for this recovery


Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
D: \ o1_mf_201716_763hhzkl_.arc
ORA-00279: change 213175 generated at 13:41:19 needed for thread 1
ORA-00289: suggestion:
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ O1_MF_1 _

17 _ % U_.ARC
ORA-00280: change 213175 for thread 1 is in sequence #17
ORA-00278: log file 'd: \ o%mf_%16_763hhzkl_.arc 'no longer needed for this
Recovery


Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
E: \ oracle \ product \ 10.2.0 \ oradata \ mydb \ redo02.log
ORA-00279: change 213180 generated at 13:41:23 needed for thread 1
ORA-00289: suggestion:
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ O1_MF_1 _

18 _ % U_.ARC
ORA-00280: change 213180 for thread 1 is in sequence #18
ORA-00278: log file 'e: \ oracle \ product \ 10.2.0 \ oradata \ mydb \ redo02.log 'no
Longer needed for this recovery


Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
E: \ oracle \ product \ 10.2.0 \ oradata \ mydb \ redo03.log
ORA-00279: change 213184 generated at 13:41:24 needed for thread 1
ORA-00289: suggestion:
E: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ MYDB \ ARCHIVELOG \ 20151109_03 \ O1_MF_1 _

19 _ % U_.ARC
ORA-00280: change 213184 for thread 1 is in sequence #19
ORA-00278: log file 'e: \ oracle \ product \ 10.2.0 \ oradata \ mydb \ redo03.log 'no
Longer needed for this recovery


Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
E: \ oracle \ product \ 10.2.0 \ oradata \ mydb \ redo01.log
Log applied.
Media recovery complete.
Open the database alter database open resetlogs;

1 2 Next Page