oracle 开启归档 界面,oracle 归档模式查看与开启

oracle 归档模式查看与开启

访问量:58

创建时间:2020-03-06

查看oracle是否开启归档

归档日志即归档重做日志，将重做日志进行归档存储，可以用来进行数据库恢复。

登陆oracle，通过命令archive log list查看，Automatic archival可以看到是disabled的，表示未开启。

[oracle@oracledb ~]$ sqlplus / as sysdba

...省略部分输出...

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2

Current log sequence 4

打开归档模式

打开归档模式，需要将数据库打开到mount状态下更改。

关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

将数据库打开到mount状态，命令startup mount

SQL> startup mount

ORACLE instance started.

Total System Global Area 834666496 bytes

Fixed Size 8798264 bytes

Variable Size 595595208 bytes

Database Buffers 222298112 bytes

Redo Buffers 7974912 bytes

Database mounted.

开启归档,命令alter database archivelog;

SQL> alter database archivelog;

Database altered.

SQL>

打开数据库到open正常模式,命令alter database open；

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

关闭归档重做日志

先关闭数据库，然后打开到mount状态，再关闭归档重做日志。再将数据库打开到open状态。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 834666496 bytes

Fixed Size 8798264 bytes

Variable Size 595595208 bytes

Database Buffers 222298112 bytes

Redo Buffers 7974912 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

oracle 归档日志存储位置更改及空间大小限制

注意：oracle的默认归档存储目录是在闪回区目录中,通过命令show parameter db_recovery;查看db_recovery_file_dest的所在目录

SQL> show parameter db_recovery;

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area/cdb1

db_recovery_file_dest_size big integer 2780M

查看v$recovery_file_dest视图，可得知fast_recovery_area的空间限制、已使用的空间、文件数等。

SQL> col NAME format a40

SQL> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID

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

/u01/app/oracle/fast_recovery_area/cdb1 2915041280 0 0 0 0

SQL>

更改闪回区目录

SQL> alter system set db_recovery_file_dest='/u01/archivelog' scope=spfile;

System altered.

SQL> show parameter db_recov

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area/cdb1

db_recovery_file_dest_size big integer 2780M

SQL>

更改闪回区空间大小

SQL> alter system set db_recovery_file_dest_size=40960M scope=spfile;

System altered.

重启数据库查看效果

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup open;

ORACLE instance started.

Total System Global Area 834666496 bytes

Fixed Size 8798264 bytes

Variable Size 595595208 bytes

Database Buffers 222298112 bytes

Redo Buffers 7974912 bytes

Database mounted.

Database opened.

SQL> show parameter db_reco

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/archivelog

db_recovery_file_dest_size big integer 40G

不使用闪回区存储归档日志

先查看一下闪回区的使用情况

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID

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

CONTROL FILE 0 0 0 0

REDO LOG 0 0 0 0

ARCHIVED LOG 0 0 0 0

BACKUP PIECE 0 0 0 0

IMAGE COPY 0 0 0 0

FLASHBACK LOG 0 0 0 0

FOREIGN ARCHIVED LOG 0 0 0 0

AUXILIARY DATAFILE COPY 0 0 0 0

8 rows selected.

SQL>

设置log_archive_dest_1变量的值用于存储归档日志

SQL> alter system set log_archive_dest_1='location=/u01/newarchivelog' scope=both;

System altered.

SQL> show parameter log_archive

NAME TYPE VALUE

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

log_archive_config string

log_archive_dest string

log_archive_dest_1 string location=/u01/newarchivelog

log_archive_dest_10 string

重启数据库使新配置生效。