记一次异机rman还原后的操作
当时从主库通过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.