database

Cloning database..Just do some Post checks !!

Well I will be covering some points which I believe should be part of post checklist for any cloned database environment. This might not be complete list as it contains point which I have encountered or heard of.

1) Change database Name and Database Id

You should try to change the database name/instance name for UAT/ Cloned environment. In case if there is specific requirement to have the same instance_name/db_name, then atleast you should try changing the database id. If you are using RMAN duplicate command, dbid will be changed automatically. But in case you do not use duplicate command, then dbid will remain same.

DBid change becomes very important if you are using rman catalog database. In case you connect to rman catalog database from new cloned DB (without changing DBID),it would resync the resetlog information in the rman catalog database. Next time you try to take RMAN database backup on production database, you will get following errors

RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20011: target database incarnation is not current in recovery catalog

Following metalink notes discuss how to handle these issues

Note 1070453.6 : RMAN: Point-in-Time Recovery of a Backup From Before Last Resetlogs
Note 237232.1 : How to Recover Through a Resetlogs Command Using RMAN

As you see, you will be better off changing Dbid (Database Id) at first place. DBNEWID can be used to change the DB_NAME or DBId or Both

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm

2) Check for database link

Check for database link present in the cloned environment. Ensure that these are select only dblinks and will not perform any DML in production databases. If you find any ,then you can either drop these or recreate them to point to any UAT or simply remove/hash out tnsnames entry corresponding to these hosts. Also check for any hard coded IP address in host column in DBA_DB_LINKS.

3)Remove or hash out any entries in tnsnames.ora pointing to production database and recovery catalog database

This again is to avoid any issues with dblink or RMAN catalog issues. Also note that when you hash the tnsnames.ora you need to place # in front of each line.

Incorrect	Correct
#TESTDB10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test10g)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb10g) ) )	#TESTDB10G = #(DESCRIPTION = # (ADDRESS_LIST = # (ADDRESS = (PROTOCOL = TCP)(HOST = test10g)(PORT = 1521)) # ) # (CONNECT_DATA = # (SERVICE_NAME = testdb10g) # ) #)

4) Check cronjobs for oracle database user
If you have copied cron entries for server from production ( as part of database migration activity) , then crosscheck which all jobs need to be enabled for this cloned environment. e.g You can easily disable any cron for RMAN database, archivelog backup.

5)Modify listener.ora file

Modify listener.ora file to include new host entry and port number. This is also quite important as in case you copied it from production server, you could turn off production listener by mistake. Check following metalink note for details

Note 460666.1 – How To Remotely Administer a Listener

6) Check for local_listener and remote_listener parameter

Check for local_listener and remote_listener parameter and modify accordingly. Note that not changing remote_listener parameter can also lead to issues where in your UAT/Test database can get registered with Listener running on Production server.

7)Modify /etc/hosts entries

Modify /etc/hosts entries to remove entries for production database. Also try to use /etc/hosts for resolving host instead of DNS.

8) Add tempfiles to Temporary tablespace

After you clone the environment, tempfiles need to be added to the database.

9) Check for Archivelog mode

Generally archivelog mode is disabled for UAT/Cloned databases. In case your production database is in archivelog mode, ensure that you disable the archiving.

10) Verify Initialization Parameters

Verify all initialization parameter’s like *_dump_dest locations,utl_file_dir ,sga_max_size , etc.

3 June, 2009 by Amit Bansal 10g 11g 0

Connections to DataBase Hang Including “/ as sysdba”

Recently I faced one issue where all the connection to database hung and it was also not possible to login to database using “/ as sysdba”.
To get access of sqlplus I used the following syntax:

$ sqlplus -prelim / as sysdba

With “prelim” option we can run some commands which will help in collection useful information about the problem.

This will work only in Oracle 10g and higher version.

After successfully getting connected run the following commands to generate Hanganalyze and systemstate traces:

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 266

SQL> oradebug tracefile_name

— This will give you the name of the tracefile generated.

SQL > oradebug dump hanganalyze 2

SQL > oradebug tracefile_name

To analyze these trace files one should be aware of Metalink Note: 215858.1.

After analyzing these files I found that following event was active and causing the hang:

<span style="font-family: arial,helvetica,sans-serif;"><span style="font-size: small;">"resmgr:cpu quantum"
Cmd: PL/SQL Execute

It means that the sessions are waiting for their turn on CPU.

This event occurs when resource manage is active and controls the allocation of CPU to processes.

We can also see the command which is causing all this: i.e some PL/SQL code was executing and spnning on for CPU.

After finding out this, checked with “TOP” command, got the PID of the process consuming all the cpu and killed that process with “kill -9”

After killing that process the users were able to connect.

So the cause of the Hang was found i.e PL/SQL, but it is still unknown why PL/SQL caused problems. 🙂

Cheers!!!

Saurabh Sood

7 April, 2009 by Saurabh Sood 10g 2

Scheduling Job through Cron??

In case you came across this link searching for Cron syntax, then you can refer to following link

http://www.adminschoice.com/docs/crontab.htm

Other’s can continue reading the article …. 😉

This was a problem which I faced yesterday. We had a mview refresh which was lagging on one of the server. On checking master site, I found that there were many pending requests coming from the second materialized view site . Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event.

These mviews were being refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result

 $ps -ef|grep scott|grep -v grep
    oracle 11725 11714  0 13:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 19981 19970  0  Feb  7  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 24794 24781  0 06:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 10538 10527  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 13972 13935  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  2601  2592  0 19:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 17274 17246  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  8308  8294  0 13:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

This was definitely wrong. I killed the database sessions and also killed the OS processes

$ ps -ef|grep Refresh_scott.sh|grep -v grep|awk ‘{print $2}’|xargs kill -9

This issue had occurred as one more site was added for mview refresh and due to locking issues (while deleting records from Mlog$), job could not complete before next refresh schedule. While scheduling scripts through cron, care should be taken to put a check if script is already running or not. This is required as Cron will spawn the new job (as per job schedule) irrespective of fact that whether earlier execution has completed or not. I used following logic to implement the same check

cnt=`ps -ef|grep Refresh_scott.sh|grep -v grep |wc -l`
if [ $cnt -eq 1 ]
then
echo "Running Fast Refresh now " > $DIR/Check_refresh_scott.log
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
else
echo "Fast refresh already running " > $DIR/Check_refresh_scott.log
date >> $DIR/Check_refresh_scott.log
exit
fi

There could be other logics too which can be used to implement the same. e.g Other could be

filename=$DIR/Check_refresh_scott.log
if [ -e $filename  ]
then
echo "Fast Refresh already running " > refresh_runnning.log
exit
else
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
rm $filename
fi

Other way could be to use DBMS_JOB and DBMS_SCHEDULER to schedule the database job.

9 February, 2009 by Amit Bansal 10g 11g 0

Recreating Database Link

I was trying to recreate database links as part of database migration activity. This involved mixture of 8i,9i and 10g database.

In case of 8i/9i it is very easy to find the old password which was used to create database link as passwords for database links are stored in Clear Text (decrypted format) in SYS.LINK$ .As a result we can easily re-create database links. Find below steps to perform the same

SQL> set pages 1000 lines 80
SQL> col db_link for a20
SQL> col host for a20
SQL> col user_id for a20
SQL> SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME            HOST                 USERID                 PASSWORD
-------------- -------------------- ----------------- -----------------
TO_AMIT         AMITDB             	 AMIT                 AMIT123

create database link TO_AMIT connect to AMIT identified by AMIT123 using 'AMITDB';
Database link created.

In case of 10g passwords are stored in encrypted format in SYS.LINK$. I tried to workaround this by recreating using identified by values.

SQL> create database link TO_AMIT connect to AMIT identified by values '14573425Z6D15503' using 'AMITDB';

Database link created.

SQL> select sysdate from dual@TO_AMIT;
select sysdate from dual@TO_APREAD
                         *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

I researched metalink and found Note 456320.1 according to which this is expected behavior and ‘identified by values’ can be used only by Oracle Internal utilities. Therefore only way to re-create database link is to use exp/imp. As this is 10g, EXPDP and IMPDP is better suited as it will help in exporting/importing database links only.

SQL> select * from DATABASE_EXPORT_OBJECTS where object_path like '%LINK%';

