Oracle flash back-a suitable time for flash back

Use a simple SQL statement to restore a table or database to a previous time point.

 

On the eve of the New Year's Eve, John, the DBA of the vertex bank, is enjoying a carnival with his friends, countdown to the arrival of the new year. Just as the midnight clock rang, Everyone cheered, and his cell phone suddenly rang. In the bank data center, the year-end accounting program is busy calculating interest, but some problems have occurred, and all interest is calculated incorrectly. Fortunately, the development team found the problem and developed an emergency solution, but this solution cannot be undone. The computing center manager asked John if he could restore the database to the state before interest calculation in some way. The time was around PM.

 

Does this DBA sound familiar to you? What Should John do?

 

Before Oracle 10 Gb, what John could do was to create a time point for restoration and restore the database to an expected time point. Unfortunately, the bank's daily backup program was not yet running at that time, so he needed around 24 hours of archiving logs to restore the database.

 

In Oracle 9i, you can use the flashback query feature to restore the records in the table to PM, and then create a group of tables manually. Although this method is possible, it is unrealistic if there are too many tables.

 

Flashabck table

 

Let's take a look at the above situation. The problematic interest calculation program may only affect a few tables. For example, it only updates the balance in the accounts table. If so, John can use the flashback table feature to restore the table to a previous point in time.

 

To perform the flashback table operation, no special settings are required. The only requirement is to enable the row movement feature of the table, which can be used when the table is created orAlter table accounts enable row Movement. The flashback table command reads the previous images of the table from the rollback segment and reconstructs the records in the table using the flash query technology introduced in Oracle 9i.

 

If a non-DBA or non-schema owner needs to perform the flashback table operation, she needs the select, delete, insert, alter, andFlashback permission, or an equivalent any table system permission.

For John, the accounts table is as follows:

Account_no number (12), <br/> balance number (15, 2) <br/> Status char (1) </P> <p>The value of the Status column is usually "a" (active), but when the interest calculation program starts, all accounts are frozen, the Status field is updated to "F" (frozen ). When the interest calculation for this account is completed, the status mark is updated to "I" (interest applied ).

 

Here are the steps John follows to use the flashback table feature:

1. He asked about the point in time to restore the database. The answer was around.
2. It defines the logical state to be restored. The following is the result obtained when you query the table.Select status, count (*) <br/> from accounts <br/> group by status; </P> <p> Status count (*) <br/> ------ -------- <br/> I 27088 <br/> F 19999 </P> <p>
3. The result shows that 27088 accounts have been processed (status = I) so far ). The logical state to be restored is that all accounts are in the "f" status, and before that, the state is "". He must restore all accounts to "F" at p.m.
4. He checked an existing account in the "I" status and used it as a benchmark for future testing.Select account_no, balance <br/> from accounts <br/> where status = 'I' <br/> and rownum <2; <br/> account_no balance <br/> ----------- -------- <br/> 21633 3913.49 </P> <p>
5. He executed the following command to restore the accounts table to the status at p.m:Flashback table accounts to timestamp <br/> to_timestamp ('2016/12/31 23:00:00 ', 'Mm/DD/YYYY hh24: MI: ss'); </P> <p>Wow! The entire table is restored to the specified timestamp. John can restore it to any distant time point allowed by the rollback segment. In addition to the timestamp, you can also use the system modification number (SCN) as follows:Flashback table accounts <br/> to SCN 9988653338; </P> <p>The table is restored to p.m., but is it the expected point? John checked the account status again:Select status, count (*) <br/> from accounts <br/> group by status; </P> <p> Status count (*) <br/> ------ -------- <br/> I 88 <br/> F 46999 </P> <p>
6. There are still 88 records in the "I" status, so P.M is not early enough; John also needs to restore the table to an earlier time. He flashed the table back to p.m. and checked the account status again:Flashback table accounts to timestamp <br/> to_timestamp ('2016/12/31 22:30:00 ', 'Mm/DD/YYYY hh24: MI: ss '); </P> <p> select status, count (*) <br/> from accounts <br/> group by status; </P> <p> Status count (*) <br/> ------ -------- <br/> A 47087 </P> <p>
7. John flashed back too far. The status of all accounts was "a" and before "F. Therefore, select a closer time of p.m.Flashback table accounts to timestamp <br/> to_timestamp ('2016/12/31 22:45:00 ', 'Mm/DD/YYYY hh24: MI: ss '); </P> <p> select status, count (*) <br/> from accounts <br/> group by status; </P> <p> Status count (*) <br/> ------ -------- <br/> F 47087 </P> <p>
8. The result is exactly what we want. He queries the information of the previous baseline account:Select balance <br/> from accounts <br/> where accountno = 21633; </P> <p> balance <br/> ------- <br/> 3836.75 </P> <p>
9. The balance for this account is $3,913.49, and the current status is p.m. After the operation is completed, the flashback command can flash back to a precise time point either forward or backward.

