Lets Discuss DBA stuffs

ORA-14061: data type or length of an index partitioning column may not be changed

2018-02-09T07:40:00.001-08:00

Error while altering the table column size
ALTER TABLE TEST_TABLE MODIFY COL_N_NUMBER VARCHAR2(13)
Error report -
SQL Error: ORA-14061: data type or length of an index partitioning column may not be changed
14061. 00000 - "data type or length of an index partitioning column may not be changed"
*Cause: User issued ALTER TABLE statement attempting to modify
data type and/or length of a column used to partition some index
defined on the table named in ALTER TABLE statement, which is
illegal
*Action: Avoid modifying data type and/or length of index
partitioning column(s)

Solution:
a) Check the constraint or index on the underlying column to be modified

select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,INDEX_NAME , status from user_constraints where TABLE_NAME='TEST_TABLE'

We found that there is a unique constraint (constraint_type='U') on column COL_N_NUMBER. So first we need to disable it before modifying column size.

b) Get the Unique Constraint Name (constraint_type='U') and disable it first

SQL> alter table TEST_TABLE disable constraint TESTI_TABLE_1
Table altered.

c) Modify the column size
SQL> ALTER TABLE KCA_PART_DETAILS MODIFY COL_N_NUMBER VARCHAR2(13 CHAR)
Table altered.

d) Enable constraint back
SQL> alter table KCA_PART_DETAILS enable constraint TESTI_TABLE_1
Table altered.

Import Error - ORA-31634: job already exists ORA-31664: unable to construct unique job name when defaulted

2016-10-12T14:22:00.001-07:00

I was getting below error while i was trying to import a schema from 11gr2 database to another same version database.

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

To resolve this error just drop the import tables which got created previously while imports run. You can find all the tables using below query and then you can get rid of them (drop table )

SELECT o.status, o.object_id, o.object_type,
          o.owner||'.'||object_name "OWNER.OBJECT",o.created,j.state
     FROM dba_objects o, dba_datapump_jobs j
    WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   and o.owner='&SCHEMA' and j.state='NOT RUNNING' ORDER BY 4,2;

This will drop all the import tables for schema which you are using to do the import and import job having status as "NOT RUNNING".

Error during impdp in 11g - ORA-31693, ORA-29913, ORA-38301

2016-07-11T08:27:00.002-07:00

While importing data (using iimpdp. DB is 11.2.0.4) i was getting the below errror:

ORA-31693: Table data object "SCHEMA"."TABLE":"PARTITION_P1" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Solution:

Go for purging the dba recyclebin. TO purge dba recyclebin your dba user need to have alter system previlege.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Script to resize datafile in Oracle Database

2015-07-08T09:00:00.003-07:00

Script to resize datafile (to reclaim free space from datafiles)

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum
      from dba_extents
      group by file_id) b,
      dba_extents c,
     (select value db_block_size
      from v$parameter
      where name='db_block_size') d
where a.file_id= b.file_id
and   c.file_id = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Start and stop HADR DB2 database

2015-02-04T13:43:00.001-08:00

Here are the steps to STOP HADR DB2 database:
-------------------------------------------------

1. Deactivate HADR on standby database:

db2 deactivate db DB2DB01
DB20000I The DEACTIVATE DATABASE command completed successfully.

2. Stop HADR on standby database:

db2 STOP HADR ON DB DB2DB01
DB20000I The STOP HADR ON DATABASE command completed successfully.

Similar scripts you would need to run on primary database as well:

1. Deactivate HADR on Primary database:
db2 deactivate db DB2DB01
DB20000I The DEACTIVATE DATABASE command completed successfully

2. Stop HADR on Primary database:
db2 stop hadr on database DB2DB01 as primary
DB20000I The STOP HADR ON DATABASE command completed successfully.

Here are the steps to START HADR DB2 database:
-------------------------------------------------

1. Activate HADR on standby database:

db2 activate db DB2DB01
DB20000I The ACTIVATE DATABASE command completed successfully.

2. Start HADR on standby database:

db2 START HADR ON DB DB2DB01 AS STANDBY
DB20000I The START HADR ON DATABASE command completed successfully.

Samilar scripts you would need to run on primary database as well:

1. Activate HADR on Primary database:

db2 activate db DB2DB01
DB20000I The ACTIVATE DATABASE command completed successfully.

2. Start HADR on Primary database:

db2 start hadr on database DB2DB01 as primary
DB20000I The START HADR ON DATABASE command completed successfully.

Slow Export job in 11gR2 / Oracle Streams AQ: enqueue blocked on low memory

2015-01-28T14:58:00.000-08:00

Today one of our frequent export job started failing due to following error in our stage database (Oracle Database version 11.2.0.4 on OEL 6)

Oracle Streams AQ: enqueue blocked on low memory

On checking further i found that the export job was slow due to low stream_pool_size in the database. The wait event of the job was showing as below:

Oracle Streams AQ: enqueue blocked on low memory"

I also checked the oracle support metalinkfor reference which you can refer as well.

EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

The issue got resolved by increasing stream_pool_size to 256MB. Export started running as expected.

ORA-01466: unable to read data - table definition has changed

2015-01-01T14:46:00.002-08:00

Checking at the errors, looks like some DDL changes are happening in the databases while export is running. On checking further at the error and looking at the LAST_DDL_TIME in some of the tables for which expdp failed, the timestamp would be the exact time when export was running.

select object_name,subobject_name , object_type ,timestamp, last_ddl_time from dba_objects where object_name=;

In my case partitions (Object_Type =TABLE PARTITION) got added in the tables when export was running which was a DDL change through some auto jobs setup for the same schema/tables for which we were taking the export.

To fix this issue don't create do any DDL changed in the database while export dump is in progress.

Check database upgrade history

2014-07-01T06:59:00.001-07:00

You can check quickly in your oracle database when was last time any patch upgrade or recompile happened by checking DBA_REGISTRY_HISTORY view

select to_char(ACTION_TIME,'MON-DD-YYYY:HH24:MI:SS')ACTION_TIME,ACTION,namespace,VERSION,ID,BUNDLE_SERIES,COMMENTS from DBA_REGISTRY_HISTORY ;

SP2-0024: Nothing to change.

2013-12-24T07:08:00.002-08:00

One of my QA Engineer in my office complained about an oracle Error while running the database script when he was doing his daily QA deployment.

Enter user-name: Test1/test1@qadev1

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP
and Real Application Testing options

