前期准备
查看归档状态为非归档，关闭并启动到mount，开启归档模式
SYS@PROD1>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/backup
Oldest online log sequence 0
Current log sequence 12
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
SYS@PROD1>alter database archivelog;
Database altered.
SYS@PROD1>alter database open;
Database altered.
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/backup
Oldest online log sequence 0
Next log sequence to archive 12
Current log sequence 12
查看日志组状态
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES INACTIVE
3 2 YES ACTIVE
SYS@PROD1>create table scott.t2(x number);
Table created.
SYS@PROD1>insert into scott.t2 values(1);
1 row created.
SYS@PROD1>commit;
Commit complete.
SYS@PROD1>select * from scott.t2;
X
----------
1
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log
6 rows selected.
删除组1日志文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01*
查看状态
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES INACTIVE
3 2 YES INACTIVE
SYS@PROD1>alter system switch logfile;
System altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO CURRENT
3 2 YES INACTIVE
SYS@PROD1>alter system switch logfile;
System altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO ACTIVE
3 2 NO CURRENT
切换日志组进行归档，当再次切换到第一组日志是hang住了，因为无物理文件去归档
SYS@PROD1>alter system switch logfile;
解决方法：
打开另一窗口，正常关闭数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
进行不完全恢复
SYS@PROD1>recover database until cancel;
Media recovery complete.
SYS@PROD1>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
使用open resetlogs方式打开，(重置日志组信息，重头记录，以前的没用了)
SYS@PROD1>alter database open resetlogs;
Database altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES UNUSED
3 2 YES UNUSED
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log
未有归档动作，但切换日志组实际上隐式触发ckpt，相关最新数据实际已经落盘，所以可以查到最新信息
SYS@PROD1>select * from scott.t2;
X
----------
1
查看归档状态为非归档，关闭并启动到mount，开启归档模式
SYS@PROD1>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/backup
Oldest online log sequence 0
Current log sequence 12
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
SYS@PROD1>alter database archivelog;
Database altered.
SYS@PROD1>alter database open;
Database altered.
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/backup
Oldest online log sequence 0
Next log sequence to archive 12
Current log sequence 12
查看日志组状态
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES INACTIVE
3 2 YES ACTIVE
SYS@PROD1>create table scott.t2(x number);
Table created.
SYS@PROD1>insert into scott.t2 values(1);
1 row created.
SYS@PROD1>commit;
Commit complete.
SYS@PROD1>select * from scott.t2;
X
----------
1
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log
6 rows selected.
删除组1日志文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01*
查看状态
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES INACTIVE
3 2 YES INACTIVE
SYS@PROD1>alter system switch logfile;
System altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO CURRENT
3 2 YES INACTIVE
SYS@PROD1>alter system switch logfile;
System altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO ACTIVE
2 2 NO ACTIVE
3 2 NO CURRENT
切换日志组进行归档，当再次切换到第一组日志是hang住了，因为无物理文件去归档
SYS@PROD1>alter system switch logfile;
解决方法：
打开另一窗口，正常关闭数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
进行不完全恢复
SYS@PROD1>recover database until cancel;
Media recovery complete.
SYS@PROD1>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
使用open resetlogs方式打开，(重置日志组信息，重头记录，以前的没用了)
SYS@PROD1>alter database open resetlogs;
Database altered.
SYS@PROD1>select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 NO CURRENT
2 2 YES UNUSED
3 2 YES UNUSED
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log
未有归档动作，但切换日志组实际上隐式触发ckpt，相关最新数据实际已经落盘，所以可以查到最新信息
SYS@PROD1>select * from scott.t2;
X
----------
1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31400681/viewspace-2130915/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
