今天在做oracle歸檔測試的時候發現了幾個問題,在這裡記錄下來希望能得到大家的纰漏和幫助
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 19 17:34:42 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
由於對oracle數據庫不太熟悉,在執行了下面的命令的時候感到好奇怪:
SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance
這句話意思是說,要想進行配置歸檔,必須屬於一個數據庫實例中,這個在RAC中會出現這個問題可以參見:
<a target=_blank href="http://shuimomo.blog.51cto.com/1141396/933434">點擊打開鏈接</a>
</pre><pre code_snippet_id="557252" snippet_file_name="blog_20141220_7_6900758" name="code" class="sql" style="font-size:18px;">但是我發現我是測試在VM的環境下,不存在這種RAC情況:我試了兩種情況第一種可以。只有mount的情況才可以操作
<table border="1" width="500" cellspacing="1" cellpadding="1"><tbody><tr><td>SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 314574800 bytes Database Buffers 96468992 bytes Redo Buffers 4288512 bytes Database mounted. SQL> alter database archivelog; Database altered.</td></tr><tr><td>SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 314574800 bytes Database Buffers 96468992 bytes Redo Buffers 4288512 bytes Database mounted. Database opened. SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instanc e</td></tr></tbody></table>
SQL> show parameter db_recovery_file_dest; NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 3882M
<span style="color: rgb(57, 57, 57); font-family: verdana, 'ms song', Arial, Helvetica, sans-serif; font-size: 14px; line-height: 21px; background-color: rgb(250, 247, 239);">發現現在缺省使用的路徑為 DB_RECOVERY_FILE_DEST,此路徑是和Oracle的Flash_back_recovery 路徑混雜在一起的,不太妥當。</span>
<span style="font-family:verdana, ms song, Arial, Helvetica, sans-serif;color:#393939;"><span style="font-size: 14px; line-height: 21px;">所以要進行修改:</span></span>
SQL> alter system set log_archive_dest = '/u01/app/oracle/arch' scope = spfile; System altered. SQL> !ls -lrt /u01/app/oracle total 48 drwxr-xr-x 2 oracle oinstall 4096 Dec 15 21:25 checkpoints drwxr-x--- 5 oracle oinstall 4096 Dec 15 22:03 cfgtoollogs drwxr-x--- 3 oracle oinstall 4096 Dec 15 22:04 oradata drwxr-x--- 3 oracle oinstall 4096 Dec 15 22:04 admin drwxrwxr-x 4 oracle oinstall 4096 Dec 15 22:04 diag drwxr-x--- 4 oracle oinstall 4096 Dec 15 22:06 flash_recovery_area
通過上面的查看你會發現數據庫並沒有為其創建一個arch目錄,在建立日志文件的時候恰恰相反,假如當前目錄下有你想創建的
文件你必須要先刪除了再創建該文件。這個要注意一下。
</pre><pre code_snippet_id="557252" snippet_file_name="blog_20141220_16_9214578" name="code" class="sql" style="font-size:18px;">SQL> !pwd /home/oracle SQL> !mkdir /u01/app/oracle/arch SQL> !ls -lrt /u01/app/oracle total 52 drwxr-xr-x 2 oracle oinstall 4096 Dec 15 21:25 checkpoints drwxr-x--- 5 oracle oinstall 4096 Dec 15 22:03 cfgtoollogs drwxr-x--- 3 oracle oinstall 4096 Dec 15 22:04 oradata drwxr-x--- 3 oracle oinstall 4096 Dec 15 22:04 admin drwxrwxr-x 4 oracle oinstall 4096 Dec 15 22:04 diag drwxr-x--- 4 oracle oinstall 4096 Dec 15 22:06 flash_recovery_area drwxr-xr-x 2 oracle oinstall 4096 Dec 19 17:41 arch SQL>