TECH SOLUTIONS: September 2013

Friday, September 27, 2013

Oracle Apps: Oracle Alerts

What are Oracle Alerts?
A: Oracle Alerts monitor your Database information and notify you when the condition that you have specified is found. You can define Alerts in any Oracle application or custom Oracle application. Some applications (Purchasing, for example) supply Alerts that Can simply be activated and used. There are two type of Alerts, Event and Periodic.

Event Based Alerts : These Alerts are fired/triggered based on some change in data in the database.

Ex: If u want to notify your manager when you create an item in the inventory you can use event based alerts. When you create an item in the inventory it will cretae a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert.In same alert you can also send the information related to that particular item

Periodic Alerts : These Alerts are triggered hourly,daily, weekly, monthly or yearly based on your input.

Ex: If you want to know list of items created on that day at the end of day you can use periodic alerts repeating periodically by single day.This alert is not based on any chages to database.this alert will notify you everyday regardless of data exists or not that means even if no items are created you wil get a blank notification.

Q: What types of actions can be generated when an Alert is triggered?
A: When an alert is triggered or the event is true, the alert can Email a Message, Submit a concurrent program request, Run a SQL statement Script or Run an operating system script.

Q: Can I build an Alert to run with my custom applications or tables?
A: Event or Periodic Alerts can work with any custom application, as long as the application is properly registered within the Oracle Applications package.

Q: Which Email packages work with Alerts?
A: Oracle Alert is designed to work with Oracle Office, Oracle Interoffice, UNIX Send mail, and VMS Mail.

Q: Can Alerts be triggered by other Tools? (i.e. other than Oracle Forms and concurrent programs)
A: Oracle Alerts can only be triggered from an application that has been registered in Oracle Applications. Alerts cannot be triggered via SQL updates or deletes to an Alert activated trigger.

Q: What is Response Processing?
A: Response processing is a component of Alerts which allows the recipients of an alert to reply with a message and have the applications take some action based on the response. Response Processing only works with Oracle Mail Products.

Q: Do I need Oracle Applications to use Alerts?
Answer——No. The following are the only components required to use Oracle Alerts. The components must be certified versions for your hardware platform and operating system.

-RDBMS
- SQL*PLUS
- FORMS
- ORACLE MAIL product (i.e. Oracle Office or InterOffice

Q: How to create an Alert?

1.Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
2. If you are going for periodic alert decide the frequency.
3. If you have chosen event based alert then find out on what event(insert,update) you want to fire the alert.
4. Decide what data need to be included in the alert.
5. Based on the data you want in the alert write a SELECT SQL statement to pull the data.
6. Create a distribution list grouping all the people to whom you want to send the alert.

Creating and Testing a Event Alert in Oracle EBS / Apps

In following example I will create an Alert which will send email to specific email ID when ever any body update or create new employee in Employee Master.

Defining an Alert :

1. Responsibility –> Alert Manager.

2. Naviate to the Alerts Window.

Alert –> Define

3. Define a Period Alert:

Application = Human Resources

Name = xxscc_test1

Enable = Selected.

4. Event

Application = Human Resources

Table = PER_ALL_PEOPLE_F

5. Enter the following SQL statement.

select employee_number

into &emp_numfrom

per_all_people_f where rowid=:rowid

6. Verify, to check whether the syntax written is correct.

7. Save

8. Define Alert Actions:

Action Name : send_email_notification

8. Define Action Details:

Click on Action Details Button

TO : Give you email ID here. Note : You can also put here also bind variable with &email if that taken in select statement.

Subject : Give Subject of you Email.

Text : hello this is test email &emp_num

9- Define Action Sets

Click on Action Sets Button.

Action Set Name = Send Email Test

Go to Action Set Details –> Members.

Action = send_email_notification as shown bellow.

10- Define Operating Unit

Click on Alert Details Button from first Alert Window.

Navigate to Installation Tab.

Operating Unit = for me it is FS_OU

11- Save.

Now if you Update or Create New Employee you will received notification in given email ID.

Oracle Database Upgrade – 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)

Oracle Database Upgrade - 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)

11.1.0.1.0

11 - Major database release number
1 - Database maintenance release number
0 - Application server release number
1 - Component specific release number
0 - Platform specific release number

http://www.oracle.com/technology/software/products/database/index.html

1. Make sure the source environment is in stable state:
    11.1.0.6 Startup Database
    Alert log location: $ORACLE_BASE/diag/rdbms/aitmr93w/aitmr93w/trace

2. Download the 11gR2 software into the stage from where it needs to be installed.

3. Oracle Database Pre-installation Requirements

Checking the Hardware Requirements:-

The system must meet the following minimum hardware requirements:

1. Memory Requirements
    1. At least 1 GB of RAM
    # grep MemTotal /proc/meminfo

    2. Determine the size of the configured swap space
    # grep SwapTotal /proc/meminfo

    The following table describes the relationship between installed RAM and the configured swap space requirement:
    |-------------------------------------------------------|
    |RAM                                     |   Swap Space                         |
    |-------------------------------------------------------|
    |Between 1 GB and 2 GB    |   1.5 times the size of RAM |
    |Between 2 GB and 16 GB |   Equal to the size of RAM |
    |More than 16 GB                |   16 GB                                   |
    |-------------------------------------------------------|

    3. To determine the available RAM and swap space, enter the following command:
    # free

2. System Architecture
    # uname -m

