Shulou(Shulou.com)06/03 Report--

When ALTER SYSTEM SWITCH LOGFILE; is executed, the modification is successful, but there is no corresponding archived log file in the archive log space in the ASM hard disk group, which means the log switch is not successful, and then the following error is prompted when you modify it through: SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;:

ERROR at line 1:ORA-16014: log 2 sequence# 14 not archived, no available destinationsORA-00312: onlinelog 2 thread 1 virtual instrument DGDATA02 UnidevasmAccording to onlinelog Group2.258.945212025'

By querying relevant logs, it can be caused by insufficient flashback space. Query the size of flashback space.

SQL > show parameter db_recoveryNAME TYPE VALUE-- db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0

It is found that the flashback space is not set so that log switching cannot be carried out, and the flashback space is modified to 3G size.

SQL > alter system set db_recovery_file_dest_size=3G scope=both

Query the flashback space size again:

SQL > show parameter db_recoveryNAME TYPE VALUE---db_recovery_file_dest stringdb_recovery_file_dest_size big integer 3G

There is already flashback space through the query, and now you need to test whether the log switch is successful.

SQL > ALTER SYSTEM SWITCH LOGFILE; System altered.SQL > ALTER SYSTEM ARCHIVE LOG CURRENT; ALTER SYSTEM ARCHIVE LOG CURRENT*ERROR at line 1:ORA-16014: log 2 sequence# 14 not archived, no available destinationsORA-00312: onlinelog 2 thread 1 purse DGDATA02 hand udevasm hand onlinelog Grey 2.258.945212025'

It is found that the switch is still not successful when executing ALTER SYSTEM ARCHIVE LOG CURRENT. Thinking that the database is not restarted when the flashback space is changed, it is best to restart the database to see if it is successful.

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startup;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 331350920 bytesDatabase Buffers 729808896 bytesRedo Buffers 5517312 bytesDatabase mounted.Database opened.

After startup, perform a log switch:

SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;System altered.SQL > ALTER SYSTEM SWITCH LOGFILE;System altered.SQL > ALTER SYSTEM SWITCH LOGFILE;System altered.

It is found that the execution is successful. Now check the ASM to see if there is an archive log. Before querying, you need to query where the archive is stored.

SQL > archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination + DGRECOVERY/arcOldest online log sequence 9Next log sequence to archive 14Current log sequence 14

Switch grid users to query ASM hard disk groups:

[oracle@udevasm] $su-gridPassword: [grid@udevasm:/home/grid] $asmcmdASMCMD > lsDGDATA01/DGDATA02/DGRECOVERY/DGSYSTEM/GRID1/ASMCMD > lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED EXTERN N 512 4096 4194304 22520 260 0260 N DGDATA01/MOUNTED EXTERN N 512 4096 4194304 20472 14132 0 14132 0 N DGDATA02/MOUNTED EXTERN N 512 4096 4194304 21500 21420 0 21420 0 N DGRECOVERY/MOUNTED EXTERN N 512 4096 4194304 20472 20392 0 20392 0 N DGSYSTEM/MOUNTED EXTERN N 512 4096 4194304 10232 10144 0 10144 0 N GRID1/ASMCMD > cd DGRECOVERY/

The data after switching are:

ASMCMD > lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED EXTERN N 512 4096 4194304 22520 260 0 260 0 N DGDATA01/MOUNTED EXTERN N 512 4096 4194304 20472 14132 0 14132 0 N DGDATA02/MOUNTED EXTERN N 512 4096 4194304 21500 21420 0 21420 0 N DGRECOVERY/MOUNTED EXTERN N 512 4096 4194304 20472 20392 0 20392 0 N DGSYSTEM/MOUNTED EXTERN N 512 4096 4194304 10232 10144 0 10144 0 N GRID1/

Query whether there are any files in the archive space

ASMCMD > cd + DGRECOVERYASMCMD > lsUDEVASM/ASMCMD > cd UDEVASM/ASMCMD > lsARCHIVELOG/ASMCMD > cd ARCHIVELOG/ASMCMD > ls2017_06_08/ASMCMD > cd 2017_06_08/ASMCMD > lsthread_1_seq_14.256.946154499thread_1_seq_15.257.946154499thread_1_seq_16.258.946154499thread_1_seq_17.259.946154499thread_1_seq_18.260.946154501thread_1_seq_19.261.946154523thread_1_seq_20.262.946154571ASMCMD > pwd+DGRECOVERY/UDEVASM/ARCHIVELOG/2017_06_08

You can use the log switch again to query whether the file has been added.

SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;System altered.

Query ASM results:

ASMCMD > lsthread_1_seq_14.256.946154499thread_1_seq_15.257.946154499thread_1_seq_16.258.946154499thread_1_seq_17.259.946154499thread_1_seq_18.260.946154501thread_1_seq_19.261.946154523thread_1_seq_20.262.946154571thread_1_seq_21.263.946154599

From the above, we can judge that the log switch is successful, and the thread_1_seq_21.263.946154599 file is added.

In this way, the log switch is successful.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.