Consistency is a key requirement and factor of a database. “Shadow Copy” and “Write-Ahead Logging” (WAL) methods are used in order to ensure consistency, WAL being more popular.
\nOpen Source databases use WAL, without needing to use built-in postgreSQL, to rename.
\nThe Oracle DBMS does this differently, it uses a redolog to rename.<\/p>\n
Properties of Oracle Redologs:<\/strong><\/p>\n \u2022 Save files to the RedoLog before making any changes.<\/p>\n \u2022 RedoLogs are used in Oracle recovery mechanisms.<\/p>\n \u2022 RedoLogs are organized into groups, with a minimum requirement of 2 groups.<\/p>\n \u2022 To maintain error-tolerance, group members need to be on separate disks, if opting to keep all group members on the same disk, then error-tolerant disk architecture should be used such as RAID 1, 5, 10, etc.<\/p>\n \u2022 All group members share the same properties.<\/p>\n \u2022 Log Sequences and their sizes are all the same.<\/p>\n \u2022 The number of RedoLog files and the no. of members are set using the Create database command.<\/p>\n \u2022 The MAXLOGFILES parameter is used to set the maximum amount of redo log file groups.<\/p>\n \u2022 MAXLOGMEMBERS defines the maximum number of members per group.<\/p>\n \u2022 Redo logs work cyclically, for example in a situation where there are 3 groups LOGWR writes sequentially first to group 1 > group 2 > group 3 > group 1 (writing on group 1 again would mean writing over previous group 1 data).<\/p>\n \u2022The number of groups is determined by the level of activity on a database and the number of transactions.<\/p>\n \u2022If there aren’t enough groups in a database then errors such as “threat cannot allocate new log” are produced.<\/p>\n Explanations on how to add groups and member as well as a few database failure scenarios have also been provided below. An example of these is restoring a database where the RedoLog has failed.<\/p>\n \u2022 A Redo Log can have different statuses<\/p>\n – Current: The redo log currently in use.<\/p>\n – Active: The status that precedes Current. Information is waiting to be written to data files. (In archive mode, would show that the archiving has still not been completed).<\/p>\n – Inactive: A redo log that is not in use. Inactive redo logs cannot be used for Instance Recovery.<\/p>\n – Unused: A redo log that has never been used.<\/p>\n – Inactive: An inaccessible or corrupted redo log.<\/p>\n – Stale: A log with incomplete contents.<\/p>\n – Deleted: A redo log that will not be used.<\/p>\n – Clearing: A redo log that’s had its contents cleared and recreated using the “alter database clear logfile” command. Once recreated the redo log has an Unused status.<\/p>\n \u2022 To return a database to its last stable state, just before a disaster, the database should be placed in archive mode. This way all of the data that’s been committed can be restored.<\/p>\n One of the advantages of being in Archive mode is being able to make a backup of a database without shutting it down. (By default a database is not in Archive mode.)<\/p>\n Log Switch: This is the term given to a Log Writer, LGWR, switching from one group to another. A checkpoint transaction is executed along with a Log Switch.<\/p>\n [crayon]alter system switch logfile; [\/crayon] Making check points frequently would decrease the database’s performance, on the other hand infrequent updates would also have a negative impact in that it would take longer to recover a database that has shutdown in an inconsistent state.<\/p>\n This code creates a checkpoint. The current state of Redo Log transactions [crayon]alter database add logfile member ‘\/export\/home\/oracle\/oradata\/suntest\/redo01b.log’ to group 1 It’s possible to create members on the same disk, but by adding members in separate physical disks we increase a database’s fault tolerance in case of a disk error. Having members on the same physical disk would also have a negative impact on performance. The entry made in AlertLog [crayon] select group#, member from v$logfile;GROUP# MEMBER [crayon]select group#, member from v$logfile;GROUP# MEMBER A. Using the Rename command \u2022 The main thing to remember is that the system requires at least 2 groups. [crayon] alter database drop logfile group 4; [\/crayon] \u2022 The last working member cannot be deleted. [crayon] alter database drop logfile member’\/export\/home\/oracle\/oradata\/suntest\/redo01a.log’; Test 1<\/strong> [crayon] chmod wrx-a redolog03.rdo The system can be accessed using a sys user. [crayon] conn hr\/hr@orclvm WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN BEFORE 01\/24\/2010 20:51:55 (CHANGE 635601) CANNOT BE USED FOR RECOVERY. [crayon]alter database open; The logs have been written. The current sessions can continue their read & write transactions.<\/p>\n [crayon]select * from v$log;[\/crayon] After entering an “alter system switch logfile” command 3 times (when wanting to place the group that is currently in a transaction, first to 2 followed by 3 and finally 1. [crayon]alter database clear unarchived logfile group 1; [crayon]select * from v$log;[\/crayon] When checked from the operating system we find that redo01.rdo or group 1 has been recreated. To make sure that this is the case, we should shut down and restart the db.<\/p>\n [crayon]shutdown immediate To check:<\/p>\n [crayon]FDISK \u2013L and MOUNT [crayon]column member format a50 [crayon]ALTER DATABASE DROP LOGFILE GROUP 3;[\/crayon] Consistency is a key requirement and factor of a database. “Shadow Copy” and “Write-Ahead Logging” (WAL) methods are used in order to ensure consistency, WAL being more popular.<\/p>\n","protected":false},"author":1,"featured_media":3409,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[383,338],"tags":[345],"class_list":["post-2600","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dba_i","category-oracle-tr","tag-oracle"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2600","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=2600"}],"version-history":[{"count":1,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2600\/revisions"}],"predecessor-version":[{"id":4824,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2600\/revisions\/4824"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=2600"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=2600"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=2600"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nspfile fast_start_mttr_target = 600
\nIf the spfile is closed in an inconsistent state, a value of 600 indicates that it should be opened within 600 seconds. The Log switches are therefore timed accordingly.<\/p>\n
\n[crayon] alter system checkpoint ; [\/crayon]
\nRedo Log file information can be viewed using the v$log and v$logfile commands.
\nv$logv$logfile<\/p>\n
\n[crayon] select group#, member from v$logfile;GROUP# MEMBER
\n—————————————————————————–
\n3 \/export\/home\/oracle\/oradata\/suntest\/redo03a.log
\n2 \/export\/home\/oracle\/oradata\/suntest\/redo02a.log
\n1 \/export\/home\/oracle\/oradata\/suntest\/redo01a.log[\/crayon]
\nAdding members<\/strong><\/p>\n
\nalter database add logfile member ‘\/export\/home\/oracle\/oradata\/suntest\/redo02b.log’ to group 2
\nalter database add logfile member ‘\/export\/home\/oracle\/oradata\/suntest\/redo03b.log’ to group 3[\/crayon]
\nNote: As this is a test environment we have placed members in the same folder, under normal circumstances members would be in created in separate physical disks.<\/p>\n
\nA guide to adding disks in a Linux environment can be found at the end of this page.<\/p>\n
\n[crayon]$ tail -f alert_suntest.logalter database add logfile member ‘\/export\/home\/oracle\/oradata\/suntest\/redo01b.log’ to group 1
\nWed Jun 2 15:45:30 2010
\nCompleted: alter database add logfile member ‘\/export\/home\/oracle\/oradata\/suntest\/redo01b.log’ to group 1 \u2026\u2026
\n[\/crayon]
\nRepeating the query.<\/strong><\/p>\n
\n——————————————————–
\n3 \/export\/home\/oracle\/oradata\/suntest\/redo03a.log
\n2 \/export\/home\/oracle\/oradata\/suntest\/redo02a.log
\n1 \/export\/home\/oracle\/oradata\/suntest\/redo01a.log
\n1 \/export\/home\/oracle\/oradata\/suntest\/redo01b.log
\n3 \/export\/home\/oracle\/oradata\/suntest\/redo03b.log
\n2 \/export\/home\/oracle\/oradata\/suntest\/redo02b.log[\/crayon]
\nAdding a group.<\/strong>
\n[crayon]alter database add logfile group 4 (‘\/export\/home\/oracle\/oradata\/suntest\/redo04a.log’,’\/export\/home\/oracle\/oradata\/suntest\/redo04b.log’) size 50M [\/crayon]<\/p>\n
\n——————————————————–
\n3 \/export\/home\/oracle\/oradata\/suntest\/redo03a.log
\n2 \/export\/home\/oracle\/oradata\/suntest\/redo02a.log
\n1 \/export\/home\/oracle\/oradata\/suntest\/redo01a.log
\n1 \/export\/home\/oracle\/oradata\/suntest\/redo01b.log
\n4 \/export\/home\/oracle\/oradata\/suntest\/redo04a.log
\n4 \/export\/home\/oracle\/oradata\/suntest\/redo04b.log
\n3 \/export\/home\/oracle\/oradata\/suntest\/redo03b.log
\n2 \/export\/home\/oracle\/oradata\/suntest\/redo02b.log[\/crayon]
\nMoving or renaming a RedoLog<\/strong>
\nThere are 2 methods of doing this:
\nA. By using the Rename command
\nB. By creating a member in the location where it is to be moved and deleting (drop) the old ones.<\/p>\n
\nFirst put the database in Mount mode
\n[crayon]shutdown immediate
\nstartup mount
\nalter database rename file ‘\/export\/home\/oracle\/oradata\/suntest\/redo03.log’ to ‘\/export\/home\/oracle\/oradata\/suntest\/redo03a.log’ ; [\/crayon]
\nB. Moving the redolog is done in the same way. Once the database has been shutdown redo03.log is copied to its new location and an “Alter Database Rename” command is executed.
\nDropping Groups<\/strong><\/p>\n
\n\u2022 Groups that are in a current or active status cannot be deleted.
\n\u2022 Files belonging to deleted groups cannot be deleted on the operating system.<\/p>\n
\nDropping Members<\/strong><\/p>\n
\n\u2022 If a member’s status is current, the status first needs to be switched.
\n\u2022 A database that’s in archive mode and has a pending archive cannot be deleted.
\n\u2022 If the operating system isn’t using the OMF (Oracle Managed File) property, the file will remain within the operating system.<\/p>\n
\n[\/crayon]
\nIf a Redo Log file has been corrupted<\/p>\n
\nWhilst in group 3 was in a Current status, oracle and oinstall groups had all privileges assigned based on the group 3 redolog.<\/p>\n
\nalter system switch logfile ;
\n[\/crayon]
\nA switch has been made to Group 1 without any problems.
\nUsing “alter system switch logfile” again will result in the database hanging.
\nAlert Log denies permission and a gives a “ARC0: Failed to archive thread 1 sequence 11 (0)” error.<\/p>\n
\nQuery executions are attempted.
\nUsing a sys user it is possible to create a test table and commit it.<\/p>\n
\nERROR: ORA-00257: archiver error. Connect internal only, until freed.
\nconn scott\/1@orclvm
\nERROR: ORA-00257: archiver error. Connect internal only, until freed.
\nalter database clear logfile group 3;
\nalter database clear logfile group 3
\nERROR at line 1: ORA-00350: log 3 of instance orcl (thread 1) needs to be archived ORA-00312:
\nonline log 3 thread 1: ‘\/u01\/app\/oracle\/oradata\/orcl\/redo03.log’
\nalter database clear unarchived logfile group 3;
\nDatabase altered, [\/crayon]
\nThe log entry in the Alert file:<\/p>\n
\nThis part is important: rman backups made before the above date are of no use, but is it possible return the database to to the point mentioned in the archive log instead of the mentioned date & time ?
\nClearing online log 3 of thread 1 sequence number 11 Sun Jan 24 21:13:56 2010 Errors in file \/u01\/app\/oracle\/admin\/orcl\/udump\/orcl_ora_6815.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: ‘\/u01\/app\/oracle\/oradata\/orcl\/redo03.log’ ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied Additional information: 2 Sun Jan 24 21:14:09 2010 Completed: alter database clear unarchived logfile group 3 Sun Jan 24 21:14:14 2010 Archiver process freed from errors. No longer stopped
\nLogged into the system as Scott.
\nExcluding the errors in group 1, the database is working as normal.
\n[crayon]alter system switch logfile; [\/crayon]
\nAfter entering the previous command, Group 3’s status was changed to current, the privileges previously granted by the system are restored and the system functions normally again. A backup of the database is made straight away and the database is later shutdown.
\nOpening Oracle:
\n[crayon]startup
\nORACLE instance started. [\/crayon]
\nTotal System Global Area 369098752 bytes Fixed Size 2020896 bytes Variable Size 159386080 bytes Database Buffers 201326592 bytes Redo Buffers 6365184 bytes Database mounted. ORA-00314: log 3 of thread 1, expected sequence# doesn’t match ORA-00312: online log 3 thread 1: ‘\/u01\/app\/oracle\/oradata\/orcl\/redo03.log’
\nFollowing the previous error, the database is in mount mode.<\/p>\n
\nORA-03113: end-of-file on communication channel
\n[\/crayon]
\nRecover database until cancel
\nMedia recovery complete.
\nThe following syntax opens the database.
\n[crayon]alter database open resetlogs; [\/crayon]
\nTest 2<\/strong>
\n[crayon] $rm -rf redo01.rdo This command deletes the redolog file [\/crayon]
\nIn the alert.log file
\nShutting down archive processes Tue Jan 26 20:30:06 2010 ARCH shutting down ARC2: Archival stopped<\/p>\n
\ntruncating (as requested) before column FIRST_CHANGE#
\nGROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
\n———- ———- ———- ———- ———- — —————- —-
\n1 1 8 52428800 1 NO CURRENT 26-J 2 1 7 52428800 1 YES ACTIVE 25-J 3 1 6 52428800 1 YES INACTIVE 25-J
\n[\/crayon]
\nThe alert log
\nErrors in file \/u01\/app\/oracle\/admin\/orcl\/bdump\/orcl_arc0_7628.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: ‘\/u01\/app\/oracle\/oradata\/orcl\/redo01.log’ ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory
\nstarts giving error messages.<\/p>\n
\nThe Alert Log starts giving the following errors. The command line cursor doesn’t respond to the “alter system switch logfile” command.
\nLinux-x86_64 Error: 2: No such file or directory Additional information: 3 Tue Jan 26 20:42:12 2010 ARC1: Failed to archive thread 1 sequence 8 (0)<\/p>\n
\nDatabase altered. [\/crayon]
\nThe following information is entered into the alert log.
\nTue Jan 26 20:45:31 2010 Thread 1 advanced to log sequence 11 Current log# 1 seq# 11 mem# 0: \/u01\/app\/oracle\/oradata\/orcl\/redo01.log Tue Jan 26 20:45:31 2010 Completed: alter database clear unarchived logfile group 1 Tue Jan 26 20:45:31 2010 Archiver process freed from errors. No longer stopped
\n[crayon]alter system switch logfile;[\/crayon]
\nThe session that was hanging due to the command is now working. Writing and deleting can continue.<\/p>\n
\ntruncating (as requested) before column FIRST_CHANGE#
\nGROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
\n———- ———- ———- ———- ———- — —————- —-
\n1 1 11 52428800 1 NO CURRENT 26-J 2 1 10 52428800 1 YES ACTIVE 26-J 3 1 9 52428800 1 YES INACTIVE 26-J<\/p>\n
\nstartup
\nORACLE instance started.
\nTotal System Global Area 369098752 bytes Fixed Size 2020896 bytes Variable Size 121637344 bytes Database Buffers 239075328 bytes Redo Buffers 6365184 bytes Database mounted. ORA-00314: log 1 of thread 1, expected sequence# doesn’t match ORA-00312: online log 1 thread 1: ‘\/u01\/app\/oracle\/oradata\/orcl\/redo01.log
\n[\/crayon]
\nRecover database until cancel alter database open resetlogs;
\nThese commands were able to restart the database successfully, indicating that there are no problems.<\/p>\n
\nChanges made to \/etc\/fstab will ensure that volumes (disks) will be mounted automatically when the operating system boots.[\/crayon]<\/p>\n
\nselect f.member,l.bytes\/1024\/1024 as “Size in MB” from v$log l,v$logfile f where l.group#=f.group#;[\/crayon]
\n[crayon]column MEMBERS format 99
\nselect STATUS, group#, THREAD#, members, bytes\/1024\/1024 MB, ARCHIVED, SEQUENCE# from v$log;[\/crayon]
\n[crayon]column member format a50
\nselect group#, member from v$logfile;[\/crayon]
\n[crayon]SELECT group#, dbid, thread#, sequence#, status
\nFROM v$standby_log;[\/crayon]
\n[crayon]SELECT GROUP#, BYTES, ‘ONLINE’ AS TYPE FROM V$LOG
\nUNION
\nSELECT GROUP#, BYTES, ‘STANDBY’ AS TYPE FROM V$STANDBY_LOG
\nORDER BY 1;[\/crayon]
\n[crayon]set linesize 1200
\ncolumn group# format 99
\ncolumn THREAD# format 99
\ncolumn sequence# format 999
\ncolumn bytes\/1024\/1024 format 9999
\ncolumn group format 99
\ncolumn USED format 99999999999
\ncolumn ARCHIVED format a5
\ncolumn STATUS format a10
\nselect group#, THREAD#, sequence#, bytes\/1024\/1024 MB, USED, ARCHIVED, STATUS from v$standby_log ; [\/crayon]
\nAdding a RedoLog in the file system
\nAdding a group.
\n[crayon]alter database add logfile group 4
\n(‘\/export\/home\/oracle\/oradata\/suntest\/redo04a.log’,’\/export\/home\/oracle\/oradata\/suntest\/redo04b.log’) size 50M [\/crayon]
\nAdding a member.
\n[crayon]alter database add logfile member ‘\/oracle\/oradata\/suntest\/redo01b.log’ to group 1
\nalter database add logfile member ‘\/oracle\/oradata\/suntest\/redo02b.log’ to group 1
\nalter database add logfile member ‘\/oracle\/oradata\/suntest\/redo03b.log’ to group 1[\/crayon]
\nAdding a StandbyLog to the file system.
\n[crayon]ALTER DATABASE ADD STANDBY LOGFILE (‘\/u02\/app\/oracle\/oradata\/FSM\/stdby_log01_1.log’) SIZE 100M ;
\nALTER DATABASE ADD STANDBY LOGFILE (‘\/u02\/app\/oracle\/oradata\/FSM\/stdby_log02_1.log’) SIZE 100M ;[\/crayon]
\nAdding a RedoLog to Automatic Storage Management (ASM)
\n[crayon]ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ( ‘+DATA’) SIZE 100M;[\/crayon]
\n[crayon]show parameter db_create_online_log_dest_[\/crayon]
\n[crayon]alter system set db_create_online_log_dest_1 = ‘+DATA’ scope=both sid=’*’
\nalter system set db_create_online_log_dest_2 = ‘+FRA’ scope=both sid=’*’
\nalter database add logfile ;[\/crayon]
\nalternatively
\n[crayon]alter database add logfile group xxx;[\/crayon]
\nThis method autmatically adds a member to +FRA
\nAdding a Standby Log
\n[crayon]ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
\nGROUP 5 SIZE 100M,
\nGROUP 6 SIZE 100M,
\nGROUP 7 SIZE 100M;
\nALTER DATABASE ADD STANDBY LOGFILE THREAD 2
\nGROUP 8 SIZE 100M,
\nGROUP 9 SIZE 100M,
\nGROUP 10 SIZE 100M;[\/crayon]
\nDrop (Deleting)
\n[crayon]ALTER DISKGROUP FRA DROP FILE ‘+FRA\/mx\/onlinelog\/redo01b-tmp.log’;[\/crayon]
\nThe FRA is the Flash or Fast Recovery Area<\/p>\n
\n[crayon]ALTER DATABASE DROP LOGFILE MEMBER ‘\/oracle\/dbs\/log3c.rdo’;[\/crayon]
\n[crayon]ALTER DATABASE CLEAR LOGFILE GROUP 3;[\/crayon]
\n[crayon]ALTER SYSTEM CHECKPOINT GLOBAL;[\/crayon]
\nIf the corrupt redo log file has not been archived, use the UNARCHIVED parameter.
\n[crayon]ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;[\/crayon]
\nDropping an Alias Name for an Oracle ASM filename
\n[crayon]ALTER DISKGROUP data DROP ALIAS ‘+data\/orcl\/erp.dbf’;[\/crayon]<\/p>\n","protected":false},"excerpt":{"rendered":"