3. Disk Space Requirements
    1. At least 1 GB of disk space in the /tmp directory
    # df -h /tmp

    If the free disk space available in the /tmp directory is less than what is required,
    then complete one of the following steps:

    a) Delete unnecessary files from the /tmp directory to meet the disk space
    requirement.
    b) Set the TMP and TMPDIR environment variables when setting the oracle
    user’s environment.

    2. To determine the amount of free disk space on the system, enter the following command:
    # df -h

    The following tables describe the disk space requirements for software files,
    and data files for each installation type on Linux x86:

    Installation Type     Requirement for Software Files (GB)
    Enterprise Edition     3.95
    Standard Edition     3.88

    Installation Type     Disk Space for Data Files (GB)
    Enterprise Edition     1.7
    Standard Edition     1.5

Checking the Software Requirements:-

1. Operating System & Kernel Requirements
    Oracle Enterprise Linux 4 Update 7
    Oracle Enterprise Linux 5 Update 2

    To determine the distribution and version of Linux installed, enter the following command:
    # cat /proc/version
    Alternatively, you can also enter the following command on some distributions of Linux:
    # lsb_release -id

    Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4:
    2.6.9 or later
    Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5:
    2.6.18 or later

    # uname -r

2. Package Requirements
    On Linux x86: Package Requirements
    rpm -q binutils-2.15.92.0.2
    rpm -q compat-libstdc++-33-3.2.3
    rpm -q elfutils-libelf-0.97
    rpm -q elfutils-libelf-devel-0.97
    rpm -q gcc-3.4.6
    rpm -q gcc-c++-3.4.6
    rpm -q glibc-2.3.4-2.41
    rpm -q glibc-common-2.3.4
    rpm -q glibc-devel-2.3.4
    rpm -q glibc-headers-2.3.4
    rpm -q libaio-devel-0.3.105
    rpm -q libaio-0.3.105
    rpm -q libgcc-3.4.6
    rpm -q libstdc++-3.4.6
    rpm -q libstdc++-devel-3.4.6
    rpm -q make-3.80
    rpm -q numactl-0.6.4.i386
    rpm -q pdksh-5.2.14
    rpm -q sysstat-5.0.5
    rpm -q unixODBC-2.2.11
    rpm -q unixODBC-devel-2.2.11

3. Compiler Requirements
    Linux-PAM Library
    Install the latest Linux-PAM (Pluggable Authentication Modules for Linux) library to
    enable the system administrator to choose how applications authenticate users.

    Setting the ORACLE_HOSTNAME Environment Variable
    In Bourne, Bash, or Korn shell:
    $ ORACLE_HOSTNAME=somehost.us.example.com
    $ export ORACLE_HOSTNAME

4. Install 11gR2 (11.2.0.1) Software only

    4.1 Entry into the "oratab" ...
    /etc/oratab -- <ORACLE_SID>:<ORACLE_BASE>/db/11.1:N

    Note: Kindly use local inventory than central as we wont be having access to use central

    4.2 ./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc &
    Where the oraInst.loc should have the below entries
    [ For installation on ems6678 otherwise modify it accordingly]

    inst_group=dba;
    inventory_loc=$ORACLE_BASE/db/oraInventory

    Note: Click on "Ignore All" if there are any swap space failures

    Installation Log: $ORACLE_BASE/db/oraInventory/logs/installActions2010-05-06_11-55-10PM.log

5. Pre-Upgrade steps:

    5.1 Executing Pre-Upgrade Tool:
    Before you start the upgrade process, to identify these checks.
        5.1.1 Copy utlu112i.sql from 11gR2 OH i.e $ORACLE_BASE/db/11.2/rdbms/admin
        to $HOME [ Assuming 11gR2 is Installed $ORACLE_BASE/db/11.2 ]
        5.2.2 Login as SYSDBA
        SQL> SPOOL $HOME/UpgDir/upgrade_info.log
        SQL> @utlu112i.sql
        SQL> SPOOL OFF

    Review the log file and take necesary actions. Then continue with upgrade.

    5.2 Correcting the Warnings [if any] found in above step

        5.2.1 WARNING : Database is using an old time zone file version. After the upgrade,
        patch the database time zone file version using DBMS_DST package to record latest time zone file version.
        Sol: From Webiv Note: 815679.1
        * For 11.1.0.6 and 11.1.0.7, after the upgrade to 11.2.0.1
        use DBSM_DST to update to DSTv11 by following <<Note 977512.1>>
        Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST

        5.2.2 Database contains stale optimizer statistics.
        EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
        [OR]
        EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');

        5.2.3 WARNING: --> SYSTEM schema default tablespace has been altered.
        .... The SYSTEM schema default tablespace is currently set to TOOLS.
        .... Prior to upgrading your database please reset the
        .... SYSTEM schema default tablespace to SYSTEM using the command:
        .... ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
        5.2.4. WARNING:--> recycle bin in use.
        .... Your recycle bin turned on.
        .... It is REQUIRED
        .... that the recycle bin is empty prior to upgrading
        .... your database.
        .... The command: PURGE DBA_RECYCLEBIN
        .... must be executed immediately prior to executing your upgrade.

    5.3 Shutdown 11gR1 Database

    5.4 Configuring 11gR2 Instance
        5.4.1 Source 11gR2 instance
        5.4.2 Copy parameter file form pfile location to 11gR2/dbs
            Change Parameters
            ------------------------
            *.compatible='11.2.0'
            shared_pool_size increase to 298MB [as suggested -- 325058560]
            user_dump_dest        -- REMOVE
            background_dump_dest -- REMOVE
            core_dump_dest        -- REMOVE