SQL> C:\db\QA1/989898989\QAFIX_989822_1.sql
SP2-0024: Nothing to change.

I checked it and found that my QA guy has not inlcuded '@' before running the query.

Correct one is:
SQL> @C:\db\QA1/989898989\QAFIX_989822_1.sql

This solved his issue.

Error while Installing 10.2.0.1 Oracle through runInstaller on OEL 6 X86_64

2013-03-07T16:54:00.001-08:00

Today while installing oracle 10.2.0.1 on server OEL 6 X86_64 i got weird error.

Installation process of 10.2.0.1 was failing at 83%
The installation process of 10.2.0.1 RDBMS software on SLES 11 x86_64 throws the following error at 83%, with “Relinking SGA fetchlet and nmcollector binary”.

INFO: make: *** [nmccollector] Error 2
INFO: End output from spawned process.
INFO: ----------------------------------
INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'collector' of makefile '/u01/app/oracle/product/10.2.0/db_1/sysman/lib/ins_emdb.mk'. See '/u01/app/oracle/oraInventory/logs/installActions2013-03-07_07-19-04PM.log' for details.
Exception Severity: 1

Later i checked this in Metalink asn according to it this can be ignore. Metalink ID 957982.1 talks about it.

Solution:
1. The popup error concerning the linking of target "collector" at about 83% of the linking process of the 10.2.0.1 base-release install should be ignored.
2. Apply 10.2.0.4 or 10.2.0.5 patchset

Reference:
BUG:8993720 - ERROR INVOKING TARGET 'COLLECTOR' OF MAKEFILE '$O_H/SYSMAN/LIB/INS_EMDB.MK'

Installing Oracle 10g Binaries on Redhat-5 (Issue with Redhat version)

2012-05-11T14:18:00.001-07:00

Installing 10g Binaries on Redhat-5 (Issue with Redhat version)

Error during redhat installation
===================================
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at /app/oraInventory/logs/installActions2012-04-27_03-12-11PM.log

------------------------------------------------------------------------------------------
Path of software:
SOFTWARE_PATH/oracle/linux/x86_64/base_10.2.0.1_64/database/install

cp -p oraparam.ini /tmp
vi /tmp/oraparam.ini

Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2
to
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2,redhat-5

Run the runIntaller using the below command to use the new oraparam.ini files:

./runInstaller -paramFile /tmp/oraparam.ini

runInstaller logfile:
------------------------
===========================You can ignore this =================================
Checking operating system requirements ...
Expected result: One of redhat-3,redhat-4,SuSE-9
Actual Result: redhat-Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Check complete. The overall result of this check is: Failed <<<<
Problem: Oracle Database 10g is not certified on the current operating system.
Recommendation: Make sure you are installing the software on the correct platform.
=======================================================================

Checking operating system package requirements ...
Check complete. The overall result of this check is: Not executed <<<<
OUI-18001: The operating system 'Linux Version redhat-Red Hat Enterprise Linux Server release 5.5 (Tikanga)' is not supported.
Recommendation: Install the required packages before continuing with the installation.
=======================================================================

Checking kernel parameters
Check complete. The overall result of this check is: Not executed <<<<
OUI-18001: The operating system 'Linux Version redhat-Red Hat Enterprise Linux Server release 5.5 (Tikanga)' is not supported.
Recommendation: Perform operating system specific instructions to update the kernel parameters.
=======================================================================

Checking Recommended glibc version
Check complete. The overall result of this check is: Not executed <<<<
OUI-18001: The operating system 'Linux Version redhat-Red Hat Enterprise Linux Server release 5.5 (Tikanga)' is not supported.
Recommendation: You may actually have installed packages which have obsoleted these, in which case you can successfully continue with the install. If you have not, it is recommended that you do not continue. Refer to the readme to find out how to get the missing packages.
=======================================================================

Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 257456MB
Check complete. The overall result of this check is: Passed
=======================================================================

Checking available swap space requirements ...
Expected result: 193092MB
Actual Result: 301529MB
Check complete. The overall result of this check is: Passed
=======================================================================

Checking Network Configuration requirements ...
Check complete. The overall result of this check is: Passed
=======================================================================

Overall status is passed. Ignore the warning which you get after this and proceed with the remaining installation.

ORA-00354: corrupt redo log block header/Issues with RMAN backup

2012-05-08T12:37:00.002-07:00

I faced this issue where in a perticular block 146150 in one of the redo log file got corrupted in one of my database. Please note that below can be implemented for the corrupt redo log block. This involves data loss as we are clearing the redo log which is having issue.I performed this for a test database.

alert log error
====================
Mon May 07 10:54:12 2012
ARC3: Log corruption near block 146150 change 83907949 time ?
CORRUPTION DETECTED: thread 1 sequence 2670 log 3 at block 146150. Arch found corrupt blocks
Errors in file /app/oracle/admin/diag/rdbms/ORCL/ORCL/trace/ORCL_arc3_20151.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 146150 change 83907949 time 04/20/2012 14:00:10
ORA-00312: online log 3 thread 1: '/ora04/oradata/ORCL/redo/g3m2.rdo'
ORA-00312: online log 3 thread 1: '/ora03/oradata/ORCL/redo/g3m1.rdo'
ARC3: All Archive destinations made inactive due to error 354
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oraarch/oradata/ORCL/ORCL_1_2670_756578622.arc' (error 354) (ORCL)

SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;

GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2725 2 ACTIVE
2 2726 2 CURRENT
3 2670 2 INACTIVE

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;

GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2728 2 CURRENT
2 2726 2 ACTIVE
3 2727 2 ACTIVE

SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;

GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2728 2 ACTIVE
2 2729 2 CURRENT
3 2727 2 ACTIVE

alert log
===========

Tue May 08 13:19:45 2012
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
Tue May 08 13:19:45 2012
Beginning global checkpoint up to RBA [0xaa6.2d.10], SCN: 85719068
Completed checkpoint up to RBA [0xaa6.2d.10], SCN: 85719068
Completed checkpoint up to RBA [0xaa6.2.10], SCN: 85718985
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 04/21/2012 06:00:18 (CHANGE 83917294) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 2670
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
Tue May 08 13:20:24 2012
Direct NFS: NFS3ERR 2 No such file or directory. path somnas301 mntport 4046 nfsport 2049
Archived Log entry 2702 added for thread 1 sequence 2725 ID 0x361bd8fe dest 1:
Archiver process freed from errors. No longer stopped

