How to be familiar with a new Oracle database

Jephe Wu -

Objective: to take over a new database and issue some commands to know the basic things about this database


a. know what platform it is (workable on 10g)
select name, platform_id,platform_name from   v$database; 

note: use the following view result to get the endian table:
select PLATFORM_ID,platform_name,endian_format from v$transportable_platform;

b.  check if it's cluster database (RAC)
show parameter cluster_database;

c. check instance status
select * from v$instance;
note: you can find out instance_name, version, startup_time, status etc

d. check database status
select * from v$database;
select open_mode from v$database;

note: you can find out dbid, dbname, db_uniq_name, created_date, log_mode, db_role, platform_name etc

e. check datafile, logfile and controlfile name and location

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
select destination from v$archive_dest;
select name from v$tempfile;  temp file name

f. check archive log status
archive log list;
select log_mode from v$database;

g. check datafile, tempfile and online logfile size
select sum(bytes)/1024/1024 "MB" from dba_data_files;  Size of datafiles in MB
select sum(bytes)/1024/1024 "MB" from dba_temp_files;   Size of tempfiles in MB
select sum(bytes)/1024/1024 from v$log;  Size of redo logs in MB (Not accounting for mirrored redolog files)

 select sum(bytes)/(1024*1024) from dba_segments;  

To get the used-up space of your datafiles 

Total Size of the database  (
Also accounting for controlfiles and mirrored redolog files)
select a.data_size+b.temp_size+c.redo_size+d.cont_size "total_size"
from ( select sum(bytes) data_size
       from dba_data_files ) a,
     ( select nvl(sum(bytes),0) temp_size
       from dba_temp_files ) b,
     ( select sum(bytes) redo_size
       from sys.v_$logfile lf, sys.v_$log l
       where = c,
     ( select sum(block_size*file_size_blks) cont_size
       from v$controlfile ) d;

h. check using spfile or pfile
show parameter spfile;

i. check dataguard status
select database_role from v$database;
 (primary or standby)

dgmgrl / as sysdba
show configuration

# check archive log destination settings.
select destination, error from v$archive_dest;

j. check characterset
select * from nls_database_parameters;
select * from v$nls_parameters;

commonly used commands:
1. alter system register
2. alter system checkpoint
3. alter system switch logfile;

1. alter system syntax

Upgrading Oracle Database Server from to by dbua

Jephe Wu -

Environment: Windows 2003 server SP2 32bit, Oracle upgraded from Oracle 9.2)
Objective: upgrade it to Oracle standard edition


Part I - Upgrading Oracle database

1. backup before doing anything.

a. rman backup
Sign on to RMAN:
 rman "target / nocatalog"
 Issue the following RMAN commands:
  BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
  BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
  BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';



b. database full export backup (expdp)

exp backup_admin/password file=d:\oracle\oradata\orcl\backup\full_orcl.dmp log=d:\oracle\oradata\orcl\backup\exp_full_orcl.log consistent=y full=y

c. binary backup (cold copy)
Backup c:\program files\oracle directory and d:\oracle data directory.
If you have enough space to backup, no matter how big the temporary tablespace is, just copy them.

After doing copy and before running dbua, check temporary tablespace datafile size, shrink it if it's huge as follows:

shutdown listener and make sure nobody is using database (bounce db if required)

SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';


Database altered.

Tablespace altered.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

d. Windows OS backup

shutdown Windows, use RIP CD or system rescue cd to backup the whole C drive(c:\program files\oracle) and d:\oracle (where data exists) by ntfsclone program.

boot from RIP CD
a. backup partition table
sfdisk -d /dev/cciss/c0d0 | ssh remoteserver 'cat > /path/to/c0d0_sfdisk-d'
for restore, use
ssh remoteserver 'cat /path/to/c0d0_sfdisk-d' | sfdisk /dev/cciss/c0d0

b. backup MBR
dd if=/dev/cciss/c0d0 count=1 bs=512 | ssh remoteserver 'dd of=/path/to/mbr_dd'
for restore, use
ssh remoteserver 'dd if=/path/to/mbr_dd' | dd of=/dev/cciss/c0d0

c. backup ntfs partition /dev/cciss/c0d0p1
ntfsclone -s -o - /dev/cciss/c0d0p1 |gzip -c | ssh remoteserver 'cat > /path/to/c0d0p1_ntfsclone.gz'
for restore, use
ssh remoteserver 'gzip -dc /path/to/c0d0p1_ntfsclone.gz' | ntfsclone -r - -O /dev/cciss/c0d0p1

e. backup OS related files c:\program files\oracle
backup c:\program files\oracle

