移动或重命名重做日志文件
有时候，可能会有如题的需求。
比较牵强的做法是在数据库运行的时候，在目标路径新增新的日志文件，然后删掉旧的数据文件。
该方法的好处是：不需要停掉数据库。
另一种方法需要停库，并需要用到操作系统的相关命令，如Linux中的“mv”。
具体如下所示：
1.停库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ atlas OPEN SQL> select a.group#,a.member,b.status,b.archived,sum(b.bytes)/1024/1024 mbytes from v$logfile a,v$log b where a.group#=b.group# group by a.group#,a.member,b.status,b.archived order by 1,2; GROUP# MEMBER STATUS ARC MBYTES ---------- -------------------------------------------------------------------- ---------------- --- ---------- 1 /oradata/ATLAS/onlinelog/o1_mf_1_b5cyjr0s_.log CURRENT NO 50 1 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_1_b5cyjrgf_.log CURRENT NO 50 2 /oradata/ATLAS/onlinelog/o1_mf_2_b5cyjs6t_.log ACTIVE NO 50 2 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_2_b5cyjsoj_.log ACTIVE NO 50 3 /oradata/ATLAS/onlinelog/o1_mf_3_b5cyjt8v_.log ACTIVE NO 50 3 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_3_b5cyjvkg_.log ACTIVE NO 50 4 /oradata/ATLAS/onlinelog/redo04a.log ACTIVE NO 50 4 /oradata/fast_recovery_area/ATLAS/onlinelog/redo04b.log ACTIVE NO 50 8 rows selected. SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c ~]$ [oracle@ora12c ~]$ ps -ef | grep ora_ oracle 46708 46048 0 22:24 pts/1 00:00:00 grep ora_ [oracle@ora12c ~]$ |
2.操作系统相关操作
|
1 2 3 |
[oracle@ora12c ~]$ mv /oradata/ATLAS/onlinelog/redo04a.log /oradata/fast_recovery_area/ATLAS/onlinelog/ [oracle@ora12c ~]$ mv /oradata/fast_recovery_area/ATLAS/onlinelog/redo04b.log /oradata/ATLAS/onlinelog/ [oracle@ora12c ~]$ |
3.启动数据库到mount阶段
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
[oracle@ora12c ~]$ ps -ef | grep ora_ oracle 46718 46048 0 22:26 pts/1 00:00:00 grep ora_ [oracle@ora12c ~]$ [oracle@ora12c ~]$ env | grep SID ORACLE_SID=atlas [oracle@ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 13 22:26:49 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1140850688 bytes Fixed Size 2923584 bytes Variable Size 771752896 bytes Database Buffers 352321536 bytes Redo Buffers 13852672 bytes Database mounted. SQL> !ps -ef | grep ora_ oracle 46726 1 0 22:26 ? 00:00:00 ora_pmon_atlas oracle 46728 1 0 22:26 ? 00:00:00 ora_psp0_atlas oracle 46730 1 2 22:26 ? 00:00:00 ora_vktm_atlas oracle 46734 1 0 22:26 ? 00:00:00 ora_gen0_atlas oracle 46736 1 0 22:26 ? 00:00:00 ora_mman_atlas oracle 46740 1 0 22:26 ? 00:00:00 ora_diag_atlas oracle 46742 1 0 22:26 ? 00:00:00 ora_dbrm_atlas oracle 46744 1 0 22:26 ? 00:00:00 ora_vkrm_atlas oracle 46746 1 0 22:26 ? 00:00:00 ora_dia0_atlas oracle 46748 1 0 22:26 ? 00:00:00 ora_dbw0_atlas oracle 46750 1 0 22:26 ? 00:00:00 ora_lgwr_atlas oracle 46752 1 0 22:26 ? 00:00:00 ora_ckpt_atlas oracle 46754 1 0 22:26 ? 00:00:00 ora_smon_atlas oracle 46756 1 0 22:26 ? 00:00:00 ora_reco_atlas oracle 46758 1 0 22:26 ? 00:00:00 ora_lreg_atlas oracle 46760 1 0 22:26 ? 00:00:00 ora_pxmn_atlas oracle 46762 1 0 22:26 ? 00:00:00 ora_mmon_atlas oracle 46764 1 0 22:26 ? 00:00:00 ora_mmnl_atlas oracle 46766 1 0 22:26 ? 00:00:00 ora_d000_atlas oracle 46768 1 0 22:27 ? 00:00:00 ora_s000_atlas oracle 46779 46721 0 22:27 pts/1 00:00:00 /bin/bash -c ps -ef | grep ora_ oracle 46781 46779 0 22:27 pts/1 00:00:00 grep ora_ SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ atlas MOUNTED SQL> |
4.更新控制文件记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> col member for a68 SQL> set linesize 400 SQL> SQL> select a.group#,a.member,b.status,b.archived,sum(b.bytes)/1024/1024 mbytes from v$logfile a,v$log b where a.group#=b.group# group by a.group#,a.member,b.status,b.archived order by 1,2; GROUP# MEMBER STATUS ARC MBYTES ---------- -------------------------------------------------------------------- ---------------- --- ---------- 1 /oradata/ATLAS/onlinelog/o1_mf_1_b5cyjr0s_.log INACTIVE NO 50 1 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_1_b5cyjrgf_.log INACTIVE NO 50 2 /oradata/ATLAS/onlinelog/o1_mf_2_b5cyjs6t_.log CURRENT NO 50 2 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_2_b5cyjsoj_.log CURRENT NO 50 3 /oradata/ATLAS/onlinelog/o1_mf_3_b5cyjt8v_.log INACTIVE NO 50 3 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_3_b5cyjvkg_.log INACTIVE NO 50 4 /oradata/ATLAS/onlinelog/redo04a.log INACTIVE NO 50 4 /oradata/fast_recovery_area/ATLAS/onlinelog/redo04b.log INACTIVE NO 50 8 rows selected. SQL> SQL> alter database rename file '/oradata/ATLAS/onlinelog/redo04a.log' to '/oradata/ATLAS/onlinelog/redo04b.log'; Database altered. SQL> SQL> alter database rename file '/oradata/fast_recovery_area/ATLAS/onlinelog/redo04b.log' to '/oradata/fast_recovery_area/ATLAS/onlinelog/redo04a.log'; Database altered. SQL> SQL> select a.group#,a.member,b.status,b.archived,sum(b.bytes)/1024/1024 mbytes from v$logfile a,v$log b where a.group#=b.group# group by a.group#,a.member,b.status,b.archived order by 1,2; GROUP# MEMBER STATUS ARC MBYTES ---------- -------------------------------------------------------------------- ---------------- --- ---------- 1 /oradata/ATLAS/onlinelog/o1_mf_1_b5cyjr0s_.log INACTIVE NO 50 1 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_1_b5cyjrgf_.log INACTIVE NO 50 2 /oradata/ATLAS/onlinelog/o1_mf_2_b5cyjs6t_.log CURRENT NO 50 2 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_2_b5cyjsoj_.log CURRENT NO 50 3 /oradata/ATLAS/onlinelog/o1_mf_3_b5cyjt8v_.log INACTIVE NO 50 3 /oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_3_b5cyjvkg_.log INACTIVE NO 50 4 /oradata/ATLAS/onlinelog/redo04b.log INACTIVE NO 50 4 /oradata/fast_recovery_area/ATLAS/onlinelog/redo04a.log INACTIVE NO 50 8 rows selected. SQL> |
5.打开数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ atlas MOUNTED SQL> alter database open; Database altered. SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ atlas OPEN SQL> |
——————————————————————
Ending。
