ORACLE Online Mobile Redo log path, delete invalid redo log

Move the redo file path, the default redo log file path is the same as the Archivelog path, in the flashback area, because the flash back zone has a size limit, so redo log and archive log, especially archive log, will burst into the flashback zone. For the sake of specification, redo log and archive log must be placed in a separate path area.

1,View the existingRedofile path

To view the existing Redo file path, see the default path below the Flashback zone:

         Sql> Select member from V$logfile;          MEMBER         --------------------------------------------------------------------------------         /oracle/app/ Oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_3_dfkstobl_.log          /oracle/app/oracle/flash_recovery_ Area/powerdes_s1/onlinelog/o1_mf_2_dfksto61_.log          /oracle/app/oracle/flash_recovery_area/powerdes_s1/ Onlinelog/o1_mf_1_dfksto1q_.log          /home/oradata/powerdes/redo_dg_021.log         /home/oradata/powerdes/redo_dg_ 022.log          MEMBER         --------------------------------------------------------------------------------         / Home/oradata/powerdes/redo_dg_023.log          6 rows selected.          Sql>

2, close the database,Copy Redofile to new address   

After you close the database, copy the file:

         sql> shutdown immediate;         Ora-01109:database not open database dismounted.         ORACLE instance shut down. sql> CP file Address: [[email protected]_test_dbm1_121_63 ~]$ MV/ORACLE/APP/ORACLE/FLASH_RECOVERY_AREA/POWERDES_S1  /onlinelog/o1_mf_3_dfkstobl_.log/home/oradata/powerdes/redo03.log [[email protected]_test_dbm1_121_63 ~]$ MV /oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfksto61_.log/home/oradata/powerdes/ Redo02.log [[email protected]_test_dbm1_121_63 ~]$ Mv/oracle/app/oracle/flash_recovery_area/powerdes_s1/onli Nelog/o1_mf_1_dfksto1q_.log/home/oradata/powerdes/redo01.log Cp/home/oradata/powerdes/redo03.log/oracl E/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_3_dfkstobl_.log Cp/home/oradata/powerdes/redo02.lo G/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfkstobl_.log Cp/home/oradata/powerdes/re Do01.log/oracLe/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_1_dfkstobl_.log  

3,Database StartupMount, performRenameToggleRedoLog Path

Execute command: ALTER DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_3_dfkstobl_.l         og ' to '/home/oradata/powerdes/redo03.log '; Alter DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfksto61_.log ' to '         /home/oradata/powerdes/redo02.log '; Alter DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_1_dfksto1q_.log ' to '                 /home/oradata/powerdes/redo01.log '; Execution error sql> ALTER DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_3_dfks         Tobl_.log ' to '/home/oradata/powerdes/redo03.log '; Alter DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_3_dfkstobl_.log ' to '         /home/oradata/powerdes/redo03.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. 

Command execution error, suggesting that standbyfile maangement parameters for automatic, automatic circumstances do not allow modification, OK, listen to it, modified to manual, so that we can move its directory address

         Sql> show parameter standby;          NAME                                        TYPE       VALUE         -----------------------------------------------------------------------------         Standby_archive_dest                   string     ?/dbs/arch         standby_file_management         string     AUTO         sql> alter system set standby_file_management = MANUAL;          System altered.          sql>                        sql> ALTER DATABASE rename file '/ORACLE/APP/ORACLE/FLASH_RECOVERY_AREA/POWERDES_S1/ONLINELOG/O1_MF _3_dfkstobl_.log ' to '/home/oradata/powerdes/redo03.log ';          Database altered.          Sql>

Perform the first success, but execute the second error, recorded as follows:

        sql> ALTER DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfkstobl_         . log ' to '/home/oradata/powerdes/redo02.log '; Alter DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfkstobl_.log ' to '         /home/oradata/powerdes/redo02.log ' * ERROR at line 1:ora-01511:error in renaming log/data files Ora-01516:nonexistent log file, data file, or temporary file "/oracle/app/oracle/flash_recovery_area/powe         Rdes_s1/onlinelog/o1_mf_2_dfkstobl_.                 Log "sql> see hint, this file non existstent log file, see is the file does not exist, check, modified to the correct file name, execute again." Execute sql> ALTER DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinelog/o1_mf_2_dfks again          To61_.log ' to '/home/oradata/powerdes/redo02.log ';          Database altered. sql> ALTER DATABASE rename file '/oracle/app/oracle/flash_recovery_area/powerdes_s1/onlinElog/o1_mf_1_dfksto1q_.log ' to '/home/oradata/powerdes/redo01.log ';          Database altered. Sql>

