Use the below steps to trace the Sessions.
Step 1: Login as sysdba
$sqlplus “/as sysdba”
Step 2: Execute the below query to get sid and serial#
SQL> select sid, serial# FROM v$session where username = 'USER_NAME';
Step 3: Enable trace Session using the below
SQL>exec dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
e.g: SQL> exec dbms_system.set_sql_trace_in_session(100,2018,TRUE)
Step 4: Check the trace files from user_dump directory.
SQL> select value from v$parameter where name = 'user_dump_dest';
Step 5: Check the trace (.trc) files from user_dump_dest directory.
Step 6: Disable trace session using the below
SQL>exec dbms_system.set_sql_trace_in_session(sid,serial#,FALSE)
e.g: SQL> exec dbms_system.set_sql_trace_in_session(100,2018,FALSE)
Tuesday, December 29, 2009
Monday, December 28, 2009
Set Environment Variable for Multiple Databases
You can use the below method to Set Environment Variable for Multiple Databases.
Step 1: Edit the .profile with the following information.
########## --Script starts Here -- ###############
print
print "Available SIDs to choose from:"
cat /var/opt/oracle/oratab | grep -v ^#
print
#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very often. Only one SID is enough
TERM=vt220; export TERM
. $HOME/.All_Profile
export ORACLE_TERM=vt100;
stty erase ^h
#stty erase ^?
EDITOR=vi; export $EDITOR
set -o vi
########## --Script Ends Here -- ###############
Step 2: Create .All_Profile with the following information.
########## --Script starts Here -- ###############
ORAENV_ASK=YES
export ORAENV_ASK
#To Set ORACLE Base
export ORACLE_BASE=/export/home/oracle1
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very ofte. Only one SID is enough
#Set PATH to executable directories
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH
. oraenv
#Set PATH for LD_LIBRARY PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
# Set Oracle user umask
umask 022
# Ensure that TWO_TASK is not set
unset TWO_TASK
# Ensure that CLASS_PATH is not set
unset CLASS_PATH
export ORACLE_PATH=$HOME
########## --Script Ends Here -- ###############
Step 3: Logout the session and Login again.
Above steps I tested in Solaris Environment.
Step 1: Edit the .profile with the following information.
########## --Script starts Here -- ###############
print "Available SIDs to choose from:"
cat /var/opt/oracle/oratab | grep -v ^#
#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very often. Only one SID is enough
TERM=vt220; export TERM
. $HOME/.All_Profile
export ORACLE_TERM=vt100;
stty erase ^h
#stty erase ^?
EDITOR=vi; export $EDITOR
set -o vi
########## --Script Ends Here -- ###############
Step 2: Create .All_Profile with the following information.
########## --Script starts Here -- ###############
ORAENV_ASK=YES
export ORAENV_ASK
#To Set ORACLE Base
export ORACLE_BASE=/export/home/oracle1
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very ofte. Only one SID is enough
#Set PATH to executable directories
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH
. oraenv
#Set PATH for LD_LIBRARY PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
# Set Oracle user umask
umask 022
# Ensure that TWO_TASK is not set
unset TWO_TASK
# Ensure that CLASS_PATH is not set
unset CLASS_PATH
export ORACLE_PATH=$HOME
########## --Script Ends Here -- ###############
Step 3: Logout the session and Login again.
Above steps I tested in Solaris Environment.
Monday, December 14, 2009
Rebuild Database
Description:
We can use the below approaches to rebuild the entire database.
Approach #1: For Smaller Database.
Step 1: Export the whole database using export utility (exp).
Step 2: Drop the whole database.
Step 3: Create the database.
Step 4: Import the whole database using import utility (imp).
Approach #2:
Step 1: Create the additional tablespace to move all the objects to this tablespace. e.g. if all the objects exists in “USERS” tablespace, we can create “USERS1” tablespace.
Step 2: Move all the objects to newly created additional tablespace using ALTER TABLESPACE command (REBUILD option).
Step 3: Again move all the objects to original tablespace.
Approach #3:
Step 1: We can use transportable tablespace to move one database to another database (only for 10g)
Approach #4:
Step 1: Take Hotbackup (Data files and control file backup) of the existing server. Rebuild the whole database using Hotbackup files.
Approach #5:
Step 1: Take Cold backup of the existing server. Rebuild the whole database using Cold backup files.
Note1: Approach #1 will avoid row chaining and fragmentation of the table and also easiest method to rebuild the whole database.
Note2: Approach #2 we need to additional space in servers. This method also avoid row chaining and fragmentation
We can use the below approaches to rebuild the entire database.
Approach #1: For Smaller Database.
Step 1: Export the whole database using export utility (exp).
Step 2: Drop the whole database.
Step 3: Create the database.
Step 4: Import the whole database using import utility (imp).
Approach #2:
Step 1: Create the additional tablespace to move all the objects to this tablespace. e.g. if all the objects exists in “USERS” tablespace, we can create “USERS1” tablespace.
Step 2: Move all the objects to newly created additional tablespace using ALTER TABLESPACE command (REBUILD option).
Step 3: Again move all the objects to original tablespace.
Approach #3:
Step 1: We can use transportable tablespace to move one database to another database (only for 10g)
Approach #4:
Step 1: Take Hotbackup (Data files and control file backup) of the existing server. Rebuild the whole database using Hotbackup files.
Approach #5:
Step 1: Take Cold backup of the existing server. Rebuild the whole database using Cold backup files.
Note1: Approach #1 will avoid row chaining and fragmentation of the table and also easiest method to rebuild the whole database.
Note2: Approach #2 we need to additional space in servers. This method also avoid row chaining and fragmentation
Tuesday, December 8, 2009
Oracle Process Manager and Notification Server (OPMN)
Description:
Oracle Process Manager and Notification Server (OPMN) is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server.
Command List:
To Get opmnctl Help :
$opmnctl help
To Start OPMN:
$opmnctl start
To Start OPMN and all managed processes:
$opmnctl startall
To Stop OPMN and all managed processes:
$ opmnctl stopall
To get the OPMN status:
$opmnctl status
We can use the following commands to start or stop component level
To Start component Level Service:
$opmnctl startproc ias-component=SERVICE_NAME
e.g:
$opmnctl startproc ias-component=HTTP_Server
To Stop Component Level Service:
$opmnctl stopproc ias-componenet=SERVICE_NAME
e.g:
$opmnctl stopproc ias-component=HTTP_Server
Oracle Process Manager and Notification Server (OPMN) is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server.
Command List:
To Get opmnctl Help :
$opmnctl help
To Start OPMN:
$opmnctl start
To Start OPMN and all managed processes:
$opmnctl startall
To Stop OPMN and all managed processes:
$ opmnctl stopall
To get the OPMN status:
$opmnctl status
We can use the following commands to start or stop component level
To Start component Level Service:
$opmnctl startproc ias-component=SERVICE_NAME
e.g:
$opmnctl startproc ias-component=HTTP_Server
To Stop Component Level Service:
$opmnctl stopproc ias-componenet=SERVICE_NAME
e.g:
$opmnctl stopproc ias-component=HTTP_Server
Thursday, December 3, 2009
Dependencies Error on Oracle Client Installation
Description:
I faced the below error at the time of Installation of Oracle BI Products Installation.
Error:
Dependencies
The ORACLE_HOME environment variable is currently set. This will prevent proper use of multiple Oracle Homes, and since it is not required for any Oracle products to function, it must be unset in your environment. Please remove this environment variable setting using the System Control Panel, restart the system, then restart the installation.
Solution I tried:
I removed the ORACLE_HOME Entry from Environment Variables.
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables ->
Restarted BI Products Installation. It was successful.
I faced the below error at the time of Installation of Oracle BI Products Installation.
Error:
Dependencies
The ORACLE_HOME environment variable is currently set. This will prevent proper use of multiple Oracle Homes, and since it is not required for any Oracle products to function, it must be unset in your environment. Please remove this environment variable setting using the System Control Panel, restart the system, then restart the installation.
Solution I tried:
I removed the ORACLE_HOME Entry from Environment Variables.
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables ->
Restarted BI Products Installation. It was successful.
Unable to View Procedure Source in TOAD - 'IN' is not a valid integer value
Description:
I unable to procedure source from Toad Schema browser. Below error message I got,
'IN' is not a valid integer value
Solution Tried:
Step 1: I logged in as Schema Owner
Step 2: Created the below view.
CREATE OR REPLACE FORCE VIEW schema_owner.all_arguments
AS
SELECT owner, object_name, package_name, object_id, overload,
argument_name, POSITION, SEQUENCE, data_level, data_type,
DEFAULT_VALUE, default_length, in_out, data_length, data_precision,
data_scale, radix, character_set_name, type_owner, type_name,
type_subname, type_link, pls_type, char_length, char_used,
subprogram_id
FROM SYS.all_arguments;
Step 3: Refresh schema browser.
Finally, I was able to view the procedure source
I unable to procedure source from Toad Schema browser. Below error message I got,
'IN' is not a valid integer value
Solution Tried:
Step 1: I logged in as Schema Owner
Step 2: Created the below view.
CREATE OR REPLACE FORCE VIEW schema_owner.all_arguments
AS
SELECT owner, object_name, package_name, object_id, overload,
argument_name, POSITION, SEQUENCE, data_level, data_type,
DEFAULT_VALUE, default_length, in_out, data_length, data_precision,
data_scale, radix, character_set_name, type_owner, type_name,
type_subname, type_link, pls_type, char_length, char_used,
subprogram_id
FROM SYS.all_arguments;
Step 3: Refresh schema browser.
Finally, I was able to view the procedure source
Tuesday, December 1, 2009
Script to Analyze whole Database
Description:
Below script can be used for Analyze Whole Database.
Script:
#!/bin/sh
. .profile
# Print Start Time
startTime=`date`
echo
echo "Starting Time $startTime"
Analyze1=/tmp/analyze.tmp
sqlplus -s system/manager << ! > $Analyze1
set pagesize 0
set feedback off
select 'analyze table', owner||'.'||table_name||
' estimate statistics;'
from dba_tables
where owner not in ('SYSTEM','SYS');
!
sqlplus -s system/manager << !
set feedback off
set pagesize 0
set echo on
set time on
set timing on
@$Analyze1
exit
!
# Print End Time
endTime=`date`
echo
echo "Analyze Schema Successfully Completed"
echo "Ending Time $endTime"
Below script can be used for Analyze Whole Database.
Script:
#!/bin/sh
. .profile
# Print Start Time
startTime=`date`
echo
echo "Starting Time $startTime"
Analyze1=/tmp/analyze.tmp
sqlplus -s system/manager << ! > $Analyze1
set pagesize 0
set feedback off
select 'analyze table', owner||'.'||table_name||
' estimate statistics;'
from dba_tables
where owner not in ('SYSTEM','SYS');
!
sqlplus -s system/manager << !
set feedback off
set pagesize 0
set echo on
set time on
set timing on
@$Analyze1
exit
!
# Print End Time
endTime=`date`
echo
echo "Analyze Schema Successfully Completed"
echo "Ending Time $endTime"
Friday, November 27, 2009
NUM_ROWS in ALL_INDEXES/USER_INDEXES/DBA_INDEXES has fractional value
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.
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.
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
Saturday, October 31, 2009
How do you enable Java Console trace options (Oracle Jinitiator) in Client Machine?
Description:
Jinitiator is a Java Virtual Machine (JVM) responsible for running the Forms Java client. We can enable trace information client machine for Forms.
Set the following Java runtime parameters in the Jinitiator Control Panel (tabs) will enable Jinitiator tracing
Start -> JInitiator Control Panel ->
Basic Tab:
Click on Enable JInitiator
Click on Java Console
Network access is Applet Host
Set Java Run Time Parameters as -mx64m -Dcache.size=50000000 -Djavaplugin.trace=true -Djavaplugin.trace.option=basic
Advanced Tab:
Java Run Time Environment as Use Jinitiator Default
Click on Enable Just In Time Compiler, Default JIT Path is symcjit
Click on Enable Debug, Default Debug Port 2502
Proxies Tab:
Click on Use browser settings
After changing all the values restart the client machine. This will produce a trace file, Jinitiator.trace in the User Home Directory. For example, if the User Home Directory is C:\Documents and Settings\username and Jinitiator version is used.
Or
Java Console window automatically opens with trace information.
Jinitiator is a Java Virtual Machine (JVM) responsible for running the Forms Java client. We can enable trace information client machine for Forms.
Set the following Java runtime parameters in the Jinitiator Control Panel (tabs) will enable Jinitiator tracing
Start -> JInitiator Control Panel ->
Basic Tab:
Click on Enable JInitiator
Click on Java Console
Network access is Applet Host
Set Java Run Time Parameters as -mx64m -Dcache.size=50000000 -Djavaplugin.trace=true -Djavaplugin.trace.option=basic
Advanced Tab:
Java Run Time Environment as Use Jinitiator Default
Click on Enable Just In Time Compiler, Default JIT Path is symcjit
Click on Enable Debug, Default Debug Port 2502
Proxies Tab:
Click on Use browser settings
After changing all the values restart the client machine. This will produce a trace file, Jinitiator
Or
Java Console window automatically opens with trace information.
Friday, October 30, 2009
Script Monitor CPU usage
#usr/bin/sh!
test=`ps -eaf -o pid|sort -r |head -200`
test=`echo $test | tr " " ","`;
tme=`date +'%d-%m-%y %H:%M'`
echo $tme
${ORACLE_HOME}/bin/sqlplus -s << @@EOF@@
/ as sysdba
SELECT n.username,s.SID,
TRUNC(((s.VALUE * 100) / (SELECT VALUE
FROM v\$sysstat
WHERE NAME = 'CPU used by this session')
),
2
) "% CPU Usage",
a.sql_text
FROM v\$sesstat s, v\$statname t, v\$session n, v\$process p, v\$sqlarea a
WHERE p.spid IN ($test)
AND s.statistic# = t.statistic#
AND n.SID = s.SID
AND DECODE (n.sql_address,
n.sql_address, n.prev_sql_addr,
n.sql_address
) = a.address
AND DECODE (n.sql_hash_value,
n.sql_hash_value, n.prev_hash_value,
n.sql_hash_value
) = a.hash_value
AND n.paddr = p.addr
AND t.NAME = 'CPU used by this session'
AND s.VALUE <> 0
ORDER BY s.VALUE DESC;
exit
test=`ps -eaf -o pid|sort -r |head -200`
test=`echo $test | tr " " ","`;
tme=`date +'%d-%m-%y %H:%M'`
echo $tme
${ORACLE_HOME}/bin/sqlplus -s << @@EOF@@
/ as sysdba
SELECT n.username,s.SID,
TRUNC(((s.VALUE * 100) / (SELECT VALUE
FROM v\$sysstat
WHERE NAME = 'CPU used by this session')
),
2
) "% CPU Usage",
a.sql_text
FROM v\$sesstat s, v\$statname t, v\$session n, v\$process p, v\$sqlarea a
WHERE p.spid IN ($test)
AND s.statistic# = t.statistic#
AND n.SID = s.SID
AND DECODE (n.sql_address,
n.sql_address, n.prev_sql_addr,
n.sql_address
) = a.address
AND DECODE (n.sql_hash_value,
n.sql_hash_value, n.prev_hash_value,
n.sql_hash_value
) = a.hash_value
AND n.paddr = p.addr
AND t.NAME = 'CPU used by this session'
AND s.VALUE <> 0
ORDER BY s.VALUE DESC;
exit
Sunday, October 25, 2009
Setting .profile
# Setting ORACLE_SID
ORACLE_SID=test
export ORACLE_SID
#Setting ORACLE_HOME
ORACLE_HOME=/export/home/oracle1/product/10.2.0/db_1
export ORACLE_HOME
#Setting TNS_ADMIN
TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin
export TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin
#Setting PATH
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH
ORACLE_SID=test
export ORACLE_SID
#Setting ORACLE_HOME
ORACLE_HOME=/export/home/oracle1/product/10.2.0/db_1
export ORACLE_HOME
#Setting TNS_ADMIN
TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin
export TNS_ADMIN=/export/home/oracle1/product/10.2.0/db_1/network/admin
#Setting PATH
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH
Monday, October 12, 2009
Script to generate Analyze Schema, Coalesce Tablespace and Compile Invalid Objects
To Generate Coalesce Tablespace:
SELECT 'ALTER TABLESPACE ' || NAME || ' COALESCE;' FROM V$TABLESPACE
WHERE NAME <> 'TEMP'
AND NAME <> 'SYS'
AND NAME <> 'SYSTEM'
AND NAME <> 'UNDOTBS1'
ORDER BY NAME
To Generate Analyze Schema:
SELECT 'execute DBMS_UTILITY.ANALYZE_SCHEMA('''|| username ||''',''COMPUTE'');' FROM dba_users
WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM'
ORDER BY USERNAME;
To Generate Invalid Objects:
SELECT ' ALTER VIEW '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='VIEW';
SELECT ' ALTER PROCEDURE '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='TRIGGER'
To Generate Index Rebuild:
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;'
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS','SYSTEM');
SELECT 'ALTER TABLESPACE ' || NAME || ' COALESCE;' FROM V$TABLESPACE
WHERE NAME <> 'TEMP'
AND NAME <> 'SYS'
AND NAME <> 'SYSTEM'
AND NAME <> 'UNDOTBS1'
ORDER BY NAME
To Generate Analyze Schema:
SELECT 'execute DBMS_UTILITY.ANALYZE_SCHEMA('''|| username ||''',''COMPUTE'');' FROM dba_users
WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM'
ORDER BY USERNAME;
To Generate Invalid Objects:
SELECT ' ALTER VIEW '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='VIEW';
SELECT ' ALTER PROCEDURE '|| owner||'.' || OBJECT_NAME || ' COMPILE;' FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='TRIGGER'
To Generate Index Rebuild:
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;'
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS','SYSTEM');
Thursday, October 8, 2009
ORA-01031 Insufficient privileges Error in EXECUTE IMMEDIATE Statement
Error Description 1:
Noticed while using EXECUTE IMMEDIATE with TRUNCATE TABLE Statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'TRUNCATE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant drop any table to TestSchema;
Error Description 2:
Noticed while using EXECUTE IMMEDIATE with ANALYZE TABLE statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant Analyze Any to TestSchema;
Error Description 3:
Noticed while using EXECUTE IMMEDIATE with MOVE TABLESPACE statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant ALTER any TABLE to TestSchema;
Noticed while using EXECUTE IMMEDIATE with TRUNCATE TABLE Statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'TRUNCATE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant drop any table to TestSchema;
Error Description 2:
Noticed while using EXECUTE IMMEDIATE with ANALYZE TABLE statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant Analyze Any to TestSchema;
Error Description 3:
Noticed while using EXECUTE IMMEDIATE with MOVE TABLESPACE statement in Procedure, error message as ORA-01031: insufficient privileges.
Work Around:
SQL> EXECUTE IMMEDIATE 'ANALYZE TABLE TestSchema.T1';
ORA-01031: insufficient privileges
Solution:
SQL>Grant ALTER any TABLE to TestSchema;
Wednesday, October 7, 2009
OC4J Configuration issue in Oracle Enterprise Manager (OEM)
Error Description:
Noticed while starting EM dbconsole "OC4J Configuration issue".
$emctl start dbconsole
OC4J Configuration issue. $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_testmachine_testdb not found.
testmachine - Host Name
testdb - Database SID
Solution:
Manually configure EM dbconsole using the below command:
$emca -config dbcontrol db
STARTED EMCA at Sep 25, 2009 12:30:45 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: testdb
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ $ORACLE_HOME
Database hostname ................ testmachine
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 25, 2009 12:39:17 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /$ORACLE_HOME/cfgtoollogs/emca/testdb/emca_2009-08-25_12-30-45-PM.log
Sep 25, 2009 12:39:29 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://testmachine:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 25, 2009 12:50:25 PM
Now you check the emctl Status using the below command:
$emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://testmachine:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory $ORACLE_HOME/testmachine_testdb/sysman/log
Use the below link to check Oracle EM Database Control:
http://testmachine:1158/em/
Commands for EM database control:
To Start DB Console:
$emctl start dbconsole
To Stop EM DB Console:
$emctl stop dbconsole
To Know the status of EM DB Console:
$emctl status dbconsole
Noticed while starting EM dbconsole "OC4J Configuration issue".
$emctl start dbconsole
OC4J Configuration issue. $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_testmachine_testdb not found.
testmachine - Host Name
testdb - Database SID
Solution:
Manually configure EM dbconsole using the below command:
$emca -config dbcontrol db
STARTED EMCA at Sep 25, 2009 12:30:45 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: testdb
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ $ORACLE_HOME
Database hostname ................ testmachine
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 25, 2009 12:39:17 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /$ORACLE_HOME/cfgtoollogs/emca/testdb/emca_2009-08-25_12-30-45-PM.log
Sep 25, 2009 12:39:29 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 25, 2009 12:41:15 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://testmachine:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 25, 2009 12:50:25 PM
Now you check the emctl Status using the below command:
$emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://testmachine:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory $ORACLE_HOME/testmachine_testdb/sysman/log
Use the below link to check Oracle EM Database Control:
http://testmachine:1158/em/
Commands for EM database control:
To Start DB Console:
$emctl start dbconsole
To Stop EM DB Console:
$emctl stop dbconsole
To Know the status of EM DB Console:
$emctl status dbconsole
Thursday, October 1, 2009
Statspack Configuration
Step 1: Login as sysdba and execute spcreate.sql ($ sqlplus "/as sysdba")
Step 2: SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Step 3: Enter value for perfstat_password
Step 4: Choose the Default tablespace for the PERFSTAT user
Step 5: Choose the Temporary tablespace for the PERFSTAT user
To Take Snapshots:
Step 1: Login to perfstat user (sqlplus perfstat/perfstat@dbname)
Step 2: Execute statspack.snap (ie. exec statspack.snap)
Step 3: Execute statspack.snap with 15 minutes interval.
Step 4: Check the list of snapshot using
SELECT snap_id, snap_time
FROM stats$snapshot;
Step 5: Execute spreport.sql to take report(SQL>@rdbms/admin/spreport.sql)
It Prompts for two snapshot Id and report location. You can use the above query to find snap_id.
Steps to Drop Statspack Configuration:
Step 1: Login as sysdba ($sqlplus "/as sysdba")
Step 2: Execute spdrop.sql (i.e SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql)
Step 2: SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Step 3: Enter value for perfstat_password
Step 4: Choose the Default tablespace for the PERFSTAT user
Step 5: Choose the Temporary tablespace for the PERFSTAT user
To Take Snapshots:
Step 1: Login to perfstat user (sqlplus perfstat/perfstat@dbname)
Step 2: Execute statspack.snap (ie. exec statspack.snap)
Step 3: Execute statspack.snap with 15 minutes interval.
Step 4: Check the list of snapshot using
SELECT snap_id, snap_time
FROM stats$snapshot;
Step 5: Execute spreport.sql to take report(SQL>@rdbms/admin/spreport.sql)
It Prompts for two snapshot Id and report location. You can use the above query to find snap_id.
Steps to Drop Statspack Configuration:
Step 1: Login as sysdba ($sqlplus "/as sysdba")
Step 2: Execute spdrop.sql (i.e SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql)
ORA-12641: Authentication service failed to initialize
test@linux1:~>sqlplus test/test@testdb
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 1 17:05:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Solution:
Step 1: check sqlnet.ora ($ORACLE_HOME/network/admin/sqlnet.ora) file
Step 2: Check SQLNET.AUTHENTICATION_SERVICES value
Step3: If SQLNET.AUTHENTICATION_SERVICES value is ALL (i.e. SQLNET.AUTHENTICATION_SERVICES=(ALL))
Step 4: Set the value for SQLNET.AUTHENTICATION_SERVICES is NONE (i.e. SQLNET.AUTHENTICATION_SERVICES=(NONE))
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 1 17:05:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Solution:
Step 1: check sqlnet.ora ($ORACLE_HOME/network/admin/sqlnet.ora) file
Step 2: Check SQLNET.AUTHENTICATION_SERVICES value
Step3: If SQLNET.AUTHENTICATION_SERVICES value is ALL (i.e. SQLNET.AUTHENTICATION_SERVICES=(ALL))
Step 4: Set the value for SQLNET.AUTHENTICATION_SERVICES is NONE (i.e. SQLNET.AUTHENTICATION_SERVICES=(NONE))
Saturday, September 26, 2009
Script to Delete Archivelog Files
#!/bin/sh
# Setting Oracle SID
ORACLE_SID=SID
export ORACLE_SID
#Setting Oracle Home
ORACLE_HOME=Oracle_Home
export ORACLE_HOME
# To Print Start Time
startTime=`date`
print
print "Starting $startTime"
# To delete archivelog files older than 3 days.
rman target/ << EOF
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-3';
crosscheck archivelog all;
exit;
EOF
# To Print End Time
endTime=`date`
print "Complete $endTime"
# Setting Oracle SID
ORACLE_SID=SID
export ORACLE_SID
#Setting Oracle Home
ORACLE_HOME=Oracle_Home
export ORACLE_HOME
# To Print Start Time
startTime=`date`
print "Starting $startTime"
# To delete archivelog files older than 3 days.
rman target/ << EOF
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-3';
crosscheck archivelog all;
exit;
EOF
# To Print End Time
endTime=`date`
print "Complete $endTime"
Friday, September 25, 2009
ORA-01113: file <> needs media recovery
Error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oradata/xx01.dbf'
Solution:
To Recover the Database:
SQL> recover database;
Media recovery complete.
To Open the Database:
SQL> alter database open;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oradata/xx01.dbf'
Solution:
To Recover the Database:
SQL> recover database;
Media recovery complete.
To Open the Database:
SQL> alter database open;
Database altered.
Saturday, September 19, 2009
Query to find Tabelspace Usage
SELECT d.status , d.tablespace_name as Name, d.CONTENTS as Type,
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.999'
)
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status , d.tablespace_name as Name, d.CONTENTS as Type,
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY';
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.999'
)
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status , d.tablespace_name as Name, d.CONTENTS as Type,
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY';
Query to find Schema size
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024) SIZE_IN_MB
FROM dba_segments s
GROUP BY s.owner;
FROM dba_segments s
GROUP BY s.owner;
Friday, September 4, 2009
ORA-00214: Controlfile Inconsistent Error
Error Description:
During database Startup noticed the below Error in alert log file:
ORA-00214: control file 'ORACLE_HOME\CONTROL02.CTL' version 3016
inconsistent with file 'ORACLE_HOME\CONTROL01.CTL' version 2993
Resolution:
Step 1: Shutdown the database abruptly (SQL>shutdown abort)
Step 2: Create the pfile using spfile (SQL>create pfile='ORACLE_HOME\pfile.ora' using spfile)
Step 3: Edit the ORACLE_HOME\pfile.ora file
Step 4: Set CONTROL_FILES Parameter in pfile.ora with only one copy of control file. If more than one exists, delete it.
(CONTROL_FILES='ORACLE_HOME\CONTROL02.CTL')
Step 5: Start the database using pfile (SQL> startup using pfile='ORACLE_HOME\pfile.ora')
Step 6: If you find any below error, execute step 3, 4 and 5 again with another control file location (CONTROL_FILES='ORACLE_HOME\CONTROL01.CTL')
01122, 00000, "database file %s failed verification check"
// *Cause: The information in this file is inconsistent with information
// from the control file. See accompanying message for reason.
// *Action: Make certain that the db files and control files are the correct
// files for this database.
01207, 00000, "file is more recent than control file - old control file"
// *Cause: The control file change sequence number in the data file is
// greater than the number in the control file. This implies that
// the wrong control file is being used. Note that repeatedly causing
// this error can make it stop happening without correcting the real
// problem. Every attempt to open the database will advance the
// control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
// make the control file current. Be sure to follow all restrictions
// on doing a backup control file recovery.
Step 7: If you find any below error, Recover the database (SQL>recover database)
01113, 00000, "file %s needs media recovery"
// *Cause: An attempt was made to online or open a database with a file that
// is in need of media recovery.
// *Action: First apply media recovery to the file.
Step 8: Open the database (SQL>alter database open)
Step 9: Shutdown the database (SQL>shutdown)
Step 10: Copy the good control file which you used to startup the database onto all other copies, whatever listed in the CONTROL_FILES Paramater in spfile.ora before editing (Step 2).
Step 11: Start the database (SQL>Startup).
During database Startup noticed the below Error in alert log file:
ORA-00214: control file 'ORACLE_HOME\CONTROL02.CTL' version 3016
inconsistent with file 'ORACLE_HOME\CONTROL01.CTL' version 2993
Resolution:
Step 1: Shutdown the database abruptly (SQL>shutdown abort)
Step 2: Create the pfile using spfile (SQL>create pfile='ORACLE_HOME\pfile.ora' using spfile)
Step 3: Edit the ORACLE_HOME\pfile.ora file
Step 4: Set CONTROL_FILES Parameter in pfile.ora with only one copy of control file. If more than one exists, delete it.
(CONTROL_FILES='ORACLE_HOME\CONTROL02.CTL')
Step 5: Start the database using pfile (SQL> startup using pfile='ORACLE_HOME\pfile.ora')
Step 6: If you find any below error, execute step 3, 4 and 5 again with another control file location (CONTROL_FILES='ORACLE_HOME\CONTROL01.CTL')
01122, 00000, "database file %s failed verification check"
// *Cause: The information in this file is inconsistent with information
// from the control file. See accompanying message for reason.
// *Action: Make certain that the db files and control files are the correct
// files for this database.
01207, 00000, "file is more recent than control file - old control file"
// *Cause: The control file change sequence number in the data file is
// greater than the number in the control file. This implies that
// the wrong control file is being used. Note that repeatedly causing
// this error can make it stop happening without correcting the real
// problem. Every attempt to open the database will advance the
// control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
// make the control file current. Be sure to follow all restrictions
// on doing a backup control file recovery.
Step 7: If you find any below error, Recover the database (SQL>recover database)
01113, 00000, "file %s needs media recovery"
// *Cause: An attempt was made to online or open a database with a file that
// is in need of media recovery.
// *Action: First apply media recovery to the file.
Step 8: Open the database (SQL>alter database open)
Step 9: Shutdown the database (SQL>shutdown)
Step 10: Copy the good control file which you used to startup the database onto all other copies, whatever listed in the CONTROL_FILES Paramater in spfile.ora before editing (Step 2).
Step 11: Start the database (SQL>Startup).
ORA-00600: internal error code
Error:
ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [],[], [],[], [], []
Description:
This erorror occurs during database startup.
Resolution:
Step 1: Start the database in Mount Stage (SQL>startup mount)
Step 2: Recover the database (SQL>recover database)
Step 3: Open the database (SQL> alter database open)
ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [],[], [],[], [], []
Description:
This erorror occurs during database startup.
Resolution:
Step 1: Start the database in Mount Stage (SQL>startup mount)
Step 2: Recover the database (SQL>recover database)
Step 3: Open the database (SQL> alter database open)
Saturday, August 29, 2009
Error - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)
Error in Oracle Enterprise Manager:
Error - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)
Solution:
Re-implementation of the $ORACLE_HOME/root.sh, the problem is resolved.
Step 1: Login as oracle user and Stop Enterprise manager control using emctl start dbconsole
Step 2: Login as root and execute $ORACLE_HOME/root.sh
Ste3 : Login as oracle user and Start Enterprise Manager Control using emctl stop dbconsole
Error - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)
Solution:
Re-implementation of the $ORACLE_HOME/root.sh, the problem is resolved.
Step 1: Login as oracle user and Stop Enterprise manager control using emctl start dbconsole
Step 2: Login as root and execute $ORACLE_HOME/root.sh
Ste3 : Login as oracle user and Start Enterprise Manager Control using emctl stop dbconsole
Friday, August 28, 2009
Setting Count Retrieval for Oracle Discoverer Desktop
Below Sections describes about setting the count for retrieval of records from Oracle Discoverer Desktop.
Log on to Discoverer Desktop
Go to Tools -> Options as shown in the figure below (Image1)
A dialog box is opened and then go to “Query Governor” tab
Then change the value to your requirement under “Limit retrieved data to”
Now the user will be able to retrieve all records without any error.
Log on to Discoverer Desktop
Go to Tools -> Options as shown in the figure below (Image1)
A dialog box is opened and then go to “Query Governor” tab
Then change the value to your requirement under “Limit retrieved data to”
Now the user will be able to retrieve all records without any error.
Query to Find Sessions using Temporary Tablespace (TEMP)
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Subscribe to:
Posts (Atom)