This is how issue was resolved.

Upgrade database from 9.2.0.5 to 9.2.0.7

2012-05-03T13:04:00.001-07:00

Steps followed to upgrade database from 9.2.0.5 to 9.2.0.7

This was just one of the specific requirement from the application team so i am posting it just in case it benefits anyone.

Steps:
Download software and extract the oracle installation software

Set the oracle environment

Shut down the database

Stop all processes

Take a backup of Oracle installation

Installation

Post Installation Task(Database Upgrade)

STEP 1: Download and extract the latest patch

You can use the oracle website to download the latest oracle patch set. Here we have installed p4163445_92070_SOLARIS64.zip

Here we have placed the patch set dump file p4163445_92070_SOLARIS64.zip at /var/opt/oracle/product

Once the patch set dump has been placed on the server extract the patch set p4163445_92070_SOLARIS64.zip

unzip p4163445_92070_SOLARIS64.zip

STEP2: Set up the oracle environments variables:
This is one of the very important step that need to be followed religiously because if this is not followed properly then this can ruin all the effort.

Set up mainly the correct ORACLE_HOME and ORACLE_SID
Export ORACLE_SID=blfxen1
Export ORACLE_HOME=/var/opt/oracle/product/9.2.0.5.0

STEP 3: SHUT DOWN DATABASE:

Ensure that before the installation begin shut down the databases which the using binaries for which you are doing the upgrading activity.

SQl>Shutdown immediate;

STEP 4: Stop all processes

Ensure that before you begin this upgradation activity all the oracle processes should be stopped.
The main oracle processes would be the database listeners as well as any agent(if running.

Lsnrctl stop
Or
lsnrop.sh stop

Ensure that you run the above command for all the listeners for the databases(if more than one) running on the same server.

STEP 5: BACKUP

This is recommended that before installation activity starts we should take a backup of the binaries on which the database is running. This is to ensure to roll back in case of any corruption after the installation activity completes.

STEP 6: INSTALLING BINARIES

a. First of all we need to direct the X applications to display on the local system. So for that export the DISPLAY parameter a:

DISPLAY=export DISPLAY=:0.0
For e.g.

DISPLAY=export DISPLAY=10.5.12.133:0.0

Where LOCAL_HOST is the address (ip or hostname) of the machine where you want to display runInstaller.

b. Check on your local machine where you are displaying the runInstaller for softwares like EXCEED/XSESSION (XSecurePro64) which supports display settings required for running the runInstaller.

c. Once the patch set dump is extracted

unzip p4163445_92070_SOLARIS64.zip

It will create a directory Disk1
Inside the directory below are the files that will be present.

(f7q21-08) [SID: blfxenv1]/var/opt/oracle/product>cd Disk1
(f7q21-08) [SID: blfxenv1]/var/opt/oracle/product/Disk1>ls -ltr
total 4280
-rwxr-xr-x 1 oracle dba 1477 Sep 1 2005 runInstaller
drwxr-xr-x 2 oracle dba 96 Sep 1 2005 response
drwxr-xr-x 4 oracle dba 1024 Sep 1 2005 install
-rwxr-xr-x 1 oracle dba 2087863 Sep 2 2005 9207_buglist.htm
-rwxr-xr-x 1 oracle dba 99327 Sep 2 2005 patch_note.htm
drwxr-xr-x 8 oracle dba 1024 Sep 2 2005 stage

d.Now,go to /var/opt/oracle/product/Disk1

Before running runInstaller ensure that file /var/opt/oracle/oraInst.loc contains the right oraInventory path

If any issue you face like the installer product are not being shown at the
GUI runInstaller window, you must give the correct path of the ORAINVENTORY in the orainst.loc file.

5.RUNINSTALLER

This will open a GUI window:

(f7q21-08) [SID: blfxdev2]/var/opt/oracle/product/Disk1>./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.6, 5.7, 5.8, 5.9 or 5.10. Actual 5.9
Passed

Checking Temp space: must be greater than 250 MB. Actual 27549 MB Passed
Checking swap space: must be greater than 500 MB. Actual 29116MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 256 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-08-20_11-15-44AM. Please wait ...(f7q21-08) [SID: blfxdev2]/var/opt/oracle/product/Disk1>Oracle Universal Installer, Version 10.1.0.4.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

Just ensure that installed product show the binaries installed

Here ensure that when you click on the Installed product (as shown above in the screen shot ) you find the Oracle products installed. If no check path of inventory_loc your file’s /var/opt/oracle/oraInst.loc and ensure that it is not corrupted. Other wise change this to path of $ORACLE_HOME/oraInventory

f11u30-06:sdas> more oraInst.loc
#Oracle Installer Location File Location
#Mon Jun 08 23:20:57 GMT 2009
inst_group=dba
inventory_loc=/var/opt/oracle/oraInventory

Also ensure that the Destination PATH taken while installation is your ORACLE_HOME and source PATH is file patchset_directory/Disk1/stage/products.xml(here my pathset_directory is /var/opt/oracle/product).

Here as shown in the above diagram, path will be the path of the product that needs to be installed. Here in this case it is /d2100/var/opt/oracle/product/stage/product.xml

At the destination side path will be the path of your old binaries. Here in this case this is ORACLE_HOME.

$ORACLE_HOME=/var/opt/oracle/product/9.2.0.5

Here once you click NEXT installer check the components present at the ORACLE_HOME location.

As shown above, the installer checks for the oracle components that are installed in the database. If the upgraded version is already installed then, an error will be popped up telling that the patchset is already applied.
Otherwise the product component will start loading on the server. Once this completes to 100% then we will get an overall statistics.

Next we will start the upgradation.

d. At last when the upgradation of binaries completes before which, you will be asked to run root.sh by the root user.
Run this file by root user otherwise contact unix SA’s
/d2100/var/opt/oracle/product/9.2.0.5.0/root.sh

Output of the root.sh
----------------------------

Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
bash-2.05# /d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

Adding entry to /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/initbillprd1.ora
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/orapwbillprd1
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/rdbms/filemap
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

STEP 7: DATABASE UPGRADE
a.Check SYSTEM Tablespace Size
Here as a pre-requisite we need to ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace. If not we need to add some space to system tablespace.
Next, check for SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

b.Check java_pool_size:
show parameter java_pool_size

Here if required increase the size of java_pool_size:

alter system set java_pool_size=’150M’ scope=spfile;

c.check shared_pool_size
show parameter shared_pool_size

Here if required increase the size of shared_pool_size

alter system set shared_pool_size =’150M’ scope=spfile;

d.If the system uses an initialization parameter file then change the value of these parameters in the pfile (initsid.ora)

Shut down the database: shut down immediate
Startup pfile=
Create spfile form pfile;
Shut immediate
Startup

Now ensure that the changes done in the pfile are getting reflected in the spfile
show parameter java_pool_size
show parameter shared_pool_size

e. Now we are ready for the upgradation as the pre check has completed.

First startup the data

Startup the database in migrate mode:
Startup migrate;

Once the database is started in migrate mode, we need to run catpatch.sql script.

Before running take a spool file
SQL> SPOOL patch_blfxenv1.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

I am attaching below log file that comes up on running the catpatch.sql script.

Once the catpatch finishes the last few lines will show you as given below:

Output file will show as given below

COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0

f.INVALID OBJECTS

Check all the invalid objects in the database. We should always run the utlrp utility once the upgrade completes.

To check the invalid objects in the database:
SQL>select * from dba_objects where status='INVALID'

Compile all the invalid objects in the database.
SQL>@?/rdbms/admin/utlrp

This completes the upgradation of the database from 9.2.0.5.0 to 9.2.0.7.0

g.CONFIRMATION

TO confirm the version of the several components installed in the database, query dba_registry

SQL> select COMP_NAME,VERSION from dba_registry;

COMP_NAME VERSION

Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0

ORA-16014: log n sequence# not archived, no available destinations

2012-05-03T12:59:00.001-07:00

Recently i created a standby database but soon at the primary side started seeing the below error:

Thu May 3 15:09:42 2012
Errors in file /app/oracle/admin/orcl/trace/bdump/orcl_arc1_246675.trc:

ORA-16014: log 9 sequence# 269 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/ora03/oradata/orcl/redo_stby/orcl_redo_g9m1.rdo'
ORA-00312: online log 9 thread 1: '/ora04/oradata/orcl/redo_stby/orcl_redo_g9m2.rdo'

Similarly

Thu May 3 15:19:52 2012

ORACLE Instance orcl - Archival Error
Thu May 3 15:19:52 2012
ORA-16014: log 10 sequence# 270 not archived, no available destinations
ORA-00312: online log 10 thread 1: '/ora01/oradata/orcl/redo_stby/orcl_redo_g10m1.rdo'
ORA-00312: online log 10 thread 1: '/ora02/oradata/orcl/redo_stby/orcl_redo_g10m2.rdo'

and

Thu May 3 15:22:29 2012
ORACLE Instance orcl - Archival Error
Thu May 3 15:22:29 2012
ORA-16014: log 6 sequence# 272 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/ora01/oradata/orcl/redo_stby/orcl_redo_g6m1.rdo'
ORA-00312: online log 6 thread 1: '/ora02/oradata/orcl/redo_stby/orcl_redo_g6m2.rdo'

To resolve this i found that the redo log groups 6,9,10 were that of standby so only option i had to get rid of these errors was to clear these unarchived redo groups.

So i did the below:
alter database clear unarchived logfile group 6;
alter database clear unarchived logfile group 9;

alter database clear unarchived logfile group 10;
Once i cleared these unarchived standby redo logs , i did not see them again :)