OBJECT_PATH                              COMMENTS                                           N
---------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/SCHEMA/DB_LINK           Private and public database links
DB_LINK                                  Private and public database links                  Y
SCHEMA/DB_LINK                           Private and public database links

From above we can find that we need to use DB_LINK in INCLUDE clause for exporting Database Links. Find below steps which I used to re-create database link in another schema

SQL> conn test/test123
Connected.
SQL> create database link TO_AMIT connect to AMIT identified by amit123 using 'test10';

Database link created.

SQL> select sysdate from dual@to_amit;

SYSDATE
---------
18-JAN-09

SQL> select owner,db_link,host from dba_db_links;

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

D:\>expdp system/oracle schemas=TEST include=DB_LINK dumpfile=expdp.dmp

Export: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:18:06

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=TEST include=DB_LINK dumpfile=expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  G:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST10\DPDUMP\EXPDP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:18:10

D:\>impdp system/oracle remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp

Import: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:20:38

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:20:40

D:\>exit

SQL> l
  1* select owner,db_link,host from dba_db_links
SQL> /

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10
AMIT                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

SQL> conn amit/amit123
Connected.
SQL> select sysdate from dual@to_Amit;

SYSDATE
---------
18-JAN-09

18 January, 2009 by Amit Bansal 10g 4

Creating Oracle Extended RAC on Oracle VM

Yesterday, I found one very useful article at OTN “Creating Oracle Extended RAC” on completely virtual environment using Oracle VM. As Virtualization is becoming popular day by day and is very cost effective, one must know how to use this to simulate actual environments. Click here for details on Oracle Extended RAC on Oracle VM.

30 December, 2008 by Saurabh Sood 10g 0

Full Database Export Failing With ORA-00980

While trying to do a full export for 10.2.0.3 database, we were getting following errors

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 980 encountered
ORA-00980: synonym translation is no longer valid
EXP-00000: Export terminated unsuccessfully

So as to debug the problem, we set errorstack for ORA-00980 as follows and tried exporting again

alter system set events '980 trace name errorstack level 3';

Export job again failed with error but this time it generated a trace file in user_dump_dest location. We got the following sql as failing statement in tracefile

ORA-00980: synonym translation is no longer valid
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM2) SYNNAM2,DBMS_JAVA.LONGNAME(SYNTAB) SYNTAB,TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID,
 TABOWNID, SYNOBJNO FROM   SYS.EXU9PTS      WHERE  TABOWNID = :1 ORDER  BY SYNTIME

A possible cause is that the package DBMS_JAVA is INVALID or absent in database so the public synonym DBMS_JAVA is no longer valid.Checking DBA_REGISTRY confirms that the JVM is in invalid state.

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
CATALOG  SYS      VALID        10.2.0.3.0 Oracle Database Catalog Views
CATPROC  SYS      VALID        10.2.0.3.0 Oracle Database Packages and Types
JAVAVM   SYS      INVALID      10.2.0.3.0 JServer JAVA Virtual Machine <--- Invalid state

We used Note 276554.1 – “How to Reload the JVM in 10.1.0.X and 10.2.0.X” in order to correct this problem.

Key things to look when diagnosing export/import issues

1)Check if you are using correct syntax. Use exp help=y or imp help=y to get the correct syntax.

2)Check if you are using correct exp/imp utility (version) against the database. Use following metalink note for compatibility matrix

Note 132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions
3)Check DBA_REGISTRY view to see if the components are in valid state.
4) Try setting errorstack if you are facing “ORA-“ error.
5)Try setting trace=y parameter in export/import. This will generate a trace file (similar to sql trace) which can be used to diagnose the problems

e.g exp system/passwd full=y file=expdat.dmp log=exp.log trace=y

22 October, 2008 by Amit Bansal 10g

database

Cloning database..Just do some Post checks !!

Connections to DataBase Hang Including “/ as sysdba”

Scheduling Job through Cron??

Recreating Database Link

Creating Oracle Extended RAC on Oracle VM

Full Database Export Failing With ORA-00980

Disclaimer!!

Recent Comments

Trending

Subscribe to Blog via Email