2. Software Installation (

 a. check before software installation
check the existing database charactset

 sql> select * from nls_database_parameters;

backup the existing spfile to pfile [optional]
sql> create pfile from spfile

b. Unzip both 1/6 and 2/6 zip files into same directory, then perform installation (must unzip and put them into the same directory)
c. Execute runInstaller to install ‘software only’ without creating database

3. Pre-upgrading preparation

a.[optional] Shrink temporary tablespace if required as above, otherwise, during installation, 11g will try to use cold backup method to copy all control,redo and data files to d:\oracle\admin\backup directory, including huge temporary tablespace files.

note: for all methods to shrink temporary tablespace under 10g, refer to support article - Resizing (or Recreating) the Temporary Tablespace [ID 409183.1]

Make sure nobody is using the database (stop listener service and bounce database if needed),  if someone is accessing database, refer to above support ID for another way to shrink temporary tablespace.

b. Confirm you have done backup for your database as step 1

During upgrade process, 11g will shutdown current database to do cold backup automatically before it performs the upgrade procedure if we enable backup option. 11g dbua will also create a batch file D:\oracle\product\10.2.0\admin\orcl\backup\SID_restore.bat for restoring our backup in case of upgrade failure.

c. After installing 11g db, Run pre-upgrade information tool
Once 11g software is installed, such as d:\oracle\\db_1, copy %ORACLE_HOME%\rdbms\admin\utlu112i.sql to somewhere, now let's login to 10g db as sysdba and startup database.

Sql> spool \path\to\upgrade.sql
Sql> @utlu112i.sql or
Sql> spool off

d. Adjust parameters if necessary before running ‘dbua’
Sql> purge dba_recyclebin;
Sql> alter system set sga_target=596M scope=spfile
  [might be adviced by pre-upgrade script,optional]

Sql> SELECT * FROM v$recover_file;
  should be no media files to recover

Resolving Outstanding Distributed Transactions
Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');

Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID';
For example:

3 rows selected.

Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is

Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

For invalid objects or invalid components, Oracle recommends running the the utlrp.sql before starting the upgrade as a means to minimize the number of invalid objects and components marked with WARNING.

Get characterset from the current database
sql> select * from nls_database_parameters;
record down nls_characterset and nls_nchar_characterset.

4. Run dbua to upgrade database

Inform the support team we are going to upgrade, ask them not to touch server.

a. actual upgrade by dbua
Cd $ORACL_HOME ( for Windows, start- program-choose 11g, database upgrade assistant )
Cd  bin
./dbua &

Do Not Move Database Files as Part of Upgrade
Choose ‘backup database’ and upgrade timezone version to version 14

Upgrade log is at C:\oracle\cfgtoollogs\dbua\orcl\upgrade2

b. Identifying Invalid Objects
After running pre-upgrade information tool, any invalid SYS/SYSTEM objects found before upgrading the database are stored in the table named registry$sys_inv_objs.
Any invalid non-SYS/SYSTEM objects found before upgrading the database are stored in registry$nonsys_inv_objs.

(sqlplus / as sysdba;

select * from sys.registry$sys_inv_objs; 
select * from sys.registry$nonsys_inv_objs;)

To identify any new invalid objects due to the upgrade
run @%ORACLE_HOME%\rdbms\admin\utluiobj.sql after finishing upgrade.
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';

should see the same number of invalid objects as sys.registry$sys_inv_objs and sys.registry$nonsys_inv_objs

c. Check the flush recovery area settings and confirm archive logging mode is enabled.

sql> show parameter db_recovery_file_dest;
Sql> archive log list;

d. Configure Enterprise Manager if required

If you are not yet using Oracle Enterprise Manager to manage your database, then install and configure Enterprise Manager Database Control.
If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:
emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]
You must run this from the Oracle home of the new Oracle Database 11g release. When prompted, provide the Oracle home from which the configuration is being upgraded.
You can also configure Enterprise Manager using DBCA. Select the Configure Database Options option, and then select the Enterprise Manager Repository option.

5. Rollback plan
a. Use the script that generated by dbua to restore to old 10g database

b. use our own rman backup to restore back

New features and changes in 11.2
Automatic Diagnostic Repository
The locations of alert logs and trace files are no longer set by the initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST. They are now kept in the Automatic Diagnostic Repository (ADR), whose location is set the by the initialization parameter DIAGNOSTIC_DEST.

Beginning with Oracle Database 11g Release 1 (11.1), the JOB_QUEUE_PROCESSES parameter is changed from a basic to a non-basic initialization parameter. Most databases only need to have basic parameters set in order to run properly and efficiently. The default value is also changed from 0 to 1000.
Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.

6. FAQ

1.    When installing over oracle, it prompts the following error message:
Cannot write to c:\oracle\product\10.2.0\db_1\bin\msvcr71.dll,
When using ‘process explorer’ and found process msdtc is holding it, which is ‘microsoft distributed coordinator’, stop that services from services.msc, if necessary, install ‘process explorer’ to find handle or dll for msvcr71.dll

