B Managing the Database

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

This appendix contains the following topics:

• Section B.1, "SQL*Plus and Oracle Net Services"

• Section B.2, "Creating Multiple Control Files"

• Section B.3, "Managing Database Files"

• Section B.4, "Database Verification Utility"

• Section B.5, "Important Note on Changes to Data File Formats for HP OpenVMS"

B.1 SQL*Plus and Oracle Net Services

When you start SQL*Plus, a Bequeath protocol adapter connection is made if no TNS connect descriptor is supplied. Refer to Chapter 5, "Configuring Oracle Net Services" for more information about Bequeath adapter.

B.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 ORA_DB logical name. However, Oracle recommends that you back up the control files and create additional copies. When working with control files, keep in mind the following:

• When you add more control files, you must add the new file names and locations to the CONTROL_FILES initialization parameter.

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

• Control files can be moved to any location.

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

See Also:

Oracle Database Administrator's Guide

B.3 Managing Database Files

During the Oracle Database installation procedure, you can 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.

This section discusses the following topics to manage database files:

• Section B.3.1, "Using Commands to Manage Database Files"

• Section B.3.2, "Adding Files"

• Section B.3.3, "Renaming Files"

• Section B.3.4, "Moving Tablespace Files"

• Section B.3.5, "Moving Redo Log Files"

B.3.1 Using Commands to Manage Database Files

This section discusses commands that you can use to manage database files.

See Also:

Oracle Database Administrator's Guide

ALTER DATABASE

You can use the ALTER DATABASE command to mount, open, or close a database, to add or drop redo log files, and to archive redo log files. You can also use this command to rename 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.

DROP TABLESPACE

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

B.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.

B.3.3 Renaming Files

If the name of the physical device is somehow dissociated from the database file locations, then the database 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:[ORACLE11G.oradata.V10TEST]SYSTEM01.DBF' TO 
3> 'MY$DISK:[ORACLE11G.oradata.V10TEST]SYSTEM01.DBF'
SQL> EXIT
$ BACKUP/LOG/VERIFY/-
DISK$1:[ORACLE11G.oradata.V10TEST]*.CTL -
MY$DISK:[ORACLE11G.oradata.V10TEST]*.CTL

Note:

The physical device name and the file location must appear exactly as in the control file. Enter the following commands to get the physical device name and the database file locations:

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

B.3.4 Moving Tablespace Files

To move a tablespace file to a new location:

1. Identify and write down the exact, fully qualified file name 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 and control files that you want to move.

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.

B.3.5 Moving Redo Log Files

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

1. Identify the fully qualified file name of the redo log files that you want to move by using one of the following methods:

   1. Start the database.

   2. Run the following query:

      SQL> SELECT * FROM V$LOGFILE;
      

2. Shut down the database, create a backup 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 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 file names specified must be correct and the new files must already exist. If either of these requirements is not met, then the statement fails.

4. Shut down the database by 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
   

B.4 Database Verification Utility

The database verification utility (DBV) is the preferred technique for verifying the integrity of the database. Run this utility with the DBV symbol. Since Oracle Database 11g release 1, DBV can be used on an open database.

To verify data in an Oracle Database 11g Release 2 (11.2) database, point to the data files from the Oracle Database 11g Release 2 (11.2) installation.

See Also:

Oracle Database Utilities for information about using SQL*Plus to verify the database

B.5 Important Note on Changes to Data File Formats for HP OpenVMS

In Oracle Database 11g, the transportable tablespace feature has been extended to enable tablespaces to be transported across different platforms. To make this feature available on HP OpenVMS has necessitated a change in the Oracle file format. Specifically, data files, created while the database is running in 11g compatibility mode, are created with a new header block. This is called the OSD header (also referred to as block zero) at the beginning of the file. There are several important points to note regarding support for this new feature on HP OpenVMS:

• Oracle Database 11g retains full backward compatibility with 9.2 data files, which on HP OpenVMS prior to 11g did not contain a block 0. That is, a database can be started in 11g compatibility mode with a mix of 9.2 and 11g data files and is fully operable (all updates, writes, read, all operations available on a 11g data file are available on the 9.2 data file).

• Data files created while the database is running in the 9.2 compatibility mode will continue to be created without the new header block.

• The cross-platform transportable tablespace feature can only be used on files which have a block 0. For 11g data files, this is the case by default. For 9.2 data files, an explicit transformation must be applied to the file to create a new data file, which will contain the required block zero. Oracle Database 11g recommends the use of the RMAN backup as copy datafile command for the process of creating a new 11g format data file, that is:

  RMAN> backup as copy datafile 'tbs_31.f' format '11g_tbs_31.f';
  

  This creates a new data file, which is a copy of the original data file but with the new block 0 header and a 11g format generic file header.

  See Also:

  For more information about the rman backup command, refer to the relevant sections in the Oracle documentation set