Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to add log file groups and log file members to the database". The explanation in this article is simple and clear and easy to learn and understand. let's study and learn how to add log file groups and log file members to the database.

Add log filegroups and log file members

First, determine the permissions of the account currently logged in, including the alter database that creates the log file group or log file members.

System permissions.

SQL > select privilege from user_sys_privs

2 where privilege='ALTER DATABASE'

PRIVILEGE

-

ALTER DATABASE

Check the database compatibility parameters before adding log filegroups or log file members.

SQL > show parameter compatible

NAME TYPE VALUE

Compatible string 11.2.0.0.0

Compatible > = 10.2.0 add log file groups or log file members, there is no need to consider whether it will exceed maxlogfiles,

The upper limit of maxlogmembers is limited.

Determine the number of log file groups existing on the system and the number of log file members in the group.

SQL > select group#,member from V$logfile

GROUP# MEMBER

1 / opt/oracle11g/oradata/oracl/redo01.log

2 / opt/oracle11g/oradata/oracl/redo02.log

3 / opt/oracle11g/oradata/oracl/redo03.log

There are three log filegroups in the system, each with one log file member.

Add a log filegroup to the system with 2. 5 log file members.

SQL > alter database add logfile group 4

2 ('/ opt/oracle11g/oradata/oracl/redo401.log','/opt/oracle11g/oradata/oracl/redo402.log')

3 size 20M

Database altered.

Note: in a real system, the above statement is obviously unreasonable because two log file members are placed on the same disk. For the availability of log files in the log filegroup, different log file members should be placed on different disks to avoid database downtime due to media failure when the log is switched to the group. View the log information after it is added. In addition, you should keep all the log files in the system of the same size, where the 20m is different from the 50m of the log files that will exist in the system later.

SQL > select group#,member from V$logfile

GROUP# MEMBER

1 / opt/oracle11g/oradata/oracl/redo01.log

2 / opt/oracle11g/oradata/oracl/redo02.log

3 / opt/oracle11g/oradata/oracl/redo03.log

4 / opt/oracle11g/oradata/oracl/redo401.log

4 / opt/oracle11g/oradata/oracl/redo402.log

SQL > select group#,blocksize,archived,members,status

2 from V$log

GROUP# BLOCKSIZE ARC MEMBERS STATUS

1 512 NO 1 INACTIVE

2 512 NO 1 INACTIVE

3 512 NO 1 CURRENT

4 512 YES 2 UNUSED

The newly added log filegroup is generally in unused state, indicating that the log filegroup has not been written yet. However, it may also be in other states, such as a log change occurs shortly after adding the log filegroup, then the state should be current. In addition, we did not specify blocksize when we created the log file, so we will use the default disk sector size, which is 512 bytes. The size of the disk sector

It is not 4KB, and it is useless to specify 4KB dint 1kB as the size of block.

SQL > alter database add logfile group 5

2 ('/ opt/oracle11g/oradata/oracl/redo501.log','/opt/oracle11g/oradata/oracl/redo502.log')

3 size 20M blocksize 4096

Alter database add logfile group 5

*

ERROR at line 1:

ORA-01378: The logical block size (4096) of file / opt/oracle11g/oradata/oracl/redo501.log

Is not compatible with the disk sector size (media sector size is 512 and host sector size is 512)

The error message speaks for itself. If the sector of the disk is 4KB, you can choose one of the values of 4KB database version 1KB Magi 512 bytes to be the block size of the log file (after database version 11.2.0).

Add log group log file members:

Adding log group log file members applies to the log group that already exists, but one or more of the log file members have been deleted for some reason. Let's add a log file member to group 2.

SQL > alter database add logfile member

2'/ opt/oracle11g/oradata/oracl/redo.log'

3 to group 2

Database altered.

You do not need to specify a size to add a log file. Oracle automatically sets the size of the newly added log file members based on the size of the log file members that already exist in the group.

Note: the most important thing is why add log filegroups and why add log file members?

Thank you for your reading, the above is the content of "how to add log filegroups and log file members in the database". After the study of this article, I believe you have a deeper understanding of how to add log filegroups and log file members to the database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.