Oracle database: database link ORA-00600: internal error code

Workaround for ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:

How to Create a Database Link in Another User’s Schema

Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.


Recently I had situation when I didn't know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.

In that case my method was:
(execute this steps as user with admin privileges)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- Grant privilege to create database link to user grant create database link to locuser; -- Create temporary procedure to execute create database link as another user CREATE OR REPLACE PROCEDURE locuser.create_dblink AS BEGIN     EXECUTE IMMEDIATE 'CREATE DATABASE LINK TEST_DB CONNECT TO RMTUSER IDENTIFIED BY VALUES ''E977335D83B8468E'' USING ''testdb'''; END create_dblink; / -- Execute procedure and create database link in another schema exec locuser.create_dblink; -- Drop temporary procedure drop procedure locuser.create_dblink; -- Revoke privilege to create database link revoke create database link from locuser;

But when I tried to test database link I’ve received this nasty error:

 ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], [], [], [], [], [] 

There is MOS note related to this error: [ID 1309705.1]

 The error suggests that when the database link was created, the was established using the syntax IDENTIFIED BY VALUES as compared to the document syntax of IDENTIFIED BY

Use of IDENTIFIED BY VALUES is reserved for internal Oracle use only.

While earlier Oracle releases allowed the use of IDENTIFIED BY VALUES, this is not documented as being valid syntax.

From Oracle release 10gR2, database links must be created using the documented syntax.

Solution: Recreate the database link using valid syntax.

So Oracle support says that I cannot use syntax IDENTIFIED BY VALUES while creating database link.

Luckily I’ve found workaround :-)



Environment: Oracle EE 11.1.0.7 on OEL 5.8


To reproduce error I’ve pulled password of another user's schema from "sys.user$" internal table logged as sys user.
Now I will try to create a database link using this password. 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- Create database link SQL> CREATE DATABASE LINK TEST_DB CONNECT TO RMTUSER IDENTIFIED BY VALUES 'E977335D83B8468E' using 'testdb'; Database link created. -- Test database link SQL> select * from dual@TEST_DB; select * from dual@TEST_DB                    * ERROR at line 1: ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], [], [], [], [], [] -- Drop SQL> drop database link TEST_DB; Database link dropped.

To workaround this problem you could extract user password using “dbms_metadata.get_ddl” package to generate DDL for database link. 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- Use this query to generate DDL select dbms_metadata.get_ddl('DB_LINK',db_link,owner)||';' from dba_db_links; -- Create database link SQL> CREATE DATABASE LINK "TEST_DB" CONNECT TO RMTUSER IDENTIFIED BY VALUES '05F57DA167252CDAD388952CAE91A1ED33' USING 'testdb'; Database link created. -- Test database link SQL> select * from dual@TEST_DB; D - X -- Drop SQL> drop database link TEST_DB; Database link dropped.

In this case, with differently hashed password, using IDENTIFIED BY VALUES is valid syntax.

Although you can create database link using this syntax have in mind that this is not documented as being valid syntax. So use valid and documented syntax when you can. 

Oracle database: Generate AWR multiple reports

Use the below script and just modify either interval or snap_id range.  

set serveroutput on

spool master_awr_control.sql

declare

cursor c is

select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt

, di.instance_name inst_name

, di.instance_number instance_number

, di.db_name db_name

, di.dbid dbid

, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id

, s.snap_id end_snap_id

, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat

, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat

, s.snap_level lvl

from dba_hist_snapshot s

, dba_hist_database_instance di

,gv$instance i

,v$database d

where s.dbid = d.dbid

and di.dbid = d.dbid

and s.instance_number = i.instance_number

and di.instance_number = i.instance_number

and di.dbid = s.dbid

and di.instance_number = s.instance_number

and di.startup_time = s.startup_time

--and s.begin_interval_time > trunc(sysdate -7) -- last last 7 days  

and  s.snap_id between 46291 and 46372 

order by di.db_name, i.instance_name, s.snap_id;

begin

for c1 in c

loop

if c1.begin_snap_id > 0 then

dbms_output.put_line('spool '||c1.inst_name||'_'

||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');

dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||

c1.instance_number||','||

c1.begin_snap_id||','||

c1.end_snap_id||',0 ));');

dbms_output.put_line('spool off');

end if;

end loop;

end;

/

spool off;

set heading off

set pages 50000

set linesize 1500

set trimspool on

set trimout on

set term off

set verify off;

set feedback off;

@master_awr_control.sql

exit

Oracle dba / apps interview questions

DBA and Apps DBA interview questions

    I found these questions challenging as these questions will surely need some time  along with practical experience on the tasks and concepts of Oracle Apps,RAC.