2.    Services, ‘oracleserviceORCL’ previously point to 10.2, after upgrade, will point to 11.2,
OracleOraDb11g_home1TNSListener (C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR ) service will be created during upgrading (dbua), at the time start doing enterprise manager repository data migrate, it will prompt you to backup EM data for downgrade, after you say ‘yes’ to continue, it will start a 11g listener.

3.    EM create repository, got error SEVERE: 'job_queue_processes' must be greater than or equal to 1.
Sql> Alter system set job_queue_processes=2; can be changed dynamically.

Part II - actual upgrade notes

1. upgrading Oracle
reboot server into Windows,  follow the Oracle upgrade guide, install Oracle database 32bit for Windows in a separated directory

2. cleanup the database before upgrading
a.  sql>exec dbms_stats.gather_dictionary_stats;
b. sql>purge dba_recyclebin;
c. sql> select * from v$recover_file; should be no media file to recover

d. Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');

e. Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

f. Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

g. Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID'
For devora01, the following is the output:
3 rows selected.

h. Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

i. run downloaded script dbupgdiag.sql from Oracle support to check the number of invalid objects detail
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

after that,Validating invalid objects first
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

j. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
SQL> @$ORACLE_HOME\rdbms\admin\utlrp.sql
Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

k.Note down the location of datafiles, redo logs, control files. Also take a backup of all configuration files like listener.ora,tnsnames.ora (C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN) ,etc., from $ORACLE_HOME

SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;

Check services.msc to confirm
OracleServiceORCL is started
OracleOraDb10g_home1TNSListener is started
c:\windows\system32\drivers\etc\hosts has a line for EM to work

ipaddress   name_with_domain name

run select * from nls_database_parameters
to get nls_characterset WE8MSWIN1252

backup c:\program files\oracle to f:\jephe\
bakcup d:\oracle to f:\jephe
record down the number of files and folders for d:\oracle
104927 files and 12986 folders around 30G

after done backup, compare the number of files and folders to make sure they are same.

stop 10g listener
sqlplus / as sysdba
shutdown immediate;

go to services.msc to stop OracleServiceORCL service.

sqlplus / as sysdba
sql> spool d:\dba\upgrade.sql
sql> @......\utlu112i.sql
sql> spool off

change compatible to 10.1.0
execute dbms_stats.gather_dictionary_stats;

3. startup 'database upgrade assistance -dbua' from start- program files - oracle 11g to upgrade the 10g database,
follow the GUI to finish upgrading.

dbua from 11gR2
significant changes before dbua
a. compatible=10.1.0
b. tempfile resize

do not move database file
backup database
upgrade timezone to 14

8. checking after upgrade

a. it should be in archive log mode
sql> archive log list;
sq> SELECT name, value FROM v$parameter WHERE name = 'compatible';
compatible parameter should be in 10.1.0.
b. check if Enterprise Manager is able to start up.
c. query invalid objects generated by upgrading process

start upgrade at 3pm.
14% to 34% to finish 'upgrading Oracle server' part.
34%-38% for upgrading Jserver Java virtual machine
upgrading EM repository takes time - 48%-71%
upgrading Oracle XML database takes long time
post upgrading database also takes time

9. Reconfigure EM

Although inside windows services.msc dbconsole is not starting, but port 5500 might be already running and you can access https://hostname:5500/em/

If you need to recreate EM, do this:
a.    Drivers\etc\hosts to add ip and hostname
b.    Make sure sysman account Is there and you can login as sysman
c.    Check if can login as sysman and dbsnmp

disable EM SSL , just use http
emctl unsecure dbconsole

10. Abandon the Upgrade
a. To cancel the upgrade by restoring the previous backup

Log in to the system as the owner of the Oracle home directory of the previous release.
Sign on to RMAN:

sql> rman "target / nocatalog"

        RESTORE CONTROLFILE FROM 'save_controlfile_location';
        RESTORE DATABASE FROM TAG before_upgrade

b. or use dbua generated sid_restore.bat script to restore the cold backup back.

11. References:

b. How to create oracle Windows service -
c. Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
d. Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]

12. configuration files location

a. Dbconsole log file is at c:\oracle\product\11.2.0\dbhome_2\xxx_orcl\sysman\log
b. db console config file at C:\oracle\product\11.2.0\dbhome_2\xxx_orcl/sysman/config/
c. listener.ora and tnsname.ora is at C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN by default
d. 1/6 and 2/6 zip file for 11.2 contains 2824files, 906 folders and totally around 2.18G after unzip.

13. check scheduled task scripts, rewrite Oracle backup script if necessary 

How to create Oracle database service and listener on Windows 2003 server

Jephe Wu -

