Oracle document i am reading

Reading about oracle everyday is very important for DBAs, i will update my reading list regularly.

19-Sep-09 (Sat) - http://www.procaseconsulting.com/learning/papers/200303%20oracle%20RAC%20concepts.pdf (Oracle RAC, Data Guard, and Recovery Manager Concepts)

Issues in Oracle EBS DataBase UPGRADE

I will try to write full document, below is issue list.

Oracle DataBase server component is in INVALID status

Before running the CATUPGRD.SQL , describe the PLAN_TABLE in the SYS schema level.
If you have OTHER_XML column in PLAN_TABLE means , this error wont come.
Otherwise, this error will come when u run the catupgrd.sql for 10.2.0.4.0 patch.

Customers upgrading to 10.2.0.4 or 11.1.0.7 will encounter the bug only and only if they are upgrading a pre-10g (e.g., 9i or 8) db where admin/utlxplan.sql was explicitly run by/as SYS to create a (user) plan table in the SYS schema. I believe this is very rare as DBA won't use the SYS schema to do explain plan of user/application queries.

Customers upgrading to 10.2.0.2, 10.2.0.3, or 11.1.0.6 won't encounter this bug during upgrade because for those releases SQL Performance Analyzer exists in the format of a one-off, which is not run as part of upgrade.
dbms_sqlpa is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column. This is why we get an error if SYS.plan_table does not have this column. The other_xml column was introduced in 10.2.0.1 and added to the temporary global plan table plan_table$. plan_table$ was introduced in 10.1 as a replacement to table plan_table which is used to store the result of explain plan command. plan_table has become a public synonym to plan_table$.

Workaround is:
-------------------------

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Following solution didn't work but may work in your case.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
....
Database mounted.
Database opened.

Executed following scripts

SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

Check the status of the components from DBA_REGISTRY

UNZIP all shell script

Below tiny script saved a lot of effort in last production cycle.

#to unzip files in a given folder.
FILES="*.zip"
for f in $FILES
do
echo "Processing $f file..."
unzip -o $f
done

Cloning a ORACLE Data Base

Cloning is faster way to replicate the database; basic principles are same across all oracle versions (8i, 9i, and 10g)

I will demonstrate it using my 8i database installation.

1) Choose a database to be cloned let’s say it is “prod” and we want to make a duplicate of it named “prodc01”
2) Shutdown prod (only in normal or immediate mode)
3) Startup prod in restricted mode
-------------------------------------------------------------------
SVRMGR> startup pfile=c:\OraAmresh\admin\prod\initPROD.ora
LRM-00109: could not open parameter file 'c:\OraAmresh\admin\prod\initPROD.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup pfile=c:\OraAmresh\admin\prod\initPROD.ora
LRM-00109: could not open parameter file 'c:\OraAmresh\admin\prod\initPROD.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup pfile=C:\OraAmresh\admin\prod\PFILE\initPROD.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
----------------------------------------------------------------------------------
4) Retrieve value of user_dump_dest parameter from pfile (init.ora)
5) Run ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOG ; ‘
----------------------------------------------------------------------------------
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
Statement processed.
SVRMGR>
---------------------------------------------------------------------------------
6) Retrieve file from the path obtained from step 4 and change it as given steps
a) Change the name of file to ‘clone.sql’
b) Remove everything other then ‘CREATE CONTROLFILE’ statement.
c) In ‘CREATE CONTROLFILE’ statement change every occurrence of to in our case from “prod” to “prodc01”
d) Change ‘REUSE’ to ‘SET’


6) Shut down database ‘prod’

-----------------------------------------------------------------------------------

SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
-------------------------------------------------------------------------------------
7) Shut down instance (Services) run below command in command prompt.

ORADIM -SHUTDOWN -SID prod -SHUTTYPE srvc,inst

8) Copy all parameter files, datafiles and Redo log files to new location.

8) Change path and SID information in new parameter file and rename it to init.ora
9) Create services for new instance using below command.
-------------------------------------------------------------------------------------

C:\OraAmresh\admin\prod01\UDUMP>ORADIM -NEW -SID prod01 -SRVC OracleServiceprod01 -PFILE C:\OraAmresh\admin\prod01\pfile
\initPROD01.ora

C:\OraAmresh\admin\prod01\UDUMP>

--------------------------------------------------------------------------------------

10) Log in to svrmgrl and connect to internal