Listener Issues after patching oracle binaries in oracle database 10.2.0.4

2012-01-07T12:51:00.000-08:00

I had patched my oracle database binaries of 10.2.0.4 with patch 6460304 but i had to do this by creating a new oracle home for my database as this was a prod database and could not bring these binaries down by bringing down all the dependent databases as they were very critical. So i created a new oracle binaries 10.2.0.4 and then applied this patch on this.

So first brought down the listeners running for the database and database as well. Then i changed the oratab entries and tried bringing up the databases and the listeners.

Did the below steps as per oracle documentation:
# Downloaded the patch from oracle and then hosted in my unix solaris machine.
# % cd 6460304
#
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
#
# 4. PostInstall Steps:
#
# cd rdbms/admin
# sqlplus "/ as sysdba"
@@catnomtt.sql
@@dbmsmeta.sql
@@dbmsmeti.sql
@@dbmsmetu.sql
@@dbmsmetb.sql
@@dbmsmetd.sql
@@catmeta.sql
@@prvtmeta.plb
@@prvtmeti.plb
@@prvtmetu.plb
@@prvtmetb.plb
@@prvtmetd.plb
@@catmet2.sql

ORCL:/app/oracle/product/10.2.0.4_64_v2:Y ---> My new Oracle Home location

LISTENER_ORCL:/app/oracle/product/10.2.0.4_64_v2:N ---> My new listener location
#ORCL:/app/oracle/product/10.2.0.4_64:Y
#LISTENER_ORCL:/app/oracle/product/10.2.0.4_64:N

C:\Documents and Settings\User1>sqlplus user@orcl

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Jan 7 13:12:33 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

SOlution: Make sure all the changes of the new oracle home is done in oratab and listener.ora. In my case i missed to make this change in listener.ora.

LISTENER_ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXX.domain.com)(PORT = 1200))
)
)
)
SID_LIST_LISTENER_ORCL=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL.domain.com)
(ORACLE_HOME = /app/oracle/product/10.2.0.4_64)
### (ORACLE_HOME = /app/oracle/product/10.2.0.4_64_v2) ###New Oracle Home after patch 6460304 07th Jan 2012####
(SID_NAME = ORCL)
)
)

This is how my issue got resolved.

Error: Oracle executable binary mismatch detected

2012-01-07T11:23:00.000-08:00

Error:
I recently faced this issue. After patching binaries of 10.2.0.4 saw the below error continuosly in alert log of the database.

WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

Please ignore if this is after oracle patch. Solution is just to bounce the database.

Gathering stats in the database

2011-12-04T21:55:00.000-08:00

Version 9.2 onwards You can gather stats of the database by the below ways:

GATHER_TABLE_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',tabname=>'TABLE_NAME',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',granularity =>'ALL',cascade => TRUE,degree => DBMS_STATS.DEFAULT_DEGREE);

GATHER_INDEX_STATS
Execute DBMS_STATS.GATHER_INDEX_STATS ('TEST','TEST_PK',estimate_percent => 30, degree => 6);

GATHER_SCHEMA_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'SCHEMA_NAME'',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree => 12)

Renaming table in Oracle

2011-12-04T20:03:00.000-08:00

