5
Managing the Database

Ensuring that Oracle9i operates successfully can involve tuning the system or modifying parameters. These tasks require a thorough understanding of HP OpenVMS Alpha system administration as well as the concepts documented in the Oracle9i Database Administrator's Guide.

This chapter contains the following topics:

• "Creating Multiple Control Files"

• "Managing Database Files"

• "Database Verification Utility and Other Useful Utilities"

• "Debugging Database Processes with ORAMBX"

5.1 SQL*Plus and Oracle Net

When you start up SQL*Plus, a bequeath protocol adapter connection will be made if no TNS connect descriptor is supplied. Refer to Chapter 9, "Oracle Net on HP OpenVMS Alpha" for more information about bequeath adapter.

5.2 Creating Multiple Control Files

Three control files are created whenever you create a database. by default, the files are named control01.ctl, control02.ctl and control03.ctl. They reside in the directory pointed to by the logical ora_db. However, Oracle Corporation recommends that you back up the control files and create additional copies. When you add more control files, be sure to add the new filenames and locations to the CONTROL_FILES initialization parameter.

Refer to the Oracle9i Database Administrator's Guide for general information. Specific information for HP OpenVMS Alpha can be summarized as follows:

• By default, the control files reside in the ORA_DB directory.

• Control files can be moved to any location.

• To guard against device failure, the control files should be placed on separate devices.

5.3 Managing Database Files

During the ORACLE installation procedure, you create one database file in the directory referenced by the logical name ORA_DB, typically ORA_ROOT:[oradata.<dbname>]system01.dbf.

To add database files to an existing tablespace, use the SQL statement ALTER TABLESPACE. You cannot remove or delete a file; however, you can remove tablespaces other than the SYSTEM tablespace.

5.3.1 Using Commands to Manage Database Files

There are some commands that are useful in managing database files. The commands mentioned here are documented fully in the Oracle9i Database Administrator's Guide.

5.3.1.1 ALTER DATABASE

In addition to using the ALTER DATABASE command to mount, open, or close a database, to add or drop redo log files, and to archive redo log files, this command can be used to rename and/or move tablespace files and redo log files.

You cannot use the ALTER DATABASE BACKUP CONTROLFILE command to back up control files to tape. To back up control files to tape, back up to disk and then copy to tape.

5.3.1.2 DROP TABLESPACE

Before using the DROP TABLESPACE INCLUDING CONTENTS command, take the tablespace offline to ensure that no temporary segments are in use.

5.3.2 Adding Files

When specifying files to be added to the database, logical names are fully translated to either physical device names or system-level concealed logical names (if defined) and then written to the control file.

5.3.3 Renaming Files

If the name of the physical device is somehow disassociated with the database file location(s), the RDBMS cannot access these files. Use the ALTER DATABASE command to RENAME the file to its current location. After renaming the files, shut down the database and then back up the control files as in the following example:

SQL> ALTER DATABASE RENAME FILE 
2> 'DISK$1:[ORACLE9i.oradata.V9TEST]SYSTEM01.DBF' TO 
3> 'MY$DISK:[ORACLE9i.oradata.V9TEST]SYSTEM01.DBF'   
SQL> EXIT
$ BACKUP/LOG/VERIFY/IGNORE=INTERLOCK -
DISK$1:[ORACLE9i.oradata.V9TEST]*.CTL -  
MY$DISK:[ORACLE9i.oradata.V9TEST]*.CTL 

$ SQLPLUS/NOLOG 
SQL> CONNECT / AS SYSDBA 
SQL> SELECT * FROM V$DBFILE;
SQL> DISCONNECT 

5.3.4 Moving Tablespace Files

To move a tablespace file to a new location perform the following steps:

1. Identify and write down the exact, fully qualified filename from the data dictionary view and shut down the database. The physical device name and the file location must appear exactly as in the control file and the data dictionary view, DBA_DATA_FILES or V$LOGFILE.

   
   
   $ SQLPLUS/NOLOG 
   SQL> CONNECT / AS SYSDBA 
   SQL> SELECT * from V$DBFILE; 
   SQL> SELECT * from V$LOGFILE; 
   SQL> SHUTDOWN 
   SQL> EXIT 
   
   
   
   
   

2. Back up the tablespace files that you want to move as well as the control files.

3. Copy or move the file to a new location (use BACKUP/VERIFY/DELETE to move the file).

   $ BACKUP/IGNORE=NOBACK/DELETE/VERIFY -
   <device>:[<dir>]<filename>.<ext> -  
   <new_device>:[<new_dir>]<new_filename>.<ext>  
   
   

4. Without opening it, mount the database in exclusive mode.

   $ SQLPLUS/NOLOG
   SQL> CONNECT / AS SYSDBA 
   SQL> STARTUP EXCLUSIVE MOUNT <dbname>
   
   

5. Rename the file in the database using the exact string taken from V$dbfile.

   SQL> ALTER DATABASE
      2> RENAME FILE '<device>:[<dir>]<filename>.<ext>'
      3> to '<new_device>:[<new_dir>]<new_filename>.<ext>';
   SQL> ALTER DATABASE <dbname> OPEN; 
   SQL> EXIT  
   

6. Back up the control files.

5.3.5 Moving Redo Log Files

Perform the following steps to move a redo log file to a new location:

1. Identify the exact, fully qualified filename of the redo log files that you want to move by one of the following methods:
   1. If your database instance is up, issue the following query:

      SQL> SELECT * FROM V$LOGFILE;
      
      

2. Shut down the database, make a backup copy of the redo log files in the new location, and mount the database in exclusive mode (not opened).

   Note: After the database is shut down, make image copies of all database, control, and redo log files as a precaution against any problems that can arise during this procedure.

   $ SQLPLUS/NOLOG 
   SQL> CONNECT / AS SYSDBA 
   SQL> SHUTDOWN 
   SQL> EXIT 
   $ BACKUP/IGNORE=NOBACK -
   <old_device>:[<dir>]<filename>.<ext> -
   <new_device>:[<new_dir>]<new_filename>.<ext> 
   $ SQLPLUS/NOLOG
   SQL> CONNECT / AS SYSDBA 
   SQL> STARTUP EXCLUSIVE MOUNT <dbname>  
   
   

   Note: Having the database mounted and closed is essential when working with the redo log files. This prevents any log files from becoming online or marked as current by the LGWR.

3. From SQL*Plus, rename the files in the database using the ALTER DATABASE command. Specify the full file path.

   SQL> CONNECT / AS SYSDBA 
   SQL> ALTER DATABASE RENAME FILE
      2> '<device>:[<dir>]<old_redofile1>.RDO',
      3> '<device>:[<dir>]<old_redofile2>.RDO' to
      4> '<device>:[<dir>]<new_redofile1>.RDO',
      5> '<device>:[<dir>]<new_redofile2>.RDO';  
   

   The filenames specified must be correct and the new files must already exist. If either of these requirements are not met, the statement will fail.

4. Shut down the database using the following command:

   SQL> SHUTDOWN  
   

5. Back up the control files for safety.

6. Restart the database using the following commands.

   SQL> CONNECT / AS SYSDBA
   SQL> STARTUP OPEN <dbname> 
   SQL> EXIT
   

5.4 Database Verification Utility and Other Useful Utilities

This section gives information about the following:

• Database verification utility

• Other useful utilities

5.4.1 Database Verification Utility

The database verification utility (DBV) is the preferred technique for verifying the integrity of your database. Invoke this utility with the DBV symbol on OpenVMS.

To use this utility to verify data in an Oracle9i Release 2 (9.2.0.2) database, point to the 9.2 files from your Oracle9i Release 2 (9.2.0.2) installation.

5.5 Debugging Database Processes with ORAMBX

Sometimes an Oracle server process will seem to be spinning or hung. To provide Oracle with useful information on this process, you may occasionally be asked to generate a trace file containing debugging information.

The command utility ORAMBX is one way to obtain this information. ORAMBX takes the process name, not its PID, as an argument. At the prompt, you feed one command at a time. When you have finished sending commands, exit with control-Z. Then a trace file will exist in ORA_DUMP that contains information that is useful for debugging purposes.

Table 5-1 lists the most common ORAMBX commands and their functions:

Table 5-1  ORAMBX Commands

The two most useful commands are DUMP and SYSTEM. DUMP shows the process' call stack, which is useful if the process is hanging or spinning. The command DUMP 1 simply generates a printout of the call stack. The command DUMP 10 prints a call stack and information about all cursors, queries, and other Oracle process information available. Likewise, the command SYSTEM 1 produces a small amount of interesting information about an instance, while SYSTEM 10 tells about almost anything happening in the instance, processes, cursors, locks.