Add a log group in Oracle and some commands for log members

Add a log group in Oracle and some commands for log members

1. Add a log group:

Alter database add logfile group 4'/oradata/oracle/oradata/oracle_sid/redo04.log 'size 100 M;

2. Change the log group size (

First, check the archiving status:

SQL> select group #, thread #, members, archived, status from v $ log;
 
GROUP # THREAD # MEMBERS ARC STATUS
-------------------------------------------------
1 1 1 YES INACTIVE
2 1 1 YES INACTIVE
3 1 1 NO CURRENT
4 1 1 YES UNUSED

4 rows selected.

Next, drop group 1 (it is best not to DROP GROUP3 at this time. As for the consequences, I did not TEST in this direction .)

SQL> alter database drop logfile group 1;
 
Database altered.

Delete redo01.log

SQL>! Rm/oradata/oracle/oradata/NC5X/redo01.log

Add redo01.log:

SQL> alter database add logfile group 1 '/oradata/oracle/oradata/ORACLE_SID/redo01.log' size 100 M;

SQL> select group #, thread #, members, archived, status from v $ log;
 
GROUP # THREAD # MEMBERS ARC STATUS
-------------------------------------------------
1 1 1 YES UNUSED
2 1 1 YES INACTIVE
3 1 1 NO CURRENT
4 1 1 YES UNUSED

4 rows selected.

In this case, switch the log:

SQL> alter system switch logfile;
 
System altered.

Note: the differences between alter system switch logfile and alter system archive log current are as follows.

Alter system switch logfile: switch logfile without waiting for archiving to complete. If archive log mode is not enabled for the database. There is no doubt about using this switch. In addition, log switching is performed for the current instance in single-instance database and RAC mode.

The alter system archive log current switch logfile only after archiving is complete. Switches logs to all instances.

In general, the results of the two commands are almost the same in the automatically archived library. The difference is that alter system archive log current takes longer time than alter system switch logfile.

3. Add members to the log group:

SQL> alter database add logfile member '/oraback/logbackup/redo01_ B .log' to group 1;
 
Database altered.

Note: The redo01_ B .log size cannot be set in the alter statement.

SQL> alter database add logfile member '/oraback/logbackup/redo01_ B .log 'size 100 M to group 1;
Alter database add logfile member '/oraback/logbackup/redo01_ B .log' size 100 M to group 1
*
ERROR at line 1:
ORA-00946: missing TO keyword.

After the Alter statement is run, the size of the Redo01_ B .log generated is the same as that of redo01.log.

The path can be different from the default path.

Finally, you must declare that several log groups can be added at the same time. You can also add several log group members. Statement format:

Alter database add logfile '/LOCATION_DUEST/REDO01.LOG' SIZE 10 M, '/LOCATION_DUEST/REDO02.LOG' SIZE 10M ......; and so on.

Adding members is similar. Not here.

4. delete a log group and its members:

Principles: The following principles must be observed before deletion. Each instance must have at least two log groups. When a group is ACTIVE or CURRENT, it cannot be deleted; deleting a log GROUP only changes the database, and the operating system files have not been deleted. When the drop logfile group n statement is applied to the deletion, all members in group n will be deleted.

Alter database drop logfile group n;

Principles for deleting log members: When you delete the last member in the group, you cannot delete the Member. When the group's turntable is in the current status, you cannot delete the member; in archive mode, files must be archived before they can be deleted. to delete a member of a log group, only the database is changed, and files of the operating system are not deleted.

Alter database drop logfile member '/LOCATION_DUST/REDO0N_N.LOG ';