In Oracle you can rename a table in the same schema as below:

alter table OWNER. rename to NEW_TABLE NAME;

Here the new table will be created in the OWNER schema. Here the target table's schema name need not be required to mention.

Oracle database Broken job 10g

2011-11-07T07:39:00.000-08:00

TO check status of broken job:

SQL> select job, broken,what from dba_jobs
JOB BROKEN WHAT
----- ------ ----
3 N PROC1;
2 Y PROC2;

Here the BROKEN column of job 2 is Y which means that this is a broken one.

To mark it as not broken:

EXEC DBMS_JOB.BROKEN(2,FALSE);

TO verify if the broken job is fixed.

SQL> select job, broken,what from dba_jobs

JOB BROKEN WHAT
--- ---- ----
3 N PROC1;
2 N PROC2;

Invalid XML/EXF/CATALOG/CATPROC component in registry

2011-10-30T01:26:00.000-07:00

In one of my database, I found that my XML/EXF/CATALOG/CATPROC component in registry got invalid.

COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter INVALID
CATALOG Oracle Database Catalog Views INVALID
CATPROC Oracle Database Packages and Types INVALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK INVALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.

Steps below:

SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catexf.sql

-- To validate EXF components
@utlrp.sql

SHUTDOWN IMMEDIATE

STARTUP

Finally all components became valid:

SQL> select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter VALID
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK VALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER COUNT(*)
------------------------------ ----------
PUBLIC 25
SYS 73
EXFSYS 20

Run utlrp

@?/rdbms/admin/utlrp.sql

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER COUNT(*)
------------------------------ ----------
PUBLIC 21
SYS 43
EXFSYS 2

SQL> SQL> select COMP_ID,COMP_NAME,STATUS from dba_registry;

COMP_ID COMP_NAM STATUS
---------- ----------------------- ---------
EXF Oracle Expression Filter VALID
CATALOG Oracle Database Catalog Views VALID
CATPROC Oracle Database Packages and Types VALID
JAVAVM JServer JAVA Virtual Machine VALID
XML Oracle XDK VALID
CATJAVA Oracle Database Java Packages VALID

6 rows selected.

WARNING: Inbound connection timed out (ORA-3136) after DR

2011-10-03T07:15:00.000-07:00

I just want to share one of my experience today with this error.
We did a DR test on one of our informatica standby database server using flashback letting primary database up and used by the application. Basically here standby database was opened as standalone database after cancelling the media recovery and opening the database. Before opening we enabled flashback in the database.

There was n number of issues faced and i would like to write on it in a separate post. Lets discuss considering error Inbound connection timed out (ORA-3136)
After we got the standby database after the application test during DR to put it back in sync with primary database and we did so.
After syncing though i was seeing that both the primary and standby was in sync i was continuouly seeing below error in alert log if standby database:

WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:10:50 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:11:20 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:11:51 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:12:21 2011
WARNING: inbound connection timed out (ORA-3136)
Sun Oct 2 20:12:31 2011
WARNING: inbound connection timed out (ORA-3136)

When I checked it further in sqlnet.log, it tells that the connection to the standby database is coming continuously from Informatica production server

***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
Time: 02-OCT-2011 19:53:15
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=148.168.**.**)(PORT=12345))

***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.3.0 - Production
Time: 02-OCT-2011 19:53:25
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=148.168.**.**)(PORT=12345))
***********************************************************************

SQL> !nslookup 148.168.**.**)(
Server: 127.0.0.1
Address: 127.0.0.1#53

Non-authoritative answer:
53.67.168.148.in-addr.arpa name = server001.pfizer.com.

Authoritative answers can be found from:

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

When i checked the server details of server001.pfizer.com in CMDB, i got to know that its informatica production application server. Then it understood that during DR application would have been pointed to the DR standby databaase and later after DR, they missed to disconnect those informatica connection to DR standby database.

Then i requested informatica team to disconnect and disable those connection to standby database from informatica production application server. Once this was done this error WARNING: Inbound connection timed out (ORA-3136) disappeared :).

Blogger Buzz: Blogger integrates with Amazon Associates

2010-04-26T08:51:00.000-07:00

Blogger Buzz: Blogger integrates with Amazon Associates

Automating Database Refreshes using BCV

2010-04-25T05:48:00.000-07:00

Steps:
1. Backup’s of the tables/database.
2. BCV snapshot of the SOURCE database
3. BRING DOWN THE LISTENERS
4. TAKE A TRACE OF THE CONTROL FILE
5. REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
6. USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
7. RUN all THE POST DATABASE SCRIPTS
8. BRING UP THE LISTENERS

BACKUP of the tables/Databases

Please ensure that before you start the refresh process you take a backup of the tables which are needed to be backed up.

BCV snapshot of the database

You can do this step in coordination with unix team where in you can request unix team/System Administrator to take a BCV snap shot of the database server from which you needs your development/test database to be refreshed. For this methodology storage team use to set up some SRDF disks on the server on which the BCV needs to be set up. Here I am taking an assumption that production database name is SOURCE-DB and the database needs to be refreshed is TARGET-DB

Once BCV is completed by the SA’s these BCV disks are placed on the dev/test server. Once this is completed, The BCV disks will be looking as below:

/dev/vx/dsk/bldmx1dg/d1000n
10G 7.6G 2.3G 78% /d1000b
/dev/vx/dsk/bldmx1dg/d1001n
172G 156G 15G 92% /d1001b
/dev/vx/dsk/bldmx1dg/d1002n
182G 119G 59G 68% /d1002b
/dev/vx/dsk/bldmx1dg/d1003n
182G 122G 57G 69% /d1003b
/dev/vx/dsk/bldmx1dg/d1004n
182G 118G 60G 67% /d1004b
/dev/vx/dsk/bldmx1dg/d1005
182G 112G 66G 63% /d1005b
/dev/vx/dsk/bldmx1dg/d1006
182G 182G 92M 100% /d1006b
/dev/vx/dsk/bldmx1dg/d1007
182G 155G 25G 87% /d1007b
/dev/vx/dsk/bldmx1dg/d1008
182G 128G 51G 72% /d1008b
/dev/vx/dsk/bldmx1dg/d1009
182G 137G 42G 77% /d1009b
/dev/vx/dsk/bldmx1dg/d1010
182G 82G 94G 47% /d1010b
/dev/vx/dsk/bldmx1dg/d1011
182G 97G 80G 55% /d1011b
/dev/vx/dsk/bldmx1dg/d1012
182G 61G 114G 35% /d1012b
/dev/vx/dsk/bldmx1dg/d1013
182G 64G 111G 37% /d1013b
/dev/vx/dsk/bldmx1dg/d1014
182G 110G 67G 63% /d1014b
/dev/vx/dsk/bldmx1dg/d1015
182G 98G 79G 56% /d1015b
/dev/vx/dsk/bldmx1dg/d1016
364G 363G 945M 100% /d1016b
/dev/vx/dsk/bldmx1dg/d1017
364G 360G 3.5G 100% /d1017b

