Step by step document to create Standby Database

Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database.
1 - Data Guard Operational Prerequisites
· Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
· The Primary Database must run in ARCHIVELOG mode.
· The hardware and Operating system architecture on primary and standby location must be same.
· Each primary and standby database must have its own control file.
· If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
· Primary database must be FORCE LOGGING mode.
2 - Preparing Primary Database for Standby Database creation
2.1 Ensure the primary database in ARCHIVELOG mode Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora9\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
2.2 Enable database in FORCE LOGGING mode
Place primary database in FORCE LOGGING mode using following SQL statement:
SQL> alter database force logging;
Database altered.
3 - Identify the primary database Datafiles
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------


C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF
10 rows selected.
4 - Make a copy of Primary Database
Make a closed backup copy of primary database by performing following
steps: 4.1 Shutdown the Primary Database Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;
4.2 Copy the Datafiles to standby location Copy the redo log files and Datafiles identified in section 3 to standby location. Note: Primary Database must be shutdown while coping the files.
5 - Restart the Primary Database Execute following command to restart the Primary Database.
SQL> startup;
6 - Create Control file for Standby Database Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';
Database altered.

The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.
7 - Create pfile from for standby database from the primary database Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.
SQL> CREATE PFILE='C:\oracle\ora92\database\initstby.ora' from spfile;
File created.
8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.
Edit created pfile from primary database.
db_name - Not modified. The same name as the primary database.
compatible - Not modified. The same as the primary database, 9.2.0.0.0.
control_files - Specify the path name and filename for the standby control file.
log_archive_start - Not modified. The same as the setting for the primary database, TRUE
standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database. db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required. log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.
log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)
standby_file_management - Set to AUTO. remote_archive_enable - Set to TRUE.
instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.
lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter. Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database. (Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window C:\>oradim -new -sid stby -intpwd stby -startmode manual
10 - Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)
)
)
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
)
)
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oracle\ora92) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = Orcl) (ORACLE_HOME = D:\oracle\ora92) (SID_NAME = Orcl) ) (SID_DESC = (GLOBAL_DBNAME = stby) (ORACLE_HOME = D:\oracle\ora92) (SID_NAME = stby) ) )
Restart the listeners using LSNRCTL utility.
% lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database. stby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby) ) )
11 - Start Physical standby database
Start up the stand by database using following commands C:\>set oracle_sid=stby
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
12 - Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined. Issue following commands from primary database session:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby' SCOPE=BOTH; System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; System altered.
13 - Initiate Log apply services
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database open read only;
alter database open read only
*ERROR at line 1:ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
Now go to primary database prompt
SQL> alter system switch logfile;
Go to stand by database prompt
SQL> alter database open read only;
Database altered.
14 - Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
Archive the current log on the primary database using following statement.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME 2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
Now connect scott/tiger@orclc on primary database and create table or insert row in any table. Now connect as sys on primary database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only; Database altered.
And check whether the changes applied on the standby database or not.
15 - Annexure 15.1
Parameter file for Primary Database
*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\Orclc\bdump'
*.compatible='9.2.0.0.0'
*.control_files='C:\oracle\oradata\Orclc\CONTROL01.CTL','C:\oracle\oradata\Orclc\CONTROL02.CTL','C:\oracle\oradata\Orclc\CONTROL03.CTL'
*.core_dump_dest='D:\oracle\admin\Orclc\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='Orclc'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=C:\oracle\ora92\database\archive\ORCLC mANDATORY'
*.log_archive_dest_2='SERVICE=stby'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=true*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE*.undo_management='AUTO'
*.undo_retention=10800*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\Orclc\udump'

15.2 Parameter file for Standby Database *.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\stby\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\stby\CONTROL_SB01.CTL'
*.core_dump_dest='D:\oracle\admin\stby\cdump'
*.db_block_size=8192*.db_cache_size=25165824
*.db_domain=''*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='stby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:\oracle\admin\stby\archive'
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='NONE'
*.shared_pool_size=50331648*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\stby\udump'
*.standby_archive_dest='C:\oracle\ora92\database\archive\ORCLC'
*.db_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.log_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby

upgradation from oracle 9i to oracle 10g

Steps for Upgrading the Database to 10g Release 2

Preparing to Upgrade

Fresh Install oracle software only 10gR2 on the same 11i instance.

Oracle 9i(9.2.0.6) to Oracle 10g(10.2.0.1)

