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

I. configure the flashback database

1. The database is in archive log mode.

SQL > select log_mode from v$database

LOG_MODE

-

ARCHIVELOG

2. Create a flashback recovery area

SQL > alter system set db_recovery_file_dest_size=10G

System altered.

SQL > alter system set db_recovery_file_dest='D:\ oracle\ product\ 11.2.0\ dbhome_1\ RDBMS'

System altered.

3. Set the flashback retention target time

SQL > alter system set db_flashback_retention_target=240

System altered.

-- the DB_FLASHBACK_RETENTION_TARGET parameter controls the retention time (in minutes). The default is 1 day. The flashback log space is reused in a circular manner, and the updated data will overwrite the old data. (this example is reserved for 4 hours)

4. Close the database and start to MOUNT state

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 3373858816 bytes

Fixed Size 2180424 bytes

Variable Size 1845496504 bytes

Database Buffers 1509949440 bytes

Redo Buffers 16232448 bytes

Database mounted.

5. Enable flashback logging and open the database

SQL > alter database flashback on

Database altered.

SQL > alter database open

Database altered.

6. Check whether flashback logging is enabled

SQL > select flashback_on from v$database

FLASHBACK_ON

-

YES

Second, use sqlplus to flash back the database

1. Create a temporary table

SQL > create table t as select * from dba_objects

Table created.

2. Query the current system schedule

SQL > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual

TO_CHAR (SYSDATE,'YY

-

2016-03-15 11:34:21

3. Do some DML operations on t-table

SQL > delete from t

72464 rows deleted.

SQL > commit

Commit complete.

SQL > insert into t select * from dba_objects where rownum commit

Commit complete.

SQL > select count (*) from t

COUNT (*)

-

one hundred

4. Flash the database back to the query time in step 2

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 3373858816 bytes

Fixed Size 2180424 bytes

Variable Size 1845496504 bytes

Database Buffers 1509949440 bytes

Redo Buffers 16232448 bytes

Database mounted.

SQL > flashback database to timestamp to_timestamp ('2016-03-15 11 hh34:mi:ss');-- can accept timestamp or system change number (SCN) parameters, but do not accept date or log world serial number

Flashback complete.

5. Whether the database query flashback is successful by opening the database in read-only mode-- pay attention to using read only mode to open the database to verify the flashback.

SQL > alter database open read only

Database altered.

SQL > select count (*) from t

COUNT (*)

-

72464

6. After the flashback is successful, open the database in resetlogs mode

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 3373858816 bytes

Fixed Size 2180424 bytes

Variable Size 1845496504 bytes

Database Buffers 1509949440 bytes

Redo Buffers 16232448 bytes

Database mounted.

SQL > alter database open resetlogs

Database altered.

SQL > select count (*) from t

COUNT (*)

-

72464

Database flashback successful

Note: if the database flashed back by flashback is too early, you can use RECOVER DATABASE UNTILE TIME times to recover to a later point in time.

-- you can also use RMAN for flashback

RMAN > flashback database to time = to_date ('2016-03-15 11 to_date 3414 21 mm hh34:mi:ss' hh34:mi:ss')

RMAN > flashback database to scn = 2728665

RMAN > flash backup to sequence = 2123 thread = 1

Third, limit the amount of flashback data generated

1. Turn off the tablespace flashback property

ALTER TABLESPACE tablespace_name FLASHBACK OFF

2. Enable tablespace flashback properties

ALTER TABLESPACE tablespace_name FLASHBACK ON;-- can only be executed in mount state

3. View the flashback properties of the tablespace

Select name,flashback_on from v$tablespace

Set up the sqlplus prompt

SQL > set sqlprompt "_ user'@'_connect_identifier >"

SYS@ocp >

-- to automatically set sqlprompt for all sqlplus sessions. Place the above command in the glogin.sql file in the ORACLE_HOME/sqlplus/admin directory

5. Flashback deletion

1. Create an exercise user

SYS@ocp > create user dropper identified by dropper

User created.

SYS@ocp > grant create session,resource to dropper

Grant succeeded.

SYS@ocp > connect dropper/dropper

Connected.

2. Create a table with indexes and constraints, and insert a row

DROPPER@ocp > create table names (name varchar2 (10))

Table created.

DROPPER@ocp > create index name_idx on names (name)

Index created.

DROPPER@ocp > alter table names add constraint name_u unique (name)

Table altered.

DROPPER@ocp > insert into names values ('John')

1 row created.

DROPPER@ocp > commit

Commit complete.

3. Confirm the content of the mode

DROPPER@ocp > select object_name,object_type from user_objects

OBJECT_NAME OBJECT_TYPE

-

NAME_IDX INDEX

NAMES TABLE

DROPPER@ocp > select constraint_name,constraint_type,table_name from user_constraints

CONSTRAINT_NAME C TABLE_NAME

-

NAME_U U NAMES

4. Delete the table

DROPPER@ocp > drop table names

Table dropped.

5. Query the Recycle Bin to see the mapping from the original name to the Recycle Bin name

DROPPER@ocp > select object_name,original_name,type from user_recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE

BIN$q+6VgWdBRGOykqOJfCyZNg==$0 NAME_IDX INDEX

BIN$u1TeIIlLS3isIPDvpSTblQ==$0 NAMES TABLE

Note: the view does not show constraints

6. You can query using the object name of the Recycle Bin, but you cannot make a DML statement.

DROPPER@ocp > select * from "BIN$u1TeIIlLS3isIPDvpSTblQ==$0"

NAME

-

John

DROPPER@ocp > insert into "BIN$u1TeIIlLS3isIPDvpSTblQ==$0" values ('Root')

Insert into "BIN$u1TeIIlLS3isIPDvpSTblQ==$0" values ('Root')

*

ERROR at line 1:

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

7. Use FLASHBACK drop to restore the table

DROPPER@ocp > flashback table names to before drop

Flashback complete.

8. Query the contents of the mode

DROPPER@ocp > select * from names

NAME

-

John

DROPPER@ocp > select object_name,original_name,type from user_recyclebin

No rows selected

DROPPER@ocp > select object_name,object_type from user_objects

OBJECT_NAME OBJECT_TYPE

BIN$q+6VgWdBRGOykqOJfCyZNg==$0 INDEX

NAMES TABLE

DROPPER@ocp > select constraint_name,constraint_type,table_name from user_constraints

CONSTRAINT_NAME C TABLE_NAME

-

BIN$cLAR1tu9Toi3u5qKdAbvIw==$0 U NAMES

9. Rename indexes and constraints back to their original names

DROPPER@ocp > alter index "BIN$q+6VgWdBRGOykqOJfCyZNg==$0" rename to name_idx

Index altered.

DROPPER@ocp > alter table names rename constraint "BIN$cLAR1tu9Toi3u5qKdAbvIw==$0" to name_u

Table altered.

DROPPER@ocp > select object_name,object_type from user_objects

OBJECT_NAME OBJECT_TYPE

-

NAME_IDX INDEX

NAMES TABLE

DROPPER@ocp > select constraint_name,constraint_type,table_name from user_constraints

CONSTRAINT_NAME C TABLE_NAME

-

NAME_U U NAMES

10. Use SYS users to delete DROPPER mode

SYS@ocp > drop user dropper cascade

User dropped.

11. Query the DBA_RECYCLEBIN view to confirm that all objects owned by the user DROPPER have been deleted.

SYS@ocp > select count (*) from dba_recyclebin where owner='DROPPER'

COUNT (*)

-

0

Note: if the original names of two tables in the Recycle Bin are the same, by default, the Flashback Drop command always restores the latest version of the table, but if it is not the desired version, you can specify the name of the Recycle Bin for the version you want to restore instead of the original name.

SQL > flashback table "BIN$q+6VgWdBRGOykqOJfCyZNg==$0" to before drop

VI. Manage the Recycle Bin

SQL > show recyclebin

User_recyclebin

Dba_recyclebin

Note: Flashback Drop does not apply to tables stored in SYSTEM tablespaces, they are deleted and cleared directly

Permanently clear deleted objects

DROP TABLE table_name PURGE;-- deleting a table does not transfer it to the Recycle Bin

PURGE TABLE table_name;-- clears the table from the Recycle Bin. If there are multiple objects with the same original name, the object that has been cleared for the longest time. You can also avoid this confusion by specifying the name of the recycle bin.

PURGE INDEX index_name;-- removes indexes from the Recycle Bin. You can also specify the original name or the Recycle Bin name.

PURGE TABLESPACE tablespace_name;-- clears all deleted objects from the tablespace.

PURGE TABLESPACE tablespace_name USER user_name;-- clears all deleted objects belonging to a user from the tablespace.

PURGE USER_RECYCLEBIN;-- clears all objects that the user deletes the file.

PURGE DBA_RECYCLEBIN;-- clears all deleted objects and requires dba permission.

7. Flashback query

All forms of flashback queries rely on undoing data to reconstruct its data at some point in the past.

1. Basic flashback query

1.1 create a test table and insert test data

USER1@mydb > create table regions (region_id number,region_name varchar2 (20))

Table created.

USER1@mydb > insert into regions values

1 row created.

USER1@mydb > insert into regions values.

1 row created.

USER1@mydb > insert into regions values (3 recorder Asia`)

1 row created.

USER1@mydb > insert into regions values (4 famous Middle East')

1 row created.

USER1@mydb > commit

Commit complete.

1.2 query the current time of the system, delete some data and confirm

USER1@mydb > select sysdate from dual

SYSDATE

-

2016-03-15 19:08:16

USER1@mydb > delete from regions where region_name like'A%'

2 rows deleted.

USER1@mydb > commit

Commit complete.

USER1@mydb > select * from regions

REGION_ID REGION_NAME

--

1 Europe

4 Middle East

1.3 make a flashback query

USER1@mydb > select * from regions as of timestamp to_timestamp ('2016-03-15 1914 08purl 1608mm Murray hh34:mi:ss')

REGION_ID REGION_NAME

--

1 Europe

2 Americas

3 Asia

4 Middle East

1.4 query the difference between the flashback time point and the present

USER1@mydb > select * from regions as of timestamp to_timestamp minus select * from regions ('2016-03-15 19 from regions as of timestamp to_timestamp 08 Fringe 08 hh34:mi:ss') minus select * from regions

REGION_ID REGION_NAME

--

2 Americas

3 Asia

You can use the DBMS_FLASHBACK package to roll back the entire session to some point in the past without affecting other sessions. DMS statements are not supported in flashback mode.

USER1@mydb > execute dbms_flashback.enable_at_time (to_timestamp ('2016-03-15 1914 08purl 08Rd hh34:mi:ss'))

PL/SQL procedure successfully completed.

USER1@mydb > select * from regions

REGION_ID REGION_NAME

--

1 Europe

2 Americas

3 Asia

4 Middle East

USER1@mydb > execute dbms_flashback.disable

PL/SQL procedure successfully completed.

Select * from table_name as of timestamp (systimestamp-interval '30' minute)

Select * from table_name as of timestamp (systimestamp-interval'1' hour)

2. Flashback table query-the first step in enabling table flashback is to support row movement on the table.

2.1 create a test table to insert test data

HR@mydb > create table dept (dept_id number,dept_name varchar2 (20))

Table created.

HR@mydb > alter table dept add constraint pk_dept primary key (dept_id)

Table altered.

HR@mydb > create table emp (emp_id number,name varchar2 (20), dept_id number)

Table created.

HR@mydb > alter table emp add constraint fk_emp foreign key (dept_id) references dept (dept_id)

Table altered.

HR@mydb > insert into dept values (1)

1 row created.

HR@mydb > select * from dept

DEPT_ID DEPT_NAME

--

1 SUPPORT

HR@mydb > commit

Commit complete.

HR@mydb > insert into emp values (101)

1 row created.

HR@mydb > commit

Commit complete.

HR@mydb > select * from emp

EMP_ID NAME DEPT_ID

101 John 1

2.2. View the current system time

HR@mydb > select sysdate from dual

SYSDATE

-

2016-03-15 21:37:36

2.3. Delete test table data

HR@mydb > delete from emp where emp_id=101

1 row deleted.

HR@mydb > delete from dept where dept_id=1

1 row deleted.

HR@mydb > commit

Commit complete.

2.4. Start flashback table

Flashback table table_name to timestamp to_timestamp (systimestamp-interval '30' minute)

HR@mydb > flashback table emp to timestamp to_timestamp ('2016-03-15 21 hh34:mi:ss' 37 hh34:mi:ss')

Flashback table emp to timestamp to_timestamp ('2016-03-15 21 VLV 3715 36 mm MMMB hh34:mi:ss')

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

-- prompt to open the row movement of the table

HR@mydb > alter table emp enable row movement

Table altered.

HR@mydb > alter table dept enable row movement

Table altered.

HR@mydb > flashback table emp to timestamp to_timestamp ('2016-03-15 21 hh34:mi:ss' 37 hh34:mi:ss')

Flashback table emp to timestamp to_timestamp ('2016-03-15 21 VLV 3715 36 mm MMMB hh34:mi:ss')

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02291: integrity constraint (HR.FK_EMP) violated-parent key not found

-- prompt for foreign key constraints

Two tables flashback at the same time to avoid constraint problems

HR@mydb > flashback table emp,dept to timestamp to_timestamp ('2016-03-15 21 hh34:mi:ss' 37 hh34:mi:ss')

Flashback complete.

2.5. Check the flashback result

HR@mydb > select * from dept

DEPT_ID DEPT_NAME

--

1 SUPPORT

HR@mydb > select * from emp

EMP_ID NAME DEPT_ID

101 John 1

Syntax variants allow you to flash back to a system change number and activate DML triggers during operation.

Flashback table emp,dept to scn 6539425 enable triggers

3. Flashback version query

-use the VERSIONS BETWEEN keyword

-- based on the returned version of scn

Select emp_id, versions_xid,versions_startscn,versions_endscn,versions_operation from emp versions between scn minvalue and maxvalue where emp_id=101

-based on the returned version of the timestamp

Select emp_id, versions_xid,versions_starttime,versions_endtime,versions_operation from emp versions between timestamp (systimestamp-25amp 1440) and systimestamp where emp_id=101

4. Flashback transaction

-- the minimum additional log alter database add supplemental log data that needs to open the library to use flashback transactions

4.1 create a test table and insert data

USER1@mydb > create table countries (name varchar2 (10))

Table created.

USER1@mydb > alter table countries enable row movement

Table altered.

USER1@mydb > insert into countries values ('Zambia')

1 row created.

USER1@mydb > insert into countries values ('Zimbabwe')

1 row created.

USER1@mydb > insert into countries values ('Zamibia')

1 row created.

USER1@mydb > commit

Commit complete.

USER1@mydb > select * from countries

NAME

-

Zambia

Zimbabwe

Zamibia

4.2 Update the test table

USER1@mydb > update countries set name='Namibia'

3 rows updated.

USER1@mydb > commit

Commit complete.

USER1@mydb > select * from countries

NAME

-

Namibia

Namibia

Namibia

4.3 query all versions of the row to determine the wrong transaction ID

USER1@mydb > select name,versions_xid,versions_operation from countries versions between scn minvalue and maxvalue

NAME VERSIONS_XID V

Namibia 080007000F040000 U

Namibia 080007000F040000 U

Namibia 080007000F040000 U

Zamibia 040009003C030000 I

Zimbabwe 040009003C030000 I

Zambia 040009003C030000 I

6 rows selected.

Query the FLASHBACK_TARNSACTION_QUERY view to see the rows affected by the transaction and give the SQL statement on how to cancel the impact.

-- the XID column is of type RAW, the VERSIONS_XID pseudo column is hexadecimal, and a type casting function is required.

SYS@mydb > select operation,undo_sql from flashback_transaction_query where xid=hextoraw ('080007000F040000')

OPERATIO UNDO_SQL

--

UPDATE update "USER1". "COUNTRIES" set "NAME" = 'Zamibia' where ROWID =' AAASjKAAEAAAACVAAC'

UPDATE update "USER1". "COUNTRIES" set "NAME" = 'Zimbabwe' where ROWID =' AAASjKAAEAAAACVAAB'

UPDATE update "USER1". "COUNTRIES" set "NAME" = 'Zambia' where ROWID =' AAASjKAAEAAAACVAAA'

-- you can also use DBMS_FLASHBACK packages for flashback transactions

Execute sys.dbms_flashback.transaction_backout (numtxns= > 2primes = > sys.xid_array ('080007000F040000'), options= > dbms_flashback.cascade)

5. Flashback data archiving-make sure to flash the table back to any time in the past.

You can create an archive in a pre-existing tablespace, but it is wiser in a new tablespace.

Create flashback archive default hrarch tablespace fbda1 quota 10g retention 5 year

The default keyword indicates that unless otherwise stated, it will be used as an archive for all tables.

Alter flashback archive hrarch set default

-- quota shows how much space the archive occupies in the tablespace. You can add more space to the existing table space or to another table space.

Alter flashback archive hrarch add tablespace fbda2 quota 10g

-- retention time can be adjusted

Alter flashback archive hrarch modify retention 7 year

-- if the data exceeds the specified retention period, the FBDA process automatically deletes it from the archive. You can also delete it manually before it expires

Alter flashback archive hrarch purge before timestamp to_timestamp ('01-01-2009)

-- only FLASHBACK ARCHIVE ADMINISTER system permissions can create, modify, or delete archives and control the retention and cleanup of archives.

Grant flashback archive administer to fbdaadmin

-- users must be granted FLASHBACK ARCHIVE permission to archive so that tables can be archived.

Grant flashback archive on hrarch to hr

-- enable archival protection of tables

Alter table hr.employees flashback archive hrarch

-- Archive protection of deleted tables

Alter table hr.employees no flashback archive

-- delete flashback data archive

Drop flashback archive hrarch

Refer to the full volume of OCP_OCA Certification examination Guide _ _ ORACLE_DATABASE_11G

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.