在线重做日志丢失后的恢复

1. 丢失多路复用日志组成员的恢复

		a. 准备数据
		#创建一个新的日志组
		SQL> select group#, member from v$logfile;
		    GROUP#    MEMBER
		-----------------------------------------------
			 3   /opt/oracle/oradata/O19C/redo03.log
			 2   /opt/oracle/oradata/O19C/redo02.log
			 1   /opt/oracle/oradata/O19C/redo01.log
			 
		alter database add logfile group 4('/opt/oracle/oradata/O19C/redo04_1.log','/opt/oracle/oradata/O19C/redo04_2.log') size 50M;
		
		SQL> select group#,status from v$log;
		    GROUP# STATUS
		---------- --------------------------------
			 1 INACTIVE
			 2 CURRENT
			 3 UNUSED
			 4 UNUSED
		
		#切换日志确保新添加的日志组被使用
		SQL> alter system switch logfile;
		SQL> /
		SQL> select group#,status from v$log;
		    GROUP# STATUS
		---------- --------------------------------
			 1 INACTIVE
			 2 ACTIVE
			 3 ACTIVE
			 4 CURRENT
		b. 删除日志组4的一个文件，模拟故障
		[oracle@oracle19c O19C]$ rm -rf redo04_1.log 
		
		SQL> alter system switch logfile;
		SQL> /
		SQL> /
		SQL> /
		SQL> /
		
		
		
		2024-09-12T11:12:42.396073+08:00
		Errors in file /opt/oracle/diag/rdbms/o19c/o19c/trace/o19c_arc0_6818.trc:
		ORA-00313: open failed for members of log group 4 of thread 1
		ORA-00312: online log 4 thread 1: '/opt/oracle/oradata/O19C/redo04_1.log'
		ORA-27041: unable to open file
		Linux-x86_64 Error: 2: No such file or directory
		Additional information: 3
		
		c. 恢复
		SQL>  select group#,status from v$log;   #如果组4当前再使用，不能进行恢复操作
		    GROUP# STATUS
		---------- --------------------------------
			 1 CURRENT
			 2 INACTIVE
			 3 INACTIVE
			 4 INACTIVE
		
		SQL> alter database drop logfile member '/opt/oracle/oradata/O19C/redo04_1.log';
		SQL> alter database  add logfile member '/opt/oracle/oradata/O19C/redo04_1.log' to group 4;

2. 在线重做日志组所有成员都丢失后的恢复

		a. 丢失inactive在线重做日志组
		SQL>  select group#,status,archived from v$log;
		    GROUP# STATUS			    ARCHIV
		---------- -------------------------------- ------
			 1 ACTIVE			    YES
			 2 CURRENT			    NO
			 3 INACTIVE			    YES
			 4 INACTIVE			    YES
		
		SQL> host rm /opt/oracle/oradata/O19C/redo03.log 
		SQL> host ls /opt/oracle/oradata/O19C/redo03.log
		ls: cannot access /opt/oracle/oradata/O19C/redo03.log: No such file or directory
		
		SQL> alter database clear logfile group 3;
		#如果inactive的日志组没有被归档，则使用命令
		alter database clear unarchived logfile group 3;
		SQL> host ls /opt/oracle/oradata/O19C/redo03.log
		/opt/oracle/oradata/O19C/redo03.log
		b. 丢失active或者current在线重做日志组
		SQL> select group#,status,archived from v$log;
		    GROUP# STATUS			    ARCHIV
		---------- -------------------------------- ------
			 1 CURRENT			    NO
			 2 INACTIVE			    YES
			 3 INACTIVE			    YES
			 4 INACTIVE			    YES
		
		SQL> host rm /opt/oracle/oradata/O19C/redo01.log
		SQL> host ls /opt/oracle/oradata/O19C/redo01.log
		ls: cannot access /opt/oracle/oradata/O19C/redo01.log: No such file or directory
		
		SQL> alter database clear logfile group 1;
		alter database clear logfile group 1
		*
		ERROR at line 1:
		ORA-01624: log 1 needed for crash recovery of instance o19c (thread 1)
		ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/O19C/redo01.log'
		
		SQL> alter system checkpoint;
		SQL> select group#,status,archived from v$log;
		    GROUP# STATUS			    ARCHIV
		---------- -------------------------------- ------
			 1 CURRENT			    NO
			 2 INACTIVE			    YES
			 3 INACTIVE			    YES
			 4 INACTIVE			    YES
		
		SQL> alter system switch logfile;
		SQL> select group#,status,archived from v$log;
		
		    GROUP# STATUS			    ARCHIV
		---------- -------------------------------- ------
			 1 ACTIVE			    NO
			 2 CURRENT			    NO
			 3 INACTIVE			    YES
			 4 INACTIVE			    YES
		
		
		SQL> alter database clear unarchived logfile group 1;
		SQL> host ls /opt/oracle/oradata/O19C/redo01.log
		/opt/oracle/oradata/O19C/redo01.log
		
		c. 丢失多个重做日志组
		如果丢失了多个在线重做日志组，那么恢复的方法就是先从最难恢复的日志开始，从难到易的顺序如下：
			§ current在线重做日志
			§ active在线重做日志
			§ unarchived在线重做日志
			§ inactive在线重做日志