Ensuring that Oracle Database 10g 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:
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.
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 GuideDuring 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:
This section discusses commands that you can use to manage database files.
See Also:
Oracle Database Administrator's GuideYou 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.
Before using the DROP TABLESPACE INCLUDING CONTENTS
command, take the tablespace offline to ensure that no temporary segments are in use.
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.
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:[ORACLE10G.oradata.V10TEST]SYSTEM01.DBF' TO 3> 'MY$DISK:[ORACLE10G.oradata.V10TEST]SYSTEM01.DBF' SQL> EXIT $ BACKUP/LOG/VERIFY/- DISK$1:[ORACLE10G.oradata.V10TEST]*.CTL - MY$DISK:[ORACLE10G.oradata.V10TEST]*.CTL
To move a tablespace file to a new location:
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
Back up the tablespace and control files that you want to move.
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
Without opening it, mount the database in Exclusive mode.
$ SQLPLUS/NOLOG
SQL> CONNECT / AS SYSDBA
SQL> STARTUP EXCLUSIVE MOUNT dbname
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
Back up the control files.
Perform the following steps to move a redo log file to a new location:
Identify the fully qualified file name of the redo log files that you want to move by using one of the following methods:
Start the database.
Run the following query:
SQL> SELECT * FROM V$LOGFILE;
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 theLGWR
.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.
Shut down the database by using the following command:
SQL> SHUTDOWN
Back up the control files for safety.
Restart the database using the following commands.
SQL> CONNECT / AS SYSDBA
SQL> STARTUP OPEN dbname
SQL> EXIT
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 10g release 1, DBV
can b e used on an open database.
To verify data in an Oracle Database 10g release 2 (10.2) database, point to the data files from the Oracle Database 10g release 2 (10.2) installation.
See Also:
Oracle Database Utilities for information about using SQL*Plus to verify the databaseIn Oracle Database 10g, 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 10g 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 10g retains full backward compatibility with 9.2 data files, which on HP OpenVMS prior to 10g did not contain a block 0. That is, a database can be started in 10g compatibility mode with a mix of 9.2 and 10g data files and is fully operable (all updates, writes, read, all operations available on a 10g 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 10g 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 10g recommends the use of the RMAN backup as copy datafile
command for the process of creating a new 10g format data file, that is:
RMAN> backup as copy datafile 'tbs_31.f' format '10g_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 10g format generic file header.
See Also:
For more information about therman backup
command, refer to the relevant sections in the Oracle documentation set