6. Upgrading Database

    6.1 Prepare Upgrade
        6.1.1 cd $ORACLE_HOME/rdbms/admin
        6.1.2 Login as SYSDBA
            sqlplus '/ as sysdba'
            startup upgrade
            SPOOL upgrade.log
            @catupgrd.sql

7. Post-Upgrade steps:

    7.1 Post-Upgrade Tool
    Run utlu112s.sql to display the results of the upgrade:
        SQL> SPOOL $HOME/UpgDir/postupgrade_status.log
        SQL> @utlu112s.sql

        Oracle Database 11.2 Post-Upgrade Status Tool           05-07-2010 01:08:37
        Component                                Status         Version HH:MM:SS
        Oracle Server                            VALID      11.2.0.1.0 00:14:38
        Oracle Text                              VALID      11.2.0.1.0 00:00:36
        Gathering Statistics                                            00:02:59
        Total Upgrade Time: 00:18:15

    PL/SQL procedure successfully completed.

    7.2 Post-Upgrade Actions
    Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to
    perform upgrade actions that do not require the database to be in UPGRADE mode:
        SQL> SPOOL $HOME/UpgDir/postupgrade_status.log
        SQL> @catuppst.sql

    7.3 Compiling Invalid Objects:
    Compiling Ivalid objects:
        SQL> SPOOL $HOME/UpgDir/Compile_Invalid.log
        SQL> @utlrp.sql
        SQL> SPOOL OFF

    7.4 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) -- Webiv Note 977512.1
    Oracle 11.2.0.1 has by default all RDBMS DST updates from DSTv1 to DSTv11 included in the software installation.
    These files are found in $ORACLE_HOME/oracore/zoneinfo and have a prefix indicating the DST version.

        7.4.1 check current RDBMS DST version and "DST UPGRADE STATUS".
            conn / as sysdba
            SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
            FROM DATABASE_PROPERTIES
            WHERE PROPERTY_NAME LIKE 'DST_%'
            ORDER BY PROPERTY_NAME;

            Check that the output gives

            -- PROPERTY_NAME VALUE
            -- ------------------------------ ------------------------------
            -- DST_PRIMARY_TT_VERSION <the old DST version number>
            -- DST_SECONDARY_TT_VERSION 0
            -- DST_UPGRADE_STATE NONE

            -- DST_PRIMARY_TT_VERSION should match the value found when selecting

        7.4.2 SELECT version FROM v$timezone_file;
            VERSION
            ----------
            4

        7.4.3 Do the actual RDBMS DST version update of the database
            7.4.3.1 Change DB into Upgrade Mode
                conn / as sysdba
                shutdown immediate;
                startup upgrade;
                set serveroutput on
                purge dba_recyclebin;

            7.4.3.2 Clean used tables
                TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
                TRUNCATE TABLE sys.dst$affected_tables;
                TRUNCATE TABLE sys.dst$error_table;

            7.4.3.3 start upgrade window
                EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);
                SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

                SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
                FROM DATABASE_PROPERTIES
                WHERE PROPERTY_NAME LIKE 'DST_%'
                ORDER BY PROPERTY_NAME;

                SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

            7.4.3.4 Restart the Database
                shutdown immediate
                startup
                set serveroutput on

            7.4.3.5 Upgrade the tables who need action
                VAR numfail number
                BEGIN
                DBMS_DST.UPGRADE_DATABASE(:numfail,
                parallel => TRUE,
                log_errors => TRUE,
                log_errors_table => 'SYS.DST$ERROR_TABLE',
                log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
                error_on_overlap_time => FALSE,
                error_on_nonexisting_time => FALSE);
                DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
                END;
                /

                VAR fail number
                BEGIN
                DBMS_DST.END_UPGRADE(:fail);
                DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
                END;
                /

            7.4.3.6 Verify the Timezone Upgrade
                SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
                FROM DATABASE_PROPERTIES
                WHERE PROPERTY_NAME LIKE 'DST_%'
                ORDER BY PROPERTY_NAME;

                SELECT * FROM v$timezone_file;

8. Miscellaneous Database Checkups
    8.1 Database, Datafile and Backup

        select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;
        select * from v$recover_file;
        select distinct status,count(*) from v$datafile group by status;
        select name, RECOVER from v$datafile_header where RECOVER != 'NO';
        select name, ERROR from v$datafile_header where ERROR is NOT NULL;
        select name, ts# from v$datafile where upper(name) like '%MISSING%';
        select NAME, FILE# from v$datafile where status='RECOVER';
        select status,count(*) from v$backup group by status;

    8.2 DBA Registry

        set lines 150 pages 500
        column COMP_NAME format a45
        column version format a15
        column status format a12
        select comp_name,version,status from dba_registry;

    8.3 DBA Registry History

        set lines 150 pages 500
        column action format a15
        column namespace format a15
        column comments format a20 wrap
        column ACTION_TIME format a30
        col BUNDLE_SERIES format a12
        column version format a15
        select * from dba_registry_history;

Scheduling RMAN cold backup and copy controlfile

RMAN cold backup has its own advantage compare to the traditional cold backup: -

1)RMAN as we all know backup only used blocks so it is fast.Here entire Database I'm taking backup.

2)In my below script I'm copying the control file to have the latest copy with me in case of recovery.
This is because of below factors:
-------------------------------------------------------------------------

"copy current controlfile"
Personally I like and use this,Because of below reasons:

*restore after total loss.