---------------------------------------------------------------------------------------
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=c:\OraAmresh\admin\prod01\pfile\initPROD01.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
SVRMGR>
--------------------------------------------------------------------------------------------
11) Run clone.sql created in step 6

--------------------------------------------------------------------------------------------
SVRMGR> connect internal
Connected.
SVRMGR> @prod01.sql
Statement processed.
SVRMGR>
-------------------------------------------------------------------------------------
12) Open database in RESETLOGS mode.
-------------------------------------------------------------------------------------
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Statement processed.
SVRMGR>
-------------------------------------------------------------------------------------
13) issue shutdown normal command

14) issue startup ;; good work  you have cloned the database

-------------------------------------------------------------------------------------

SVRMGR> startup pfile=c:\OraAmresh\admin\prod01\pfile\initPROD01.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> select name from V$database;
NAME
---------
PROD01
1 row selected.
-------------------------------------------------------------------------------------
Note : Cloning across OS is possible in Oracle Release 10 G

Create ORACLE 8i database in window

Create ORACLE 8i database in window.

We will learn to create an oracle 8i database; ultimate objective is to upgrade same to 9i


System preparation:

1) Run a disk clean up
2) Run disk defragmenter.
3) Install database software

Pre database creation steps:

1) Check system resources (Memory, RAM, privileges)
2) Familiarize with initialization parameter.
3) Decide block size.
4) Decide Oracle SID
5) Use undo table space to manage your undo record rather then roll back segment.
6) Create parameter file (init.ora) {name is initORACLE_SID.ora}
7) Create backup and recovery strategy.

Steps to Create/verify Dir structure:

1) Find out your oracle home
2) Now create folders in following order
 C:\ORACLE_HOME\ADMIN\ORACLE_SID
 C:\ORACLE_HOME\ADMIN\ORACLE_SID\UDUMP
 /* Place for user/custom generated dump files */
 C:\ORACLE_HOME\ADMIN\ORACLE_SID\BDUMP /* place for dump file alert log etc. */
 C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE /* Place for parameter file */
 C:\ORACLE_HOME\ADMIN\ORACLE_SID\LOGS /* Place for log file*/
 C:\ORACLE_HOME\ORADATA\ORACLE_SID /*Place for data file*/

Note: Your password file will be @ C:\ORACLE_HOME\DATABASE\pwdORACLE_SIDora


Let’s Jump to action:

1) Place initORACLE_SID.ora file @ C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE

An example pfile is given below {you can use it in your database creation ; file name will be initPROD.ora}




-------
#
# Copyright (c) 1991, 2000 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = "prod"

instance_name = prod

service_names = prod

db_files = 1024

control_files = ("C:\oraAmresh\oradata\prod\control01.ctl", "C:\oraAmresh\oradata\prod\control02.ctl", "C:\oraAmresh\oradata\prod\control03.ctl")

open_cursors = 100
max_enabled_roles = 30
db_file_multiblock_read_count = 8

db_block_buffers = 12465

shared_pool_size = 17019392

large_pool_size = 614400
java_pool_size = 0

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 150

parallel_max_servers = 5

log_buffer = 32768

#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each

# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=C:\oraAmresh\oradata\prod\archive"
# log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = ( RBS0 )

# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = true

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

oracle_trace_collection_name = ""
# define directories to store trace and alert files
background_dump_dest = C:\oraAmresh\admin\prod\bdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = C:\oraAmresh\admin\prod\udump

db_block_size = 4096

remote_login_passwordfile = Shared

os_authent_prefix = ""

# The following parameters are needed for the Advanced Replication Option
job_queue_processes = 4
job_queue_interval = 60
open_links = 4

distributed_transactions = 10
compatible = 8.1.0.0.0
sort_area_size = 65536
sort_area_retained_size = 65536










2) Create Oracle Service.

Each instance requires a window service to create this run blow command.

3) ORADIM –NEW –SID prod –INTPWD manager –STARTMODE AUTO –PFILE C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE\init

4) Open command prompt and run following command {you can change various parameters as par your need.}

a) svrmgrl
b) connect to internal
c) shutdown any other database (if running)
d) run below statement


CREATE DATABASE prod
LOGFILE 'C:\ORACLE_HOME \oradata\prod\LOG1A.ora' SIZE 1024K,
'C:\ORACLE_HOME \oradata\prod\LOG1B.ora' SIZE 1024K,
'C:\ORACLE_HOME \oradata\prod\LOG1C.ora' SIZE 1024K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'C:\ORACLE_HOME \oradata\prod\sys1prod.dbf' SIZE 264M REUSE AUTOEXTEND ON NEXT 10240K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1;