Above ‘b’ in /d****b represents the BCV disks.

BRING DOWN THE LISTENERS

echo "Stopping listener : `date`" >> ${BCV_LOG}

lsnrctl stop

TAKE A TRACE OF THE CONTROL FILE

Here we use to bring up the database as SOURCE-DB on the test server. This is done because while the database BCV was done there were already many transactions are running on the database. So you need to shut down the database cleanly.

Below is the script that we use to bring up SOURCE database on the test server and then shut it down cleanly to have its SMON do all the transaction recovery.

Step1: set up the oracle environments. Here in my case I am using script /var/opt/oracle/env/.env to set up the environment

/var/opt/oracle/env/.env

Set the ORACLE_SID correctly.

export ORACLE_SID=

Connect as sysdba to the database and redirecting this to a log file $BCV_LOG
sqlplus "/ as sysdba" <>${BCV_LOG}

Startup the database with the spfile/pfile of the source database. Here you can use of minimal parameters which are required to bring up the database
startup mount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;

The above command will bring up the database SOURCE-DB on the target server using the control files of SOURCE-DB on the target server that is a copy of the CONTROL file of the SOURCE DATABASE of the source server.

Now take a trace of the control file. We are doing this so as to REUSE and recreate the control file of the SOURCE DATABASE on the test box.

alter database backup controlfile to trace as '/d1000/var/opt/oracle/admin//udump/control__$proc_date.trc'; --------------------------------------1

Now shut down this database
shutdown immediate
exit
eot

REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER

Below we are going to the location where the command 1 will create the trace file.

echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump

Now below is what we are doing:
i. Below is the sample trace file which we get on execting step1

ii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
TARGET-DB to SOURCE-DB
RESETLOG to NORESETLOGS
ARCHIVELOG to NOARCHIVELOG

Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
iii. Here basically there are two control file script in the trace file so we are taking one and passing all these to file control_.sql.
iv. Below is the script that we are using.

sed -e "1,/# be invalidated/d" -e "s///g" -e "s/RESETLOGS/NORESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" -e "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql

v. Run the generated control file for the database.

. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}

startup nomount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;
@control_.sql

This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.

vi. Recovery
As ofcourse the database will ask for recovery and we will recover as below and once the media recovery completes we will be open the database.

recover database;
alter database open;

vii. Do a clean shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.

shutdown immediate;
eot

USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER

viii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
REUSE to SET
SOURCE-DB to TARGET-DB
NORESETLOG to RESETLOGS
NOARCHIVELOG to ARCHIVELOG

Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
ix. Here basically there are two control file script in the trace file so we are taking one (here with the SET option) and passing all these to file control_.sql

x. Below is the script that we are using.

echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump

sed -e "1,/# be invalidated/d" -e "s/REUSE/SET/g" -e "s///g" -e "s/NORESETLOGS/RESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql

xi. Run the generated control file for the database.
xii. Here before you run the control file script @control_.sql, you need to make sure that you are removing/moving the control files which are older one and are on the same location as per the pfile/spfile that is being used to bring up SOURCE-DB database.

mv /d1001b/oradata//control01.ctl /d1001b/oradata//control01.ctl.bkp
mv /d1002b/oradata//control02.ctl /d1002b/oradata//control02.ctl.bkp

. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}

startup nomount
@control_.sql

This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.

xiii. Reset all the logs
Here as we don’t need the redo logs of the SOURCE-DB reset all the logs

alter database open resetlogs;

xiv. shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.

shutdown immediate;
eot

xv. Create the password file
Before creating remove the older password file

cd $ORACLE_HOME/dbs
rm orapw
orapwd file=orapw password=spongeb entries=5

xvi. Start the database

export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}

RUN all THE POST DATABASE SCRIPTS

You may need to run the past database refresh scripts like below and if anything else you may include in your script as well.

!sleep 10
set echo on
spool post_refresh__$proc_date.lst
@/var/opt/oracle/scripts/POST_change_passwords.sql
@/var/opt/oracle/scripts/post__sql.sql
@/var/opt/oracle/scripts//post__others.sql

spool off
exit

eof

BRING UP THE LISTENERS

echo "Starting listener : `date`" >> ${BCV_LOG}

lsnrctl start

Upgrading Oracle database from version 9205 to 9207

2010-04-25T05:38:00.000-07:00

Steps followed

Download software and extract the oracle installation software

Set the oracle environment

Shut down the database

Stop all processes

Take a backup of Oracle installation

Installation

Post Installation Task(Database Upgrade)

STEP 1: Download and extract the latest patch

You can use the oracle website to download the latest oracle patch set. Here we have installed p4163445_92070_SOLARIS64.zip

Here we have placed the patch set dump file p4163445_92070_SOLARIS64.zip at /var/opt/oracle/product

Once the patch set dump has been placed on the server extract the patch set p4163445_92070_SOLARIS64.zip

unzip p4163445_92070_SOLARIS64.zip

STEP2: Set up the oracle environments variables:
This is one of the very important step that need to be followed religiously because if this is not followed properly then this can ruin all the effort.

Set up mainly the correct ORACLE_HOME and ORACLE_SID
Export ORACLE_SID=blfxen1
Export ORACLE_HOME=/var/opt/oracle/product/9.2.0.5.0

STEP 3: SHUT DOWN DATABASE:

Ensure that before the installation begin shut down the databases which the using binaries for which you are doing the upgrading activity.

SQl>Shutdown immediate;

STEP 4: Stop all processes

