Pro Oracle Database 11g Administration (24 page)

Say you want to resize the online redo logs to be 200MB each. First, you add the new groups that are 200MB using the ADD LOGFILE GROUP statement. The following example adds log group 4 with two members sized at 200MB:

alter database add logfile group 4

('/ora02/oraredo/O11R2/redo04a.rdo',

'/ora03/oraredo/O11R2/redo04b.rdo') SIZE 200M;

■
Note
You can specify the size of the log file in bytes, kilobytes, or megabytes.

105

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

After you’ve added the log files with the new size, you can drop the old online redo logs. A log group must have an INACTIVE status before you can drop it. You can check the status of the log group, as shown here:

SQL> select group#, status, archived, thread#, sequence# from v$log; You can drop an inactive log group with the ALTER DATABASE DROP LOGFILE GROUP statement: SQL> alter database drop logfile group ;

If you attempt to drop the current online log group, Oracle returns an ORA-01623 error stating that you can’t drop the current group. Use the ALTER SYSTEM SWITCH LOGFILE statement to switch the logs and make the next group the current group:

SQL> alter system switch logfile;

After a log switch, the log group that was previously the current group retains an active status as long as it contains redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status, Oracle throws an ORA-01624 error stating that the log group is required for crash recovery. Issue an ALTER SYSTEM CHECKPOINT command to make the log group inactive: SQL> alter system checkpoint;

Additionally, you can’t drop an online redo-log group if doing so leaves your database with only one log group. If you attempt to do this, Oracle throws an ORA-01567 error and informs you that dropping the log group isn’t permitted because it would leave you with less than two log groups for your database (Oracle requires at least two log groups to function).

Dropping an online redo-log group doesn’t remove the log files from the OS. You have to use an OS

command to do this (such as the rm Linux/Unix command). Before you remove a file from the OS, ensure that it isn’t in use and that you don’t remove a live online redo-log file. For every database on the server, issue this query to view which online redo-log files are in use: SQL> select member from v$logfile;

Before you physically remove a log file, first switch the online redo logs enough times so that all online redo-log groups have recently been switched; doing so causes the OS to write to the file and thus give it a new timestamp. For example, if you have three groups, ensure that you perform at least three log switches:

SQL> alter system switch logfile;

SQL> /

SQL> /

Now, verify at the OS prompt that the logfile you intend to remove doesn’t have a new timestamp: $ ls -altr

When you’re absolutely sure the file isn’t in use, you can remove it. The danger in removing a file is that if it happens to be an in-use online redo log and the only member of a group, you can cause serious damage to your database. Make sure you have a good backup of your database and that the file you’re removing isn’t used by any databases on the server.

Adding Online Redo-Log Files to a Group

You may occasionally need to add a log file to an existing group. For example, if you have an online redo-log group that contains only one member, you should consider adding a log file (to provide a higher level of protection against a single log-file member failure). Use the ALTER DATABASE ADD LOGFILE MEMBER

106

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

statement to add a member file to an existing online redo-log group. You need to specify the new member file location, name, and group to which you want to add the file: SQL> alter database add logfile member '/ora02/oraredo/O11R2/redo04c.rdo'

to group 4;

Ensure that you follow standards with regard to the location and names of any newly added redo-log files.

Removing Online Redo-Log Files from a Group

Occasionally, you may need to remove an online redo-log file. For example, your database may have experienced a failure with one member of a multiplexed group, and you want to remove the apostate member. First, make sure the log file you want to drop isn’t in the current group: SELECT a.group#, a.member, b.status, b.archived, SUM(b.bytes)/1024/1024 mbytes FROM v$logfile a, v$log b

WHERE a.group# = b.group#

GROUP BY a.group#, a.member, b.status, b.archived

ORDER BY 1, 2

If you attempt to drop a log file that is in the group with the CURRENT status, you receive the following error:

ORA-01609: log 4 is the current log for thread 1 - cannot drop members If you’re attempting to drop a member from the current online redo-log group, then force a switch as follows:

SQL> alter system switch logfile;

Use the ALTER DATABASE DROP LOGFILE MEMBER statement to remove a member file from an existing online redo-log group. You don’t need to specify the group number because you’re removing a specific file:

SQL> alter database drop logfile member '/ora02/oraredo/O11R2/redo04a.rdo'; You also can’t drop the last remaining log file of a group. A group must contain at least one log file. If you attempt to drop the last remaining log file in a group, you receive the following error: ORA-00361: cannot remove last log member

Moving or Renaming Redo-Log Files

Sometimes, you need to move or rename online redo-log files. For example, you may have added some new mount points to the system, and you want to move the online redo logs to the new storage. You can use two methods to accomplish this task:

• Add the new log files in the new location, and drop the old log files.

• Physically rename the files from the OS.

If you can’t afford any downtime, consider adding new log files in the new location and then dropping the old log files. See the section, “Adding Online Redo-Log Groups,” for details on how to add a log group.

Also see the section “Resizing Online Redo-Log Groups” for details on how to drop a log group.

107

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

You can also physically move the files from the OS. You can do this with the database open or closed. If your database is open, ensure that the files you move aren’t part of the current online redo-log group (because those are actively written to by the log-writer background process). It’s dangerous to try to do this task while your database is open because on an active system, the online redo logs may be switching at a rapid rate, which creates the possibility that you attempt to move a file at the same time it’s switched to be the current online redo log.

