5. manual backup and recovery-open the backup and recovery of the database (Exercise 5 and 6)

Sections 3rd and 4 describe how to restore and restore a database from a consistent backup, but the disadvantage is that the database must be closed during the operation. Because in the actual environment, users and application systems are always using databases, and the database cannot be closed, how can we back up and restore the opened database, this section and the next section will be discussed.
When data is opened, the data file and control file are updated, and the redo log is written and archived. In this case, the tablespace is placed in the backup mode during backup, and then the data file is backed up, after the backup is complete, restore the tablespace to the normal state. When the database fails, you can copy part or all of the files from the backup path to restore them. Note that each data file header has a sequence number, all data files must have the same SCN number before the database can be opened.

As mentioned above, you need to set the tablespace to the backup mode when enabling database backup. When the tablespace is in the backup mode, the processing operations are as follows:

• The header marker of the tablespace indicates that the file is about to be hot backed up;
• The tablespace data file executes a checkpoint. All "dirty" data blocks in the memory are written into the data file, and the checkpoint SCN is written into the data file header and control file, at this time, any changes in the file are frozen;
• Add a start backup record to the alarm Log File
• Before any block in the data file changes for the first time, the relevant data block will be copied to the redo log, and a standard redo vector will be generated for the block changes.

When the tablespace is in backup mode, you can use the copy command of the operating system to copy data files to other places. Since the redo log during Hot Standby must retain copies of each changed data block (only for the first change), the database should be backed up when there are few database activities.

After the database is copied, The tablespace is removed from the backup mode and the following actions are performed:

• The hot backup mark is cleared, indicating that the backup is complete;
• Terminate record backup SCN and use it as the redo vector;
• The checkpoint structure of the data file is unfrozen and matches with other parts of the database;
• Redo generation returns to the normal mode.

Exercise 5: Back up the opened database

In this exercise, use alter tablespace... The begin BACKUP command is used to back up the data files in the table space. After the backup is complete, use alter tablespace... The end backup or alter Database End BACKUP command disconnects the tablespace from the hot backup mode. To open a complete database for backup processing, you need to copy each online data file and back up the current control file.

Step 1: Generate database activities
Insert data of 40 years into the date_log of the TINA user. After the data is restored, check whether the data exists to check whether the data is successfully restored.

1 SQL> insert into Tina. date_log values (sysdate + 40*365 );
2 SQL> commit;
3 SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
4 SQL> select create_date from Tina. date_log;

Step 2: Create a hot backup script

The script is created as follows:

1. Switch log files to archive all changes before backup;
2. Set a tablespace to the hot standby mode;
3. Use operating system commands to copy files in the space;
4. Disconnects the tablespace from the hot backup mode;
5. 2-4 operations are repeated for each tablespace;
6. Switch the log file and archive the changes in the backup;
7. Back up the current control file

The following files are created:

• Backup script file (open_backup. SQL) using the text editor to create a hot backup script;
• Backup command file (open_backup_command. SQL) created and executed in open_back. SQL;
• The backup command file output file (open_backup_output.list) is used to record the execution results of open_backup_command. SQL.

1 Set feedbackoff pagesize 0 heading off verify off linesize 200 trimspool on
2 define dir = 'd: \ oracle \ code \ chap5'
3 define fil = 'd: \ oracle \ code \ TMP \ open_backup_commands. SQL'
4 define SPO = '& dir \ open_backup_output.lst'
5 prompt *** spooling to & fil
6 set serveroutput on
7 spool & fil
8 prompt spool & spo
9 prompt archive log list ;;
10 prompt alter system switch logfile ;;
11 declare
12 cursor cur_tablespace is
13 select tablespace_name from dba_tablespaces
14 where status <> 'read only ';
15 cursor cur_datafile (TN varchar) is
16 select file_name
17 from dba_data_files
18 where tablespace_name = tn;
19 begin
20 for CT in cur_tablespace Loop
21 dbms_output.put_line ('alter tablespace '| CT. tablespace_name | 'in in backup ;');
22 For Cd IN cur_datafile (Ct. tablespace_name) loop
23 dbms_output.put_line ('host copy' | cd. file_name | '& dir ');
24 end loop;
25 dbms_output.put_line ('alter tablespace '| CT. tablespace_name | 'end backup ;');
26 end loop;
27 end;
28/
29 prompt alter system switch logfile ;;
30 prompt alter Database Backup controlfile to '& dir \ backup. CTL' reuse ;;
31 prompt archive log list ;;
32 prompt spool off ;;
33 spool off;
34 @ & fil