I run this command as the *final* command of my database,
run {allocate channel c1 type disk; copy current controlfile to '/some OS location'.

Effectively,I'm backing up my "Backup Meta Data" at the end of each RMAN backup.
My RMAN shell scripts look like this:
--------------------------------------------

1)Backup database in mount State because rman expect Database to be in mount state for taking cold backup

2)copy current controlfile to some location
I'm not saying do not use these other options, just be sure we know ,What we are getting and whether we are able to recover the Database.Test your backups in competency servers and be sure that you can do restore & recovery in case of failure.

3)In my rman scripts,I've used
%U=To have unique backupset number
%T=Date of backup

4)Snapshot Controlfile:
Snapshot controlfile is used by rman to backup control file.Personal speaking not required to do so.If we have copy of current controlfile.Here I have used to save one in the current location of rman backup location.

5)Retention policy:
It is that period of time,till when you want to keep the backup in rman repository.Usually decide this policy well in advance and inform the team.In my case till 7 days I can obtain the backup for restore and recovery purpose.

Below are my scripts:
------------------------
1)run_TESTDB.txt:
---------------------
run_TESTDB.txt is for the setting I'm going to configure for RMAN.We have to create this file first than the actual shell script for taking rman backup.

-- RMAN Configured
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_BK_SET2_%U_%T';
backup database TAG='TESTDB_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u05/DB_BACKUP_TESTDB/RMAN_COLDBACKUP_WEEKLY/snapcf_TESTDB.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

2)rman_TESTDB.sh:
--------------------
rman_TESTDB.sh consist of setting Oracle Environment Variables and calling the run_TESTDB.txt file for taking the rman cold backup.

-- TESTDB RMAN COLD BACKUP
export ORACLE_SID=TESTDB
export ORACLE_HOME=/u01/oracle11g/product/11.2.0/dbhome_1
export ORACLE_BASE=/u01/oracle11g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
rman target sys/TESTDBdba @/u05/DB_BACKUP_TESTDB/Backup_Scipts/run_TESTDB.txt log=/u05/DB_BACKUP_TESTDB/logs/rman_cold_weekly_backup_logs/rman_TESTDB.log

Scheduling RMAN Backup job:
-------------------------------
If I want to schedule this backup every sunday at 9pm.The cronjob script will be:
$vi crontab.oracle

#Script for RMAN COLD BACKUP WEEKLY TESTDB DATABASE
################################################################
00 21 * * 0 /u05/DB_BACKUP_TESTDB/SCRIPTS/rman_TESTDB.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/testdb_rman_cron.log

$crontab crontab.oracle =>scheduling job using 'crontab' command at 9pm every sundays.

Verifying cronjob:
-----------------------
$crontab -l =>List the jobs that are scheduled using Cronjob.Verify the rman job also if we have schedule as above.

Database COLD backup & Restore from TAPE

Here I want to duplicate the PROD database to QA.
Here my PROD db is not a live Prod database instead it is a reporting database.

There are some limitations and restrictions like REPORTING DB is in no archivelog mode and no disk space to take the backup and has given permission to take down the DB for a cold backup to TAPE.

DB Backup on the source host:

PROD_HOST:/
#!/bin/ksh
export ORACLE_HOME=/fin04/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=PRODDB
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
$ORACLE_HOME/bin/rman msglog Backup_PRODDB_COLD.${TODAY}_${TIME}.log << EOF
connect target /
run {
shutdown immediate;
startup mount;
allocate channel ch01 type sbt_tape;
allocate channel ch02 type sbt_tape;
allocate channel ch03 type sbt_tape;
allocate channel ch04 type sbt_tape;
allocate channel ch05 type sbt_tape;
allocate channel ch06 type sbt_tape;
allocate channel ch07 type sbt_tape;
allocate channel ch08 type sbt_tape;
allocate channel ch09 type sbt_tape;
allocate channel ch10 type sbt_tape;
send 'NB_ORA_POLICY=PRODDB_ORA_CDC_PROD,NB_ORA_CLIENT=PROD_HOST,NB_ORA_SCHED=full';
backup
INCREMENTAL LEVEL=0
database ;
backup current controlfile;
sql 'alter database open';
release channel ch01;
release channel ch02;
release channel ch03;
release channel ch04;
release channel ch05;
release channel ch06;
release channel ch07;
release channel ch08;
release channel ch09;
release channel ch10;
}
list incarnation of database;
exit
EOF

:- Backup completed:

RMAN>
piece handle=6pn19kt1_1_1 tag=TAG20120121T024033 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch01: backup set complete, elapsed time: 00:00:58
Finished backup at 21-JAN-12

Starting Control File and SPFILE Autobackup at 21-JAN-12
piece handle=c-3861913925-20120121-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 21-JAN-12

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 PRODDB 3861913925 CURRENT 6574375077 29-OCT-04

DB Restore on the destination/new host:-
==========================================

unixhost(PRODDB) /export/home/oracle
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 21 17:15:54 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (not mounted)

RMAN> set dbid=3861913925 ;

executing command: SET DBID

RMAN> run{
2> allocate channel ch01 type sbt_tape;
3> send 'NB_ORA_POLICY=PRODDB_ORA_CDC_PROD,NB_ORA_CLIENT=PROD_HOST-bn';
4> restore controlfile from autobackup; <---- We have to use autobackup for TAPE
5> }

allocated channel: ch01
channel ch01: sid=544 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2010042405)

sent command to channel: ch01