Your database should be created as this point but in order to make it working we need to do some additional tasks.


I am giving my session snap shot for this.


SVRMGR> spool C:\OraAmresh\admin\prod\LOGS
SVRMGR> connect internal/********
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup nomount pfile=c:\oraAmresh\admin\pfile\initprod.ora
LRM-00109: could not open parameter file 'c:\oraAmresh\admin\pfile\initprod.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup nomount pfile=c:\oraAmresh\admin\prod\pfile\initprod.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
SVRMGR>
SVRMGR>
SVRMGR> CREATE DATABASE prod
2> LOGFILE 'C:\oraAmresh\oradata\prod\LOG1A.ora' SIZE 1024K,
3> 'C:\oraAmresh\oradata\prod\LOG1B.ora' SIZE 1024K,
4> 'C:\oraAmresh\oradata\prod\LOG1C.ora' SIZE 1024K
5> MAXLOGFILES 32
6> MAXLOGMEMBERS 2
7> MAXLOGHISTORY 1
8> DATAFILE 'C:\oraAmresh\oradata\prod\sys1prod.dbf' SIZE 264M REUSE AUTOEXTEND ON NEXT 10240K
9> MAXDATAFILES 254
10> MAXINSTANCES 1
11> CHARACTER SET WE8ISO8859P1
12> NATIONAL CHARACTER SET WE8ISO8859P1;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE prodT01
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd01.DBF' SIZE 80M AUTOEXTEND ON
3> NEXT 32K MAXSIZE 150M
4> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
5> LOGGING
6> ONLINE
7> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE prodT02
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd02.DBF' SIZE 80M AUTOEXTEND ON
3> NEXT 32K MAXSIZE 150M
4> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
5> LOGGING
6> ONLINE
7> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE ROLLBACK SEGMENT prod_dummy TABLESPACE system
2> STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2);
Statement processed.
SVRMGR> alter rollback segment prod_dummy online;
Statement processed.
SVRMGR>
SVRMGR> CREATE TEMPORARY TABLESPACE temp_prod
2> TEMPFILE 'C:\ORAAMRESH\ORADATA\prod\prod_TEMP.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M
4> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE users
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prod01.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
4> ONLINE
5> ;
Statement processed.
SVRMGR>
SVRMGR> drop tablespace users;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE users
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd03.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
4> ONLINE;
Statement processed.
SVRMGR>
SVRMGR> ALTER USER SYS IDENTIFIED BY MANAGER TEMPORARY TABLESPACE
2> TEMPORARY_DATA
3> ;
Statement processed.
SVRMGR> ALTER USER SYSTEM IDENTIFIED BY MANAGER TEMPORARY TABLESPACE
2> TEMPORARY_DATA
3> ;
Statement processed.
SVRMGR>
SVRMGR> create public rollback segment PD01 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD02 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD03 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD04 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD05 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD01 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD02 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD03 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD04 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD05 ONLINE;
Statement processed.
SVRMGR>
SVRMGR> create public rollback segment PD21 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD22 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD23 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD24 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD25 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD21 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD22 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD23 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD24 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD25 ONLINE;
Statement processed.
SVRMGR>
SVRMGR> connect sys/manager
Connected.
SVRMGR> select name from v$database;
NAME
---------
PROD
1 row selected.




Next step is to connect thru sys/manger account or “/as sysdba”

And run following scripts.

catalog.sql {creates the data dictionary }
catsnmp.sql { creates the dbsnmp account and the SNMPAGENT role. The catsnmp.sql script is run by catalog.sql during the installation }
catexp7.sql (creates the necessary export views{for full database export})
catproc.sql {Creates structure needed to run PL/SQL}









10 XXRPA_ENTITLEMENT_REFERENCE_TABLE.sql rita/apb/functional/datamodel/scripts 1.2
15 XXRPA_ENTITLEMENTS_WFL02_LOG_TABLE.sql rita/apb/functional/datamodel/scripts 1.1
17 XXRPA_LANDPARCEL_ORDER_S_SEQUENCE.sql rita/apb/functional/datamodel/scripts 1.2
21 XXRPA_NR_REFERENCE_AMOUNT_TABLE.sql rita/apb/functional/datamodel/scripts 1.2
29 XXRPA_REF_AMTS_LINK_TABLE.sql rita/apb/functional/datamodel/scripts 1.2