1)Explain Database migration steps in details?(DBA interview question)
Ans: Database migration or Data migration involves a team effort of DBAs,Developers,Data Modelors/Database Designers and Applications Administrator if the other applications servers like JBOSS/tomcat is involved.When we are working in team the best way to complete this task is to prepare a plan of tasks along with time and table well in advance on an excel sheet and intimate each of the team members to communicate effectively.
From DBA's point below is an example of one such task,Check the below link for the details:

2)What are the 3 ips used in RAC configuration,explain them in brief?(DBA interview question)
Ans: 3 IPs used in RAC configuration are public,private and virtual IP.
1.Public IP address:Public IP adress is the normal IP address typically used to manage storage,system and database.
2.private IP address:Private IP address is used only for internal clustering processing (i.e Cache Fusion or as interconnect).
3.Virtual IP address:VIP is used by database applications to enable failover when one cluster node fails.

3)What is cache fusion?(DBA interview question)
Ans: Oracle RAC uses Cache Fusion to synchronize the data stored in the buffer cache of each database instance. Cache Fusion moves current data blocks (which reside in memory) between database instances, rather than having one database instance write the data blocks to disk and requiring another database instance to reread the data blocks from disk. When a data block located in the buffer cache of one instance is required by another instance, Cache Fusion transfers the data block directly between the instances using the interconnect, enabling the Oracle RAC database to access and modify data as if the data resided in a single buffer cache.

4)What is voting Disk,what is the major use of it?(DBA interview question)
Ans:Voting disk Manages cluster membership and arbitrates cluster ownership
between the nodes in case of network failures.The voting disk is a file that
resides on shared storage.

5)What is OCR,what is the major use of it?(DBA interview question)
Ans:Oracle Cluster Registry (OCR)– Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases. The OCR also stores information about processes that Oracle Clusterware controls. The OCR resides on shared storage that is accessible by all the nodes in your cluster.

6)In RAC 11g as we all know ASM can be used for storing OCR and voting disk,if that is the case how the clusterware start?(DBA interview question)
Ans: The clusterware starts and works properly because of the new feature added in Oracle11g release2 for ASM i.e ACFS(ASM cluster file system).ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution available.

7)How you change apps password?Explain the syntax of FNDCPASS?(Apps DBA interview question)
Ans: FNDCPASS utility is used to change the password of apps/applsys and all other application products users like gl,ar.FNDCPASS utilliy is supplied with various parameters and options,details are given below along with one task for complete understanding.

Note: Backup FND_ORACLE_USERID and FND_USER tables before using FNDCPASS utility for safety purpose.

Note 1: Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same.
If you change the password for one, FNDCPASS automatically changes the others

Note 2:After changing the password as the golden rule for apps dba check the log file if it gives
error than password is unchaged.Error details in log file can be as give below:

ERRORs:
APP-FND-01496 Results From FNDCPASS Chaning The APPLSYS password
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password

Note 3:When changing 'apps' password it is important to run autoconfig to propagate the change across the application or else we will need change the 'apps' password in following files manually which can be a tedious task.
1. $IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file
2. $FND_TOP/resource/wfmail.cfg
3. $COMMON_TOP/admin/scripts/adcmctl.ch
4. $OA_HTML/bin/appsweb.cfg
5. $AD_TOP/admin/template/CGIcmd.dat

Q 8) What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?(Apps DBA inteview question)
Ans: If we lost the 'apps' user password we can see this password in wdbsvr.app file.
The file wdbsvr.app file is located under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &
Sample entry in wdbsvr.app
[DAD_VISION11I]
connect_string = VISION11I
password = apps
username = APPS
default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I ,
it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).So story about this file doesn't stop here,this URL which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine
or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

Note:In Apps 11i we have apps password without encrypted form in the below mentioned locations:

• iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
• ORACLE_HOME/reports60/server/CGIcmd.dat

In R12 you won't see these files anymore because Modplsql module got removed and we won't be seeing the files with apps password.

9)Explain the process when we login with 'sysadmin' what happens at the backend?(Apps DBA interview question)
Which table is involved?
Ans: When we login using 'sysadmin' user and password there is a validation takes place,once the user gets validated than E-business suite home page is displayed.

8)How you troubleshoot apps login issue?(Apps DBA interview question)
Ans:Atul's link explain this in best way,please go through it even for troubleshooting at our work environment also.

http://becomeappsdba.blogspot.com/2006/10/troubleshoot-oracle-apps-web-server.html

9)Explain the major troubling which you had done in Oracle Apps?
Ans:We have to explain the recent troubeshooting which we had done recently.Whenever we are doing troubleshooting in Oracle Applications,it is very important to get familiar with the log file locations,depending
on release of Apps.In Apps 11i and R12 the log file locations have changed,so best link I found to remember this locations is given below:

http://easyoracleapps.wordpress.com/category/r12/

10)What do we have in FND_NODES?when do we run FND_CONC_CLONE.SETUP_CLEAN ?(Apps DBA interview question)

Ans:FND_NODES have the information about node_name,node_mode, support_cp.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table to clear corrupted setup after performing
clonning of Applications.

##
## before cleanup
##

SQL>select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
HOST01 O Y N Y N
MIDTIER01 O N Y N Y
APPSERVER21 N Y N Y <-- bogust="" br="" host="">APPSERVER22 N Y N Y <-- bogust="" br="" host="">APPSERVER23 N Y N Y <-- bogust="" br="" host="">AUTHENTICATION O N N N N

6 rows selected.



##
## Cleanup
##
login as 'applmgr'(application filesystem owner),set the application environment and run
FND_CONC_CLONE script to remove bogust host entries.Now login with 'apps' user in Database.

SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


##
## After cleanup
##

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES
/


##
## Run AutoConfig
##
Note:Context file location
In Apps 11i:$APPL_TOP/admin/$TWO_TASK.xml
In Apps R12:$INST_TOP/appl/admin/TWO_TASK_.xml

## Host01
In Apps 11i:
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/ appspass=

In Apps R12:
$AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/TWO_TASK_.xml
appspass=apps


## Host
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_host02.xml appspass=


##
## After AutoConfig runs
##


select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
MIDTIER01 O N Y N Y
AUTHENTICATION O N N N N
HOST01 O Y N Y N


11)Where do we find the details of all the ports in Oracle Apps?
Ans: Details of all the ports for various severs are present in portlist.ini file.
This file is located inside technology stack in the below mentioned path:

/oraAPP/apps/tech_st/10.1.2/install

The portlist.ini file have the details of all the ports information of all the servers like HTTP,Java,Oracle Notification
Server,Oracle Developer suite,DATABASE LISTENER,Web Cache,Reports Services,DCM Discovery,Log Loader which can be
seen below.

-bash-3.2$ view portlist.ini

;OracleAS Components reserve the following ports at install time.
;As a post-installation step, you can reconfigure a component to use a different port.
;Those changes will not be visible in this file.

[System]
Host Name = TESTNODEebs.in.apps.com

[Ports]
Oracle HTTP Server port = 7778
Oracle HTTP Server Listen port = 7779
Oracle HTTP Server SSL port = 8250
Oracle HTTP Server Listen (SSL) port = 4444
Java Object Cache port = 7000
Oracle Notification Server Request port = 6003
Oracle Notification Server Local port = 6102
Oracle Notification Server Remote port = 6201
Oracle Developer Suite HTTP port = 8888
Oracle Developer Suite JMS port = 9240
Oracle Developer Suite RMI port = 23910
ASG port = 7891
Oracle HTTP Server Diagnostic port = 7200
Log Loader port = 44000
Application Server Control RMI port = 1850
DCM Discovery port = 7100
Oracle Net Listener = 1521
Oracle Management Agent Port = 1157
Application Server Control port = 1156
Web Cache HTTP Listen port = 7778
Web Cache HTTP Listen (SSL) port = 8250
Web Cache Administration port = 9400
Web Cache Invalidation port = 9401
Web Cache Statistics port = 9402
Reports Services bridge port = 14011
Reports Services discoveryService port = 14021
Reports Services SQL*Net port = 1950

If you don't know port number of your web server you can check it in file
$IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

12)How we check the sysadmin password which we are using to login Oracle E-business suite home page is correct?(Apps DBA interview question)
Ans:We Use Following query to check whether 'sysadmin' password that we are
using is correct or incorrect.

select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using is wrong. We have to change sysadmin password using 'FNDCPASS' utility.

Oracle database: While enabling archivelog mode ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

While trying to change the database into archivelog mode I got this error:
-----------------


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

----------


Do not close the database in a way that it will require instance recovery(e.g. shutdown abort)
before you will mount it for archivelog mode change otherwise you
will get the below error "ORA-00265", always try to shutdown the database gracefully
by using "shutdown immediate".

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             381684408 bytes

Database Buffers           67108864 bytes

Redo Buffers                6008832 bytes

Database mounted.

Database opened.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             381684408 bytes

Database Buffers           67108864 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/oracle11g

Oldest online log sequence     438

Next log sequence to archive   440

Current log sequence           440                                                     

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM


when I saw the allert log.

"alert.log" 

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM

and even I cannot login into the DB via TOAD.

so logged into the unix server directly and invoked the sqlplus.
and then backed up the aud$ table and truncated the data in it.

SQL> select count(*) from aud$;

COUNT(*)
----------
200055


SQL> Create table aud_backup tablespace users as select * from aud$ ;

Table created.

SQL> truncate table aud$;

Table truncated.

SQL> select count(*) from aud$;

COUNT(*)
----------
0

Oracle DBA interview Q&A in an Indian style

1- How can I get diagnostic information from a hung instance before I restart the database?

/u01/app/oracle/bin>sqlplus system/mypass -prelim /nolog

SQL*Plus: Release 10.2.0.2.0 - Production  

> oradebug setmypid

Statement processed.  

> oradebug hanganalyze 12

Hang Analysis in /u01/app/oracle/admin/mysid/udump/mysid_ora_1234.trc

========================================================================
2- How to enable tracing for a specific or current session?

In 11g:

For current: exec dbms_monitor.session_trace_enable(NULL, NULL);

For a specific: exec dbms_monitor.session_trace_enable(144, NULL); -- 144 is the SID number

10g

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE,binds => TRUE);

========================================================================
3- How to copy schema into same database?

Using the impdp utility.

Let say we are copying SCOTT schema to SCOTT_NEW

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;

SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

This eliminates the creation of dump file, by using database link with the parameter network_link      

========================================================================
4- RAC to non-RAC cloning steps?

1- Run Preclone script on both Dbtier and AppsTier on the SOURCE system.
2- Create backup on SOURCE Databases to FILESYSTEM (/backup)
run {
allocate channel d1 type disk;
backup format '/backup/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backup/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

3- compress database ORACLE HOME on the source node, then move it to the
source node using GZIP utility as the following:
tar cvf – DB_Home | gzip > DB_Home.tar.gz

4- Copy the tar file of the Oracle HOME which has been taken in the previous step
to the target node and extract this file.

5- Copy the backup pieces which has been taken in step2 to the TARGET node, and in the
same location where the backup pieces has been taken in step 2 (è /backup)

6- Run adcfgclone.pl script on the TARGET db node using the following command:
perl adcfgclone.pl dbTechStack
This will create the init parameter file, context file and environment files on the target
node.

7- now on the TARGET node and as oracle user, create the directories where the
datafiles,onlinelogs and tempfiles are stored on your ASM storage:
SOURCE NODE TARGET NODE
+DATA1/prod/datafile >> $ mkdir $DATA_TOP/prod/datafile
+DATA1/prod/onlinelog >> $ mkdir $DATA_TOP/prod/onlinelog
+DATA1/prod/tempfile >> $ mkdir $DATA_TOP/prod/tempfile
Suppose that the DATA_TOP on the target node is /u01/app/CLONE/oradata

8- Change initialization parameters on the init file on the target instance:
db_file_name_convert =('+DATA1, '/u01/app/CLONE/oradata')
log_file_name_convert =('+DATA1', '/u01/app/CLONE/oradata')

9- now on the target node, startup the database in nomount mode

10- check that you can access the source database, by copying tnsEntries
from the SOURCE Node to the Target node tnsnames.ora file.
Step 11) on the target node, connect to the RMAN using the following command, where
PROD is your source database, and CLONE is the target database:
$ rman target sys/sys@PROD auxiliary /
RMAN>
run
{
allocate auxiliary channel ch1 type disk;
duplicate target database to CLONE;
release channel ch1;
}

12- After the RMAN command completed successfully, shutdown the database,
then change the following database initialization parameters, in init parameter file:
_no_recovery_through_resetlogs=TRUE
undo_management to =MANUAL
undo_tablespace=UNDOTBS1
Now open the database with RESETLOGS option;

13- Create APPS_UNDOTS1 tablespace using the following statements:
CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE
'/u01/app/CLONE/oradata/prod/datafile/undo01.dbf' SIZE 4000M AUTOEXTEND
ON NEXT 100M MAXSIZE 7000M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

14- now edit the init parameter file, and change the following initialization
parameters
undo_management to =AUTO
undo_tablespace= APPS_UNDOTS1
and remove the added initialization parameter_no_recovery_through_resetlogs=TRUE
Drop all unused undo tablespaces

15- Disable database archive on the TARGET database, and start the database in
open mode


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

Oracle database cross platform migration methods:
1. Export / Import to include the use of Datapump facilities. All versions support Export/Import but for Datapump 10.1.0.2 or higher is required
2. Transportable Tablespaces 10G or Later
3. RMAN Convert Database functions. 10G or Later
4. Streams Replication
5. Create Table As Select (CTAS)
6. Dataguard Heterogeneous Primary and Physical Standbys
7. Oracle Golden Gate (For assistance with Oracle Golden Gate, an SR needs opened with the correct team)

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

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