Oracle Corporate Support
Problem Repository
1. Prob# 1018477.6 EXPORTING TO TAPE ON UNIX SYSTEMS
2. Soln# 2066781.6 HOW TO EXPORT TO TAPE ON UNIX SYSTEMS
3. Prob# 1010840.6 ORACLE BACKUP AND RECOVERY: AN INSIDER'S PERSPECTIVE
4. Soln# 2058869.6 BULLETIN
5. Prob# 1006268.6 MOVING ORACLE_HOME
6. Soln# 2048518.6 MOVING ORACLE_HOME
7. Bul# 106373.655 CHANGING DBNAME OR ORACLE_SID
8. Bul# 102543.6 MOVING A DATABASE
9. Prob# 1018231.6 STEPS TO MANUALLY CREATE A 7.3 DATABASE
10. Soln# 2066506.6 STEPS TO MANUALLY CREATE A 7.3 DATABASE
1. Prob# 1018477.6 EXPORTING TO TAPE ON UNIX SYSTEMS
Problem ID : 1018477.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.XX
Affected Oracle Vsn : V07.XX
Summary:
EXPORTING TO TAPE ON UNIX SYSTEMS
+=+
Problem Description:
====================
The basic function of the export utility is to extract the objects of a
database and locate them in a flat file that is usually on tape or on disk.
The process for exporting to tape is explained in this bulletin.
Problem Explanation:
====================
This bulletin examines several considerations and steps for exporting data to
tape on unix platforms either directly or using a unix named pipe. Several
items are covered including:
* Why do you need to export to tape?
* How do you calculate the size of your export?
* Exporting directly to tape. (An example is provided.)
* How do you export to tape via unix named pipes.
* How do you create a compressed export file?
* How do you export to a remote tape device?
* How do you perform export/import over the network using named
pipes?
+==+
Diagnostics and References:
2. Soln# 2066781.6 HOW TO EXPORT TO TAPE ON UNIX SYSTEMS
Solution ID : 2066781.6
For Problem : 1018477.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.XX
Affected Oracle Vsn : V07.XX
Summary:
HOW TO EXPORT TO TAPE ON UNIX SYSTEMS
+=+
EXPORTING TO TAPE ON UNIX PLATFORMS
==============================================================================
In this document you will find instructions on how to perform an export to
tape on UNIX platforms either directly or by using unix named pipes.
It will discuss in detail the following considerations:
o Why do you need to export to tape?
o How do you calculate the size of your export?
o Exporting directly to tape. (An example is provided.)
o How do you export to tape via unix named pipes?
o How do you create a compressed export file?
o How do you export to a remote tape device?
o How do you perform export/import over the network using named
pipes?
It should be noted that this document is based on the work of Khaled Kassis,
Oracle US and John Benford, Oracle UK.
==============================================================================
==============================================================================
o Why do you need to export to a tape ?
Export to tape is not a preferred practice, but it may be necessary
for the following reasons:
o Lack of disk space - you do not have enough disk space to perform
the export to a disk.
o If your export file will be greater than 2 Gigabytes - There is a
unix restriction of 2G on the size of a file.
-----------------------------------------------------------------------------
o How to calculate the size of your export?
If the site is unsure how large a resultant export file will be, they
can use the following commands to calculate its size:
(1) Create a unix named pipe:
% mknod /tmp/exp_pipe p
(2) Start the export in the background, specifying the named pipe as
the output file:
% exp file=/tmp/exp_pipe <other options> &
(3) Next, dd in from the named pipe, out to /dev/null in 1K blocks:
% dd if=/tmp/exp_pipe of=/dev/null bs=1024
(4) This will return the size of the export file in 1K blocks as
follows:
<no. of 1K blocks>+0 records in
<no. of 1K blocks>+0 records out
------------------------------------------------------------------------------
o Exporting directly to tape.
Once you have decided that you need to perform the export directly to
tape, you will need to change the syntax of your export statement so
that export knows the name of the tape device and how much data can be
written to that tape device.
There are only 2 parameters that you need to change to do this:
- FILE - The name of the tape device you are exporting to
e.g. /dev/rmt/0 .
- VOLSIZE - The amount of data that can be written to one tape.
Considerations:
- If the entire export file will fit onto a single tape, then a
volsize of 0 (variable length) can be used
(e.g. exp <other options> volsize=0)
- If the resultant export file is larger than a single tape, then
volsize needs to be set accordingly
(e.g. exp <other options> volsize=<size>M)
Please note, there is a 4 Gigabyte limit to the volsize parameter.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AN EXAMPLE OF EXPORTING DIRECTLY TO TAPE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- This example involves a full database export to a QIC 150 (150 Megabytes
capacity) to a tape device "/dev/rmt/0m", where the resultant file is 200M.
The following syntax is used:
(1) os> exp userid=scott/tiger full=y file=/dev/rmt/0M volsize=145M
- You will note that the volsize is set to 145M, even though the tape is a
QIC 150. This is because there may not be exactly 150M of tape on the
volume.
- Once the export utility has written <volsize> to the tape, it will
prompt for the next tape:
(2) "Please mount the next volume and press <ret> when you are done."
- To import from the resultant export on tape, the following commands
are used :
(3) os> imp userid=scott/tiger full=y file=/dev/rmt/0M volsize=145M
------------------------------------------------------------------------------
o How do you export to tape via unix named pipes?
On some Oracle platforms/versions there may be difficulties with exporting
directly to tape using the Oracle export utility. In these cases, you may
need to perform the export to tape via unix named pipes.
A unix named pipe is a FIFO special file, created using the unix mknod
command. The syntax of the mknod command may change from port to port. It is
important, therefore, that you consult your systems manual.
Please note: Exporting via a named pipe (to tape or disk), is slower
than using export directly. This is because of the size limit
of a unix named pipe (usually 8K).
- The following commands should be used to export to tape via a named pipe:
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. Next, dd in from the named pipe, out to the tape device, in the
background :
os> dd if=/tmp/exp_pipe of=<tape device> &
3. Start the export, specifying the named pipe as the output file:
os> exp file=/tmp/exp_pipe <other options>
- To import from the resultant export on tape, the following commands
are used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. Next, dd in from the tape device, out to the named pipe, in the
background :
os> dd if=<tape device> of=/tmp/imp_pipe &
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
------------------------------------------------------------------------------
o How do you create a compressed export file?
If you have calculated the size of the file your export will produce and
it is too large to fit onto disk, you may want to consider producing a
compressed export file as an alternative to exporting directly to tape.
Please note, this method should be thoroughly tested before being implemented.
- To create a compressed export file:
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. Start compress in the background reading in from the named pipe,
writing out to 'export.dmp.Z' :
os> compress < /tmp/exp_pipe > export.dmp.Z &
3. Start the export, specifying the named pipe as the output file :
os> exp file=/tmp/exp_pipe <other options>
- To import from the resultant export on tape, the following commands
would be used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. Start uncompress in the background reading from 'export.dmp.Z',
writing out to the named pipe :
os> uncompress < export.dmp.Z > /tmp/imp_pipe
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
------------------------------------------------------------------------------
o How do you export to a remote tape device?
You may want to perform an export directly to tape but you do not
have a local tape drive on your machine. There is, however, a tape drive
on another machine on the network, to which you have remote shell (rsh)
access.
- The following commands allow you to perform the export to the remote
machine, either to file or to a tape device :
1. Create a unix named pipe :
os> mknod /tmp/exp_pipe p
2. Next, dd in from the named pipe, and out to the remote tape device
via a remote shell :
os> dd if=/tmp/exp_pipe | rsh <hostname> dd of=<file
or device> &
3. Start the export, specifying named pipe as the output file :
os> exp file=/tmp/exp_pipe <other options>
- To import from the resultant export on tape, the following commands
would be used :
1. Create a unix named pipe :
os> mknod /tmp/imp_pipe p
2. Start a remote shell, in the background that dd's from the remote
tape, piping to the local named pipe :
os> rsh <hostname> dd if=<file or device> | dd
if=/tmp/imp_pipe &
3. Start the import, specifying the named pipe as the input file :
os> imp file=/tmp/imp_pipe <other options>
------------------------------------------------------------------------------
o How do you perform import/export over the network using named pipes?
----------
- Host 1:
----------
* mknod -p /dev/FIFO.dmp
* exp u/p FILE=/dev/FIFO.dmp yada, yada, yada...
----------
- Host 2:
----------
* mknod -p /dev/FIFO2.dmp
* rsh host1 dd if=/dev/FIFO.dmp > /dev/FIFO2.dmp &
* imp u/p FILE=/dev/FIFO2.dmp yada, yada, yada...
Please note:
It is important that you verify that the named pipe is ready on each side
before you start the process.
=================================
Additional information
=================================
For more information on the RDBMS export/import utility:
* Oracle7 Server Utility User's Guide, Part 1
+==+
References:
3. Prob# 1010840.6 ORACLE BACKUP AND RECOVERY: AN INSIDER'S PERSPECTIVE
Problem ID : 1010840.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS Generic
Affected Oracle Vsn : Generic
Summary:
ORACLE BACKUP AND RECOVERY: AN INSIDER'S PERSPECTIVE
+=+
Problem Description:
====================
The most critical issue to keeping a production system alive is to have a
solid backup strategy. You have to be prepared for multiple problems that can
cause the database to go down: media, hardware, or software failure. A good
backup strategy will save you from losing data, ensuring integrity and minimal
down time.
Problem Explanation:
====================
Bulletin 106910.344 by VODDIRAJ discusses different types of backups and
different recovery options. The bulletin discusses physical backups and not
logical backups. Physical backups are hot backup, cold backup. A logical
backup is export.
+==+
Diagnostics and References:
* {6165.6,Y,100} STRATEGIES ON BACKUP AND RECOVERY
* {6166.6,Y,100} HOT BACKUPS AND COLD BACKUPS
4. Soln# 2058869.6 BULLETIN
Solution ID : 2058869.6
For Problem : 1010840.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS Generic
Affected Oracle Vsn : Generic
Summary:
BULLETIN
+=+
Solution Description:
=====================
Document ID: 106910.344
Title: Oracle Backup and Recovery: An Insider's Perspective
Creation Date: 2-January-1992
Last Revision Date: 2-January-1992
Revision Number: 0
Product: RDBMS
Product Version: 6.0
Platform: GENERIC
Information Type: ADVISORY
Impact: MEDIUM
Abstract: This paper discusses backup and recovery of the Oracle
database. It covers the different types of backups and
different recovery options. This paper discusses only
physical backups and not logical backups. An export of
the database is an example of a logical backup.
Keywords: BACKUP;RECOVERY;MEDIA;INSTANCE
_____________________________________________________________________________
ORACLE BACKUP AND RECOVERY: AN INSIDER'S PERSPECTIVE
INTRODUCTION
============
Why do we need backups?
======================
The most important responsibility of a DBA is to prepare for the
possibility of media, hardware and software failure. Should any of
these failures occur the major goal is to make the database available
to users within an acceptable time, ensuring that committed data is
undamaged.
This paper discusses only physical backups and not logical backups.
An export of the database is an example of a logical backup.
What are the things that go wrong and eventually lead to recovery?
=================================================================
One or more database files damaged.
One or more Redo log files (including online) damaged.
One or all control files damaged.
What is recovery?
================
Restoring the files from backup and rolling forward in time is RECOVERY.
AN INTERNAL VIEW
================
In order to understand the BACKUP AND RECOVERY procedures and to get
a good indepth knowledge of internal data structures, the DBA
should know what is stored in the control file, datafiles and log
files.
Control file
============
Control file reflects the structure of a database at particular point
in time. It contains the checkpoint information, names of log files
and data files, header information of the files and log sequence number
which is very important for recovery purposes. The recovery is done
only by applying the log files whose sequence number is greater than
log sequence number in the control file.
Datafile information in control file:
------------------------------------
- Names of datafiles and log files with exact path.
- File size.
- Block size.(Oracle block size)
- Whether the datafile is online or offline.
- Whether the datafile was taken offline automatically or not.
- Whether the datafile belongs to the system tablespace or not.
- Entry for each datafile giving a log sequence number when tablespace
was taken offline.
Log file information in control file:
------------------------------------
- Name with exact path.
- File size.
- Block size. (O/S block size)
- Log sequence#
- Has the file been archived.
Information in the datafile header
==================================
- Log sequence number of next log file that could be applied.
- Whether online backup in progress.
Information in the log file header
==================================
- Log sequence#
- Archival information.
BACKUP
======
Offline Backup
==============
A backup taken when the database is shut down normally is known as
offline or a cold backup. Copying of the datafiles, control file and
online redo log files must be done by using an operating system
copy utility. This is a considered as a complete backup of the
database. Any changes made after this backup will be unrecoverable if
the database is running in NOARCHIVELOG mode. All transactions are
recorded in online redo log files whether archiving or not. When redo
logs are archived (ARCHIVELOG mode), ORACLE allows you to apply these
transactions after restoring files that were damaged (assuming an
active Redo log file was not among the files damaged).
Whenever the schema of the database is changed i.e., a new datafile is
added or a file is renamed or a tablespace is created or dropped,
shutdown the database and at least make a copy of the control file and
the newly added datafile. A complete backup of the database is
preferred.
Online Backup
=============
At sites where database must operate 24 hours per day and when it is
not feasible to take offline backups, then an alternative is provided
by ORACLE RDBMS to perform physical backups while the database remains
available for both reading and updating. For this kind of backup the
database must be in ARCHIVELOG mode. Only data files and current
control file need to be backed up. Unlike offline backups, the unit of
a online backup is tablespace, and any or all tablespaces can backed
up whenever needed. Different datafiles can be backed up at different
times.
Procedure
---------
ALTER TABLESPACE ts_name BEGIN BACKUP
Then perform an operating system backup of all datafiles in that
tablespace. Once the backup is completed then it is very important
to issue the command:
ALTER TABLESPACE ts_name END BACKUP
All this must be done while the database is open.
Frequency of online backups
---------------------------
In order to determine how frequently to back up the files of a
database, balance the amount of time available for taking backups
and the time available for recovery after media failure. The time for
recovery depends on how old your most recent copy of the damaged file
is. The older your backup, the more redo log files need to be applied,
and the longer recovery will take.
Backup strategies should be tested before being used to protect a
production database. Ensure that backups of all datafiles and of all
necessary redo logs are kept, and that backups are restored correctly.
(If file compression is used, verify that the file is correct after
decompression.)
What happens between BEGIN BACKUP and END BACKUP?
------------------------------------------------
Once the ALTER TABLESPACE ts_name BEGIN BACKUP is issued the status
in the datafile header is changed to indicate that the datafile is
being backed up. Oracle stops recording the occurrence of checkpoints
in the header of the database files. This means that when a database
file is restored, it will have knowledge of the most recent checkpoint
that occurred BEFORE the backup, not any that occurred during the
backup. This way, the system will ask for the appropriate set of redo
log files to apply should recovery be needed. Since vital information
needed for recovery is recorded in the Redo logs, these REDO LOGS are
considered as part of the backup. Hence, while backing up the database
in this way the database must be in ARCHIVELOG mode. Status in the
datafile header is not reset until END BACKUP is issued.
On END BACKUP, the system again begins noting the occurrence of the
checkpoints in each file of the database. The checkpoint in the
datafile header is changed during the next log switch after END BACKUP
is issued. The above information will allow the tablespace to be
recovered as if the database had been offline when the backup took
place.
Critical Files
==============
All the files belonging the database are important. Along with
other tablespaces, special care should be taken to ensure that the
SYSTEM tablespace and tablespaces containing rollback segments, are
protected by backups. Also backup the control file and datafile
immediately after adding it to a tablespace or after creating
tablespace if archiving is enabled. If media failure damages a datafile
that has not been backed up, recovering it's tablespace is not
possible. After backing up the newly added datafile, include it in the
regular datafile backup rotation.
RECOVERY
========
Instance Failure
================
Instance failure is a hardware, software, or system failure that
prevents an instance from continuing work. It can be caused by
a CPU failure, an operating system failure, a power outage, failure
of one of the ORACLE background processes or a failure to access a
required database file when the file is not lost or damaged.
Instance Recovery
=================
Instance recovery is automatic. Restarting the database performs the
instance recovery. It involves two steps.
1) Rolling forward.
- data that has not been recorded in the database
- the contents of rollback segments
2) Rolling back transactions that have been explicitly rolled
back or have not been committed.
3) Releasing any resources held by transactions in process at the
time of the failure.
4) Instance recovery is not necessary if the database is shutdown
normally.
Media failure
=============
Media failure is a hardware, software, or system failure that prevents
reading or writing to files that are required to operate the database.
Media failure is a failure caused by the loss of the control file,
database files or redo log file.
Media Recovery
==============
What is needed for media recovery?
---------------------------------
The database must be operating in ARCHIVELOG mode. In addition, you must
have the latest backup of database, all online redo log files, archived logs,
current control file.
Performing Recovery
-------------------
You can use different commands to recover your database, they are:
1) RECOVER DATABASE
This command is only used with a current control file. Database must
be mounted, but not OPEN. The control file is compared with the
datafile header and brings the datafiles up to date by applying
archived redo logs to make the control file entries match the datafile
header. Online redo logs are applied to make the datafiles current.
Once the recovery is complete open the database with:
ALTER DATABASE OPEN
2) RECOVER DATAFILE <file>
This command is used when database is up and can't be brought down.
Can also be used when the database is in mounted state. The tablespace
which contains these datafiles must be taken offline.
Issue RECOVER DATAFILE <file>,<file> command. You will be
prompted for log files. The changes will be applied only to these
files.
Once the media recovery is complete the tablespace can be brought
online. Allows 'multi-tasking' recovery. Different datafiles can be recovered
parallelly using different sessions or terminals. Very useful when
there are several datafiles to be recovered.
3) RECOVER TABLESPACE
Tablespace must be offline. Database must be in OPEN state.
Recovers a single tablespace to the current state. This command can't
be used on SYSTEM tablespace or a tablespace which has rollback
segments having a status "in use".
If having database OPEN is not an issue, can recover using standard
recovery (RECOVER DATABASE)
4) RECOVER DATABASE MANUAL
Manual recovery after media failure enables you to control how
many redo log files to apply to the database. This can used to undo
an inadvertent change to the database by stopping recovery before the
change took place. MANUAL option needed for recovery with a control
file backup (old copy) and current control file is not available.
Database must be MOUNTed but not OPEN. After MOUNTing the database
connect internal and issue RECOVER DATABASE MANUAL command. Then you
will be prompted beginning with the earliest redo log file recorded in
the header of each database file. The recovery process will continue to
prompt for redo log files until CANCEL is typed when prompted for the
next redo log file. Recovery can be cancelled at any time of any redo
log.
5) RECOVER DATABASE UNTIL <time>
Is same as RECOVER DATABASE MANUAL except the granularity is recovery
is stopped at a specified in time within a log file.
Cannot be used for recovery with an old copy(backup) of control file.
6) RECOVER DATABASE MANUAL UNTIL <time>
Can be used for recovery with an old copy(backup) of control file.
Everything else is similar as RECOVER DATABASE UNTIL.
Opening the Database
--------------------
As security measure before starting the recovery backup datafiles,
online logs, and control file. If space is a constraint then atleast
backup the online logs and control files.
Open the database with: ALTER DATABASE OPEN [NO]RESETLOGS
Resetlogs
Before using RESETLOGS option take a cold backup of the database. Once
RESETLOGS is used then the redo log files can't be used at all.
The RESETLOGS clears all the online redo logs and modifies all the
online data files to indicate no recovery is needed. After resetting
the redo logs none of the existing log files or data file backups can
be used. In the control file log sequence number is modified, which is
very important for recovery purposes. The recovery will be applied only
to the log files whose sequence number is greater than log sequence
number in the control file. One has to be very cautious when using
RESETLOGS option. One more important factor to remember is that all
datafiles must be online otherwise they will become useless once the
database up.
Noresetlogs
The NORESTLOGS option will allow to apply the online redo logs and
will not clear the redo log files during startup. Leaves online logs
intact. Only used in scenario where MANUAL RECOVERY started and
CANCELled, and then RECOVER DATABASE is started.
After Recovery
--------------
Take a offline BACKUP after recovering the database using either of
the above options.
CONCLUSION
==========
Oracle provides a Database Administrator with many recovery
options to recover from various types of failure. Each option is
dependent upon the failure and the available backup files. Hence, good
backup strategy is essential for recovery.
-----------------------------------------------------------------------------
Oracle WorldWide Customer Support
Solution Explanation:
=====================
+==+
References:
5. Prob# 1006268.6 MOVING ORACLE_HOME
Problem ID : 1006268.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.00.XX
RDBMS V07.10.XX
Affected Oracle Vsn : Generic
Summary:
MOVING ORACLE_HOME
+=+
Problem Description:
====================
This bulletin lists the actions to be taken to move ORACLE_HOME to another
place on the same machine or to a different machine altogether.
Search Words: change, control files, alter database, ORACLE HOME, ORA-9782,
ORA-205, ORA-0205
+==+
Diagnostics and References:
* {3254.6,Y,100} MOVING ORACLE_HOME
* {3255.6,Y,100} ORA-09782: SFIFI: ANOTHER INSTANCE HAS THE SAME DATABASE MOUNTED
* {3256.6,Y,100} ORA-00205: ERROR IN IDENTIFYING CONTROL FILE '%S
ref: {7806.6} BUL-102543.6
/A>
. Soln# 2048518.6 MOVING ORACLE_HOME
Solution ID : 2048518.6
For Problem : 1006268.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.00.XX
RDBMS V07.10.XX
Affected Oracle Vsn : Generic
Summary:
MOVING ORACLE_HOME
+=+
Solution Description:
=====================
This information is also present in RTSS Bulletin #106828.344
Introduction
------------
A working Oracle installation consists of 3 major parts:
(a) Oracle software
(b) the database, which includes the data, control and the log files
(c) correctly configured configuration files, environment variables,
and system files
Thus, if one decides to move an Oracle installation from one file system to
another or from one machine to another, one has to appropriately move and
reconfigure all the above components.
The following discussion details the issues to be kept in mind when one
decides to move the complete Oracle installation.
Some basic assumptions
----------------------
1. If you are moving the oracle instance to another machine, it is assumed
for the purpose of this discussion that the operating system on the
new machine is binary compatible to the original OS. Note that different
versions of the operating system from the same vendor might not be
completely binary compatible.
2. The Oracle instance was completely and normally shutdown before any
actions to copy/move the installation were taken.
(a) Moving the Oracle Software
------------------------------
Following are some of the possible ways an installation of Oracle software
may be moved to a new location:
1. Changing the name of a directory that is in the ORACLE_HOME path.
2. Moving to a completely different file system on the same machine.
3. Moving to a completely different file system on a new machine.
In cases (2) and (3) above, the files on the Oracle distribution need to be
copied to the new location. One can use a recursive copy, remote copy, TAR,
CPIO or File Transfer Protocol (ftp) in BINARY mode for this purpose.
It is good idea to compare filesizes (using ls) and checksums (using sum)
after the copy to the remote location has been accomplished.
(b) Moving the Oracle Database
------------------------------
The Oracle database maintains a complete listing of the location of various
database and log files that come into play for RDBMS operations. These entries
are maintained in the Control file. Thus, a complete rehash of these entries
pointing to the new locations is critical to achieve normal RDBMS operations.
Following are the actions to be taken to appropriately change the entries
in the control file:
On the original instance, do:
$ sqldba
SQLDBA> connect internal;
SQLDBA> spool filenames.txt;
SQLDBA> select * from dba_data_files;
SQLDBA> select * from v$logfile;
The list of datafiles and logfiles for all the tablespaces should be in
'filenames.txt'.
On the new instance, do:
$ sqldba
SQLDBA> connect internal;
SQLDBA> startup mount [DBNAME];
SQLDBA> ALTER DATABASE RENAME FILE '<old path>/filename' to
'<new path>/filename';
Perform the ALTER DATABASE RENAME FILE for ALL the datafiles and logfiles
mentioned in 'filenames.txt'.
Also refer to bulletins on "Moving a Database" (RTSS Bulletin 102543.6)
and "Changing DBNAME or ORACLE_SID" (RTSS Bulletin 106373.655). Ask Oracle
WorldWide Customer Support for more details.
NOTE: Just in case your control files need to be re-created at the new
ORACLE_HOME because of corruption, it is a good idea to create a
trace file that includes the CREATE CONTROLFILE statement. This
trace file will come in handy if your old instance cannot be brought
up anymore.
To create the trace file:
1. sqldba
2. SQLDBA> connect internal
3. SQLDBA> alter database backup controlfile to trace
4. Change directory to <user_dump_dest>
5. "ls -lat" to see the latest trace file
6. Remove the timestamp and version lines from the top of the file
7. Remove the RECOVER statement from the file
Now, you have a script ready to recreate the control files in case
there is a need to do so.
(c) Reconfiguring all other Oracle and System-based settings
------------------------------------------------------------
1. If the new ORACLE_HOME is on the same machine, make sure the entry in
the oratab (/etc/oratab or /var/opt/oracle/oratab) file is correctly
updated. If you are moving to a new machine, either copy the file over or
create a new one. Again make sure that the ORACLE_HOME entry is correct.
If you change the SID instance, make sure to also change the ORACLE_SID.
NOTE: Please make sure that the ORACLE_HOME you specify contains no soft
links to other directories or file systems. Please specify the
entire ABSOLUTE path to ORACLE in ALL places.
2. Configure your initSID.ora and configSID.ora file to reflect the new
location of parameter, control, log and dump file destinations. Following
are some of the parameters to be updated. Others may need to be changed
depending on the way your system is configured:
initSID.ora: ifile
configSID.ora: control_files
background_dump_dest
log_archive_dest
db_name (if applicable)
Refer to your Oracle Server Administrator's Guide for more details.
3. If you are changing systems, make sure that the Shared Memory and Semaphore
parameters for the UNIX kernel are configured correctly. Refer to the
Oracle Installation and Configuration Guide (ICG) or the System Admin's
Guide for more details. Also make sure you have enough swap space
configured on the new system. Usually, swap space should be 2.5 to 3 times
the RAM on your system.
4. In case you are using the ORACLE_BASE (OFA) structure, you will need to
create a symbolic link (using ln), $ORACLE_HOME/dbs/initSID.ora, pointing
to the location of initSID.ora in the OFA structure, usually
$ORACLE_BASE/admin/<DB_NAME>/pfile/initSID.ora.
5. If you move to a new machine and intend to automate the instance startup
and shutdown when your machine comes up/down respectively, you will need to
configure the system startup/shutdown to process to include the new
Oracle instances. Refer to bulletin on "Automatic startup and shutdown of
Oracle database instances" (Bulletin 105870.898).
6. Appropriately reset the following environment variables in the .profile
or .login files of the various users:
$ORACLE_HOME
$ORACLE_SID (if applicable)
$FORMS30PATH
$ORAKITPATH
$TNS_ADMIN
Refer to bulletin on "Oracle Environment Variables on Unix" (103795.387)
for more details.
7. If your $ORACLE_HOME/rdbms/lib/sysliblist has references to a library using
an absolute path, this reference might need to be changed.
8. If your makefiles have references to a library, source or include file
using an absolute path, these references might need to be changed.
9. Make sure that the new file system does not have a NO-SUID flag set.
Most systems have these flags in the /etc/fstab file. Check your System
Administrator's Guide for more details.
10.Remove/rename the user defaults file for the Oracle installer. The file
is called 'usrdfl.log' and is usually in $ORACLE_HOME/orainst. The file
may contain references to sources of certain libraries, which may now
be invalid.
11.If you are changing systems, you will need to add entries for SQL*Net
listeners in the new /etc/services file.
12.If you are changing systems, make sure that the 'oracle' userid and the
'dba' group (or their equivalent) are created on the new system. If your
/etc/passwd and /etc/group file is yellow-paged, make sure the entries
are created on the *local* /etc/passwd and /etc/group respectively.
13.If you have written certain scripts and/or cron jobs that have ORACLE_HOME,
ORACLE_SID or any other environment variable that is due to change,
hardcoded in them, you will need to change those appropriately.
14.You will need to copy any scripts and/or cron jobs over to the new machine.
Problems To Look Out For
------------------------
1. Make sure there is enough disk space (using df) on the machine/filesystem
for all the software, control, data, log files etc. BEFORE you decide to
move the Oracle Installation. You can use 'du' to find out the disk
requirements of your Oracle installation.
2. In case you are changing machines, make sure enough swap space, shared
memory, semaphores have been configured. Refer to the ICG and the Sys.
Admin. Guide for your system.
3. ORA-9782: sfifi: another instance has the same database mounted
- Make sure that the original instance was shutdown normally, before the
copy to the new machine/filesystem was done.
- Make sure that the $ORACLE_HOME/dbs/sgadef<SID>.dbf file does not
exist if the database instance is down. If the file exists, simply
rename the file.
- Make sure that the environment variable $ORACLE_HOME, the ORACLE_HOME
in /etc/oratab (or /var/opt/oracle/oratab) and the ORACLE_HOME entry
in the control file (using strings <file>) is the same.
They should all be the same as the entry in the control files. If you
still get the above error after this step, make sure that there is
no symbolic link in the ORACLE_HOME path.
- If none of the above work for you, you may try to recreate the control
files. If you had not created the trace file to recreate the control
files, and if your previous instance can still be brought up, use the
"ALTER DATABASE BACKUP CONTROLFILE TO TRACE" command.
Proceed as follows:
- Bring up the original instance and do:
a) ALTER DATABASE BACKUP CONTROLFILE TO TRACE
b) Change directory to <user_dump_dest>
c) "ls -lat" to see the latest trace file
d) Edit the newly created file and comment out the lines from
e) the top of the file that list the version numbers and
timestamps.
f) Also comment out the instruction to RECOVER the database.
g) STARTUP NOMOUNT
h) Run the newly edited script. That should create the control
files afresh
If its not possible to bring up the old instance to create the trace
file, use CREATE CONTROLFILE to recreate the controlfile. You will
have to figure out the names and sizes of the log and data files.
4. ORA-205: "error in identifying control file '%s'"
Make sure that:
- All the control files are located where they should be.
- Control files are owned by the Oracle owner and the DBA group.
- Permissions are appropriate all the way to the control file(s). The
permissions for the control files themselves usually are 0640.
- Path to the controlfile is absolute and does not contain any soft links.
NOTE:
If you are moving the database from one machine to another via a tape backup,
and the target machine has the same OS version, file/directory permissions,
and Unix tree structure, there is no need to reinstall from distribution
media.
+==+
References:
7. Bul# 106373.655 CHANGING DBNAME OR ORACLE_SID
Document ID: 106373.655
Title: Changing dbname or ORACLE_SID
Creation Date: 07 September 1994
Last Revision Date: 07 September 1994
Revision Number: 0
Product: RDBMS
Product Version: 7
Platform: UNIX
Information Type: ADVISORY
Impact: MEDIUM
Abstract: This bulletin describes how to change the dbname for a
database, or the ORACLE_SID for an instance, without
having to recreate the database.
Keywords: SID;DBNAME;CHANGING;ORACLE_SID;INSTANCE;CONTROLFILE
-----------------------------------------------------------------------
Modifying a database to run under a new ORACLE_SID
--------------------------------------------------
1. Shutdown instance
2. Backup all control, redo and data files.
3. Go thru the .profile, .cshrc, .login, oratab, tnsnames.ora(for net v2),
and redefine the environment variable ORACLE_SID to a new value.
ie search thru disks and do a grep ORACLE_SID *
4. cd $ORACLE_HOME/dbs and rename the following files:
o init<sid>.ora (or use pfile to point to the init file.)
o control file(s) This is optional if you don't rename any of the
controlfiles, and the control_files parameter is
used. control_files would be set in the initSID.ora
file or in a file it references with the ifile
parameter. Make sure control_files doesn't point to
any old file names, if you renamed them.
o crdb<sid>.sql & crdb2<sid>.sql This is optional. These are
only used at database creation.
5. cd $ORACLE_HOME/rdbms/admin and rename the file:
o startup<sid>.sql This is optional.
(On some platforms, this file may be in $ORACLE_HOME/rdbms/install.)
Make sure the contents of this file do not reference old initSID.ora
files that have been renamed. This file simplifies the process to
"startup exclusive" your database.
6. To rename the database files and redo log files, you would follow the
instructions in the bulletin: 98863.723.
7. Change the ORACLE_SID environment variable to the new value.
8. start up database and verify it works. Once you have done this, shutdown
the database and take a final backup of all control, redo and data files.
9. When the instance is started, the control file gets updated with the
current ORACLE_SID.
=============================================================================
Changing the dbname for a database
----------------------------------
1. sqldba
2. connect internal
3. alter database backup controlfile to trace;
This will write in a trace file, the CREATE CONTROLFILE command that
would recreate the controlfile as it currently exists.
4. Exit and go to the directory where your trace files are located.
They are usually in the $ORACLE_HOME/rdbms/log directory.
If user_dump_dest is set in the initSID.ora, then go to the directory
listed in the user_dump_dest variable.
The trace file will have the form "ora_NNNN.trc with NNNN being a number.
5. Get the CREATE CONTROLFILE command from the trace file and put it in a
new file called something like ccf.sql.
6. Edit the ccf.sql file and modify the CREATE CONTROLFILE command.
Just change the word "REUSE" to "SET",and "NORESETLOGS" to "RESETLOGS",
and modify the dbname.
Old line:
CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...
New line:
CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...
Then save the ccf.sql file.
7. Rename the old control files for backup purposes and so they are not in
the way of creating the new ones.
8. Edit initSID.ora so that db_name="newdbname".
9. sqldba
10. connect internal
11. startup nomount
12. @ccf
13. alter database open;
14. Make sure the database is working. Shutdown and backup the database.
References
----------
ORACLE7 SERVER, SQL Language Reference Manual
________________________________________________________________________________
Oracle Worldwide Customer Support
8. Bul# 102543.6 MOVING A DATABASE
Moving a database RDBMS
Reem Adranly Version 6
October 1992
The 'ALTER DATABASE' command can be used to move all files associated with the
system tablespace, the redo log files, and files belonging to all non-system
tablespaces. This command can only be executed when the database is in a
'mounted' state. When using this statement to rename it is important that the
exact name of the file is used for the old-filename. It is best to spool this
information for reference before beginning this operation.
MOVING SYSTEM TABLESPACE/REDO LOG FILES/ENTIRE DATABASE:
=======================================================
1. Get exact fully qualified file name from data
dictionary view before shutting down the database
$ sqldba
connect internal
spool filenames.txt
select * from dba_data_files
select * from v$logfiles;
disconnect
shutdown
2. Mount database in exclusive mode (do not open it)
$ sqldba
startup mount DBNAME
3. Copy or move file to new location
Use an operating system copy command and copy the files into the new
location.
4. Rename file in database using the exact string taken
from dba_data_files
$ sqldba
connect internal
alter database
rename file '<old_path><filename>'
to '<new_path><filename>';
alter database open;
NOTE: Full syntax of the ALTER DATABASE can be found in the Sql*Language
reference manual, or in the dba guide. One statement can be issued to rename
more than one file.
MOVING A FILE OF NON-SYSTEM TABLESPACE:
======================================
The ALTER TABLESPACE command can be used to rename (or move) files of
non-system tablespaces. This command is useful if you wish the rest of the
database to remain accessible. Getting the fully qualified name for the file
is also important for this command.
1. Get exact fully qualified file name from data
dictionary view.
$ sqldba
connect internal
select * from dba_data_files where tablespace_name='<name_of_talbespace>';
2. Take the tablespace offline:
$ sqldba
connect internal
alter tablespace TABLESPACENAME offline;
3. Copy or move file to new location (use backup/delete
to move file)
Use an operating system copy command and copy the files into the new
location.
4. Rename file in database using the exact string taken
from dba_data_files in step 1.
$ sqldba
connect internal
alter tablespace TABLESPACENAME
rename datafile '<old_path><filename>'
to '<new_path><filename>;
alter tablespace TABLESPACENAME online;
disconnect
NOTE: Full syntax of the ALTER TABLESPACE can be found in the Sql*Language
reference manual, or in the dba guide. One statement can be issued to rename
more than one file.
================================================================================
RTSS Bulletin Board Bulletin #102543.6
9. Prob# 1018231.6 STEPS TO MANUALLY CREATE A 7.3 DATABASE
Problem ID : 1018231.6
Affected Platforms : Sun Solaris V2 Sparc
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.03.02.XX
Affected Oracle Vsn : V07.03.02.XX
Summary:
CREATE DATABASE FAILED AND COULD NOT CREATE INIT.<SID> ERRORS ON 7.3 INSTALL IL
+=+
Problem Description:
====================
Creating the database using ./orainst results in the following errors:
* create database failed
* could not create init<SID>.ora
Problem Explanation:
====================
You have encountered bug 357329. Creating a new database with
installer 4.0 requires an OFA compliant directory structure.
In a non-OFA compliant structure, you must manually create the
database.
Search words: manual, dbase, init.ora
+==+
Diagnostics and References:
* {2402.6,Y,100} CREATE DATABASE FAILS
* {2655.6,Y,100} INIT.ORA
* {8839.6,Y,100} INSTALLING RDBMS 7.3.2.X
ref: {7588.6} BUG-357329
10. Soln# 2066506.6 STEPS TO MANUALLY CREATE A 7.3 DATABASE
Solution ID : 2066506.6
For Problem : 1018231.6
Affected Platforms : Sun Solaris V2 Sparc
Affected Products : Oracle7 Server
Affected Components : RDBMS V07.03.02.XX
Affected Oracle Vsn : V07.03.02.XX
Summary:
STEPS TO MANUALLY CREATE A 7.3 DATABASE
+=+
Solution Description:
=====================
Create the database manually by doing the following:
1. Make a backup copy of your init(SID>.ora
$ cd $ORACLE_HOME/dbs
$ cp init.ora init<SID>.ora
2. Edit your init<SID>.ora as follows:
$ vi init<SID>.ora
Change the db_name parameter from "DEFAULT" to <SID>
3. Bring up server manager in line mode. Enter the following commands:
$ svrmgrl
SVRMGRL> connect internal
4. Now bring up the instance.
Note: Please make sure that your <$ORACLE_HOME> is the
absolute pathname.
SVRMGRL> startup nomount pfile=<$ORACLE_HOME>/dbs/init<SID>.ora
5. Issue the following commands:
Note: Please make sure that your <$ORACLE_HOME> is the
absolute pathname.
SVRMGRL> create database <SID>;
SVRMGRL> alter tablespace system add datafile 'name' size xx;
SVRMGRL> create rollback segment seg_name;
SVRMGRL> alter rollback segment seg_name online;
SVRMGRL> exit
% sqlplus system/manager
% @<$ORACLE_HOME>/sqlplus/admin/pupbld.sql
6. Run the catalog.sql and catproc.sql scripts. You must
be connected as internal.
Note: Please make sure that your <$ORACLE_HOME> is the
absolute pathname.
% svrmgrl
SVRMGRL> connect internal
SVRMGRL> @<$ORACLE_HOME>/rdbms/admin/catalog.sql
SVRMGRL> @<$ORACLE_HOME>/rdbms/admin/catproc.sql
+==+
References:
This is a copy of an article published @ http://www.oracle.com/