Starting restore at 21-JAN-12

channel ch01: looking for autobackup on day: 20120121

channel ch01: autobackup found: c-3861913925-20120121-00
channel ch01: control file restore from autobackup complete
output filename=+QADB_REDO_01/PRODDB/controlfile/current.264.773170015
output filename=+QADB_REDO_02/PRODDB/controlfile/current.266.773170019
Finished restore at 21-JAN-12
released channel: ch01

RMAN>
RMAN>

=====mount the database with the restore controlfile=======

RMAN> sql 'alter database mount';

using target database control file instead of recovery catalog
sql statement: alter database mount

===Start the restore:

#!/bin/ksh
export ORACLE_HOME=/fin03/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=MYDB
NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export NLS_DATE_FORMAT
/fin03/u0001/oracle/product/10.2.0/db_1/bin/rman debug trace db_restore_trace1.log msglog db_restore.log append< { run
allocate channel ch01 type disk;
crosscheck backup;
delete noprompt expired backup;
}

Now:
===

Started the restore again with trace on just in case:-
========================================================

RMAN-08511: piece handle=6on195dc_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch09: restore complete, elapsed time: 06:19:06
RMAN-08023: channel ch05: restored backup piece 1
RMAN-08511: piece handle=6kn195da_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch05: restore complete, elapsed time: 06:28:23
RMAN-08023: channel ch04: restored backup piece 1
RMAN-08511: piece handle=6ln195db_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch04: restore complete, elapsed time: 06:32:18
RMAN-03091: Finished restore at 23-JAN-2012 02:05:25

RMAN-08031: released channel: ch01
RMAN-08031: released channel: ch03

After the crosscheck and cleanup. RMAN restored 2TB database in less than 7hrs.

In my Restore script I forgot to include "switch datafile all" at the end.
Since we are using "set newname for datafile 1 to NEW" I should have added "switch datafile all"

After restore:-
==================================

RMAN> run {
2> allocate channel ch01 type disk;
3> switch datafile all;
4> }

This didn't work since the channels used for restore has been released and my restore session has been ended.

But after the restore we can do this by going to RMAN>

While database in mount state and all the files has been restored:

RMAN>
RMAN> catalog start with '+QADB_DATA_01/QADB/datafile';
switch database to copy;

RMAN>

This will only work is ALL the datafiles are restored, otherwise the SWITCH DATABASE TO COPY will fail.

Before you open the database:
disable any change tracking

SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Now:-

SQL>ALTER DATABASE OPEN;

Since this is arestore from COLD backup there is no media recovery and hence no "resetlogs option"

At this point database is UP and running:

Stage-2:-

Now I want to rename this Database to something else. I am using NID to change the DBNAME and DBID of the restored database.

unixhost(PRODDB) /export/home/oracle
>tnsping PRODDB1

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 24-JAN-2012 07:55:08

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/fin03/u0001/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = unixhost-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRODDB.TWCABLE.COM) (INSTANCE_NAME = PRODDB1)))
OK (10 msec)

unixhost(PRODDB) /export/home/oracle
>nid TARGET=SYS/s09s20y7@PRODDB1 DBNAME=QADB

DBNEWID: Release 10.2.0.4.0 - Production on Tue Jan 24 07:56:39 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database PRODDB (DBID=3861913925)

Connected to server version 10.2.0

Control Files in database:
+QADB_REDO_01/PRODDB/controlfile/current.264.773170015
+QADB_REDO_02/PRODDB/controlfile/current.266.773170019

Change database ID and database name PRODDB to QADB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3861913925 to 340462486
Changing database name from PRODDB to QADB
Control File +QADB_REDO_01/PRODDB/controlfile/current.264.773170015 - modified
Control File +QADB_REDO_02/PRODDB/controlfile/current.266.773170019 - modified
Datafile +QADB_DATA_01/PRODDB/datafile/system.416.773331423 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/ps_cm_acctg_line_idx1.417.773315313 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/aart_data_lg.373.773319401 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amlarge.363.773335877 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amapp.496.773334279 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amlarge.340.773315185 - dbid changed, wrote new name

Datafile +QADB_DATA_01/PRODDB/datafile/pcapp.508.773335933 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/pclarge.401.773329153 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/perfstat.468.773331615 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/poapp.456.773315299 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/polarge.267.773327515 - dbid changed, wrote new name

Control File +QADB_REDO_01/PRODDB/controlfile/current.264.773170015 - dbid changed, wrote new name
Control File +QADB_REDO_02/PRODDB/controlfile/current.266.773170019 - dbid changed, wrote new name
Instance shut down

Database name changed to QADB.
Modify parameter file and generate a new password file before restarting.
Database ID for database QADB changed to 340462486.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

***
Change the DBNAME and other log/dump directories that has the old name from the parameter file.

Here I have the parameter file already setup for the clone so I am just changing the control_file location in my clone parameter file.

Clone Oracle Applications 11i / R12

