oracle 11g 更改歸檔模式、目錄、日誌大小

一、更改Oracle為歸檔模式
   1.關閉oracle
SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.

    2.啟動為mount狀態
SQL> startup mount

ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             738199088 bytes
Database Buffers         1828716544 bytes
Redo Buffers                2945024 bytes
Database mounted.

  3.更改為歸檔模式
SQL>   alter database archivelog
  Database altered.
備註：‘archivelog’為 歸檔模式；‘noarchivelog’為非歸檔模式。

 

  4.更改資料庫為‘開啟’狀態
SQL>  alter database open

  5.檢視歸檔模式資訊
SQL>   archvie log list
它會提示歸檔的模式、是否啟用、引數
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     88
Next log sequence to archive   90
Current log sequence           90

 

二、更改歸檔目錄
   1.檢視引數db_recovery_file_dest

  （1）“db_recovery_file_dest”是用於定義儲存歸檔日誌的目錄。透過show parameter命令，顯示引數的值為（預設為）fast_recovery_area。示例如下：

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
 

  （2）檢視v$recovery_file_dest檢視，可得知fast_recovery_area的空間限制、已使用的空間、檔案數等。

SQL> select * from v$recovery_file_dest;

NAME     SPACE_LIMIT      SPACE_USED     SPACE_RECLAIMABLE     NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\oracle\fast_recovery_area  4385144832    4346230272           2741248             102

   2.更改歸檔日誌目錄
   語法：alter system set   引數=值  scope=spfile;

   示例：SQL> alter system set db_recovery_file_dest='D:\oracle\archivelog'    scope=spfile;

                System altered.

 

三、更改歸檔日誌大小

   1.檢視引數'db_recovery_file_dest_size'值
SQL> show parameter db_recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\archivelog
db_recovery_file_dest_size           big integer 4182M

 

   2.更改引數'db_recovery_file_dest_size'值大小

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

System altered.

 

   3.關閉資料庫，重啟資料庫

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup open;
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             738199088 bytes
Database Buffers         1828716544 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.

   4.再次檢視引數'db_recovery_file_dest_size'值
SQL> show parameter db_reco
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\archivelog
db_recovery_file_dest_size           big integer 41820M