3. Manual Backup recovery-Disable database backup and full restoration (Exercises 1 and 2)

The simplest way to protect data is to copy all the database files to another place. In case of a problem, you can overwrite these files to the original location for recovery and then start the database, such an operation is called full (or all) Consistent database backup and recovery. Here, we note that consistent Database Backup usually refers to cold backup (Closing the database). In this situation, data files, redo logs, and control files are marked with the same SCN number.

Sections 3rd and 4 describe three types of full recovery:
(1) The database is completely backup and restored without restoration;
(2) completely disable backup and recovery;
(3) Incomplete recovery.

 

Exercise 1: Back up closed Databases

This exercise backs up all practice data files, simulates a serious fault, deletes all database files, restores all database files from the backup, and opens the database.

Step 1: Generate database activity logs

Here we insert data to the date_log table of the TINA user. After the data is restored, check whether the data exists to check whether the restoration is successful. The script is as follows:

1 -- insert a data record of 10 years into date_log, and then check whether the data exists.
2 SQL> insert into Tina. date_log values (sysdate + 10*365 );
3 SQL> commit;
4 SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
5 SQL> select create_date from Tina. date_log;

 

Step 2: create a backup script

Using scripts to create and execute commands is a fast, simple, and correct method. Next we will create a database script named closed_copy. SQL, these scripts can be used to add error processing, verification, logs, and other parts.

1 remark set SQL * Plus varivalbes to manipulate output
2 set feedback off heading off verify off trimspool off
3 set pagesize 0 linesize 200
4 remark set SQL * Plus user variables used in this script
5 define dir = 'd: \ oracle \ code \ chap4'
6 define fil = 'd: \ oracle \ code \ TMP \ closed_backup_commands. SQL'
7 prompt *** spooling to & fil
8 remark create a command file with file backup commands
9 spool & fil
10 select 'host copy' | Name | '& dir' from V $ datafile order by 1;
11 select 'host copy' | member | '& dir' from V $ logfile order by 1;
12 select 'host copy' | Name | '& dir' from V $ controlfile order by 1;
13 select 'host copy' | Name | '& dir' from V $ tempfile order by 1;
14 spool off;
15 remark shutdown the database cleanly
16 shutdown immediate;
17 remark run the copy file commands form the Operating System
18 @ & fil
19 remark start the database again
20 startup;

• Set the SQL * Plus variable in line 2-3 to avoid displaying unnecessary content in the command file from the database;
• Line 5-6 sets user variables for commands 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, 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 9th notifies SQL * Plus to write all the output results on the screen to a file named closed_backup_commands. SQL defined by the variable fil.
• Line 10-13 provides multiple operating system commands to copy each data file in the Practice Database. Symbol | used to connect strings in SQL commands. Use the V $ dynamic view to copy data files, online redo log files, control files, and temporary files. Host is used to run the operating system command at the SQL * Plus prompt;
• Line 3 stops writing commands to the closed_backup_commands. SQL file;
• Row 3 closes the database normally;
• The first line starts to execute all the commands created earlier. Because the database file is large, it takes much time to execute them;
• Row 3 starts the database again.
  Note: To execute the preceding script, use the sysdba role sys or system to connect to the database.

Step 3: run the backup script

Create a closed_copy. SQL script by using step 2. Run the script using the start or @ command:

1 SQL> @ D: \ oracle \ code \ chap4 \ closed_copy. SQL

 

The command line window appears during running. Do not close it. After running, copy the practice database to the target path, as shown in:

 

 

Exercise 2: Restore the entire database

In this exercise, all the files in the Practice database will be restored in the previous exercise. Once the files are restored, the database can be opened without restoration.

Step 1: delete database files

To simulate the real environment, we can imagine that a newbie accidentally deleted all database files when the database was closed. Here we close the database and delete all database files in the Practice Database.

 

Step 2: Restore the backup database file

Copy the database file backed up by the previous exercise to the Practice database file location.

 

Step 3: Open the database

Here we start the database by loading the control file first. The specific script is as follows:

1 SQL> startup Mount

 

The following discussion is not necessary, but helps you understand the database restoration and recovery mechanisms. Open the database alarm log D: \ oracle \ product \ 10.1.0 \ admin \ practice \ bdump \ alert_practice.log (open as needed)

 

With the above information, we can know that when the database is shut down, we need to shut down processes such as SMON and archive before the database can be completely shut down. Run the mount command to start the database and check whether the related columns are consistent with the closing time:

1 SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
2 SQL> SELECT FILE #, status, checkpoint_change #, checkpoint_time,
3 last_change #, last_time from V $ datafile;

Through the query, we can find that the database shutdown time 15:10:32 is exactly the time when the data file checkpoint occurred: 31:

 

1 SQL> select group #, sequence #, status, first_change #, first_time from V $ log order by first_change #;

 

In the preceding file, the SCN of the data file checkpoint is 513045, and the first change number in the current redo log is 510573. Therefore, database backup occurs when the 3rd group of redo logs is in the current state.
After scanning the view of the loaded control file, you can use the alter database open command to open the database.

 

Step 4: Confirm database Restoration
The Oracle database cannot be opened unless the data files, control files, and online redo logs are consistent. You can query data from Tina. date_log to check whether there is a data record 10 years later than the current time.
-- Insert a data record of 10 years into date_log, and check whether the data exists after it is inserted.

1 SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
2 SQL> select create_date from Tina. date_log order by create_date DESC;