Clone Oracle Applications 11i / R12
Clone Oracle Applications 11i / R12
===========================
Cloning-Cloning is the process used to create a copy of an existing Oracle Applications system. There are various scenarios for cloning an Oracle Applications system.
Use Rapid Clone to create template files for cloning on the source system. After the source system is copied to the target, Rapid Clone updates these templates to contain the new target system configuration settings. Rapid Clone will not change the source system configuration.
There are three phases to the cloning process:
1. Prepare the Source System
Execute the following commands to prepare the source system for cloning.
a) Prepare the source system database tier for cloning
Log on to the source system as the ORACLE user and run the following commands:
cd <RDBMS ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>
perl adpreclone.pl dbTier
b) Prepare the source system application tier for cloning
Log on to the source system as the APPLMGR user and run the following commands on each node that contains an APPL_TOP:
cd <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>
perl adpreclone.pl appsTier
FOR R12
cd <INST_TOP>/admin/scripts
perl adpreclone.pl appsTier
2. Copy the Source System to the Target System
Copy the application tier file system from the source Applications system to the target node by executing the following steps in the order listed. Ensure the application tier files copied to the target system are owned by the target APPLMGR user, and that the database tier files are owned by the ORACLE user.
A good idea is to tar the directories in the sources and untar it on the target node.
This would not cause any permission problems and links would be preserved.
a Copy the database tier file system
Log on to the source system database node as the ORACLE user.
Option 1 – Cold Backup Perform a normal shutdown of the source system database and Applications
Copy the database (DBF) files from the source to the target system Copy the source database ORACLE_HOME to the target system
Option 2 – Hot backup Put database in begin backup mode
Copy the database (DBF) files from the source to the target system
Put the database in end backup mode
Copy the source database ORACLE_HOME to the target system
3 Configure the Target System
Execute the following commands to configure the target system. You will be prompted for the target system specific values (SID, Paths, Ports, etc)
a.Configure the target system database server

Option 1 – Cold Backup being Used
Log on to the target system as the ORACLE user and type the following commands to configure and start the database:
cd <RDBMS ORACLE_HOME>/appsutil/clone/binperl adcfgclone.pl dbTier.
Steps to follow in cold backup cloning:-
The following are the steps for performing database cloning using cold backup

Assumptions : You are using Linux flavour OS and following same directory structure

1. Take the cold backup of source database

2. Take controlfile trace and pfile or spfile (that was using by the source database)

3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

4. Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp

5. Place pfile or spfile in “dbs” directory on target

6. Copy the remaining files to their respective locations (If any directories are missing, do create them)

7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID

8. Connect as sysdba and Startup the database
Option 2 – Hot Backup being Used
Log on to the target system as the ORACLE user
Configure the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>/appsutil/clone/bin

perl adcfgclone.pl dbTechStack
Create the target database control files manually
Start the target system database in open mode

*If you are Using RMAN , Duplicate the Database from source to target.
Run the library update script against the database
cd <RDBMS ORACLE_HOME>/appsutil/install/<CONTEXT NAME>
sqlplus “/ as sysdba” @adupdlib.sql <libext>
where <libext> is “sl” for HP-UX, “so” for any other UNIX platform and not required for Windows.
Configure the target database (the database must be open)
cd <RDBMS ORACLE_HOME>/appsutil/clone/bin
perl adcfgclone.pl dbatabase <target context file>
where target context file is:
<RDBMS ORACLE_HOME>/appsutil/<Target CONTEXT_NAME>.xml

Respond to the prompt as follows-

Database SID	Target database sid
Database name	Target system domain name
Target system database name	Target system database name
Target instance is a Real Application Cluster (RAC) instance (y/n)	Answer yes if it is a part of rac instanceThis prompt only appears when you answered “yes” to the previous question.yes to this question if the current host is the first node being configured in the target system RAC cluster. The tool will then ask for the number of nodes that will exist in the final RAC instance and gather, the following information for every node Hostname Database Sid Instance number Listener port Private interconnect name
Current node is the first node in an N Node RAC Cluster (y/n)	Answer “no” to this qstion if at least one node of the target RAC cluster has already been configured by Rapid Clone (i.e if you already replied “yes” to this question for any other node in the cluster). The tool will then prompt for the following information to connect to a life node (the answers must describe a node that has already been configured):- Hostname- Database Sid - Listener port
RDBMS ORACLE_HOME directory	Path to the Target system RDBMS ORACLE_HOME
Number of DATA_TOP’s: DATA_TOP 1: DATA_TOP 2: DATA_TOP 3:	Database mount points. Enter the number of distinct directories containing the target database dbfs, then their paths.

Assumptions-
1) Latest versions of Perl, JRE,JDK,ZIP utilities are available on source and target systems.
2) Latest AD Minipack is applied on Application Tier
3) Latest Autoconfig Template Patch and Latest RapidClone Patches are applied to Application Tier.
4) Source database is in Archive log mode. Ex: Alter database archivelog and Change log_archive_enable to TRUE in initSID.ora
Steps to do on Source System:
1) Log into Database Tier as DB user and run adpreclone.
Ex: cd $ORACLE_HOME/appsutil/scripts/CONTEXT NAME/ perl adpreclone.pl dbTier
2) Log into Application Tier as Apps user and run preclone.
Ex: cd $COMMON_TOP/admin/scripts/CONTEXT NAME/ perl adpreclone.pl appsTier
3) Place the database in Begin Backup Mode. Ex: alter database begin backup (10g onwards) or alter tablespace <tablespace name> begin backup(Prior to 10g)
4) Copy all the datafiles to Backup directory or to target system.
Note: Copying of control, log and temp file is not necessary.
5) Backup the control file to trace (located in udump) and copy it to Backup directory.
Ex: alter database backup control file to trace
6) Remove database from Begin Backup Mode. Ex: alter database end backup
7) Now copy DB_TOP as DB user to Backup directory or target system. (Take Backup of Archive logs also if they are located in some other place)

