重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: oracle 闪回数据库使用point进行闪回试验 [打印本页]

作者: 王亮 时间: 2020-4-5 16:32
标题: oracle 闪回数据库使用point进行闪回试验

1、查看数据库的归档模式

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

2、查看数据库flashback database模式和闪回日志存放的目录

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

SQL> show parameter db_recovery_file

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/fast_recovery_area

db_recovery_file_dest_size big integer 4182M

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

由上可以看出数据库尚未开启闪回数据库，且闪回日志存放在快速恢复区中

3、修改闪回日志保存时间

SQL> alter system set db_flashback_retention_target=7200 scope=BOTH;

System altered.

4、开启闪回数据库功能

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size 2253624 bytes

Variable Size 989859016 bytes

Database Buffers 587202560 bytes

Redo Buffers 7393280 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

5、检查修改的功能是否生效

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

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

db_flashback_retention_target integer 7200

由上看出闪回数据库功能已开启，同时闪回日志保存时间已变更为5天。

6、查看快速恢复区的使用情况

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG .11 0 2

BACKUP PIECE 0 0 0

IMAGE COPY 0 0 0

FLASHBACK LOG 2.39 0 2

FOREIGN ARCHIVED LOG 0 0 0

7、创建闪回还原点

SQL> create restore point flashback1;

Restore point created.

8、创建用户并关联HYCS表空间

查询数据文件

SQL> select name from v$datafile;

NAME

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

/u01/app/oradata/orcl/SYSTEM.256.1034746429

/u01/app/oradata/orcl/SYSAUX.257.1034746429

/u01/app/oradata/orcl/UNDOTBS1.258.1034746429

/u01/app/oradata/orcl/USERS.259.1034746429

/u01/app/oradata/orcl/HYCS.DBF

查询数据库中的用户

SQL> select username,user_id from dba_users;

USERNAME USER_ID

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

SYS 0

SYSTEM 5

OUTLN 9

MGMT_VIEW 73

FLOWS_FILES 74

MDSYS 57

ORDSYS 53

EXFSYS 42

DBSNMP 30

WMSYS 32

APPQOSSYS 31

USERNAME USER_ID

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

APEX_030200 77

OWBSYS_AUDIT 79

ORDDATA 54

CTXSYS 43

ANONYMOUS 46

SYSMAN 71

XDB 45

ORDPLUGINS 55

OWBSYS 78

SI_INFORMTN_SCHEMA 56

OLAPSYS 60

USERNAME USER_ID

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

SCOTT 83

ORACLE_OCM 21

XS$NULL 2147483638

MDDATA 64

DIP 14

APEX_PUBLIC_USER 75

SPATIAL_CSW_ADMIN_USR 69

SPATIAL_WFS_ADMIN_USR 66

创建用户，并关联表空间HYCS

SQL> create user HYCS identified by HYCS default tablespace HYCS temporary tablespace temp1;

User created.

查看数据库用户及默认表空间

SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SYS SYSTEM TEMP1

SYSTEM SYSTEM TEMP1

HYCS HYCS TEMP1

OUTLN SYSTEM TEMP1

MGMT_VIEW SYSTEM TEMP1

FLOWS_FILES SYSAUX TEMP1

MDSYS SYSAUX TEMP1

ORDSYS SYSAUX TEMP1

EXFSYS SYSAUX TEMP1

DBSNMP SYSAUX TEMP1

WMSYS SYSAUX TEMP1

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

APPQOSSYS SYSAUX TEMP1

APEX_030200 SYSAUX TEMP1

OWBSYS_AUDIT SYSAUX TEMP1

ORDDATA SYSAUX TEMP1

CTXSYS SYSAUX TEMP1

ANONYMOUS SYSAUX TEMP1

SYSMAN SYSAUX TEMP1

XDB SYSAUX TEMP1

ORDPLUGINS SYSAUX TEMP1

OWBSYS SYSAUX TEMP1

SI_INFORMTN_SCHEMA SYSAUX TEMP1

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

OLAPSYS SYSAUX TEMP1

SCOTT USERS TEMP1

ORACLE_OCM USERS TEMP1

XS$NULL USERS TEMP1

MDDATA USERS TEMP1

DIP USERS TEMP1

APEX_PUBLIC_USER USERS TEMP1

SPATIAL_CSW_ADMIN_USR USERS TEMP1

SPATIAL_WFS_ADMIN_USR USERS TEMP1

31 rows selected.

连接HYCS用户

创建测试用表

SQL> create table hycs_test as

2 select rownum as id,

3 to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,

4 trunc(dbms_random.value(0, 100)) as random_id,

5 dbms_random.string('x', 20) random_string

6 from dual

7 connect by level <= 100;

SQL> select count(*) from hycs_test;

COUNT(*)

----------

100

9、闪回数据库

查询闪回点

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

闪回数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size 2253624 bytes

Variable Size 989859016 bytes

Database Buffers 587202560 bytes

Redo Buffers 7393280 bytes

Database mounted.

SQL> flashback database to restore point flashback1;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SYS SYSTEM TEMP1

SYSTEM SYSTEM TEMP1

OUTLN SYSTEM TEMP1

MGMT_VIEW SYSTEM TEMP1

FLOWS_FILES SYSAUX TEMP1

MDSYS SYSAUX TEMP1

ORDSYS SYSAUX TEMP1

EXFSYS SYSAUX TEMP1

DBSNMP SYSAUX TEMP1

WMSYS SYSAUX TEMP1

APPQOSSYS SYSAUX TEMP1

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

APEX_030200 SYSAUX TEMP1

OWBSYS_AUDIT SYSAUX TEMP1

ORDDATA SYSAUX TEMP1

CTXSYS SYSAUX TEMP1

ANONYMOUS SYSAUX TEMP1

SYSMAN SYSAUX TEMP1

XDB SYSAUX TEMP1

ORDPLUGINS SYSAUX TEMP1

OWBSYS SYSAUX TEMP1

SI_INFORMTN_SCHEMA SYSAUX TEMP1

OLAPSYS SYSAUX TEMP1

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SCOTT USERS TEMP1

ORACLE_OCM USERS TEMP1

XS$NULL USERS TEMP1

MDDATA USERS TEMP1

DIP USERS TEMP1

APEX_PUBLIC_USER USERS TEMP1

SPATIAL_CSW_ADMIN_USR USERS TEMP1

SPATIAL_WFS_ADMIN_USR USERS TEMP1

30 rows selected.

10、删除闪回恢复点

SQL> DROP RESTORE POINT FLASHBACK1;

Restore point dropped.

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)