Ensure that before you begin this upgradation activity all the oracle processes should be stopped.
The main oracle processes would be the database listeners as well as any agent(if running.

Lsnrctl stop
Or
lsnrop.sh stop

Ensure that you run the above command for all the listeners for the databases(if more than one) running on the same server.

STEP 5: BACKUP

This is recommended that before installation activity starts we should take a backup of the binaries on which the database is running. This is to ensure to roll back in case of any corruption after the installation activity completes.

STEP 6: INSTALLING BINARIES

a. First of all we need to direct the X applications to display on the local system. So for that export the DISPLAY parameter a:

DISPLAY=export DISPLAY=:0.0
For e.g.

DISPLAY=export DISPLAY=:0.0

Where LOCAL_HOST is the address (ip or hostname) of the machine where you want to display runInstaller.

b. Check on your local machine where you are displaying the runInstaller for softwares like EXCEED/XSESSION (XSecurePro64) which supports display settings required for running the runInstaller.

c. Once the patch set dump is extracted

unzip p4163445_92070_SOLARIS64.zip

It will create a directory Disk1
Inside the directory below are the files that will be present.

() [SID: db_name]/var/opt/oracle/product>cd Disk1
() [SID: db_name]/var/opt/oracle/product/Disk1>ls -ltr
total 4280
-rwxr-xr-x 1 oracle dba 1477 Sep 1 2005 runInstaller
drwxr-xr-x 2 oracle dba 96 Sep 1 2005 response
drwxr-xr-x 4 oracle dba 1024 Sep 1 2005 install
-rwxr-xr-x 1 oracle dba 2087863 Sep 2 2005 9207_buglist.htm
-rwxr-xr-x 1 oracle dba 99327 Sep 2 2005 patch_note.htm
drwxr-xr-x 8 oracle dba 1024 Sep 2 2005 stage

d.Now,go to /var/opt/oracle/product/Disk1

Before running runInstaller ensure that file /var/opt/oracle/oraInst.loc contains the right oraInventory path

If any issue you face like the installer product are not being shown at the
GUI runInstaller window, you must give the correct path of the ORAINVENTORY in the orainst.loc file.

5.RUNINSTALLER

This will open a GUI window:

() [SID: blfxdev2]/var/opt/oracle/product/Disk1>./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.6, 5.7, 5.8, 5.9 or 5.10. Actual 5.9
Passed

Checking Temp space: must be greater than 250 MB. Actual 27549 MB Passed
Checking swap space: must be greater than 500 MB. Actual 29116MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 256 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-08-20_11-15-44AM. Please wait ...() [SID: blfxdev2]/var/opt/oracle/product/Disk1>Oracle Universal Installer, Version 10.1.0.4.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct

Just ensure that installed product show the binaries installed

Here ensure that when you click on the Installed product (as shown above in the screen shot ) you find the Oracle products installed. If no check path of inventory_loc your file’s /var/opt/oracle/oraInst.loc and ensure that it is not corrupted. Other wise change this to path of $ORACLE_HOME/oraInventory

f11u30-06:sdas> more oraInst.loc
#Oracle Installer Location File Location
#Mon Jun 08 23:20:57 GMT 2009
inst_group=dba
inventory_loc=/var/opt/oracle/oraInventory

Also ensure that the Destination PATH taken while installation is your ORACLE_HOME and source PATH is file patchset_directory/Disk1/stage/products.xml(here my pathset_directory is /var/opt/oracle/product).

Here as shown in the above diagram, path will be the path of the product that needs to be installed. Here in this case it is /d2100/var/opt/oracle/product/stage/product.xml

At the destination side path will be the path of your old binaries. Here in this case this is ORACLE_HOME.

$ORACLE_HOME=/var/opt/oracle/product/9.2.0.5

Here once you click NEXT installer check the components present at the ORACLE_HOME location.

As shown above, the installer checks for the oracle components that are installed in the database. If the upgraded version is already installed then, an error will be popped up telling that the patchset is already applied.
Otherwise the product component will start loading on the server. Once this completes to 100% then we will get an overall statistics.

Next we will start the upgradation.

d. At last when the upgradation of binaries completes before which, you will be asked to run root.sh by the root user.
Run this file by root user otherwise contact unix SA’s
/d2100/var/opt/oracle/product/9.2.0.5.0/root.sh

Output of the root.sh
----------------------------

Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
bash-2.05# /d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Running Oracle9 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

Adding entry to /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/initbillprd1.ora
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/orapwbillprd1
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/rdbms/filemap
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

STEP 7: DATABASE UPGRADE
a.Check SYSTEM Tablespace Size
Here as a pre-requisite we need to ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace. If not we need to add some space to system tablespace.
Next, check for SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

b.Check java_pool_size:
show parameter java_pool_size

Here if required increase the size of java_pool_size:

alter system set java_pool_size=’150M’ scope=spfile;

c.check shared_pool_size
show parameter shared_pool_size

Here if required increase the size of shared_pool_size

alter system set shared_pool_size =’150M’ scope=spfile;

d.If the system uses an initialization parameter file then change the value of these parameters in the pfile (initsid.ora)

Shut down the database: shut down immediate
Startup pfile=
Create spfile form pfile;
Shut immediate
Startup

Now ensure that the changes done in the pfile are getting reflected in the spfile
show parameter java_pool_size
show parameter shared_pool_size

e. Now we are ready for the upgradation as the pre check has completed.

First startup the data

Startup the database in migrate mode:
Startup migrate;

Once the database is started in migrate mode, we need to run catpatch.sql script.

Before running take a spool file
SQL> SPOOL patch_db_name.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

I am attaching below log file that comes up on running the catpatch.sql script.

Once the catpatch finishes the last few lines will show you as given below:

Output file will show as given below

COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0

f.INVALID OBJECTS

Check all the invalid objects in the database. We should always run the utlrp utility once the upgrade completes.

To check the invalid objects in the database:
SQL>select * from dba_objects where status='INVALID'

Compile all the invalid objects in the database.
SQL>@?/rdbms/admin/utlrp

This completes the upgradation of the database from 9.2.0.5.0 to 9.2.0.7.0

g.CONFIRMATION

TO confirm the version of the several components installed in the database, query dba_registry

SQL> select COMP_NAME,VERSION from dba_registry;

COMP_NAME VERSION

Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0

Recycle Bin in Oracle 10G

2010-04-25T05:31:00.000-07:00

Recycle Bin in Oracle 10G

Introduction

In our day to day work we often come across scenario when a table gets deleted from database by mistake and we end up losing the data. The only way to recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.

In Oracle Database 10G Flash back Table feature is introduced. It makes the revival of dropped tables as simple as executing some sql commands. This feature works similar to windows recycle bin which we use in our daily work. Let’s see how it works:

How it works

First we create a test table.

SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);

TABLE CREATED

SQL> INSERT INTO TEST VALUES (‘version 1’, sysdate);

1 ROW CREATED

SQL> SELECT * FROM TEST;

VERSION CHANGE_TI
------------- ----------------
version 1 21-AUG-07

Now let’s drop this table and see what happens.

SQL> DROP TABLE TEST;

TABLE DROPPED

Let’s check the Recycle bin.

SQL> SELECT * FROM RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST DROP
TABLE USERS 2007-08-21:13:59:00
2007-08-21:14:02:42 9.0858E+12 YES YES
96232 96232 96232 32

This looks pretty weird. Let’s format the output.

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE, CAN AS ‘UND’, CAN_PURGE AS ‘PUR’, DROPTIME FROM RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR
------------------------------------------- ------------------------ ---------- ----- --------
DROPTIME
---------------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST TABLE YES YES
2007-08-21:14:02:42

What happened here is table TEST got renamed and it’s still present in the database. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$Z/6jVsGtRHe46leTmSFf5g==$0, preserving the complete object structure of the dropped table.

Recycle Bin

Recycle bin is a logical structure that catalogs the dropped objects. To see the contents of recycle bin one can use following command on Sql*Plus prompt (version 10.1)

SQL> SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TABLE 2007-08-21:14:02:42

It shows the original name and recycle bin name of the dropped object.

There are two recycle bin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.

USER_RECYCLEBIN: Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN: Lets administrators see all dropped objects in the recycle bin.

Turning recycle bin ON and OFF

Recycle bin is enabled by default in Oracle 10G. You can turn it OFF or ON from RECYCLEBIN initialization parameter, at system or session level.
To turn it OFF
• ALTER SESSION SET recyclebin = OFF;
• ALTER SYSTEM SET recyclebin = OFF;

To turn it ON
• ALTER SESSION SET recyclebin = ON;
• ALTER SYSTEM SET recyclebin = ON;

Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.

The initial value of the recyclebin parameter can be set in the text initialization files init.ora:
recyclebin=on

Reinstating the Table

To reinstate the table to its original state use the following command:

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

FLASHBACK COMPLETE.

Now the table is reinstated.
SQL> SELECT * FROM TEST;

VERSION CHANGE_TI
---------- ---------
version 1 21-AUG-07

The table is reinstated. If you check the recycle bin now it will be empty.

SQL> SELECT * FROM RECYCLEBIN;

NO ROWS SELECTED.

When we drop a table with RECYCLEBIN initialisation parameter set to ON, it does not frees up the space in original tablespace. We have to purge the recycle bin to free the space:

SQL> PURGE RECYCLEBIN;

Dropped table will be deleted permanently.

If you want to drop the table permanently at one go without using the Flashback feature, you can use the following command:

SQL> DROP TABLE TEST PURGE;

It will not rename the table and will delete it permanently.
Managing the Recycle Bin
So far we have learnt that once a table or database object is dropped, it doesn’t free the space and get renamed. So, what will happen if dropped objects take up all the space?
Actually, this situation never arrives. When a tablespace is completely filled with recycle bin data such that the data files have to extend to make room for more data, the tablespace is said to be under ‘Space Pressure’. In case of ‘Space Pressure’ recycle bin data is automatically purged in a first-in-first-out manner. The dependent objects e.g. indexes are removed before the table gets purged.
If user quotas are defined for a particular table space, Space pressure situation can occur for a user even if the table space is free. In this case also recycle bin data belonging to that user is purged in FIFO manner.
We can purge the table from recylce bin using following command:
SQL> PURGE TABLE TEST;
One can also use the recycle bin name to purge it.
SQL> PURGE TABLE ‘BIN$Z/6jVsGtRHe46leTmSFf5g==$0’;
This command will delete the table permanently along with its associated objects e.g. indexes, constraints etc. If you want to delete the dependent object from recyclebin, it can be done using following command:
SQL> PURGE INEDEX IND_TEST;
If the recycle bin for a particular user in table space needs to be purged it can be done using following command:
SQL> PURGE TABLESPACE USERS USER GAURAV;
You as a DBA can purge all the objects in any tablespace using

SQL> PURGE DBA_RECYCLEBIN;
So, recyclebin can be managed in several ways depending on the requirements.
Dropped Table Versions
Consider a scenario, where a user creates and drops a table several times with same name.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 1', sysdate);
1 row created.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 2', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 3', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
So, now if we reinstate the table ‘TEST’, which table will get reinstated? Let’s check it out.

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

VERSION CHANGE_TI
---------- ---------
version 3 09-OCT-07

So, we see that the version which was dropped last was reinstated first. So, recycle bin follows Last In First Out (LIFO) algorithm to reinstate the tables with same name.

You can’t reinstate the other two tables unless you rename them.

SQL> flashback table test to before drop rename to test2;
SQL> flashback table test to before drop rename to test1;

Warning while reinstating the Tables

Be warned that when you undrop a table, it gets reinstated to its original name, but its associated objects e.g. indexes, triggers are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. Original name has to be retrieved manually and then applied to the flashback objects.

This information is stored in view named user_recylcebin. Following query should be used to retrieve the old names, before flashing - back the table:

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'Table name ')
AND ORIGINAL_NAME! = 'Table name';

After the table is flashed-back, all the associated objects will be reinstated to their name shown in ‘Object_Name’ column.

Then you can rename them to their original names using Alter command.

Exceptions:
• Bitmap Indexes: Once they are dropped, they are not placed in recycle bin and hence not retrievable.
• Materialized View Logs: when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints: that reference another table are lost when the table is put in the recyclebin and then restored.
• Constraint Names: are also not retrievable from the view.

Other uses of Flashback Tables

Recycle bin can also be used to reinstate the Table to a different point in time, replacing the current table to a different version in past. e.g. following example reinstates current table to a System Change Number (SCN) 2390874650.

Flashback table test to SCN 2390874650;

This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table.

Conclusion

In this document we have covered some useful features of Recylcebin in Oracle.
To Sum up:

• Recycle bin maintains the version of dropped objects and reinstate them in LIFO order.
• Flashing back a table reinstates the table to its original name, but all the associated objects have to be renamed to their original name.
• Even if the recyclebin parameter is turned off, objects already present in recyclebin can be reinstated.

References:
1. http://www.orafaq.com/node/968
2. http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html