Description:
When I used the below query to select NUM_ROWS but, I got the fractional value.
SQL>SELECT NUM_ROWS FROM DBA_INDEXES WHERE TABLE_NAME=’TABLE_NAME’;
Returned Value is 1228868.88217523.
I have used Analyze table estimate statistics with 10 percent, Sometimes Analyze table estimate statistics will not round the value for NUM_ROWS.
Solution I tried:
To avoid this I used ANALYZE TABLE COMPUTE STATISTICS.
Friday, November 27, 2009
Monday, November 23, 2009
How do I Select Oracle tables from Linked server (SQL Server)?
We can you use the below query to select Oracle tables.
SELECT * FROM OPENQUERY (linked server name,'SELECT * FROM TAB')
Use the below link to create Linked Server:
http://ragudba.blogspot.com/2009/11/how-to-create-sql-server-linked-server.html
SELECT * FROM OPENQUERY (linked server name,'SELECT * FROM TAB')
Use the below link to create Linked Server:
http://ragudba.blogspot.com/2009/11/how-to-create-sql-server-linked-server.html
How to Create SQL Server Linked Server? / Establish Connection between Oracle and SQL Server
Description:
I have used below steps to create Linked server. Using linked server we can access Oracle data from SQL Server.
Linked Server Name: TEST
Oracle Database: Oracle10g
Oracle Client Version: Oracle 9i
SQL Server Version: Microsoft SQL Server 2000
Step 1: Install Oracle 9i client (Runtime option is enough) on SQL Server machine. You can use 10g Client also.
Step 2: Check client server connectivity using tnsping utility from SQL Server machine,
cmd>tnsping Database_SID
Step 3: Log into SQL Server as “sa” and execute the below script
/****** Object: LinkedServer [TEST] Script Date: 11/23/2009 07:48:55 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'TEST', @provider=N'MSDAORA', @datasrc=N'TEST', @provstr=N'Provider=MSDAORA.1;User ID=reporter;Data Source=TEST;Persist Security Info=False'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dpub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
Step 4: After Execution of the above script go to Properties of the “TEST” Linked Server.
Server Enterprise Manager -> Microsoft SQL Servers -> Security -> Linked Servers -> TEST
Step 5: Go to Security tab from “TEST” Linked Server. Enter the details for Local Login (use SQL Server Login Credential), Remote User and Remote Password (use Oracle Login Credentials)
Step 6: Enable Be made using this security context
Enter the Oracle Username and Password which you entered on Step 5.
Step 7: Enter “OK”
Note: Sometimes you may get Error 7399: OLE DB provider 'MSDAORA' reported an error. To solve this error reboot the SQL Server.
I have used below steps to create Linked server. Using linked server we can access Oracle data from SQL Server.
Linked Server Name: TEST
Oracle Database: Oracle10g
Oracle Client Version: Oracle 9i
SQL Server Version: Microsoft SQL Server 2000
Step 1: Install Oracle 9i client (Runtime option is enough) on SQL Server machine. You can use 10g Client also.
Step 2: Check client server connectivity using tnsping utility from SQL Server machine,
cmd>tnsping Database_SID
Step 3: Log into SQL Server as “sa” and execute the below script
/****** Object: LinkedServer [TEST] Script Date: 11/23/2009 07:48:55 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'TEST', @provider=N'MSDAORA', @datasrc=N'TEST', @provstr=N'Provider=MSDAORA.1;User ID=reporter;Data Source=TEST;Persist Security Info=False'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'dpub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
Step 4: After Execution of the above script go to Properties of the “TEST” Linked Server.
Server Enterprise Manager -> Microsoft SQL Servers -> Security -> Linked Servers -> TEST
Step 5: Go to Security tab from “TEST” Linked Server. Enter the details for Local Login (use SQL Server Login Credential), Remote User and Remote Password (use Oracle Login Credentials)
Step 6: Enable Be made using this security context
Enter the Oracle Username and Password which you entered on Step 5.
Step 7: Enter “OK”
Note: Sometimes you may get Error 7399: OLE DB provider 'MSDAORA' reported an error. To solve this error reboot the SQL Server.
How to change Solaris10 Hostname?
Description:I followed below steps to change the hostname for Solaris 10 (x86).
Step 1: Logged as “root”
Step 2: Added /etc/hosts file with new Solaris Hostname
eg: 192.168.246.128 Soltest
Step 3: Execute the below command to write nodename with new Solaris Hostname
#echo “Soltest” > /etc/nodename
Step 4: Execute the below command to write hostname.e1000g0 or /etc/hostname.pcn0 or file with new Solaris Hostname
#echo “Soltest” /etc/hostname.e1000g0
or
#echo “Soltest” /etc/hostname.pcn0
Step 5: #uname –S Soltest
Step 1: Logged as “root”
Step 2: Added /etc/hosts file with new Solaris Hostname
eg: 192.168.246.128 Soltest
Step 3: Execute the below command to write nodename with new Solaris Hostname
#echo “Soltest” > /etc/nodename
Step 4: Execute the below command to write hostname.e1000g0 or /etc/hostname.pcn0 or file with new Solaris Hostname
#echo “Soltest” /etc/hostname.e1000g0
or
#echo “Soltest” /etc/hostname.pcn0
Step 5: #uname –S Soltest
Friday, November 20, 2009
ORA-20000: Oracle Text error: DRG-10599: column is not indexed
Description:
Below error occured while selecting the values from table (Oracle Text)
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
Solution I tried:
I created the index using the below command
SQL>create index index_name on table_name (column_name) indextype is ctxsys.context;
Below error occured while selecting the values from table (Oracle Text)
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
Solution I tried:
I created the index using the below command
SQL>create index index_name on table_name (column_name) indextype is ctxsys.context;
Cloning Oracle database/Hotbackup Restoration/Synchronize Production database with Test database
Description:
We can use the below methods to synchronize/clone Production database to Test database. Before discussing about clone the database. I would like recall Hotbackup process.
Hotbackup Process:
Step 1: Login as “SYSDBA” using the below
$sqlplus “/as sysdba”
Step 2: Change the database to Begin backup mode using the below command.
SQL>alter database begin backup;
Note: “alter database begin backup” command will change all tablespaces to begin backup mode (starting from Oracle10g).
Step 3: Copy all the datafiles to Hotbackup location using “cp” (unix) / “ocopy” (windows)
Step 4: Change the database to End Backup mode using the below command
SQL>alter database end backup;
Step 5: Take the control file backup using the below command
SQL> alter database backup controlfile to '/Hotbackuplocation/control.sql';
Sample Control File:
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'ORACLE_HOME/TESTDB/REDO01.LOG' SIZE 40M,
GROUP 2 'ORACLE_HOME/TESTDB/REDO02.LOG' SIZE 40M,
GROUP 3 'ORACLE_HOME/TESTDB/REDO03.LOG' SIZE 40M
-- STANDBY LOGFILE
DATAFILE
'ORACLE_HOME/TESTDB/SYSTEM01.DBF',
'ORACLE_HOME/TESTDB/UNDOTBS01.DBF',
'ORACLE_HOME/TESTDB/SYSAUX01.DBF',
'ORACLE_HOME/TESTDB/USERS01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_BIG_TAB01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_SMALL_TAB01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_BIG_IDX01.DBF ',
'ORACLE_HOME/TESTDB/TESTDB_SMALL_IDX01.DBF'
CHARACTER SET WE8MSWIN1252
;
Cloning Databases/Hotbackup Restoration/Synchronize Production database with Test database
Step 1: Shutdown the test database if it is already running
SQL>shutdown immediate or SQL>shutdown abort
Step 2: Copy all the Hotbackup datafiles and control file backup from Production/Source database to test database.
Note: Initially, you can copy all the Hotbackup datafiles to one directory and then you can spread all the datafiles to the destination directory. If have space constraint, you can keep all the files in one directory, but you should change the path of the datafiles in control file. You check the control file’s datafile location and OS datafiles location, it should match or you may get the error.
Step 3: Start the database in NO MOUNT stage
SQL>startup nomount
Step 4: Create control file using the control file backup.
Step 5: Mount the database using below command
SQL> alter database mount;
Step 6: Recover the database using backup controlfile
SQL>recover database until cancel using backup controlfile;
Step 7: Database prompts for archivelog file and/ or redolog file names. It keeps failing until the redo log file with matching SCN is provided.
Please check the below link to do recovery with redolog file.
http://ragudba.blogspot.com/2009/11/ora-01113-media-recovery.html
Step 8: Open the database using the below command
SQL> alter database open resetlogs;
Database opened.
Step 9: After recovery you check the Temp tablespace exists or not. If did not exists, Create the Temporary tablespace using the below
ALTER TABLESPACE TEMP ADD TEMPFILE 'ORACLE_HOME/TESTDB/TEMP01.DBF'
SIZE 28311552 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
We can use the below methods to synchronize/clone Production database to Test database. Before discussing about clone the database. I would like recall Hotbackup process.
Hotbackup Process:
Step 1: Login as “SYSDBA” using the below
$sqlplus “/as sysdba”
Step 2: Change the database to Begin backup mode using the below command.
SQL>alter database begin backup;
Note: “alter database begin backup” command will change all tablespaces to begin backup mode (starting from Oracle10g).
Step 3: Copy all the datafiles to Hotbackup location using “cp” (unix) / “ocopy” (windows)
Step 4: Change the database to End Backup mode using the below command
SQL>alter database end backup;
Step 5: Take the control file backup using the below command
SQL> alter database backup controlfile to '/Hotbackuplocation/control.sql';
Sample Control File:
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'ORACLE_HOME/TESTDB/REDO01.LOG' SIZE 40M,
GROUP 2 'ORACLE_HOME/TESTDB/REDO02.LOG' SIZE 40M,
GROUP 3 'ORACLE_HOME/TESTDB/REDO03.LOG' SIZE 40M
-- STANDBY LOGFILE
DATAFILE
'ORACLE_HOME/TESTDB/SYSTEM01.DBF',
'ORACLE_HOME/TESTDB/UNDOTBS01.DBF',
'ORACLE_HOME/TESTDB/SYSAUX01.DBF',
'ORACLE_HOME/TESTDB/USERS01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_BIG_TAB01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_SMALL_TAB01.DBF',
'ORACLE_HOME/TESTDB/TESTDB_BIG_IDX01.DBF ',
'ORACLE_HOME/TESTDB/TESTDB_SMALL_IDX01.DBF'
CHARACTER SET WE8MSWIN1252
;
Cloning Databases/Hotbackup Restoration/Synchronize Production database with Test database
Step 1: Shutdown the test database if it is already running
SQL>shutdown immediate or SQL>shutdown abort
Step 2: Copy all the Hotbackup datafiles and control file backup from Production/Source database to test database.
Note: Initially, you can copy all the Hotbackup datafiles to one directory and then you can spread all the datafiles to the destination directory. If have space constraint, you can keep all the files in one directory, but you should change the path of the datafiles in control file. You check the control file’s datafile location and OS datafiles location, it should match or you may get the error.
Step 3: Start the database in NO MOUNT stage
SQL>startup nomount
Step 4: Create control file using the control file backup.
Step 5: Mount the database using below command
SQL> alter database mount;
Step 6: Recover the database using backup controlfile
SQL>recover database until cancel using backup controlfile;
Step 7: Database prompts for archivelog file and/ or redolog file names. It keeps failing until the redo log file with matching SCN is provided.
Please check the below link to do recovery with redolog file.
http://ragudba.blogspot.com/2009/11/ora-01113-media-recovery.html
Step 8: Open the database using the below command
SQL> alter database open resetlogs;
Database opened.
Step 9: After recovery you check the Temp tablespace exists or not. If did not exists, Create the Temporary tablespace using the below
ALTER TABLESPACE TEMP ADD TEMPFILE 'ORACLE_HOME/TESTDB/TEMP01.DBF'
SIZE 28311552 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Thursday, November 19, 2009
ORA-01113 "Media Recovery"
Description:
Below error I noticed when I start the database.
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: '/ORAIndex2/testdb/test_big_idx01.dbf'
Solution I tried:
Step 1: Shutdown the database if it is running using the below
SQL>shutdown abort;
Step 2: Logged as SYSDBA
$sqlplus “/as sysdba”
Step 3: Start the database in Mount stage
SQL>startup mount;
To check status of the database use the below query
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step 4: Recover the database using controlfile backup
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2100514654 generated at 10/25/2009 15:02:01 needed for thread
1
ORA-00289: suggestion : /ORAArchive/testdb/1_7840.ARC
ORA-00280: change 2100514654 for thread 1 is in sequence #7840
Specify log: {=suggested | filename | AUTO | CANCEL}
/ORARedo1/testdb/redo01a.log
Log applied.
Media recovery complete.
Note: I logged into /ORAArchive/testdb directory I did not find any file 1_7840.ARC. Hence I used Redo Log files to recover the database.
Step 5: Open the database with Resetlog option:
SQL> alter database open resetlogs;
Database altered.
Below error I noticed when I start the database.
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: '/ORAIndex2/testdb/test_big_idx01.dbf'
Solution I tried:
Step 1: Shutdown the database if it is running using the below
SQL>shutdown abort;
Step 2: Logged as SYSDBA
$sqlplus “/as sysdba”
Step 3: Start the database in Mount stage
SQL>startup mount;
To check status of the database use the below query
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step 4: Recover the database using controlfile backup
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2100514654 generated at 10/25/2009 15:02:01 needed for thread
1
ORA-00289: suggestion : /ORAArchive/testdb/1_7840.ARC
ORA-00280: change 2100514654 for thread 1 is in sequence #7840
Specify log: {
/ORARedo1/testdb/redo01a.log
Log applied.
Media recovery complete.
Note: I logged into /ORAArchive/testdb directory I did not find any file 1_7840.ARC. Hence I used Redo Log files to recover the database.
Step 5: Open the database with Resetlog option:
SQL> alter database open resetlogs;
Database altered.
Tuesday, November 17, 2009
“DBA_REGISTRY” status “INVALID” after an upgrade
Description:
I noticed the below objects in DBA_REGISTRY status as INVALID after an upgrade. I followed the below steps to solve this issue. Below steps I have tried based on Metalink ID: 753041.1
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Text
Spatial
Oracle interMedia
Oracle Data Mining
Oracle Workspace Manager
Solution I tried:
Step 1: Login as SYSDBA using the below command
$ sqlplus “/as sysdba”
Step 2: Shutdown the database if it is running using the below command
SQL> shutdown immediate
Step 3: SQL> startup ugrade;
Step 4: I ran the following sql files,
SQL>@$ORACLE_HOME/ctx/admin/ctxpatch.sql --Oracle Text
SQL>@$ORACLE_HOME/md/admin/sdopatch.sql --Spatial
SQL>@$ORACLE_HOME/ord/im/admin/impatch.sql -- Oracle Intermedia
SQL>@$ORACLE_HOME/rdbms/admin/odmpatch.sql --Oracle Data Mining
SQL>@$ORACLE_HOME/rdbms/admin/owmpatch.sql --Oracle Workspace Manager
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql - To recompile all the Invalid Objects
Step 5: SQL>Shutdown immediate;
Step 6: SQL>startup;
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected
I noticed the below objects in DBA_REGISTRY status as INVALID after an upgrade. I followed the below steps to solve this issue. Below steps I have tried based on Metalink ID: 753041.1
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Text
Spatial
Oracle interMedia
Oracle Data Mining
Oracle Workspace Manager
Solution I tried:
Step 1: Login as SYSDBA using the below command
$ sqlplus “/as sysdba”
Step 2: Shutdown the database if it is running using the below command
SQL> shutdown immediate
Step 3: SQL> startup ugrade;
Step 4: I ran the following sql files,
SQL>@$ORACLE_HOME/ctx/admin/ctxpatch.sql --Oracle Text
SQL>@$ORACLE_HOME/md/admin/sdopatch.sql --Spatial
SQL>@$ORACLE_HOME/ord/im/admin/impatch.sql -- Oracle Intermedia
SQL>@$ORACLE_HOME/rdbms/admin/odmpatch.sql --Oracle Data Mining
SQL>@$ORACLE_HOME/rdbms/admin/owmpatch.sql --Oracle Workspace Manager
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql - To recompile all the Invalid Objects
Step 5: SQL>Shutdown immediate;
Step 6: SQL>startup;
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected
Oracle Database Packages and Types INVALID status in DBA_REGISTRY
Description:
I noticed Oracle Database Packages and Types status is INVALID in DBA_REGISTRY . I followed the below steps to solve this issue. This issue occurred on 10g (10.2.0.2.0) database.
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Packages and Types
Solution I tried:
Step 1: Login as SYSDBA using the below command
$ sqlplus “/as sysdba”
Step 2: Shutdown the database if it is running using the below command
SQL> shutdown immediate
Step 3: SQL> startup ugrade;
Step 4: I ran the following sql files,
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
catalog.sql is to recreate Oracle database Catalog
catproc.sql is to recreate Oracle database Packages and Types
utlrp.sql is to recompile all the invalid objects
Step 5: Shutdown immediate;
Step 6: startup
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected
I noticed Oracle Database Packages and Types status is INVALID in DBA_REGISTRY . I followed the below steps to solve this issue. This issue occurred on 10g (10.2.0.2.0) database.
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Packages and Types
Solution I tried:
Step 1: Login as SYSDBA using the below command
$ sqlplus “/as sysdba”
Step 2: Shutdown the database if it is running using the below command
SQL> shutdown immediate
Step 3: SQL> startup ugrade;
Step 4: I ran the following sql files,
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
catalog.sql is to recreate Oracle database Catalog
catproc.sql is to recreate Oracle database Packages and Types
utlrp.sql is to recompile all the invalid objects
Step 5: Shutdown immediate;
Step 6: startup
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected
PROCEDURE TO SHUTDOWN ORACLE APPLICATION SERVER 10g RELEASE 2 (SUN SOLARIS)
Steps to Shutdown Oracle Application Server BI & Forms Home:
$PATH=/usr/bin:/user/orainfra/BI/bin
$export PATH
$emctl stop iasconsole
$exit
Steps to Shutdown Oracle Application Server Infrastructure Home:
Step 1: Enterprise Manager control – IAS Console Shutdown
$emctl stop iasconsole
Step 2: Enterprise Manager Control - DB Console for Application Server Database Shutdown:
$emctl stop dbconsole
Step 3: Application Server Database Shutdown:
$sqlplus “/as sysdba”
SQL> shutdown immediate or shutdown normal
“Database closed”
SQL>exit
Step 4: Listener Control Shutdown:
$lsnrctl stop
$PATH=/usr/bin:/user/orainfra/BI/bin
$export PATH
$emctl stop iasconsole
$exit
Steps to Shutdown Oracle Application Server Infrastructure Home:
Step 1: Enterprise Manager control – IAS Console Shutdown
$emctl stop iasconsole
Step 2: Enterprise Manager Control - DB Console for Application Server Database Shutdown:
$emctl stop dbconsole
Step 3: Application Server Database Shutdown:
$sqlplus “/as sysdba”
SQL> shutdown immediate or shutdown normal
“Database closed”
SQL>exit
Step 4: Listener Control Shutdown:
$lsnrctl stop
Monday, November 16, 2009
Oracle Application Server Release 2 URL’s
Description:
You can use the below URL to get Oracle Application Server Release 2 Component Pages.
To get Enterprise Manager Console welcome page:
http://Host Address:5500/em
(Username: sys Password: SysPassword)
To get Infrastructure and Business Intelligence Home welcome Page:
http://Host Address:1810
(User name: ias_admin Password: Provided password at the time of Installation)
To get Discoverer Plus Welcome Page:
http://Host Address:7778/discoverer/plus
To get Discoverer Viewer Welcome Page:
http://Host Address:7778/discoverer/viewer
To get Oracle Application Server Portal welcome Page:
http://Host Address:7778/pls/portal (For Portal)
(Single Sign On Username: orcladmin Password: Provided password at the time of Installation)
Note: If the above port did not match, you can check portlist.ini file to get the entire Port list.
You can use the below URL to get Oracle Application Server Release 2 Component Pages.
To get Enterprise Manager Console welcome page:
http://Host Address:5500/em
(Username: sys Password: SysPassword)
To get Infrastructure and Business Intelligence Home welcome Page:
http://Host Address:1810
(User name: ias_admin Password: Provided password at the time of Installation)
To get Discoverer Plus Welcome Page:
http://Host Address:7778/discoverer/plus
To get Discoverer Viewer Welcome Page:
http://Host Address:7778/discoverer/viewer
To get Oracle Application Server Portal welcome Page:
http://Host Address:7778/pls/portal (For Portal)
(Single Sign On Username: orcladmin Password: Provided password at the time of Installation)
Note: If the above port did not match, you can check portlist.ini file to get the entire Port list.
PROCEDURE TO START ORACLE APPLICATION SERVER 10g RELEASE 2 (SUN SOLARIS)
Steps to Start Oracle Application Server Infrastructure Home:
Step 1: Listener Control Start up:
$lsnrctl start
Step 2: Application Server Database Start up:
$sqlplus “/as sysdba”
SQL> startup
“Database opened”
SQL>exit
Step 3: Enterprise Manager Control - DB Console for Application Server
Database startup:
$emctl start dbconsole
Step 4: Enterprise Manager control – IAS Console Start up
$emctl start iasconsole
Steps to Start Oracle Application Server Business Intelligence and Forms Home:
Step 1: Set Environment Variable PATH for BI and Forms Home as,
$PATH=/usr/bin:/user/orainfra/BI/bin
$export PATH
$emctl start iasconsole
$exit
Step 1: Listener Control Start up:
$lsnrctl start
Step 2: Application Server Database Start up:
$sqlplus “/as sysdba”
SQL> startup
“Database opened”
SQL>exit
Step 3: Enterprise Manager Control - DB Console for Application Server
Database startup:
$emctl start dbconsole
Step 4: Enterprise Manager control – IAS Console Start up
$emctl start iasconsole
Steps to Start Oracle Application Server Business Intelligence and Forms Home:
Step 1: Set Environment Variable PATH for BI and Forms Home as,
$PATH=/usr/bin:/user/orainfra/BI/bin
$export PATH
$emctl start iasconsole
$exit
Sunday, November 15, 2009
How do I check USB drive connected to Solaris x86?
Step 1: Login as root
Step 2: Type cfgadm command to see USB detected or not
#cfgadm
Step 2: Type cfgadm command to see USB detected or not
#cfgadm
Friday, November 13, 2009
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
Error Description:
I noticed the below error during expor schema using exp utility
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
Solution I tried:
Step 1: Login as SYSDBA
Step 2: Execute $ORACLE_HOME/javavm/install/initdbj.sql
Step 3: Re-run export
I noticed the below error during expor schema using exp utility
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
Solution I tried:
Step 1: Login as SYSDBA
Step 2: Execute $ORACLE_HOME/javavm/install/initdbj.sql
Step 3: Re-run export
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
Error Description:
I noticed the below error during export schema using exp utility:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Solution I tried:
Step 1: Log in as SYSDBA
Step 2: Execute $ORACLE_HOME/rdbms/admin/catmetx.sql
Step 3: Execute $ORACLE_HOME/rdbms/admin/utlrp.sql
I noticed the below error during export schema using exp utility:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Solution I tried:
Step 1: Log in as SYSDBA
Step 2: Execute $ORACLE_HOME/rdbms/admin/catmetx.sql
Step 3: Execute $ORACLE_HOME/rdbms/admin/utlrp.sql
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
Error Description:
I have noticed the below error during export schema using exp utility:
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
EXP-00000: Export terminated unsuccessfully
Solution I tried:
Step 1: Login as SYSDBA
Step 2: GRANT EXECUTE ON sys.lt_export_pkg TO PUBLIC;
Step 3: Re-Run export
I have noticed the below error during export schema using exp utility:
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
EXP-00000: Export terminated unsuccessfully
Solution I tried:
Step 1: Login as SYSDBA
Step 2: GRANT EXECUTE ON sys.lt_export_pkg TO PUBLIC;
Step 3: Re-Run export
Thursday, November 12, 2009
Error accessing package DBMS_APPLICATION_INFO
Noticed the below error when I try to connect the database through client.
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Solution I tried:
I executed the below files after logging in as SYSDBA
SQL>@$ORACLE_HOME/rdbms/admin/dbmsotpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmsapin.sql
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Solution I tried:
I executed the below files after logging in as SYSDBA
SQL>@$ORACLE_HOME/rdbms/admin/dbmsotpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmsapin.sql
ORA-04063: ORA-04063: package body "EXFSYS.DBMS_RLMGR_DR" has errors
Noticed Below error in Alert log file:
ORA-12012: error on auto execute of job 42585
ORA-04063: ORA-04063: package body "EXFSYS.DBMS_RLMGR_DR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.DBMS_RLMGR_DR"
ORA-06512: at line 1 has errors
Solution I tried:
Re-created "EXFSYS" sample schema using the below commmand.
Step 1: Login as SYSDBA
Step 2: @$ORACLE_HOME/rdbms/admin/catexf.sql
ORA-12012: error on auto execute of job 42585
ORA-04063: ORA-04063: package body "EXFSYS.DBMS_RLMGR_DR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.DBMS_RLMGR_DR"
ORA-06512: at line 1 has errors
Solution I tried:
Re-created "EXFSYS" sample schema using the below commmand.
Step 1: Login as SYSDBA
Step 2: @$ORACLE_HOME/rdbms/admin/catexf.sql
How do I install Sample Schema in Oracle 10g
You can you use the below step to install Sample schema (hr,oe etc) Oracle 10g
Execute the below script after login as SYSDBA.
SQL>$ORACLE_HOME/demo/schema/mksample.sql
Execute the below script after login as SYSDBA.
SQL>$ORACLE_HOME/demo/schema/mksample.sql
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
I noticed the below error while using drop user cascade;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
Solution I tried:
SQL>ALTER SESSION SET RECYCLEBIN=OFF;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
Solution I tried:
SQL>ALTER SESSION SET RECYCLEBIN=OFF;
Wednesday, November 11, 2009
Solaris Guest Addition Installation for x86 Solaris Virtual Machine
You can use the below steps to install Solaris Guest Addition Installation for x86 Solaris Virtual Machine
Step 1: Start the Solaris Virtual Machine (VirtualBox-3.0.10-54097-Win.exe)
Step 2: with Solaris Window selected, choose Devices -> Install Guest Additions.
Step 3: CD icon will show up from desktop
Step 4: Login as root using terminal window
Step 5: Go to VBoxSolarisAdditions.pkg using the below
#cd /cdrom/vboxadditions_3.0.10_54097
Step 6: Execute the below command to add Virtual Box Additions.
#pkgadd –d VBoxSolarisAdditions.pkg
Step 7: Final Message should show as “Installation of was successful”.
Step 1: Start the Solaris Virtual Machine (VirtualBox-3.0.10-54097-Win.exe)
Step 2: with Solaris Window selected, choose Devices -> Install Guest Additions.
Step 3: CD icon will show up from desktop
Step 4: Login as root using terminal window
Step 5: Go to VBoxSolarisAdditions.pkg using the below
#cd /cdrom/vboxadditions_3.0.10_54097
Step 6: Execute the below command to add Virtual Box Additions.
#pkgadd –d VBoxSolarisAdditions.pkg
Step 7: Final Message should show as “Installation of
Tuesday, November 10, 2009
Oracle Database Performance Tuning Methods/Approaches
We can use the below Approaches to avoid Oracle Database Performance slowness:
Approach 1: Check the long running queries parallely collect the statistics all the queries running on DB
Approach 2: Do the Explain plan query by query
Approach 3: Analyze Tablescans and Index scan (Create index if necessary)
Approach 4: Create Partition if necessary and also if the license supports
Approach 5: Purge recycle bin (if it is 10g)
Approach 6: SQL trace with TKProf
Approach 7: we can go ahead with Optimizer hints if required
Approach 8: Analyze the whole database schemas
Approach 9: Coalesce the tablespace.
Approach 10: Rebuild mulitple objects if necessary.
Approach 11: Configure Statspack Report.
Approach 12: Analyze the Statspack Report.
Approach 13: We need to check whether all the database objects placed in USERS or Manually Created Tablespace (Some times all the objects will be placed on SYSTEM or SYSAUX Tablespace, this may cause performance slowness)
Approach 14: For network related performance issue we can use SDU and TDU parameters.
Approach 1: Check the long running queries parallely collect the statistics all the queries running on DB
Approach 2: Do the Explain plan query by query
Approach 3: Analyze Tablescans and Index scan (Create index if necessary)
Approach 4: Create Partition if necessary and also if the license supports
Approach 5: Purge recycle bin (if it is 10g)
Approach 6: SQL trace with TKProf
Approach 7: we can go ahead with Optimizer hints if required
Approach 8: Analyze the whole database schemas
Approach 9: Coalesce the tablespace.
Approach 10: Rebuild mulitple objects if necessary.
Approach 11: Configure Statspack Report.
Approach 12: Analyze the Statspack Report.
Approach 13: We need to check whether all the database objects placed in USERS or Manually Created Tablespace (Some times all the objects will be placed on SYSTEM or SYSAUX Tablespace, this may cause performance slowness)
Approach 14: For network related performance issue we can use SDU and TDU parameters.
How do I set ORACLE Environment Variable
Execute the below steps to set Environment variable for UNIX:
Step 1: Login as oracle
Step 2: Execute the below command to set ORACLE_HOME and ORACLE_SID
. /usr/local/bin/oraenv
Step 1: Login as oracle
Step 2: Execute the below command to set ORACLE_HOME and ORACLE_SID
. /usr/local/bin/oraenv
Subscribe to:
Posts (Atom)