How can I recreate the OraInventory if it gets corrupted or removed?
Solution
In cases where the OraInventory is missing or otherwise corrupt, recreate the oraInventory directory on UNIX systems, using the following steps. In a normal installation, there is a Global Inventory (OraInventory) and a Local Inventory ($ORACLE_HOME/inventory).
Locate the oraInst.loc file, which may be in different locations, depending on your system:
/var/opt/oracle/oraInst.loc file or /etc/oraInst.loc
Important: Theses example uses a typical directory, considered an $ORACLE_BASE, and a typical UNIX group which installed the Oracle products. Ensure that the correct values are used for your system.
The oraInventory directory is usually a directory under the $ORACLE_HOME. For example, if the $ORACLE_HOME is equal to “/u01/oracle/product/10g”, then the OraInventory could be “/u01/oracle/OraInventory”.
Change the permissions to be appropriate, (using your directory location):
chmod 644 /var/opt/oracle/oraInst.loc
For consistency, copy the file to Oracle home directory, (using your directory location):
Run Oracle Universal Installer from your Oracle home as below, (using your site specific directory location and Oracle home name):
cd $ORACLE_HOME/oui/bin ./runInstaller -silent -attachHome ORACLE_HOME=”/u01/oracle/product/10.2″ ORACLE_HOME_NAME=”Ora10gHome”
Check the inventory output is correct for your Oracle home:
$ORACLE_HOME/OPatch/opatch lsinventory -detail
If the table at the beginning of the output is showing the proper directories, and the Oracle home components are properly reflected in the details, then the Global Inventory has been successfully created from the Local Inventory. At this time, you may patch an maintain your Oracle home, as normal.
It is always recommended to have the recovery catalog. If the target database controlfiles are lost recovery can become difficult if not impossible.Having recovery catalog makes the DBA life easier at the time of critical scenario. Even for larger system the use of a recovery catalog can increase the backup performance.
Recovery Catalog Schema can be created in the Target database or in any test or development database. It is not at all recommended to create the recovery catalog in the target database itself. Make sure to have a separate database for the recovery catalog always .Also its recommended to create the recovery catalog database in a different machine. If creating the Recovery catalog database in different machine is not possible then ensure that the recovery catalog and target databases do not reside on the same disk. If both your recovery catalog and your target database suffer hard disk failure, your recovery process is much more difficult. If possible, take other measures as well to eliminate common points of failure between your recovery catalog database and the databases you are backing up.
The recovery catalog contains information about RMAN operations, including:
+ Datafile and archived redo log backup sets and backup pieces + Datafile copies + Archived redo logs and their copies + Tablespaces and datafiles on the target database + Stored scripts, which are named user-created sequences of RMAN commands + Persistent RMAN configuration settings
How to create recovery catalog ?
Creating recovery catalog is a 3 step process .The recovery catalog is stored in the default tablespace of the recovery catalog schema. SYS cannot be the owner of the recovery catalog.
1. Creating the Recovery Catalog Owner 2. Creating the Recovery Catalog 3. Registering the target database
1. Creating the Recovery Catalog Owner
1.1 Size of recovery catalog schema :
Size of recovery catalog schema depends on
a) The number of databases monitored by the catalog.
b) The rate at which archived redo log generates in the target database
c) The number of backups for each target database
d) RMAN stored scripts stored in the catalog
1.2 Creating the Recovery Catalog Owner
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
% sqlplus '/ as sysdba'
SQL> CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE tools TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tools;
SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;
2. Creating the Recovery Catalog
log in to rman and create the catalog schema.Look at this example:
In the below example ” catdb ” is the catalog database connection string. Before creating the recovery catalog make sure to have the tnsnames.ora entry for the catalog database in the target server and the listener must be up and running in the catalog database server.You must be able to connect to the catalog database from sqlplus from the target server.
% rman catalog rman/rman @ catadb
RMAN> CREATE CATALOG;
3. Registering the target database
After making sure the recovery catalog database is open, connect RMAN to the target database and recovery catalog database and register the database . Make sure that your target database is either open or in Mount stage.Look at this example:
% rman TARGET / CATALOG rman/rman @ catdb
RMAN> REGISTER DATABASE;
RMAN creates rows in the catalog tables to contain information about the catalog database .Copy all the pertinent data from the controlfile into the catalog, synchronizing the catalog with the control file. You can register multiple target databases in a single recovery catalog, if they do not have duplicate DBIDs. RMAN uses the DBID to distinguish one database from another.
How to Upgrade recovery catalog Schema ?
When you upgrade target database to the latest version you need to upgrade the RMAN catalog schema.Connect to RMAN from the target database so that you can use, the target database’s RMAN executable. Look at the example :
% rman target / catalog rman/rman @ catdb RMAN> UPGRADE CATALOG; RMAN-06435: recovery catalog owner is rman RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> UPGRADE CATALOG;
Issuing ‘upgrade catalog’ will only upgrade the catalog schema to be compatible with the higher release of RMAN; it will not upgrade the catalog database in any way. You have to connect to recovery catalog database catdb and run “upgrade catalog” twice.
How to upgrade recovery catalog database ?
Upgrading the recovery catalog database is same as the any other database upgrade steps.Upgrading the catalog database do not upgrade the catalog database schema.
How to remove catalog ?
The “drop catalog;” command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation. Look at the example :
RMAN> DROP CATALOG;
How to unregister the target database from the recovery catalog ?
From 10G onwards, the process is simplified by introducing a new RMAN command to unregister the target database from the recovery catalog.Look at the example :
RMAN> UNREGISTER DATABASE <database_name> ;
The command “unregister database ” should be executed only at the RMAN prompt. This is a restriction to use this command.Also RMAN must be connected to the recovery catalog in which the target database is registered. ( Ref. Note 252800.1 )
Prior to release 10G, in order to unregister the target database you need to execute the following statement in the recovery catalog database connected as recovery catalog schema owner.Look at the example :
To unregister a database from the recovery catalog prior to Oracle 10g (Ref. Note 1058332.6).
How to backup of the Recovery Catalog ?
Recovery catalog database is just like any other database.This database backup need to be taken every time after the target database backup. You can take Physical backup or Logical backup of the catalog database.You can use RMAN for the backup of the recovery catalog database .
Few guideline for recovery catalog database
+ Run the recovery catalog database in ARCHIVELOG mode so that you can do point-in-time recovery if needed. + Set the retention policy to a REDUNDANCY value greater than 1. + Do not use another recovery catalog as the repository for the backups. + Configure the control file autobackup feature to ON.
How to restore and Recover recovery catalog from Backup ?
Restoring and recovering the recovery catalog is much like restoring and recovering any other database
Compatibility of the Recovery Catalog
When you use RMAN with a recovery catalog in an environment where you have run past versions of the database, you can wind up with versions of the RMAN client, recovery catalog database, recovery catalog schema, and target database that all originated in different releases of the database.
Here is a note which gives detailed information about the compatibility matrix
The schema version of the recovery catalog is stored in the recovery catalog itself. The information is important in case you maintain multiple databases of different versions in your production system, and need to determine whether the catalog schema version is usable with a specific target database version.
To determine schema version of recovery catalog connect to catalog database from the recover catalog user and then query RCVER table. Look at the example :
% sqlplus rman/rman @catdb
SQL > SELECT * FROM rcver;
VERSION ------------ 11.01.00
If the table displays multiple rows, then the highest version in the RCVER table is the current catalog schema version. The table stores only the major version numbers and not the patch numbers. For example, assume that the rcver table displays the following rows:
Use the utlxplan.sql script to create the table as instructed below.
SQL> @?/rdbms/admin/utlxplan
Note that the plan table format can change between versions so ensure that you create it using the utlxplan script from the current version.
2. To Populate the Plan Table ————————————–
SQL> explain plan for
Explained.
3. Displaying The Execution Plan ——————————————
Version 9.2 ————–
With Oracle 9i version 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
To generate a formatted explain plan of the query that has just been ‘explained’:
SQL> set lines 130 SQL> set head off SQL> spool SQL> alter session set cursor_sharing=EXACT; SQL> select plan_table_output from table(dbms_xplan.display(‘PLAN_TABLE’, Null,’ALL’)); SQL> spool off
Version 10.2 —————-
In addition to the standard explain plan option, you can pull execution plans from the library cache if the SQL has already been executed.
a) To get the plan of the last executed SQL issue the following:
SQL> set linesize 150 SQL> set pagesize 2000 SQL> select * from table(dbms_xplan.display_cursor(null,null, ‘ALL’));
b) If you know the hash value of the SQL, you can use dbms_xplan.display_cursor as follows:
SQL> set linesize 150 SQL> set pagesize 2000 SQL> select * from TABLE(dbms_xplan.display_cursor(‘&SQL_ID’, &CHILD));
c) We can also get run time statistics with some additional options and if we use the gather_plan_statistics hint.
e.g:
SQL> select /*+ gather_plan_statistics */ col1, col2 etc….. SQL> set linesize 150 SQL> set pagesize 2000 SQL> select * from TABLE(dbms_xplan.display_cursor(‘&SQL_ID’, &CHILD,’ALL IOSTATS LAST’))
sql_id: specifies the sql_id value for a specific SQL statement, as shown in V$SQL.SQL_ID, V$SESSION.SQL_ID or V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last executed statement of the current session is shown.
cursor_child_no: specifies the child number for a specific sql cursor, as in V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER. If not specified, all child cursors for the specified sql_id are displayed.
Note that to use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate error message.
Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases. Both these log files are stored inside the ADR home. The ADR root directory is known as ADR BASE. Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. This parameter is set by DIAGNOSTIC_DEST initialization parameter.
If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:
If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.
If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.
for e.g SQL> show parameter diagno
NAME TYPE VALUE ——————– ——- —————————————– diagnostic_dest string /u01/oracle/product/ora11g/log
The location of an ADR home is given by the following path, which starts at the ADR base directory:
ADR_BASE/diag/product_type/product_id/instance_id
For example, for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:
ADR_base/diag/rdbms/ora11g/ora11g/
Within the ADR home directory are subdirectories where the database instance stores diagnostic data.
alert ( The XML-formatted alert log )
trace ( Background and server process trace files and SQL trace files and text alert.logfiles )
cdump ( Core files )
XML formatted alert.log ——————————- The alert log is named log.xml and is stored in the alert subdirectory of ADR home. To get the log.xml path ADR_HOME/diag/product_type/product_id/instance_id/alert
from sqlplus SQL> select value from v$diag_info where name =’Diag Alert’; ADRCI utility to view a text version of the alert log (with XML tags stripped)
Text formatted alert.log ——————————–
The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.
To view the text only alert.log file ADR_HOME/diag/product_type/product_id/instance_id/trace
from sqlplus SQL> select value from v$diag_info where name =’Diag Trace’; or SQL>show parameter background_dump_dest
Open file alert_SID.log with a text editor
The alert log of a database is a chronological log of messages and errors, including the following:
All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
Administrative operations, such as the SQL statements CREATE ALTER DROP DATABASE TABLESPACE and Oracle Enterprise Manager or SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
Several messages and errors relating to the functions of shared server and dispatcher processes
Errors during the automatic refresh of a materialized view
Flashback Table happens in place by rolling back only the changes made to the table or tables and their dependent objects, such as indexes. Note that Flashback Table is different from Flashback Drop: Flashback Table undoes recent transactions to an existing table whereas Flashback Drop recovers a dropped table; Flashback Table uses data in the undo tablespace whereas Flashback Drop uses the recycle bin. The FLASHBACK TABLE command brings one or more tables back to a point in time before any number of logical corruptions have occurred on the tables. To be able to flashback a table, you must enable row movement for the table; because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for applications that depend on the table’s ROWIDs to remain constant. Before performing the Flashback Table operation, you first enable row movement in the affected tables, as in the following syntax:
SQL> alter table
enable row movement;
Limitations and Restrictions on Flashback Tables :
·Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
·The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
·Flashback Query :
Flashback Query, available in Oracle 9i, has been enhanced to include two new types of queries:
Flashback Versions Query and Flashback Transaction Query. Flashback Versions Query allows a user or the DBA to see all versions of a table’s row between two times, and with Flashback Transaction Query you can see all transactions that changed a row between two times.
Flashback Versions Query provides an easy way to show all versions of all rows in a table between two SCNs or time stamps, whether the rows were inserted, deleted, or updated. Even if a row was deleted and reinserted several times, all of these changes are available with Flashback Versions Query.
Flashback Transaction Query, in contrast, drills down into the history of table changes based on a transaction ID. Using Flashback Versions Query, you found out which transaction changed the salary information, but you don’t know who made the change. Flashback Transaction Query provides this additional level of detail.
In contrast to referencing the actual table in Flashback Versions Query, Flashback Transaction Query uses the data dictionary view FLASHBACK_TRANSACTION_QUERY to retrieve transaction information for all tables involved in a transaction. This view provides the SQL statements that you can use to undo the changes made by a particular transaction.
Flashback Query is Not available after restarting the database.
This Feature provides a way to restore accidentally dropped tables. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle places it in a recycle bin. Objects in the recycle bin remain there until the user decides to permanently remove them or until space limitations begin to occur on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users view the recycle bin and undrop the dropped table and its dependent objects. Flashback Drop is available even after restarting the database.
Limitations and Restrictions on Flashback Drop :
·The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.
·There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
·While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
·You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
·A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
·Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
·Partitioned index-organized tables are not protected by the recycle bin.
·The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
The New Oracle 10g Flashback Technologies Consist Of The Following :
Flashback Database
Flashback Drop
Flashback Table
Flashback Query
·Flashback Database :
The Flashback Database allows you to flash the entire database back to a specific point-in time. It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is not based on undo data but on flashback logs.
If flashback database is enabled, its flashback logs are stored in the Flash Recovery area. Flashback logs are written sequentially During normal database operation, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.
The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.
DB_FLASHBACK_RETENTION_TARGET:
A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.
The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.
The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
Limitations of Flashback Database :
Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:
·Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.
·You cannot use Flashback Database to undo a shrink datafile operation.
·If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
· When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.
If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
Before copying the database to a new location, it is necessary to perform a full cold backup of the database, whilst the database is shutdown. This will ensure that no data will be lost if the copying of the database is unsuccessful.
WARNING ------------
Creating a copy of a database involves usage of the CREATE CONTROLFILE command (explained below). If this command is not executed correctly it could corrupt the production database. This will mean that the original database will need to be restored from a backup.
1. OBTAIN DATABASE INFORMATION FROM CONTROLFILE -----------------------------------------------------------------------
In order to move the database, it is necessary to create a script containing information about the files of the database.
This is done by executing the following commands.
a. sqlplus /nolog
b. SQL> connect / as sysdba
c. ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
This will create a trace file in the trace file directory. The file will have the extension .trc, and is located either in the directory defined by the initialization parameter 'user_dump_dest', or, if this is not defined, in ORACLE_HOME/rdbms/log. The file should be copied to a name such as ccf<NEW_SID>.sql, where <NEW_SID> is to be the ORACLE_SID of the copied database.
2. IDENTIFY FILES TO BACKUP/COPY --------------------------------------------
a. Identify database and log files
The CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql can then be used to identify the various database files and redo log files that need to be backed up. The file names will be in single quotes and separated by commas after the words LOGFILE and DATAFILE, e.g:
CREATE CONTROLFILE REUSE DATABASE "FAMY" RESETLOGS ARCHIVELOG MAXLOGFILES 6 MAXLOGMEMBERS 2 MAXDATAFILES 10 MAXINSTANCES 1 MAXLOGHISTORY 100 LOGFILE GROUP 1 ( '/oracle/tberryha/fritz/log_disk1/famylog1v713.dbf', '/oracle/tberryha/fritz/log_disk2/famylog1v713.dbf' ) SIZE 50K, GROUP 2 ( '/oracle/tberryha/fritz/log_disk1/famylog2v713.dbf', '/oracle/tberryha/fritz/log_disk2/famylog2v713.dbf' ) SIZE 50K DATAFILE '/oracle/tberryha/fritz/fritz_system/famy_system01.dbf' SIZE 8M, '/oracle/tberryha/fritz/fritz_data/famy_data01.dbf' SIZE 20M, '/oracle/tberryha/fritz/fritz_rollback/famy_rollback01.dbf' SIZE 20M, '/oracle/tberryha/fritz/fritz_temp/famy_temp01.dbf' SIZE 20M, '/oracle/tberryha/fritz/fritz_data/famy_data02.dbf' SIZE 5M, '/oracle/d2/V7141/dbs/x' SIZE 1M ; It is also possible to obtain a listing of the files of the database by executing the following sql commands:
SQLPLUS username/password Note: The user must have sufficient privileges to be able to see the dba views 'sys.dba_data_files', 'sys.v$logfile' and in addition the database must be open.
SPOOL files.log
SQL> SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;
SQL> SELECT member FROM sys.v$logfile
SPOOL OFF Note: This will create a spool file called 'files.log' which will record the results of the previous commands.
b. Identify controlfiles
This can be done either by referring to the init<SID>.ora 'control_files' parameter, or from 7.0.16 onwards, the table sys.v$controlfile can be used to identify the controlfiles of the database via the following statement:
SPOOL control.log SELECT name FROM v$controlfile; SPOOL OFF
This will create a file called control.log in the current directory which will contain the names of the controlfiles for the database.
Shutdown instance via SHUTDOWN NORMAL, and then take full cold backup of:
a. All the files identified in step 2 above.
b. All parameter files. Note: the main parameter file will usually be called init<SID>.ora, in addition to which there may also be other parameter files. These will be identified by 'ifile' (included file) parameters in the init<SID>.ora. These additional parameter files are usually called config<SID>.ora.
4. MAKE A COPY OF THE DATABASE ------------------------------------------- Shutdown instance via SQLPlus, SHUTDOWN NORMAL.
Copy all parameter files, controlfiles, and all files noted in step 2 above to their new location taking care to preserve ownership and permissions. When the database has been copied, it will not be possible to use the same SID and therefore the ORACLE_SID environment variable must be changed to a new SID, and the copied init<SID>.ora must be renamed to init<NEW_SID>.ora, and any parameter files pointed to by an 'ifile' parameter (e.g. parameter files such as config<SID>.ora) should be renamed to incorporate the NEW_SID (i.e. config<NEW_SID>.ora).
5. SET UP PARAMETER FILES FOR THE COPIED DATABASE ---------------------------------------------- -------------------------
Edit the value of the control_files parameter in the init<NEW_SID>.ora to be the name and location that you want to use for the new control files. The controlfiles should be given a different name to distinguish them from the old database. In addition, change the DB_NAME parameter in the init<NEW_SID>.ora to be an appropriate name for the new database. Any 'ifile' parameters of the parameter file will need to be edited to point to the new name of the include file in the new location.Ensure that the parameter DB_BLOCK_SIZE is the same as the originating database, otherwise when creating the controlfile you may receive: ORA-01160 "file is not a %s"
6. PREPARE THE 'CREATE CONTROLFILE COMMAND' FOR THE COPIED DATABASE ------------------------------------------------------------------------------------------------------
In order to establish the new database in the new location, the CREATE CONTROLFILE command in the file ccf<NEW_SID>.sql should be executed. The following steps illustrate how CREATE CONTROLFILE command is prepared.
a. The file ccf<NEW_SID>.sql must be edited before use. The CREATE CONTROLFILE command will be preceded by a series of comments and a STARTUP NOMOUNT command. These need to be stripped out of the file. In addition, after the create controlfile command, there will be a number of comments and the commands RECOVER DATABASE and ALTER DATABASE OPEN, which should also be stripped out, leaving just the create controlfile
command itself.
b. The CREATE CONTROLFILE command itself should also be edited. Change the CREATE CONTROLFILE command in 'ccf<NEW_SID>.sql' to have the new database name, and add the word 'SET', e.g:
c. The CREATE CONTROLFILE command also specifies the files which make up the database, and these must also be changed to name the files of the new database in the new location, e.g:
LOGFILE GROUP 1 ( '/old_path/old_logfile_name1', '/old_path/old_logfile_name2' ) SIZE 50k would become: LOGFILE GROUP 1 ( '/new_path/new_logfile_name1', '/new_path/new_logfile_name2' ) SIZE 50k and DATAFILE '/old_path/old_file_name1' SIZE 5M, '/old_path/old_file_name2' SIZE 10M ; would become: DATAFILE '/new_path/new_file_name1' SIZE 5M, '/new_path/new_file_name2' SIZE 10M ;
d. After Recreating Controlfile
o Tablespace TEMP is available o DBA_TEMP_FILES does not show a tempfile
If using ALTER DATABASE BACKUP CONTROLFILE TO TRACE, to get a CREATE CONTROLFILE SCRIPT - the Datafile for Tablespace TEMP (TEMPFILE) is not listed in
"CREATE CONTROLFILE STATEMENT"
You must add the TEMPFILE manaually after Creating the Controlfile
> Alter database backup controlfile to trace ;
You should check Tracefile: (in udump) to get Statement on how to Add the Tempfile to temp Tablespace
--> see end of tracefile
EXAMPLE: ======== Create Controlfile ..... ... ; ... # Commands to add tempfiles to temporary tablespaces. # Online tempfiles have complete space information. # Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/U01/ORADATA/T920/TEMP01.DBF' SIZE 10485760 REUSE AUTOEXTEND OFF; # End of tempfile additions. #
Simply adjust this command (path/name) to your requirements and do for example:
7. CHECK REMOTE_LOGIN_PASSWORDFILE PARAMETER ---------------------------------------------------------------------- Prior to executing the create controlfile script, check whether the initialisation parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid passwordfile should exist in ORACLE_HOME/dbs or created using orapwd, otherwise you will receive an ora-600 [kzsrsdn:1].
8. EXECUTE THE 'CREATE CONTROLFILE' COMMAND FOR THE COPIED DATABASE ------------------------------------------------------------------------------------------------------ Having prepared the create controlfile script, it is now necessary to run the script from within the new instance. This is done by executing the following:
a. at the operating system prompt, change the value of the environment variable ORACLE_SID from OLD_SID to NEW_SID. b. SQLPlus c. CONNECT / AS SYSDBA SQL> STARTUP NOMOUNT PFILE=/<full path>/init<NEW_SID>.ora d. SQL> @ccf<NEW_SID>
Note: if any files which should be specified in the CREATE CONTROLFILE command are omitted, these files cannot be added to the new database at a later date. In addition, if any of the files specified in the CREATE CONTROLFILE command are NOT changed from their original names, then the corresponding files of the original database will become part of the copied database, and it will not be possible to restore them to the original database. If this happens, and if the files in question are important, this will mean that the original database will need to be restored from a backup.
When recreating the controlfile, it is possible to encounter ORA-1565, ORA-1503 and ORA-9782, "another instance has this database mounted", the ORACLE_SID of the original database is still in the header of the first datafile of the copy and oracle checks if an sgadef<ORACLE_SID>.dbf still exists in $ORACLE_HOME/dbs, to workaround this problem shutdown the other database of which you made a copy prior to issueing the create controlfile command.
NOTE: If you are using online 'hot' backup at this point you should issue:
recover database until cancel using backup controlfile;
Apply all the archives you have
e. SQL> ALTER DATABASE OPEN RESETLOGS
9.MAKE A FULL COLD BACKUP OF THE COPIED DATABASE ----------------------------------------- -------------------------------
SHUTDOWN and take a full cold backup of the database in the new location. The full cold backup can be done as detailed in steps 2 and 3.
