Shulou(Shulou.com)06/01 Report--

Suppose the following happens to Oracle databases:

In a table space, important data of a table is destroyed or deleted.

Misuse of the DDL language alters the structure of one or more tables in a table space, so flashbacks cannot be used to recover those tables.

3. The table has been deleted by mistake and is no longer in the recycle bin, such as deleting the table with the purge option.

So how do we recover?

One possible approach is to utilize Oracle's Tablespace Point In Time Recovery (TSPITR), a technique that performs incomplete recovery on only a portion of the database. This technique creates a secondary database from a subset of the target database's tablespaces, performs an incomplete restore on only this subset, and then replaces the tablespaces in the target database with the secondary database's tablespaces. The end result looks as if only that subset is recovered and restored, while the rest of the target database is kept up-to-date.

Manual point-in-time recovery of tablespaces is cumbersome, but with RMAN you can automatically perform TSPITR, which facilitates restoring the contents of one or more tablespaces to a certain point in time without affecting other tablespaces or objects in the database. TSPITR is a useful recovery tool, but the following prerequisites need to be clear before using it:

The database must be in archive mode and there must be a corresponding backup set.

The table space to be restored must be self-contained and independent of objects in other table spaces. For example, if a table contains indexes in other tablespaces, they either participate in the recovery together, or the dependencies are removed before recovery can occur.

Of course, TSPITR is not a panacea for all table space disasters. For example, it cannot be used to restore a table space that has been deleted. Also, if you rename a table space, you cannot revert it to a point in time prior to the renaming.

Implementing TSPITR using RMAN is simple, and several steps are required to ensure success. Here is an example of how TSPITR technology can be used.

1. Suppose a table in the table space CMES has been deleted by mistake and cannot be retrieved from the recycle bin.

sqlplus cmes/cmes@mes

drop table c_material_t purge;

2. Determine the approximate time point when the damage occurred

Here, first set the environment variable used to control the date and time format under the operating system prompt, and use this session to complete the following work to ensure the correct resolution of the date and time:

set nls_date_format=yyyy-mm-dd hh34:mi:ss

View current time:

select sysdate from dual;

SYSDATE

-------------------

2016-02-15 21:30:48

3. Verify dependencies of tablespaces

Check the data dictionary view TS_PITR_CHECK to determine if a table space has dependencies on other tables:

select obj1_owner, obj1_name, ts1_name, obj2_owner, obj2_name, ts2_name

from ts_pitr_check

where (ts1_name = 'CMES' and ts2_name != 'CMES')

or (ts1_name != 'CMES' and ts2_name = 'CMES');

The view is populated with rows of one-to-one relationships, where an object in table space ts1_name depends on an object in table space ts2_name. The result of the check is that objects in the cmes tablespace have no dependencies on other tablespace objects.

If a dependency is found for a tablespace object, you can temporarily remove the dependency or add the tablespaces containing the dependent objects to the recovery set altogether. The latter works better and ensures logical consistency between tables is maintained.

Identify objects that will be lost after TSPITR

Look at the data dictionary view TS_PITR_OBJECTS_TO_BE_DROPPED to determine which objects will be lost when the table space is restored at the target time:

select owner, name, to_char(creation_time, 'yyyy-mm-dd hh34:mi:ss') create_time

from ts_pitr_objects_to_be_dropped

where tablespace_name = 'CMES'

and creation_time > to_date('2016-02-15 21:20:00', 'yyyy-mm-dd hh34:mi:ss');

Check the results, restore tablespaces cmes at the chosen time will be safe and no objects will be lost. If there are missing objects, you can export them before performing the restore operation, and then import them again after the restore is complete.

5. Automatically execute TSPITR using the RMAN command

Create a new auxiliary directory auxdata under D:\, which RMAN will use to create a temporary auxiliary database.

Start the RMAN command to connect to the target database:

rman target /

Execute recovery command:

recover tablespace cmes until time '2016-02-15 21:20:00' auxiliary destination 'd:\auxdata';

6. The restored tablespaces will be offline, so you need to manually execute commands to bring tablespaces online.

sql'alter tablespace cmes online';

7. Verify the state of the table space and confirm that the lost table has been recovered

select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CMES ONLINE

RMES ONLINE

INDX ONLINE

select count(*) from cmes.c_material_t;

COUNT(*)

----------

345

Another point to note is that after TSPITR is complete, tablespaces backed up prior to the recovery target time will no longer be available, so you should back up the tablespaces or the entire database as soon as possible.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.