Oracle 9i home ==> /oracle/app/oracle/testdb/9.2.0

Oracle 10g home ==> /u01/app/oracle/product/10.2.0/

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01

export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144


==================================

Step 1

Copy utlu102i.sql , utltzuv2.sql 10g oracle home to /tmp folder. Then run both scripts.
This scripts will show the preupgrade steps.

ORACLE_HOME ==> 10g Home

cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp
cp $ORACLE_HOME/rdbms/admin/utltzuv2.sql /tmp
===================================

Step 2

Then login 9i oracle home and login sql prompt. Then run that above scripts.

sqlplus '/as sysdba'

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

===================================

Check the log file and solve that issues.

spool Database_Info.log

Oracle Database 10.2 Upgrade Information Utility 04-23-2008 11:07:05
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 9.2.0.6.0
--> compatible: 9.2.0
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 8082 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 577 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 10842 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 14 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 30 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 109 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "large_pool_size" needs to be increased to at least 8388608
.
**********************************************************************
Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No deprecated parameters found. No changes are required.
.
**********************************************************************
Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [install]
--> Real Application Clusters [upgrade] INVALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] UPGRADED
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] UPGRADED
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... ODM
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
WARNING: SYSAUX tablespace is present.
.... Minimum required size for database upgrade:500 MB
.... Online
.... Permanent
.... Readwrite
.... ExtentManagementLocal
.... SegmentSpaceManagementAuto
.

=======================================
Step 3

Check the above output file and resolve the warning and failed messages
=======================================

Increase the SYSTEM tablespace

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='SYSTEM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='SYSTEM' GROUP BY FILE_NAME;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys8.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys9.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys10.dbf' SIZE 4096M;


TEMP

alter database tempfile '/oracle/app/oracle/testdata/tmp1.dbf' resize 4096M;

APPS_TS_QUEUES

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_QUEUES';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_QUEUES' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_QUEUES ADD DATAFILE '/oracle/app/oracle/testdata/queues3.dbf' SIZE 1024M;


APPS_TS_TX_DATA

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_TX_DATA';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data12.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data13.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data14.dbf' SIZE 1024M;

ODM

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='ODM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='ODM' GROUP BY FILE_NAME;

alter database datafile '/oracle/app/oracle/testdata/odm.dbf' resize 250m;

OLAP

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='OLAP';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='OLAP' GROUP BY FILE_NAME;

alter database datafile '/oracle/app/oracle/testdata/olap.dbf' resize 250m;

CREATE TABLESPACE sysaux DATAFILE '/oracle/app/oracle/testdata/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

============================================

Step 4

Check for the TIMESTAMP WITH TIMEZONE Datatype.

SQL> @utltzuv2.sql

DROP TABLE sys.sys_tzuv2_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

Query sys.sys_tzuv2_temptab Table to see if any TIMEZONE data is affected by
version 2 transition rules

PL/SQL procedure successfully completed.


Commit complete.

=============================================
Step 5

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.


SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ODM');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OLAPSYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MDSYS');

.... SYS
.... ODM
.... OLAPSYS
.... MDSYS
==================================================

Step 6

REVOKE CONNECT RIGHTS TO ABOVE 12 USERS

SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');


GRANTEE
=======
CFD
DMS
HCC
DGRAY
EUL_US
SSOSDK
WEBSYS
PROJMFG
SERVICES
WIRELESS
EDWEUL_US

GRANTEE
------------------------------
MOBILEADMIN

12 rows selected.
============================================

SELECT 'REVOKE CONNECT FROM 'grantee';' FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');


Take the spool on above scripts and run sql prompt.

===========================================

Remove or comment the following initparameters 10 oracle home.

Copy init.ora file 9i to 10g oracle home, Then change the parameter file.

-->"optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"
===========================================

Check the spool log file to Add and Increase the below parameter size

shared_pool_size=181217280
streams_pool_size=50331648
large_pool_size=8388608

===========================================

Check the free tablespace size

select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name in ('SYSTEM','APPS_TS_QUEUES','APPS_TS_TX_DATA','ODM','SYSAUX') GROUP BY TABLESPACE_NAME;

===========================================

set below env

export ORACLE_SID=TEST
export ORACLE_BASE=/u01/app/
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
==========================================

After completing pre upgrade steps, you have to login 10g oracle home.