Next copy APPL_TOP, COMMON_TOP and ORA_TOP as Apps user to Backup directory or target system.
Steps to do on Target System:
1) Copy all the files from Backup Directory to this system under respective user groups and change the permissions for all the directories recursively.

2) Log into Database Tier as DB user and configure it.
Ex: cd $ORACLE_HOME/appsutil/clone/bin/ perl adcfgclone.pl dbTechStack

3) Create a control file manually from the file which we backed up from trace.
a. Remove all lines before the startup nomount statement
b. Modify the REUSE to SET
c. Modify Source DB SID to Target SID (Here PROD to TEST)
d. Modify NORESETLOGS TO RESETLOGS
e. Delete all lines after the CHARACTER SET statement
f. Change the datafile locations according to their locations in target system.
Note: Save the file as an sql executable file with .sql extension.

4) Now modify the initSID.ora file accordingly for Target system and enable archive log mode in it.

5) Now start the database in nomount state. Ex: startup nomount pfile=”<initSID.ora location>”

6) Now run the modified control file (sql file). With this database will be in mount state.

7) After the database is mounted, execute recover command using backup control file.
Ex: recover database using backup controlfile until cancel

Now open the database. Ex: alter database open resetlogs

9) Now add a temp file to the target database.                                                                                                        Ex: 1) create temporary tablespace TEMP tempfile ‘<temp file location>’ size 50M autoextend off.
Ex: 2) alter tablespace TEMP add tempfile ‘<temp file location>’ size 50M autoextend off
10) Log into the Application Tier as Apps user and configure it.
Ex :cd $COMMON_TOP/clone/bin/ perl adcfgclone.pl appsTier                                                                      Finishing tasks:
(1) Update Profile options
(2) Update Printer Settings
(3) Update the workflow configuration settings
(4) Verify the APPLCSF variable setting
(5) Update the session_cookie_domain value in icx_parameters
Log Files:
1) cd $ORACLE_HOME/appsutil/log/CONTEXT NAME/*.log
2) cd $APPPL_TOP/admin/CONTEXT NAME/log/*.log
steps to follow in hot backup cloning using rman:-

Connect to the target database using rman backup the database on node 1                                   rman target /
backup database;

2) Move the following files to the NODE 2:
- backup pieces
- controlfile backup piece
- the parameter file i.e init.ora file to the default location i.e $ORACLE_HOME/dbs
3) Modify PFILE accordingly, invoke Rman on the node 2 after setting the Oracle environment variables and start the database in nomount mode:
export ORACLE_HOME, ORACLE_SID, PATH
$rman target /
RMAN> startup nomount;

Restore the controlfile from the backup piece.
RMAN> restore controlfile from ‘/node2/database/backup/c-3932056136-20070213-02′;

Mount the database
RMAN > alter database mount
Now catalog the backup pieces that was moved from NODE 1
RMAN> catalog backuppiece ‘/node2/database/backup/o1_mf_annn_TAG20070213T002925_2×21m6ty_.bkp’;
Now restore the datafiles
RMAN> run
{
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}

b Configure the target system application tier server nodes
On the applications Tier nodes , make sure you copy the database tns entry from the database tier to the
806 ORACLE_HOME/network/admin/tnsnames.ora
set the ORACLE_HOME to the target 806 ORACLE_HOME

set the TNS_ADMIN to $ORACLE_HOME/network/admin

set the TWO_TASK to the connection Identifier proided in tnsnames.ora
ie. before you run adcfgclone.pl , you should be able to connect to the database using sqlplus
as apps ( apps/<appspwd>@<conection_identifier>)
Log on to the target system as the APPLMGR user and type the following commands:
cd <COMMON_TOP>/clone/bin
perl adcfgclone.pl appsTier

Provide the values required for creation of the new APPL_TOP context file:

Database server node	Hostname for the machine hosting the database server
Does the target system have more than one applications tier server node (y/n)	Answer yes if the target system is part of a multi-nodes configuration. The tool with then prompt for the hostnames of:- concurrent processing node- administration node - forms server node - web server node
Is the target system APPL_TOP divided into multiple mount points (y/n)?	Answer yes if the target system APPL_TOP is divided across multiple mount points. The tool will then prompt for each auxiliary mount (4 mounts): - APPL_TOP mount point - APPL_TOP aux.1 - APPL_TOP aux.2 - APPL_TOP aux.3
APPL_TOP mount point	APPL_TOP directory
COMMON_TOP directory	COMMON_TOP directory
ORACLE_HOME directory	ORACLE_HOME directory
iAS ORACLE_HOME directory	iAS ORACLE_HOME directory
Location of JDK 1.3.1	Location of JDK 1.3.1

This will prompt you new port pool for applictaion tier services as well as new APPLTOP,COMMON_TOP,ORACLE_HOME and IAS_ORACLE_HOME
Successful completion of this task will bring up your application tier services on the target or the cloned node.

Cold Backup Cloning

Cold Backup Cloning

Assumptions:

1) Target database is similar in configuration to source database.

2) oratest and appltest users are created in the target database under dba group.

3) Environment is always set when logged in as oracle or applmgr in source system.

Pre Cloning Steps:

1) DB Tier is precloned first. Ex: cd $ORACLE_HOME/appsutil/scripts/SID/ perl adpreclone.pl dbTier

2) Apps Tier to be precloned next. Ex: cd $COMMON_TOP/admin/scripts/SID/ perl adpreclone.pl appsTier

Cold Backup:

1) Shutdown Application, Listener and Database. Verify if they are down.

$ ps -ef|grep applprod

applprod 10031 1471 0 18:24:46 pts/ta 0:00 ps -ef

applprod 10032 1471 1 18:24:46 pts/ta 0:00 grep applprod

applprod 1471 29301 0 18:06:07 pts/ta 0:00 –sh

$ ps -ef|grep tns

applprod 10105 1471 0 18:25:41 pts/ta 0:00 grep tns

$ ps -ef|grep oraprod

applprod 10107 1471 0 18:25:46 pts/ta 0:00 grep oraprod

oraprod 29760 29518 0 17:48:26 pts/tb 0:00 -sh

2) Copy APPL_TOP, COMMON_TOP and ORA_TOP from applmgr and move it to Target system under appltest directory.

Ex : scp –r <filename> root@<ipaddress>:<location>

3) Copy DATA_TOP and DB_TOP from oracle and move it to Target System under oratest directory.

Ex : scp –r <filename> root@<ipaddress>:<location>

4) Now change the ownerships of the moved files in the target system accordingly.

Ex: chown –R appltest:dba <filename>

chown –R oratest:dba <filename>

Post Cloning Steps:

1) DB Tier is configured first. Ex: cd $ORACLE_HOME/appsutil/clone/bin Perl adcfgclone.pl dbTier.

2) Enter apps password and all other relevant information.

3) The database is started.

4) Apps Tier is configured next. Ex: cd $COMMON_TOP/clone/bin perl adcfgclone.pl appsTier

5) Enter apps password and all other relevant information accordingly.

6) After it is completed, it prompts whether to start Applications or not.

Log Files :

1) cd $ORACLE_HOME/appsutil/log/CONTEXT NAME/*.log

2) cd $APPPL_TOP/admin/CONTEXT NAME/log/*.log or

cd /u01/inst/apps/PROD_ashirwad/admin/log/StageAppsTier_11021810.log(R12)

Note: If u want to take backup use commands

tar -cvf filename.tar <directoryname> ß creates

tar -xvf filename.tar <directoryname> ß reads

tar –C <location> -xvf filename.tar ß untars at a particular location

We don't run precone in daily backup its auto schedule.

We don't run precone in daily backup its auto schedule.
Can we still clone test instace from such backup ?

If you run preclone before(let us say 3 months ago) and during those days there were no patching etc. i belive you can try clone. But i suggest if you take daily backup( I assume you are taking cold backup) now run preclone while your system is running than use this backup for can be sure.

Clone an Oracle database using a cold backup..

This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.

1. Identify and copy the database files
With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
```
set lines 100 pages 999
col name format a50
select name, bytes
from    (select name, bytes
 from v$datafile
 union all
 select name, bytes
 from  v$tempfile
 union  all
 select  lf.member "name", l.bytes
 from v$logfile lf
 , v$log l
 where lf.group# = l.group#) used
, (select sum(bytes) as poo
 from dba_free_space) free
/
```
Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.

Stop the source database with:
```
shutdown immediate
```
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

Start the source database up again
```
startup
```
2. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:
```
create pfile='init<new database sid>.ora' from spfile;
```
This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Note. Pay particular attention to the control locations.
3. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
```
alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
/
```
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
- Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
- Remove any lines that start with --
- Remove any lines that start with a #
- Remove any blank lines in the 'CREATE CONTROLFILE' section.
- Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
- Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
- If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
```
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/u03/oradata/dg9a/redo01.log'  SIZE 100M,
  GROUP 2 '/u03/oradata/dg9a/redo02.log'  SIZE 100M,
  GROUP 3 '/u03/oradata/dg9a/redo03.log'  SIZE 100M
DATAFILE
  '/u03/oradata/dg9a/system01.dbf',
  '/u03/oradata/dg9a/undotbs01.dbf',
  '/u03/oradata/dg9a/cwmlite01.dbf',
  '/u03/oradata/dg9a/drsys01.dbf',
  '/u03/oradata/dg9a/example01.dbf',
  '/u03/oradata/dg9a/indx01.dbf',
  '/u03/oradata/dg9a/odm01.dbf',
  '/u03/oradata/dg9a/tools01.dbf',
  '/u03/oradata/dg9a/users01.dbf',
  '/u03/oradata/dg9a/xdb01.dbf',
  '/u03/oradata/dg9a/andy01.dbf',
  '/u03/oradata/dg9a/psstats01.dbf',
  '/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/dg9a/temp01.dbf'
     SIZE 104857600  REUSE AUTOEXTEND OFF;
```
4. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
```
echo $ORACLE_SID
```
If this doesn't output the new database sid go back and investigate.
5. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):
```
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
```
5. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
```
sqlplus "/ as sysdba"
@/home/oracle/cr_<new database sid>
```
It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
```
ORA-01113: file 1 needs media recovery
```
You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
```
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
ORA-27038: skgfrcre: file exists
```
Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.
6. Perform a few checks
If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:
- Check that the database has opened with:
```
select status from v$instance;
```
  The status should be 'OPEN'
- Make sure that the datafiles are all ok:
```
select distinct status from v$datafile;
```
  It should return only ONLINE and SYSTEM.
- Take a quick look at the alert log too.
7. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
```
alter database rename global_name to <new database sid>
/
```
8. Create a spfile
From sqlplus:
```
create spfile from pfile;
```
9. Change the database IDIf RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
```
shutdown immediate
startup mount
exit
```
From unix:
```
nid target=/
```
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
```
shutdown immediate
startup mount
alter database open resetlogs
/
```
10. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.
11. Finished