Because the table has never been dropped, all objects dependent on it, such as indexes, constraints, and triggers, are well-available. All independent objects that reference the table, such as stored procedures, are also valid.

If John wants to flash back to the txn table in addition to the accounts table, he can use commas to separate the tables and then flash back to multiple tables at a time:

Flashback table bank. accounts, bank. txn to SCN 1234567; </P> <p>

A powerful SQL statement is used to flash back the entire table.

 

Let's take a look at another situation, if Laura accidentally dropped a key lookup table gl_master. After an error was found, she asked John if he could restore the table. In Oracle10g, when you drop a table, you only rename the table and put it in the logical container recycle bin.

 

To restore the table, John runs the following command:

Flashback table gl_master to before drop; </P> <p>No recovery work is required, and this table will be reproduced immediately. Note: Unlike the previous flash back operation, this operation does not need to re-create data from the rollback segment, but only removes the table from the recycle bin.

 

Flashback Database

When a logical error occurs not only on several tables, you can use the flashback database command to restore the database to the previous time point faster. The flashback database command does not use a rollback segment, but uses a disk called a flash recovery zone to restore the database. Use the flashback database command to restore the database without using database backup.

 

To flash back the entire database, John needs to make the following preparations so that the database can be flashed back:

1. Configure a 2 GB flash recovery zone by setting the following parameters:Db_recovery_file_dest =/usr/users/Oracle/10.1/recovery_area <br/> db_recovery_file_dest_size = 2G </P> <p>
2. Use the following parameters to configure the maximum flash time in minutes:Db_flashback_retention_target = 1440This value indicates the maximum flashback time. the maximum possible time is determined by the available space in the flashback recovery zone. The preceding three parameters can be set using the alter system command.
3. To enable the database to flash back, the database must work in archivelog mode. In the Mount phase, John executed the following command before open:Alter database flashback on; <p>When flashback database is allowed, it regularly writes changes to the data block to a specific type of log, which is called a flashback log. These logs are not written by the traditional lgwr process, but by the new process called rvwr. Unlike conventional redo logs, flashback log files do not require DBA to create and maintain. They are automatically created in the specified flashback recovery area by Oracle managed files (OMF) and will not be archived.

 

Flashback operation

1. In the example of the vertex bank described above, John confirmed that the flashback table was not feasible and he needed to roll back the entire database to a certain point in time. He chose p.m. as the starting point and executed the following command:Flashback database to timestamp <br/> to_timestamp ('2017/24/60 <br/> 23:00:00 ', 'Mm/DD/YYYY hh24: MI: ss '); </P> <p>This operation executes all required work, such as restoring data files using the information in the Flash recovery area. The entire database is restored to the p.m. state, and John does not need to perform any manual recovery operations. In addition to timestamp, you can also use SCN to flash back. The flash back operation is recorded in the warning log.
2. Even though the database was recovered to p.m., the question is, is it correct? John used the same reference point technology as before. He opens the database in read-only mode and queries the account status:Alter database open read only; <p> select status, count (*) <br/> from bank. accounts <br/> group by status; </P> <p> Status count (*) <br/> ------ -------- <br/> I 255 <br/> F 46832 </P> <p>
3. The answer is no. There are still 255 records in the "I" status. He needs to flash back to an earlier time, such as p.m., as shown below:Shutdown immediate <br/> startup Mount </P> <p> flashback database to timestamp <br/> to_timestamp ('2016/12/31 22:30:00, <br/> 'Mm/DD/YYYY hh24: MI: ss'); </P> <p> alter database open read only; </P> <p>
4. Execute the same query on the accounts table:Select status, count (*) <br/> from bank. Accounts <br/> group by status; </P> <p>He found that all the accounts are in the "A" status, which means that he flashed back too far and moved the database back to a closer State, for example, p.m., as shown below:Shutdown immediate <br/> startup Mount </P> <p> flashback database to timestamp <br/> to_timestamp ('2016/12/31 22:45:00, <br/> 'Mm/DD/YYYY hh24: MI: ss'); </P> <p> alter database open read only; </P> <p>
5. Now query the status again:Select status, count (*) <br/> from bank. Accounts <br/> group by status; </P> <p>All accounts are in the "f" status, which is exactly what we need. Now John restores the database to the desired state. He can open the database and execute the required updates:Shutdown immediate <br/> startup Mount <br/> alter database open resetlogs; <br/>