[oracle@sys43 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 23 14:48:39 2008

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 473956352 bytes
Fixed Size 1220024 bytes
Variable Size 297796168 bytes
Database Buffers 163577856 bytes
Redo Buffers 11362304 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> @catupgrd.sql


Oracle Database 10.2 Upgrade Status Utility 04-23-2008 16:23:48
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:48:24
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:06:07
Oracle XDK VALID 10.2.0.1.0 00:06:03
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:25
Oracle Text VALID 10.2.0.1.0 00:01:08
Oracle XML Database VALID 10.2.0.1.0 00:01:30
Oracle Real Application Clusters INVALID 10.2.0.1.0 00:00:02
Oracle Data Mining VALID 10.2.0.1.0 00:00:19
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:16
OLAP Catalog VALID 10.2.0.1.0 00:01:13
Oracle OLAP API VALID 10.2.0.1.0 00:00:38
Oracle interMedia VALID 10.2.0.1.0 00:05:22
Spatial INVALID 10.2.0.1.0 00:04:21
.
Total Upgrade Time: 01:31:00
========================================
spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
spool off

========================================
SQL>shutdown immediate
========================================

The 9idata directory has no files
so go to following folder and run that perl scripts.
/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old/cr9idata.pl


bash-2.05b$ perl cr9idata.pl Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ... Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata... Copy finished. Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata! bash-2.05b$
============================
Then normal startup

SQL>startup
============================

Compile invalid objects

SQL> @utlrp.sql

========================================
SQL> shut immediate

cp env to oracle_home_10g and edit new values

Copy tnsnames.ora, listeners.ora and sqlnet.ora 9i to new 10g orale home.
cp tns_admin(9i) to oracle 10g

Start listener
================================================

SQL>startup

================================================

SymptomsIn 10gR2, setting the environment variable ORA_NLS10 causes the following error:ERROROra-12705: cannot access nls data files or invalid environment specified ora-127
This is script cr9idata.pl located following path.

/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old

bash-2.05b$ perl cr9idata.pl

Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ...Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata...Copy finished.

Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata!
===========================================================
Implement and run Autoconfig on the new Database home

1. Copy AutoConfig to the RDBMS ORACLE_HOME

Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

Steps:
* On the Application Tier (as the APPLMGR user):

a) Log in to the APPL_TOP environment and source the APPSORA.env file

b) Create appsutil.zip file. This will create appsutil.zip in $APPL_TOP/admin/out

perl $AD_TOP/bin/admkappsutil.pl

bash-2.05b$ perl admkappsutil.pl

Starting the generation of appsutil.zip
Log file located at /progs2/11i/uat/applmgr/uatappl/admin/log/MakeAppsUtil_09051242.log
output located at /progs2/11i/uat/applmgr/uatappl/admin/out/appsutil.zip
MakeAppsUtil completed successfully.


c) Copy or FTP the appsutil.zip file to the

* On the Database Tier (as the APPLMGR or ORACLE user):

d) cd RDBMS ORACLE_HOME

e) Source 10g CONTEXT_NAME.env file

f) unzip -o appsutil.zip


2. Generate your Database Context File. Execute the following commands to create your Database Context File:

Steps:

a) cd RDBMS ORACLE_HOME

b) CONTEXT_NAME.env

c) cd <10.1.0>/appsutil/bin

d) perl adbldxml.pl tier=db appsuser=APPSuser appspasswd=APPSpwd

perl adbldxml.pl tier=db appsuser=apps appspasswd=xxxxx


Steps:
a) cd /appsutil/bin

b) adconfig.sh contextfile=CONTEXT.XML appspass=APPSpwd



References

Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1
Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Doc ID: Note:362203.1

Note 135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
Note 159657.1 - Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
Note 170282.1 - PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
Note 263809.1 - Complete checklist for manual upgrades to 10gR1 (10.1.0.x)
Note 293658.1 - 10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512
Note 316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
Note 356082.1 - ORA-7445 [qmeLoadMetadata()+452] During 10.1 to 10.2 Upgrade
Note 406472.1 - Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software
Note 407031.1 - ORA-01403 no data found while running utlu102i.sql/utlu102x.sql on 8174 database
Note 412271.1 - ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
Note 465951.1 - ORA-600 [kcbvmap_1] or Ora-600 [Kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release
Note 466181.1 - 10g Upgrade Companion
Note 471479.1 - IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1
Note 557242.1 - Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed
Oracle Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.ht

Step by step document to create Standby Database

rajesh oracle dba