有些測試環境，對資料的準確度要求不是很高的，可以切成非歸檔模式。

非歸檔改為歸檔模式比較簡單,設定如下引數就可以了。

然後

startup mount

alter database archivelog;

aler database open

檢視archive log lis就可以了

但是歸檔改為非歸檔，我試了一下，碰到了以下問題，

---關掉了flashback。。。。。之後重啟

SQL>startup mount

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     242
Next log sequence to archive   244
Current log sequence           244

SQL> show parameter log_archive

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------log_archive_dest string /u01/app/oracle/arch

貌似沒有改過來，

然後我重啟了一次，還是現實歸檔，儘管提示noarchvelog執行成功，

對於這種情況，我決定重建控制檔案。

alter database backup controlfile to trace;

在udump下找到對應的spid的trace檔案按

可以用如下的sql來查詢

select spid from v$process where addr in (

select addr from v$session where sid in (select sid from v$mystat where rownum<2));

SQL> select spid from v$process where addr='3792379C';

SPID
------------
12718

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oel1 admin]$ cd $ORACLE_BASE
[oracle@oel1 oracle]$ cd admin
[oracle@oel1 admin]$ cd *
[oracle@oel1 PODD]$ cd udump
[oracle@oel1 udump]$ ll *12718*
-rw-r----- 1 oracle dba 10070 Oct 30 22:29 podd_ora_12718.trc

CREATE CONTROLFILE REUSE DATABASE "PODD" NORESETLOGS NOARCHIVELOG

把裡面的force logging archivelog該為noarchivelog

然後繼續

SQL> startup nomount
ORACLE instance started.

Total System Global Area 398458880 bytes
Fixed Size                  1219664 bytes
Variable Size             113247152 bytes
Database Buffers          276824064 bytes
Redo Buffers                7168000 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "PODD" NORESETLOGS NOARCHIVELOG
。。。。。。

Control file created.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     243
Current log sequence           245

這樣歸檔就改為非歸檔模式了。

ORACLE資料庫歸檔改為非歸檔

相關文章