當時從主庫通過rman備份到目前測試庫還原之後，由於備份是在備庫備份的，所以資料庫還原後狀態為readonly，standby_file_management參數為auto。首先需要通過alter database clear logfile group 日誌組;讓資料庫在磁碟創建日誌文件。出現問題 ...
當時從主庫通過rman備份到目前測試庫還原之後，由於備份是在備庫備份的，所以資料庫還原後狀態為readonly，standby_file_management參數為auto。
首先需要通過alter database clear logfile group 日誌組;讓資料庫在磁碟創建日誌文件。
出現問題1、重做日誌物理文件不存在，當時日誌組4狀態為current，無法在readonly模式下進行更改操作。問題2、參數文件的undo表空間與控制文件的不對應
SQL> alter database rename file '/redo1/redo/redo04a.log' to '/u3/redo1/redo/redo04a.log';
alter database rename file '/redo1/redo/redo04a.log' to '/u3/redo1/redo/redo04a.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01621: cannot rename member of current log if database is open
ORA-00312: online log 4 thread 1: '/redo1/redo/redo04a.log'
ORA-00312: online log 4 thread 1: '/redo2/redo/redo04b.log'
SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/redo1/redo/redo04a.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
SQL> alter database drop logfile group 4; -----就算成功了，不會刪除磁碟上的文件，需手動刪除
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: '/redo1/redo/redo04a.log'
ORA-00312: online log 4 thread 1: '/redo2/redo/redo04b.log'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4.2758E+10 bytes
Fixed Size 2262656 bytes
Variable Size 4966058368 bytes
Database Buffers 3.7447E+10 bytes
Redo Buffers 342855680 bytes
Database mounted.
SQL> alter database rename file '/redo1/redo/redo04a.log' to '/u3/redo1/redo/redo04a.log';
alter database rename file '/redo1/redo/redo04a.log' to '/u3/redo1/redo/redo04a.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is
automatic.
SQL> show parameter standby;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> alter system standby_file_management='manual';
alter system standby_file_management='manual'
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system set standby_file_management='manual' scope=both;
System altered.
SQL> alter database rename file '/redo1/redo/redo04a.log' to '/u3/redo1/redo/redo04a.log';
Database altered.
SQL> alter database rename file '/redo2/redo/redo04b.log' to '/u3/redo2/redo/redo04b.log';
Database altered.
SQL> alter database clear logfile group 4; ----讓資料庫在磁碟創建日誌文件
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SQL> select GROUP# ,STATUS,MEMBERS from v$log;
GROUP# STATUS MEMBERS
---------- ---------------- ----------
1 UNUSED 2
2 UNUSED 2
3 UNUSED 2
4 CURRENT 2
5 UNUSED 2
6 UNUSED 2
7 UNUSED 2
8 UNUSED 2
8 rows selected.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> alter database recover managed standby database finish force;
alter database recover managed standby database finish force
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-16157: media recovery not allowed following successful FINISH recovery
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 20472
Session ID: 769 Serial number: 3
SQL> create undo tablespace undotbs2 datafile'/u3/undotbs2.dbf' size 10g autoextend off;
create undo tablespace undotbs2 datafile'/u3/undotbs2.dbf' size 10g autoextend off
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter tablespace UNDOTBS1 offline;
alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-01109: database not open
SQL> create pfile='/u3/pfile.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@FIHSER68 ~]$ vi /u3/pfile.ora -----將undo_tablespace參數刪除
[oracle@FIHSER68 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 27 11:57:12 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u3/pfile.ora';
ORACLE instance started.
Total System Global Area 4.2758E+10 bytes
Fixed Size 2262656 bytes
Variable Size 5234493824 bytes
Database Buffers 3.7178E+10 bytes
Redo Buffers 342855680 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS10 ----此為控制文件中記錄的undo_tablespace.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> create spfile from pfile='/u3/pfile.ora';
File created.