• Set the SQL * Plus variable in line 1st to avoid displaying unnecessary content in the command file from the extracted results from the database;
• Line 2-4 sets the user variable for the command within the script range. The dir variable specifies the path where the backup file will be copied, and fil specifies the file name for generating the BACKUP command, the SPO variable defines the log of script execution. In the following script, you can add & before the variable name to reference the variable;
• Line 3 displays the output result using the SQL * Plus prompt command;
• Row 3 notifies SQL * Plus to write all the output results on the screen to a file named open_backup_commands. SQL defined by the variable fil;
• Line 3 writes a spool command to the spool file open_backup_commands. SQL. when running the open_backup_commands. SQL script, the execution log can be recorded in the open_backup_output.list file;
• Line 3 writes the command to the command file to display the currently archived information command. When restoring the database, you need to know the sequence number of the current log file;
• 10th switch the log file from the current log file to the next log file. This will check all data files and create a new archive log file;
• Line 11-27 respectively places the tablespace in the backup mode, copies all data files to the backup Destination path, and disconnects the tablespace from the backup mode. Two cursors are used here: the list of all tablespaces in the database and the list of data files in each tablespace. The specific process is as follows: to obtain the table space cursor value cyclically, first set the table space to the backup mode, then obtain all the data files in the table space and copy them to the target backup path, the tablespace is in contact with the backup mode;
• 28th switch the current log file to the next log file to ensure that actions created during Hot Backup can be archived;
• Row 3 should immediately create a backup of the control file after the backup of the data file is completed. The script uses an SQL statement to generate a copy of the file. The Reuse keyword indicates that the file will be overwritten if it exists;
• Line 30-32 instructs the open_backup_commands. SQL file to list the archive information to the spool file of the backup script, and then terminate the file offline;
• Execute the created hot backup script in line 3.

Step 3: run the backup script

Execute the script created in step 2 in SQL * Plus. When the script runs, the tablespace and data files are placed in backup mode, in this case, you can view the V $ backup view in another SQL * Plus session to check which files are in backup mode.

After the backup is complete, you can also place the user tablespace in the backup mode to view the backup status of online data files.

1 SQL> alter tablespace users begin backup;
2 SQL> select D. tablespace_name tablespace, B. File #, B. Status, B. Change #, B. Time
3 from dba_data_files D, V $ backup B
4 where B. File # = D. file_id
5 order by tablespace_name;

 

After the backup is enabled, view the alarm log and obtain the following backup mode record.

The warning shows the time when the backup operation is enabled for the system tablespace. Run the script Generation Command 11:17:52 to place the system tablespace in the backup mode, and the system tablespace is in the backup mode, during the waiting process, the database Performs related operations. It takes 38 seconds to copy the system data file. From 11:18:30, the command is submitted to remove the system tablespace from the backup mode, then the system tablespace is restored to the normal mode.
The commands in this exercise implement the recommended continuous backup method of Oracle, which can be alter tablespace... The time interval between the begin/end backup statements is minimized to avoid large redo information during the hot backup process. In addition, if the database crashes during the hot backup process, it is much faster and easier to end the backup of some data files in a tablespace than to end the backup of multiple spaces including the master database files.

Step 4: Check the BACKUP command

View the open_backup_commands. SQL File Created by the script. This file helps you understand the entire Hot Backup process.

 

Exercise 6: Back up archived log files

In exercise 5, we have performed hot backup on the database data files. After the actual backup, the database continuously creates archive log files. After a period of time, these files will fill the archiving path. This requires regular cleanup of archiving paths, which can be escaped to a tape backup device or a remote machine when disasters (floods, fires, earthquakes, etc.) occur) as a result, the machine is damaged. You can use offline data files, control files, and offline archive files to restore the database.

Step 1: Find an archive file

The archive file exists in the Parameter definition path of log_archive_dest in the configuration section of the database initialization file. You can view it through the parameter file, the show parameter command, or the V $ archive_dest view. For how to set this parameter, see Step 1 in Exercise 3.

Step 2: Create an archive script

You can use the move command of the operating system to change the archived files from the archive path to another place. In the archive_backup. SQL file, generate a Backup command file archive_back_commands. SQL. This command first finds all the archive log files created yesterday from the V $ archive_log view, and transfers these archive logs to a backup path using the move command.

1 Set feedbackoff pagesize 0 heading off verify off linesize 200 trimspool on
2 define dir = 'd: \ oracle \ code \ chap5 \ archive'
3 define fil = 'd: \ oracle \ code \ TMP \ archive_back_commands. SQL'
4 spool & fil
5 prompt archive log next ;;
6 select 'host copy' | Name | '& dir'
7 from V $ archived_log
8 where completion_time> = trunc (sysdate)-1
9 and completion_time <trunc (sysdate );
10 spool off;
11 @ & fil

• Set the variables required for SQL * Plus in line 2-3. The dir variable specifies the path where the backup file will be copied;
• Rows 1 and 2 start and end respectively to record the SQL output to a file;
• Row 3 notifies the database to archive any Unarchived online redo log files. This command is very important. Because the database is in the archive state and there are many redo activities, the archive log next command can ensure that before the SQL * Plus command prompt control is returned, all online redo logs to be archived are archived. Note that this command has two semicolons. This generates a semicolon in the generated command file;
• Line 6-9 obtains the archived log file from the V $ archived_log view. A move command is generated for the path using the file name. In this script, the files created yesterday will be transferred, and the trunc function will delete the time section of sysdate changed, this script transfers all archives created within 24 hours from 00:00:00 yesterday to 23:59:59;
• 11th lines of spool file content;

Step 3: run the archive script

After the archive_backup. SQL is created, Log On As sys and run the script from SQL * Plus. If this script is run multiple times, a message is displayed, indicating that the file is not found in the mobile command. This is normal because the archive file has been transferred.

Step 4: Confirm archiving log backup

Open the archive backup path and confirm that the file has been transferred to the target path to check whether all the expected archive log files have been transferred successfully.