The next example shows how to move the online redo-log files with the database shut down. Here are the steps:

1. Shut down your database:

SQL> shutdown immediate;

2. From the OS prompt, move the files. This example uses the mv command to accomplish this task:

$ mv /ora02/oraredo/O11R2/redo01a.rdo /ora03/oraredo/O11R2/redo01a.rdo $ mv /ora02/oraredo/O11R2/redo02a.rdo /ora03/oraredo/O11R2/redo02a.rdo $ mv /ora02/oraredo/O11R2/redo03a.rdo /ora03/oraredo/O11R2/redo03a.rdo 3. Start up your database in mount mode:

SQL> startup mount;

4. Update the control file with the new file locations and names:

SQL> alter database rename file '/ora02/oraredo/O11R2/redo01a.rdo'

to '/ora03/oraredo/O11R2/redo01a.rdo';

SQL> alter database rename file '/ora02/oraredo/O11R2/redo02a.rdo'

to '/ora03/oraredo/O11R2/redo02a.rdo';

SQL> alter database rename file '/ora02/oraredo/O11R2/redo03a.rdo'

to '/ora03/oraredo/O11R2/redo03a.rdo';

5. Open your database:

SQL> alter database open;

You can verify that your online redo logs are in the new locations by querying the V$LOGFILE view. I also recommend that you switch your online redo logs several times and then verify from the OS that the files have recent time stamps. Also check the alert.log file for any pertinent errors.

Summary

This chapter described how to configure and manage control files and online redo-log files. Control files and online redo logs are critical database files; a normally operating database can’t function without them.

Control files are small binary files that contain information about the structure of the database. Any control files specified in the initialization file must be available for you to mount the database. If a control file becomes unavailable, then your database will cease operating until you resolve the issue. I highly recommend that you configure your database with at least three control files. If one control file becomes unavailable, you can replace it with a copy of a good existing control file. It's critical that you know how to configure, add, and remove these files.

Online redo logs are crucial files that record the database’s transaction history. If you have multiple instances connected to one database, then each instance generates its own redo thread. Each database must be created with two or more online redo-log groups. You can operate a database with each group having just one online redo-log member. However, I highly recommend that you create your online redo-log groups with two members in each group. If an online redo log has at least one member that can 108

CHAPTER 5 ■ MANAGING CONTROL FILES AND ONLINE REDO LOGS

be written to, your database will continue to function. If all members of an online redo-log group are unavailable, then your database will cease to operate. As a DBA, you must be extremely proficient with creating, adding, moving, and dropping these critical database files.

The chapters up to this point in the book have covered tasks such as installing the Oracle software, creating databases, and managing tablespaces, datafiles, control files, and online redo log files. The next several chapters concentrate on how to configure a database for application use, including topics such as creating users and database objects.

109

CHAPTER 6 ■ USERS AND BASIC SECURITY

Managing Default Users

When you create a database, Oracle creates several default database users. The specific users that are created vary by database version. If you’ve just created your database, you can view the default user accounts as follows:

SQL> select username from dba_users order by 1;

Here is an example listing of some default database user accounts:

USERNAME

------------------------------

APPQOSSYS

DBSNMP

DIP

ORACLE_OCM

OUTLN

SYS

SYSTEM

To begin securing your database, you should minimally change the password for every default account and then lock any accounts that you’re not using. After creating a database, I usually lock every default account and change their passwords to expired; I unlock default users only as they’re needed.

The following script generates the SQL statements that lock all users and set their passwords to expired: select

'alter user ' || username || ' password expire account lock;'

from dba_users;

A locked user can only be accessed by altering the user to an unlocked state. For example: SQL> alter user outln account unlock;

A user with an expired password is prompted for a new password when first connecting to the database as that user. When connecting to a user, Oracle checks to see if the current password is expired, and if so prompts you as follows:

ORA-28001: the password has expired

Changing password for

New password:

After entering the new password, you’re prompted to enter it again:

Retype new password:

Password changed

Connected.

■
Note
You can lock the SYS account, but this has no effect on your ability to connect as the SYS user using OS

authentication or when using a password file.

If you’ve inherited a database from another DBA, then sometimes it’s useful to determine whether another DBA created a user or if a user is a default account created by Oracle. As mentioned earlier, usually several user accounts are created for you when you create a database. The number of accounts varies 112

CHAPTER 6 ■ USERS AND BASIC SECURITY

somewhat by version and options installed. Run this query to display users that have been created by another DBA versus those created by Oracle (such as those created by default when the database is created): select

distinct u.username

,case when d.user_name is null then 'DBA created account'

else 'Oracle created account'

end

from dba_users u

,default_pwd$ d

where u.username=d.user_name(+);

■
Note
The DEFAULT_PWD$ view is available starting with Oracle Database 11g. For more details about guidelines regarding checking for default passwords, see My Oracle Support note 227010.1.

When you’re determining if a password is secure, it’s useful to check to see whether the password for a user has ever been changed. If the password for a user has never been changed, this may be viewed as a security risk. This example performs such a check: