5
Managing the Database

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

This chapter presents the following topics:

• Creating Database Links

• Creating Multiple Control Files

• Managing Database Files

• Database Verification Utility and Other Useful Utilities

• Debugging Database Processes with ORAMBX

Server Manager and SQL*Net

When you start up Server Manager, a bequeath adapter connection will be made if no TNS connect descriptor is supplied.

On Alpha OpenVMS systems, Server Manager is linked two-task. This requires Server Manager to make connections to the Oracle8 Enterprise Edition using SQL*Net.

Starting Server Manager

After executing the ORAUSER_<dbname>.COM file for a given instance, you can start using Server Manager.

SVRMGR Line-mode

When starting Server Manager in line mode, implement one of the following steps before invoking SVRMGR:

• Define the logical name ORA_DFLT_HOSTSTR to a valid SQL*NET V8 service name

or

• Use the bequeath adapter

To invoke Server Manager, enter the following command:

$ SVRMGRL

SET ECHO and SET TERMOUT Functionality in SVRMGR

In Server Manager (SVRMGR), the command SET TERMOUT OFF only stops output of SQL commands from going to the screen. The
SET ECHO OFF command turns off echoing of the command being executed. SET TERMOUT OFF does not stop all output, just the output returned by a given SQL command.

Creating Database Links

If you are connected to a database, you can access data from another database via a database link, using SQL*Net.

The following is an example of a statement that creates a database link.

SQL> CREATE [PUBLIC ] DATABASE LINK PROD CONNECT TO SCOTT 
  2>  IDENTIFIED BY TIGER 
  3>  USING '<SQL*Net connect string>';  

<SQL*Net connect string> specifies a remote database and is defined in your SQL*Net TNSNAMES.ORA file or, if you are using Oracle Names, is known to Oracle Names. Please consult your SQL*Net documentation for more information.

If you have upgraded from Oracle V7.1.5 or below, and have database links that specify SQL*Net Version 1 connect strings, you must drop the links and recreate them with SQL*Net Release 8 connect strings.

After creating the link, you can query tables on the remote database by using the database link name. For example, to select data from the DEPT table of the database identified by database link PROD, you could enter the following command:

SQL> SELECT * FROM DEPT@PROD;    

Creating Multiple Control Files

Two control files are created whenever you create a database. 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 Oracle8 Server Administrator's Guide for general information. Specific information for Alpha OpenVMS 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.

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:[DB_<dbname>].

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.

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 Oracle8 Server Administrator's Guide.

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.

CREATE TABLE

If you have export files generated before Version 6.0.31 with check constraints or defaults that span lines, the entire CHECK option condition statement (including the right parenthesis) must be on one line. If it is not, you will be able to create and export these tables, but not import them. An import attempt will cause an ORA-00921 error.

Correct:

CREATE TABLE TEST ( 

	COL1 CHAR (10),

	COL2 CHAR (1),

	CHECK (COL2 IN ('1', '2', '3')) 

	);

Incorrect:

CREATE TABLE TEST ( 

	COL1 CHAR (10), 

	COL2 CHAR (1), 

	CHECK (COL2 IN 

	('1', '2', '3'))); 

DROP TABLESPACE

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

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.

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:

SVRMGR> ALTER DATABASE RENAME FILE 
	2> 'DISK$1:[ORACLE.TEST.DB_V8TEST]ORA_SYSTEM.DBS' TO 
	3> 'MY$DISK:[ORACLE.TEST.DB_V8TEST]ORA_SYSTEM.DBS'   
SVRMGR> EXIT
$ BACKUP/LOG/VERIFY -

DISK$1:[ORACLE.TEST.DB_V8TEST]*.CON - 
MY$DISK:[ORACLE.TEST.DB_V8TEST]*.CON 
SVRMGR> EXIT

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.

   $ SVRMGRL

   SVRMGR> CONNECT INTERNAL AS SYSDBA
SVRMGR> SELECT * from V$DBFILES;
SVRMGR> SELECT * from V$LOGFILE;
SVRMGR> SHUTDOWN
SVRMGR> 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=(INTERLOCK,NOBACK)/DELETE /VERIFY -	
   	<device>:[<dir>]<filename>.<ext> - 	
   	<new_device>:[<new_dir>]<new_filename>.<ext>  
   

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

   $ SVRMGRL 
   SVRMGR> CONNECT INTERNAL AS SYSDBA 
   SVRMGR> STARTUP EXCLUSIVE MOUNT <dbname>  
   

5. Rename the file in the database using the exact string taken from DBA_DATA_FILES.

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

6. Back up the control files.

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:
   • If your database instance is up, issue the following query:

     	SQL> SELECT * FROM V$LOGFILE;
     

   • Look in the ORA_INSTANCE directory for the CREATE_<database>.SQL file. This file is created at install time and lists where the redo log files were created.

   • Dump the control file.

   • Shut down the database, make a second 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.

     $ SVRMGRL 
     SVRMGR> CONNECT INTERNAL AS SYSDBA 
     SVRMGR> SHUTDOWN 
     SVRMGR> EXIT 
     $ BACKUP/IGNORE=(INTERLOCK,NOBACK) -
     
     	<old_device>:[<dir>]<filename>.<ext> -
     
     	<new_device>:[<new_dir>]<new_filename>.<ext> 
     $ SVRMGRL 
     SVRMGR> CONNECT INTERNAL AS SYSDBA 
     SVRMGR> STARTUP EXCLUSIVE MOUNT <dbname>  
     

3. From Server Manager, rename the files in the database using the ALTER DATABASE command. Specify the full file path.

   SVRMGR> CONNECT INTERNAL AS SYSDBA 
   SVRMGR> 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 file must already exist. If either of these requirements are not met, the statement will fail.

4. Shut down the database using the following commands.

   SVRMGR> SHUTDOWN  
   

5. Back up the control files.

6. Restart the database using the following commands.

   SVRMGR> CONNECT INTERNAL AS SYSDBA
   SVRMGR> STARTUP OPEN <dbname> 
   SVRMGR> EXIT    
   

Database Verification Utility and Other Useful Utilities

This section gives information about the following:

• Database verification utility

• Other useful utilities

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 Alpha OpenVMS.

To use this utility to verify data in an Oracle8i Release 3 (8.1.7) database, point to the 8.1.7 files from your Oracle8i Enterprise Edition Release 3 (8.1.7) installation.

Other Useful Utilities

The utilities listed in this section are included in the ORA_RDBMS directory, mostly for internal use. USE AT YOUR OWN RISK.

FILES

Given either the PID or process name, this command file uses SDA to show any files for which the process has open channels. You need the CMKRNL privilege to run this script.

$ FILES <PID_or_process_name> [<optional_selection _string>]

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.

The most common ORAMBX commands are:

DUMP	<level>	Dump call stack
PGA	<level>	Dump the fixed pga
SGA	<level>	Dump the fixed sga
SYSTEM	<level>	Perform system state dump
EVENT	<text>	Set process event
SESEVENT	<text>	Set session event
BLOCK	<dba><level>	Dump block(s) at specified level
MEMORY LOG		Dump log of memory protection events
SUSPEND		Suspend process at current mode
FLUSH		Flush any pending writes to trace file

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.