4, check to see the current path:

        Sql> Show parameter Db_file_name_convert; NAME TYPE VALUE--------------------------------------------------- --------------------------Db_file_name_convert string sql> sql> Select M          Ember from V$logfile; MEMBER--------------------------------------------------------------------------------/home/oradata/powe rdes/redo03.log/home/oradata/powerdes/redo02.log/home/oradata/powerdes/redo01.log/home/oradata/          Powerdes/redo_dg_021.log/home/oradata/powerdes/redo_dg_022.log/home/oradata/powerdes/redo_dg_023.log          6 rows selected.          Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/home/oradata/powerd Es/system01.dbf/home/oradata/powerdes/sysaux01.dbf/home/oradata/powerdes/undotbS01.dbf/home/oradata/powerdes/users01.dbf/home/oradata/powerdes/powerdesk01.dbf/home/oradata/po werdes/plas01.dbf/home/oradata/powerdes/pl01.dbf/home/oradata/powerdes/help01.dbf/home/oradata/         POWERDES/ADOBELC01.DBF/HOME/ORADATA/POWERDES/SMS01.DBF/HOME/ORADATA/POWERDES/PLCRM01.DBF NAME --------------------------------------------------------------------------------/home/oradata/powerdes/pow Erdesk02.dbf/home/oradata/powerdes/datagm01.dbf/home/oradata/powerdes/plimp01. Dbf/home/oradata/powerdes/dwetl01. Dbf/home/oradata/powerdes/dw02. Dbf/home/oradata/powerdes/timdba01.          DBF rows selected. Sql>

Reboot to see if the new path takes effect

sql> shutdown immediate;         Ora-01109:database not open database dismounted.         ORACLE instance shut down.         sql> startup Mount;          ORACLE instance started.              Total System Global area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable size         1.9059E+10 bytes Database buffers 7784628224 bytes Redo buffers 145174528 bytes         Database mounted.          Sql> sql> Select member from V$logfile; MEMBER--------------------------------------------------------------------------------/home/oradata/powe rdes/redo03.log/home/oradata/powerdes/redo02.log/home/oradata/powerdes/redo01.log/home/oradata/          Powerdes/redo_dg_021.log/home/oradata/powerdes/redo_dg_022.log/home/oradata/powerdes/redo_dg_023.log          6 rows selected. Sql>

5. Clear the redundant Redo log Group

View the current log file

        Sql> select * from V$log;                    group#    thread#  sequence#      BYTES  BLOCKSIZE members          ARC         ---------------------------------- -----------------------------         STATUS              first_change# first_time   next_change# next_time         --------- --------------------------------------------------------                    1         1            139   52428800              1 YES         Current            1.4120E+10 31-mar-17      2.8147E+14                     3         1              0   52428800              1 YES         clearing            1.4120E+10 31-mar-17      2.8147E+14                     2         1              0   52428800              1 YES         UNUSED                1.4120E+10 31-mar-17      1.4120E+10 31-mar-17           

View the current redo log file

         Sql> select * from V$logfile; group# STATUS TYPE------------------------MEMBER-------------------------------------------- ------------------------------------Is_---3 online/home/oradata/powe                    Rdes/redo01.log No 2 online/home/oradata/powerdes/redo02.log No group# STATUS TYPE------------------------MEMBER------------------------------- -------------------------------------------------Is_---1 online/hom E/oradata/powerdes/redo03.log NO 4 Standby/oracle/app/oracle/fast_recovery_are         A/powerdes_s2/onlinelog/o1_mf_4_dfw3zf9v_.lo group# STATUS TYPE------------------------ MEMBER--------------------------------------------------------------------------------Is_---g YES 5 Standby/oracle/app/ora  Cle/fast_recovery_area/powerdes_s2/onlinelog/o1_mf_5_dfw3zg5r_.lo g YES group# STATUS TYPE------------------------MEMBER----------------------------------------------------------- ---------------------Is_---6 standby/oracle/app/oracle/fast_recover          Y_area/powerdes_s2/onlinelog/o1_mf_6_dfw3zh1q_.lo g YES 6 rows selected. Sql>

To be conservative, select * from V$log; log files that cannot be found can be deleted, even if they are completely out of current.

        sql> ALTER DATABASE drop logfile Group 5;          Database altered.          sql> ALTER DATABASE drop logfile Group; 6;          Database altered.          sql> ALTER DATABASE drop logfile Group 4;          Database altered.          Sql>                 Check, the redundant redo file has been removed         sql> select member from V$logfile;          MEMBER         --------------------------------------------------------------------------------         /home/ Oradata/powerdes/redo01.log         /home/oradata/powerdes/redo02.log         /home/oradata/powerdes/redo03.log          

ORACLE Online Mobile Redo log path, delete invalid redo log