: Windows 2003 server SE, upgrading Oracle standard edition to
Objective: to be familiar with Oracle service and listener creation and deletion in case needed.

Normally, if you install Oracle 11g on Windows, it'll create listener and service automatically. In case you have deleted them and need to create back, here are the steps:


1. delete Oracle Windows service (database and listener)

a. manually delete from registry

run regedit or regedt32
Open regedit and browse to the following key

Find the service name 'OracleOraDb11g_home1TNSListener' or 'OracleOraDb10g_home1TNSListener'
right click it and export to a .reg file, then delete the key. (You might need to reboot server as refresh might not be able to remove service name completely, if that's the case, try also the following sc command to remove it)

b. use sc to delete service

backup the registry according to point a, then run:

sc delete OracleOraDb11g_home1TNSListener

2. create Oracle Windows service - Listener
a. sc

Create listener service:
sc create OracleOraDb11g_home1TNSListener [binPath= "C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR.exe"]
note: after binPath=, there must be a space there

b. run netca to create listener service by Oracle

Start -> All programs -> Oracle - OraDb11g_home1 -> Configuration and Migration Tools -> Net Configuration Assistant

When asking for Listener Name, don't change anything, just leave the default name 'LISTENER' there, it will create service name as OracleOraDb11g_home1TNSListener

3. create Oracle Windows service - database

a. stop OracleServiceorcl service (orcl is the sid here)

net stop OracleServiceORCL

b. use oradim to delete service

c:\> oradim -delete -sid orcl

c. use oradim to create again


c:\> oradim -new -id orcl -startmode auto

to use OS authentication

SID    The same SID name as the SID of the database you are upgrading.
PASSWORD    The password for the new release 10.2 database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.
USERS    The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
ORACLE_HOME    The release 10.2 Oracle home directory. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory.

4. References


How to cleanup Windows disk space?

Jephe Wu -

Objective: explore ways to clean up Windows disk drive space.


1. use 'portable folder' software to get bigger picture
  use portable folder to clean up C/D drive recycler folder
2. delete browser cache files and disk cleanup to compress unused files, also empty recycle bin
3. clear system center configuration manager cache files ( control panel - configuration manager - delete cache)

5. create a scheduled task to use tool 'blat' to send disk space notification by email

dir c:\ d:\ | find "free" > c:\blat\drivespace.txt
dir c:\ d:\"sql backups\daily backups" /OD >> c:\blat\drivespace.txt
c:\blat\blat c:\blat\drivespace.txt -subject "C and D Drive free Space and database daily backup logs" -to -server smtp -f

How to get HP ILO ip address?

Jephe Wu -

Objective: get the HP Proliant server IP address online or offline
Environment: Windows and/or Linux


1. hponcfg

search google 'HP Lights-Out Online Configuration Utility for Windows' or 'HP Lights-Out Online Configuration Utility for Linux' to access the following site:

C:\Program Files\HP\hponcfg>hponcfg /w iLo.xml

This will export the current settings from ILO, you can modify it then import it:

C:\Program Files\HP\hponcfg>hponcfg /f ilo.xml

2. web interface

Install HP PSP on Windows/Linux, access it through http://localhost:2301 or https://localhost:2381, management processor part to find out the IP address and link status.

3. Reboot the server and press the F8 key when the server is booting and displays the HP integrated Lights Out text.

4. References:

a. - Reconfigure iLo network settings using HPONCFG

b. HP Lights-Out Online Configuration Utility for Linux

c. HP Lights-Out Online Configuration Utility for Windows Server 2003/2008

How to prepare Oracle Installation on OEL5/RHEL5/CentOS5

Jephe Wu -

Environment: Linux 5.6 x86_64(OEL/CentOS/RHEL), without ULN support

Objective: preparing minimal system to install Oracle database


1. OEL5
as oracle-validated rpm requires UEK kernel(Unbreakable Enterprise kernel). The oracle-validated package is intended for OEL and RHEL systems registered and configured to use the Unbreakable Linux Network (ULN)

a. install Oracle-validated rpm from
b. prepare public Oracle yum repository according to
b. yum install oracle-validated-1.1.0-7.el5.x86_64.rpm
it will install all required rpm, but it won't prepare system such as creating oracle user, /etc/sysctl.conf etc

2. RHEL5/CentOS5

a. get required package from OEL5 system if you have, refer to point 1, install those rpms manually
b. just run command rpm -Uhv oracle-validated-<version>-<release>.rpm to get missing dependencies, just install them manually.
c. check required packages list from Oracle support article:
Master Note of Linux OS Requirements for Database Server [ID 851598.1]
then install those rpms manually.

d. Oracle support ID: Linux OS Installation with Reduced Set of Packages for Running Oracle Database Server [ID 728346.1]
e. Oracle Linux source rpms at and

f. Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6 -

g. Oralce Linux 6 installation -