$ sqlplus user/pwd@catalog_db;
SQL> select db_key, dbid, name from rc_catabase;
SQL> exec dbms_rcvcat.unregisterdatabase(db_key, dbid);
2008/12/16
Unregister Database From Catalog
Log onto your rman catalog database:
2008/12/15
EXP-00056, ORA-31600
Database: 9.2.0.5
OS: Win2000 SP4
Export of this 9i database fails with:
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in
function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Solution:
Connect as sys and run the following script:
OS: Win2000 SP4
Export of this 9i database fails with:
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in
function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Solution:
Connect as sys and run the following script:
SQL> @$ORACLE_HOME/rdbms/admin/catmeta.sql
Recover Database with Only RMAN Hot Backup File
Database: 10.2.0.2.0
OS: RHEL 4
I needed to recover a database which was used 5 months ago. The only thing I have is
a tar ball including RMAN host backup files. No parameter files. No control files. No any other files.
I made it happed following steps like this:
1. create a pfile from some other similar databas.
2. Update the parameters according to the database being recovered.
3. Put the backup files into default RMAN backup directory ($ORACLE_HOME/dbs) and
change control file autobackup file name to default settings in RMAN (to '%F');
4. Startup nomount using pfile just created.
5. Restore spfile from autobackup.
6. Shutdown immediate and startup using new spfile.
7. Create pfile from spfile and update parameters accordingly to your local disk
structure.
8. Shutdown immediate and startup using new pfile.
9. Create spfile from pfile.
10.Restore control file from autobackup file.
11.You already got control files now, and following the normal recovery procedure to
do an imcomplete recovery and open database resetlogs.
OS: RHEL 4
I needed to recover a database which was used 5 months ago. The only thing I have is
a tar ball including RMAN host backup files. No parameter files. No control files. No any other files.
I made it happed following steps like this:
1. create a pfile from some other similar databas.
2. Update the parameters according to the database being recovered.
3. Put the backup files into default RMAN backup directory ($ORACLE_HOME/dbs) and
change control file autobackup file name to default settings in RMAN (to '%F');
4. Startup nomount using pfile just created.
5. Restore spfile from autobackup.
6. Shutdown immediate and startup using new spfile.
7. Create pfile from spfile and update parameters accordingly to your local disk
structure.
8. Shutdown immediate and startup using new pfile.
9. Create spfile from pfile.
10.Restore control file from autobackup file.
11.You already got control files now, and following the normal recovery procedure to
do an imcomplete recovery and open database resetlogs.
ORA-06553: PLS-801: internal error [56319]
Database: 10.2.0.2.0
OS: migration from RHEL 64bit to RHEL 32bit
I needed to restore a database onto RHEL 23 bit platform while the only thing I got in hand is a tar ball, which is a backup from the database on RHEL 64 bit platform.
After all the process, I could bring up the database running with the following error:
ORA-06553: PLS-801: internal error [56319]
After searching a little bit, I found this is related migration error between 64 bit and 32 bit platform. The oracle suggestion is to run utlrp.sql in the process of migration.
Well, a solution worked out for me perfectly:
1. shutdown immediate
2. startup upgrade
3. @$ORACLE_HOME/rdbms/admin/utlirp.sql
4. shutdown immediate
5. startup
6. @$ORACLE_HOME/rdbms/admin/utlrp.sql
7. shutdown immediate
8. startup
Metalink document '272322.1 Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL'
recommends running utlirp.sql script which would do both regeneration of compiled
code of PL/SQL modules (which UTLIP.SQL does) and then recompiles all invalid PL/SQL
modules again (which UTLRP.SQL does).
OS: migration from RHEL 64bit to RHEL 32bit
I needed to restore a database onto RHEL 23 bit platform while the only thing I got in hand is a tar ball, which is a backup from the database on RHEL 64 bit platform.
After all the process, I could bring up the database running with the following error:
ORA-06553: PLS-801: internal error [56319]
After searching a little bit, I found this is related migration error between 64 bit and 32 bit platform. The oracle suggestion is to run utlrp.sql in the process of migration.
Well, a solution worked out for me perfectly:
1. shutdown immediate
2. startup upgrade
3. @$ORACLE_HOME/rdbms/admin/utlirp.sql
4. shutdown immediate
5. startup
6. @$ORACLE_HOME/rdbms/admin/utlrp.sql
7. shutdown immediate
8. startup
Metalink document '272322.1 Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL'
recommends running utlirp.sql script which would do both regeneration of compiled
code of PL/SQL modules (which UTLIP.SQL does) and then recompiles all invalid PL/SQL
modules again (which UTLRP.SQL does).
Recompiling Invalid Schema Objects
Identify invalid objects:
1. ALTER object_type owner.object_name COMPILE;
2. EXEC DBMS_DLL.ALTER_COMPILE('OBJECT_TYPE', 'OWNER', 'OBJECT_NAME);
3. EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'OWNER');
4. utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
* 0 - The level of parallelism is derived based on the CPU_COUNT parameter.
* 1 - The recompilation is run serially, one object at a time.
* N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
Show errors in case of compilation warnings:
1. ALTER SESSION SET PLSQL_WARNINGS='enable:all';
2. show error;
3. Query user_errors table;
4. Query dba_errors table;
SQL> SELECT owner, object_type, object_name
SQL> FROM dba_objects
SQL> WHERE status='INVALID';
1. ALTER object_type owner.object_name COMPILE;
2. EXEC DBMS_DLL.ALTER_COMPILE('OBJECT_TYPE', 'OWNER', 'OBJECT_NAME);
3. EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'OWNER');
4. utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
* 0 - The level of parallelism is derived based on the CPU_COUNT parameter.
* 1 - The recompilation is run serially, one object at a time.
* N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
Show errors in case of compilation warnings:
1. ALTER SESSION SET PLSQL_WARNINGS='enable:all';
2. show error;
3. Query user_errors table;
4. Query dba_errors table;
2008/11/30
Clean Uninstall Oracle for Windows
1. Uninstall oracle components using OUI
2. Run regedit and delete the following keys:
Delete the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key which contains registry entries for all Oracle products by using regedit.
Delete any references to Oracle services in the following registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\.
Looks for the entries that starts with "Ora" which are obviously related to Oracle.
3. Root the system
4. Delete the Oracle home directory
5. Delete the Oracle Program Files directory
6. Delete the Oracle Start Menu shortcuts directory
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
7. Remove Oracle references from the path.
Start -> Settings -> Control Panel -> System -> Advanced -> Environments Variables
2. Run regedit and delete the following keys:
Delete the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key which contains registry entries for all Oracle products by using regedit.
Delete any references to Oracle services in the following registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\.
Looks for the entries that starts with "Ora" which are obviously related to Oracle.
3. Root the system
4. Delete the Oracle home directory
5. Delete the Oracle Program Files directory
6. Delete the Oracle Start Menu shortcuts directory
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
7. Remove Oracle references from the path.
Start -> Settings -> Control Panel -> System -> Advanced -> Environments Variables
OMS Cannot Communicate with OMA on another Host
GC 10.2.0.4 on Windows Server 2003 R2
Agent 10.2.0.4 on RHEL 4
Problem:
Inside Grid Control, click on host and it takes a long time to see home page of that host.
Click on Host Performance page, error show up, something like "oracle.sysman.emSDK.emd.comm.CommException: No route to host: connect".
Check emagent.log, it seems all right. Check emoms.log, the same error is reported.
Solution:
From the error, it's something like no communication between oms and agent.
Restart agent several times, it succeed every time. Agent uploaded file successfully!
The communication from agent to oms seems all right. The problem might be in the communication from oms to agent. Use telnet to contact with agent using "telnet host port", it got refused! Some firewall or proxy might be between oms and agent. Contacted with SA, updated the firewall configuration on that port, it worked. The Host Performance page show up and reponsed quickly.
Agent 10.2.0.4 on RHEL 4
Problem:
Inside Grid Control, click on host and it takes a long time to see home page of that host.
Click on Host Performance page, error show up, something like "oracle.sysman.emSDK.emd.comm.CommException: No route to host: connect".
Check emagent.log, it seems all right. Check emoms.log, the same error is reported.
Solution:
From the error, it's something like no communication between oms and agent.
Restart agent several times, it succeed every time. Agent uploaded file successfully!
The communication from agent to oms seems all right. The problem might be in the communication from oms to agent. Use telnet to contact with agent using "telnet host port", it got refused! Some firewall or proxy might be between oms and agent. Contacted with SA, updated the firewall configuration on that port, it worked. The Host Performance page show up and reponsed quickly.
Configure Database Target Monitoring Credentials for Grid Control
GC 10.2.0.4
After intalling an agent on a linux host, everything in Grid Control is fine except that database status is unknown with a error "Metrics Collection Error". After I googled problem and checked documents, I found this may be caused by incorrect monitoring credentials settings. Oracle uses dbsnmp account to monitor target. In order to monitor database target, dbsnmp account must be valid (not locked) and what OMS expects and each componnet must be aware of the password for this account.
According to Section "Specifying New Target Monitoring Credentials" in "Oracle Enterprise Manager Advanced Configuration", there are two ways to modify monitoring credentials: Grid Control and emctl command.
Trying to configure database inside Grid Control by clicking on "configure", Properties page didn't show up as instructed.
So go to emctl command under AGENT_HOME/bin (on the target host):
emctl config agent credentials [Target_name[:Target_type]]
For example:
emctl config agent credentials:oracle_database
You can list the target names by:
emctl config agent listtargets
After configuration, go to database home page in Grid Control, it worked. Home page show up!
After intalling an agent on a linux host, everything in Grid Control is fine except that database status is unknown with a error "Metrics Collection Error". After I googled problem and checked documents, I found this may be caused by incorrect monitoring credentials settings. Oracle uses dbsnmp account to monitor target. In order to monitor database target, dbsnmp account must be valid (not locked) and what OMS expects and each componnet must be aware of the password for this account.
According to Section "Specifying New Target Monitoring Credentials" in "Oracle Enterprise Manager Advanced Configuration", there are two ways to modify monitoring credentials: Grid Control and emctl command.
Trying to configure database inside Grid Control by clicking on "configure", Properties page didn't show up as instructed.
So go to emctl command under AGENT_HOME/bin (on the target host):
emctl config agent credentials [Target_name[:Target_type]]
For example:
emctl config agent credentials
You can list the target names by:
emctl config agent listtargets
After configuration, go to database home page in Grid Control, it worked. Home page show up!
Agent Cannot Upload Data onto OMS
GC 10.2.0.4
Agent is started successfully, but no new target is displayed inside Grid Control.
Using command "emctl status agent", I found that no data was uploaded onto OMS.
Solution: emctl secure agent
Agent is started successfully, but no new target is displayed inside Grid Control.
Using command "emctl status agent", I found that no data was uploaded onto OMS.
Solution: emctl secure agent
Grid Control Loader Trhoughput Critical Error
GC 10.2.0.4
On the home page, I received critical error like this:
Metric Loader Throughput (rows per second)
Loader Name xxxxxxxxxxxxxxxxxxxx:4889_Management_Service,XMLLoader0
Severity Critical
Solution:
Add the following line into the file emoms.properties inside OMS_HOME/sysman/config:
em.loader.threadPoolSize = 2
Don't forget to shutdown oms before any update.
On the home page, I received critical error like this:
Metric Loader Throughput (rows per second)
Loader Name xxxxxxxxxxxxxxxxxxxx:4889_Management_Service,XMLLoader0
Severity Critical
Solution:
Add the following line into the file emoms.properties inside OMS_HOME/sysman/config:
em.loader.threadPoolSize = 2
Don't forget to shutdown oms before any update.
2008/11/10
Set Up OS Authentication
OS authentication allows Oracle to pass control of user authentication to the operating system.
UNIX and LINUX:
1) Use "useradd" and "passwd" command to create OS user.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) CREATE USER ops$user IDENTIFIED EXTERNALLY.
4) Grant privileges to the user.
WINDOWS:
1) Create local user (Start > Programs > Administrative Tools > Computer Management) or domain user in Active Directory.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) Set SQLNET.AUTHENTICATOIN_SERVICES=(NTS) option in sqlnet.ora file.
4) Create a new registry parameter only if you are not authenticating a domain name with a user.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, add value, "OSAUTH_PREFIX_DOMAIN" for the name, "REG_EXPAND_SZ" for the data type, "true" for the string value. true enables the server to differentiate between multiple frank usernames, whether they are local user frank, domain user frank on sales, or domain user frank on another domain in your network. Entering false causes the domain to be ignored and local user frank to become the default value of the operating system user returned to the server.
5) CREATE USER ops$user IDENTIFIED EXTERNALLY; local user
CREATE USER "OPS$DOMAIN\USER" IDENTIFIED EXTERNALLY; domain user
"" is required, and characters inside must be in uppercase.
UNIX and LINUX:
1) Use "useradd" and "passwd" command to create OS user.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) CREATE USER ops$user IDENTIFIED EXTERNALLY.
4) Grant privileges to the user.
WINDOWS:
1) Create local user (Start > Programs > Administrative Tools > Computer Management) or domain user in Active Directory.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) Set SQLNET.AUTHENTICATOIN_SERVICES=(NTS) option in sqlnet.ora file.
4) Create a new registry parameter only if you are not authenticating a domain name with a user.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, add value, "OSAUTH_PREFIX_DOMAIN" for the name, "REG_EXPAND_SZ" for the data type, "true" for the string value. true enables the server to differentiate between multiple frank usernames, whether they are local user frank, domain user frank on sales, or domain user frank on another domain in your network. Entering false causes the domain to be ignored and local user frank to become the default value of the operating system user returned to the server.
5) CREATE USER ops$user IDENTIFIED EXTERNALLY; local user
CREATE USER "OPS$DOMAIN\USER" IDENTIFIED EXTERNALLY; domain user
"" is required, and characters inside must be in uppercase.
VNI-4040
OMS is out of sync with agent.
Solution:
shutdown Agent, delete the .q files, and start the agent. Refresh the node where the agent is running on.
Solution:
shutdown Agent, delete the .q files, and start the agent. Refresh the node where the agent is running on.
High CPU Utilization after start OMS
Grid Control 10.2.0.2
Repository Database 10.1.0.4
Windows 2003 R2 SP2
After new installation of 10g Grid Control on this server, CPU keeps running 100%. Every time I stop OMS using "opmnctl stopall", CPU utilization falls back to normal.
Every time "opmnctl startall" is issued, CPU utilization go up to 100% again and never fall down.
After checking Task Manager, I found out oracle.exe occupies near 99% of CPU utilization.
On windows platform, there is only one oracle master process, and it is divided into several oracle threads.
In order to check CPU utilization for each thread, "Performance Monitor" is the best solutoin. "Diagnosing High CPU Utilization on NT" (metalink note: 116236.1) gives a detailed explanation.
After some check on dynamic performance views, using
SELECT ss.sid,se.command,ss.value CPU ,se.username,se.program
FROM v$sesstat ss, v$session se
WHERE ss.statistic# IN
(SELECT statistic#
FROM v$statname
WHERE name = 'CPU used by this session')
AND se.sid=ss.sid
ORDER BY ss.sid;
I'm still unable to identify the problem, even though I can find the suspicious sid.
I run into a discussion on a forum, and found a solution as follows:
Shutdown OEM, login as SYSMAN user and restart the provisioning daemon by executing the two packaged procedures
SYSMAN> execute MGMT_PAF_UTL.STOP_DAEMON
PL/SQL procedure successfully completed.
SYSMAN> execute MGMT_PAF_UTL.START_DAEMON
PL/SQL procedure successfully completed.
Start OEM again and the problem is gone.
And it worked.
It seems like a bug inside oracle.
Repository Database 10.1.0.4
Windows 2003 R2 SP2
After new installation of 10g Grid Control on this server, CPU keeps running 100%. Every time I stop OMS using "opmnctl stopall", CPU utilization falls back to normal.
Every time "opmnctl startall" is issued, CPU utilization go up to 100% again and never fall down.
After checking Task Manager, I found out oracle.exe occupies near 99% of CPU utilization.
On windows platform, there is only one oracle master process, and it is divided into several oracle threads.
In order to check CPU utilization for each thread, "Performance Monitor" is the best solutoin. "Diagnosing High CPU Utilization on NT" (metalink note: 116236.1) gives a detailed explanation.
After some check on dynamic performance views, using
SELECT ss.sid,se.command,ss.value CPU ,se.username,se.program
FROM v$sesstat ss, v$session se
WHERE ss.statistic# IN
(SELECT statistic#
FROM v$statname
WHERE name = 'CPU used by this session')
AND se.sid=ss.sid
ORDER BY ss.sid;
I'm still unable to identify the problem, even though I can find the suspicious sid.
I run into a discussion on a forum, and found a solution as follows:
Shutdown OEM, login as SYSMAN user and restart the provisioning daemon by executing the two packaged procedures
SYSMAN> execute MGMT_PAF_UTL.STOP_DAEMON
PL/SQL procedure successfully completed.
SYSMAN> execute MGMT_PAF_UTL.START_DAEMON
PL/SQL procedure successfully completed.
Start OEM again and the problem is gone.
And it worked.
It seems like a bug inside oracle.
Relocate tempfile in 10g
database 10.1.0.4
You cannot use "ALTER DATABASE RENAME FILE ... TO ..." to relocate a tempfile in 10g.
If the temporary tablespace to which tempfile belongs is the default tablespace, then
1) Copy the tempfile to the new location using OS command.
2) Create new temporary tablespace
CREATEA TEMPORARY TABLESPACE new_name TEMPFILE 'new_tempfile_name' REUSE;
3) Make it the default temp tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'new_name';
4) Drop the old tablespace
DROP TABLESPACE 'old_name' including contents and datafiles;
If you want to keep the same temporary tablespace name, then rename the old name first using:
You cannot use "ALTER DATABASE RENAME FILE ... TO ..." to relocate a tempfile in 10g.
If the temporary tablespace to which tempfile belongs is the default tablespace, then
1) Copy the tempfile to the new location using OS command.
2) Create new temporary tablespace
CREATEA TEMPORARY TABLESPACE new_name TEMPFILE 'new_tempfile_name' REUSE;
3) Make it the default temp tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'new_name';
4) Drop the old tablespace
DROP TABLESPACE 'old_name' including contents and datafiles;
If you want to keep the same temporary tablespace name, then rename the old name first using:
SQL> ALTER TABLESPACE 'old_name' RENAME TO 'new_name';
2008/10/26
ORA-12500 on windows platform
Oracle 9.2.5.0, Windows 2000
This is a recent issure I found concerning oracle net service.
If I start OracleServiceSID under a domain user, and start OracleOracleHomeTNSListener under LocalSystem, I cannot connect to oracle through local naming on the same machine, I get ORA-12500. But I can connect to oracle using OS authentication on the same machine, and I can also connect to oracle through local naming from a client machine. But this wired thing will never happen if I start both of them under the same domain user, or start OracleServiceSID under LocalSystem.
Big difference between Linux/Unix and Windows platform regarding Oracle is that windows have domain user, and service for oracle, and dierent services can be started under different users. DBAs should always pay attention to under which user the service is started. Different privs can lead to oracle errors.
This is a recent issure I found concerning oracle net service.
If I start OracleServiceSID under a domain user, and start OracleOracleHomeTNSListener under LocalSystem, I cannot connect to oracle through local naming on the same machine, I get ORA-12500. But I can connect to oracle using OS authentication on the same machine, and I can also connect to oracle through local naming from a client machine. But this wired thing will never happen if I start both of them under the same domain user, or start OracleServiceSID under LocalSystem.
Big difference between Linux/Unix and Windows platform regarding Oracle is that windows have domain user, and service for oracle, and dierent services can be started under different users. DBAs should always pay attention to under which user the service is started. Different privs can lead to oracle errors.
Discovering node incorrectly due to listener configuration
Oracle 9.2.0.5, Windows 2000
Normally, oracle will dynamically register service with the name defined in the parameter service_names. So if you add the same entry inside the listener.ora, it will list the same service twice when you execute "lsnrctl status" command. Sometimes, it's not necessary to add this entry into listener.ora. But recently, when I was trying to discover node from OEM, I could not discover the database
on that node or the discovering process generated some error. The OMS, agent, listener are all running. OHS and Listener can be discovered. I finally found that I didn't list any service in my listener.ora file. After I added that service, the discovering process is fine. I'm not sure if this is the reason or some kind of oracle bug for 9i. Worth a shot!
Note:
When the same thing happened, you may want to check out the dbsnmp.log, nmiconf.log, and Oracle%Oracle_Home%Agent.nohup file inside the directory %Oracle_Home%\network\log.
Always try restarting Agent.
Normally, oracle will dynamically register service with the name defined in the parameter service_names. So if you add the same entry inside the listener.ora, it will list the same service twice when you execute "lsnrctl status" command. Sometimes, it's not necessary to add this entry into listener.ora. But recently, when I was trying to discover node from OEM, I could not discover the database
on that node or the discovering process generated some error. The OMS, agent, listener are all running. OHS and Listener can be discovered. I finally found that I didn't list any service in my listener.ora file. After I added that service, the discovering process is fine. I'm not sure if this is the reason or some kind of oracle bug for 9i. Worth a shot!
Note:
When the same thing happened, you may want to check out the dbsnmp.log, nmiconf.log, and Oracle%Oracle_Home%Agent.nohup file inside the directory %Oracle_Home%\network\log.
Always try restarting Agent.
Startup OracleServiceSID using spfile
Oracle 9.2.5.0, Windows Server 2000
If ORA_SID_AUTOSTART is set to TRUE in Registry, oracle instance will be started automatically when OracleServiceSID is started.
If ORA_SID_PFILE is set the full path of the initializaiton parameter file, oracle instance will be started using the specified pfile when OracleServiceSID is started. If this entry is not present, then oradim will try to start the database with an SPFILE or PFILE from %ORACLE_HOME%\database.
If ORA_SID_AUTOSTART is set to TRUE in Registry, oracle instance will be started automatically when OracleServiceSID is started.
If ORA_SID_PFILE is set the full path of the initializaiton parameter file, oracle instance will be started using the specified pfile when OracleServiceSID is started. If this entry is not present, then oradim will try to start the database with an SPFILE or PFILE from %ORACLE_HOME%\database.
Could not start Oracle 9i database http service
This error looks like one that frequently appeared when starting the OHS on a DB9iR2 on Windows Platform. If you try to start the OHS by the windows services console and by the command line, a conflict with the lock file (pid file located in %Oracle_Home%\Apache\Apache\logs\) will prevent the OHS from starting, you must delete the file prior to attempt starting the OHS. You cannot start OHS with both, the console
and the command line, you should choose just one of them.
and the command line, you should choose just one of them.
Web Based EM DB Control for Oracle 9i
There is no actually web version EM database control for oracle 9i. But DBAs can launch EM from web browser through JInitiator.
Oracle JInitiator is implemented as a plug-in (Netscape Navigator) or ActiveX Object (IE).
Oracle JInitiator allows you to specify the use of the Oracle certified JVM (Java Virtual Machine) on web clients instead of relying on the default JVM provided by the browser.
There are many docs on OTN website.
Some versions of JInitiator (ex. 1.1.8.19) must be plugged in manually to Firefox. These are the steps:
1) Install JInitiator.
2) C:\Program Files\Oracle\JInitiator 1.1.8.19\bin\NP JinitXXXX.dll XXXX = version
3) Paste it under this directory of Firefox (C:\Program Files\Mozilla Firefox\plugins)
4) Restart Firefox.
Oracle JInitiator is implemented as a plug-in (Netscape Navigator) or ActiveX Object (IE).
Oracle JInitiator allows you to specify the use of the Oracle certified JVM (Java Virtual Machine) on web clients instead of relying on the default JVM provided by the browser.
There are many docs on OTN website.
Some versions of JInitiator (ex. 1.1.8.19) must be plugged in manually to Firefox. These are the steps:
1) Install JInitiator.
2) C:\Program Files\Oracle\JInitiator 1.1.8.19\bin\NP JinitXXXX.dll XXXX = version
3) Paste it under this directory of Firefox (C:\Program Files\Mozilla Firefox\plugins)
4) Restart Firefox.
RMAN Performance when using UNC Paths
It is not advisable to take RMAN backups through the network on Windows. Taking the backup to a local disk and then transfering the backup pieces on the UNC path is 10 times quicker, this matches the maximum bandwidth of the link. It was a windows issue.
ORA-19504 due to windows user privs using rman backup
If you are using windows and rman backup destination is network device, OracleServiceSID must be started by OS user with permissions to write to this network device. OracleServiceSID running under LocalSystem has no access right to network device. So, you have to set account for OracleServiceSID in service control panel and restart that service.
2008/10/18
Trick to delete job stuck in OEM
Sometimes, deleting job in OEM makes the job status 'deleting' and the job will get stuck. Try to stop the agent on the host where the job is aimed to and start again. The job will be able to be deleted.
Fixit job can not be deleted before you delete it from the event it is associated to.
Fixit job can not be deleted before you delete it from the event it is associated to.
Steps to cleanly stop OMS
to stop the management server:
$ORACLE_HOME/Apache/Apache/bin/apachectl stop
$ORACLE_HOME/bin/oemctl stop oms sysman/password
shut down database normally
$ORACLE_HOME/bin/lsnrctl stop
to start
$ORACLE_HOME/bin/lsnrctl start
start database normally
$ORACLE_HOME/bin/oemctl start oms sysman/password
$ORACLE_HOME/Apache/Apache/bin/apachectl start
$ORACLE_HOME/Apache/Apache/bin/apachectl stop
$ORACLE_HOME/bin/oemctl stop oms sysman/password
shut down database normally
$ORACLE_HOME/bin/lsnrctl stop
to start
$ORACLE_HOME/bin/lsnrctl start
start database normally
$ORACLE_HOME/bin/oemctl start oms sysman/password
$ORACLE_HOME/Apache/Apache/bin/apachectl start
How to Shutdown Oracle Instance on Windows Platform?
Oracle 9.2, Windows 2000
Shutdown oracle instance through SQL*Plus is always the safe way.
If you want to shutdown oracle instance through oracle service on windows platform, make sure the following parameters are set in the Registry.
Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX\:
ORA_SID_SHUTDOWN = TRUE|FALSE - Set this to TRUE to enable the proper shutdown of the database
ORA_SID_SHUTDOWN_TIMEOUT = Timeout(in seconds) - Sets the maximum time (in seconds) to wait for the shutdown to compplete before the service for a particular SID stops. This timeout should be set high enough to give the database time to complete the shutdown. If this is lower than the time needed, you won't have a clean shutdown. You can check this in the alert file.
ORA_SID_SHUTDOWNTYPE = n|i|a - Where n = SHUTDOWN NORMAL; i = SHUTDOWN IMMEDIATE; a = SHUTDOWN ABORT
Failure to do so, then stopping a service in this manner, will do WORSE than a SHUTDOWN ABORT because Windows will just "clean" the memory. This action is akin to performing KILL -F in Windows or a KILL -9 in Unix. Any cold backups are there likely to be useless.
If you want the oracle instance startup automatically when the system reboot, the following key needs to be set.
ORA_SID_AUTOSTART = TRUE|FALSE
Notice that:
1) OracleServiceSID is running does not guarantee that oracle instance is running.
2) Users cannot connect to oracle unless OracleServiceSID is running.
Shutdown oracle instance through SQL*Plus is always the safe way.
If you want to shutdown oracle instance through oracle service on windows platform, make sure the following parameters are set in the Registry.
Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX\:
ORA_SID_SHUTDOWN = TRUE|FALSE - Set this to TRUE to enable the proper shutdown of the database
ORA_SID_SHUTDOWN_TIMEOUT = Timeout(in seconds) - Sets the maximum time (in seconds) to wait for the shutdown to compplete before the service for a particular SID stops. This timeout should be set high enough to give the database time to complete the shutdown. If this is lower than the time needed, you won't have a clean shutdown. You can check this in the alert file.
ORA_SID_SHUTDOWNTYPE = n|i|a - Where n = SHUTDOWN NORMAL; i = SHUTDOWN IMMEDIATE; a = SHUTDOWN ABORT
Failure to do so, then stopping a service in this manner, will do WORSE than a SHUTDOWN ABORT because Windows will just "clean" the memory. This action is akin to performing KILL -F in Windows or a KILL -9 in Unix. Any cold backups are there likely to be useless.
If you want the oracle instance startup automatically when the system reboot, the following key needs to be set.
ORA_SID_AUTOSTART = TRUE|FALSE
Notice that:
1) OracleServiceSID is running does not guarantee that oracle instance is running.
2) Users cannot connect to oracle unless OracleServiceSID is running.
2008/10/05
VNI-2015: authentication error
VN-2015 error occurs because of mismatch of the required privileges of the user. It all depends on the OS and type of USER.
If the OS is MS-NT, please follow the procedure.
If you are a DOMAIN user, the user will need "Logon as Batch Job" privilege. On a Primary Domain Controller, if the account that has "Logon as Batch Job" privilege is not a member of the Local Administrator group, you will also need to grant Logon Locally.
If you are a local NT user, you will need "Logon as Batch Job" privilege.
Verify the Intelligent Agent's startup account is the default SYSTEM.
If the batch job needs to access network resource, you need to verify the user has the access privilege to the network resource.
If the OS is MS-NT, please follow the procedure.
If you are a DOMAIN user, the user will need "Logon as Batch Job" privilege. On a Primary Domain Controller, if the account that has "Logon as Batch Job" privilege is not a member of the Local Administrator group, you will also need to grant Logon Locally.
If you are a local NT user, you will need "Logon as Batch Job" privilege.
Verify the Intelligent Agent's startup account is the default SYSTEM.
If the batch job needs to access network resource, you need to verify the user has the access privilege to the network resource.
ORA-27100
ORA-27100: Shared memory realm already exists
Cause: Tried to start duplicate instances, or tried to restart an instance that had not been properly shutdown
Action: Use a different instance name, or cleanup the failed instance's SGA
To resolve ORA-27100, you should execute the following instructions:
-Move the spfile from its default location or rename the spfile
-Restart the service
-Now the service will start the database using pfile, with old SGA settings. This would start your database successfully.
-Create the new spfile from pfile.
Another cause for this error is trying to start the database using a wrong user on windows platform. This happened to me when I tried to start the database using a domain user on windows 2000. After changing the user to LocalSystem, database was started successfully.
Cause: Tried to start duplicate instances, or tried to restart an instance that had not been properly shutdown
Action: Use a different instance name, or cleanup the failed instance's SGA
To resolve ORA-27100, you should execute the following instructions:
-Move the spfile from its default location or rename the spfile
-Restart the service
-Now the service will start the database using pfile, with old SGA settings. This would start your database successfully.
-Create the new spfile from pfile.
Another cause for this error is trying to start the database using a wrong user on windows platform. This happened to me when I tried to start the database using a domain user on windows 2000. After changing the user to LocalSystem, database was started successfully.
2008/06/20
"ORA-12547: TNS:lost contact" when creating database in Oracle 10g
Problem: I didn't optionally to create a database at install time, and when trying to do so later using the dbca tool, I keep getting the error "ORA-12547: TNS:lost contact" popping up.
Action: Install libaio & libaio-devel packages which are asynchronous library for Linux.
Action: Install libaio & libaio-devel packages which are asynchronous library for Linux.
2008/06/16
Manually Drop an Oracle Database
1)Set the Oracle SID of the database which you interested to drop.
3)Mount the database.
$ export ORACLE_SID=XXXX
3)Mount the database.
SQL> startup force mount
SQL> alter system enable restricted session;
SQL> drop database
EXP-00091 Exporting questionable statistics tips
The "EXP-00091 Exporting questionable statistics" error happens when you export table data with its related optimizer statistics, and Oracle cannot verify the currency of these statistics. When CBO statistics are created/updated with dbms_stats, the time is noted, but it’s always a good idea not to export statistics since they should be recalculated after import.
You can remove the EXP-00091 error by exporting with "statistics="none" or by setting the client character set (e.g. export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1).
The Oracle docs note:
EXP-00091 Exporting questionable statistics
Cause: Export was able to export statistics, but the statistics may not be useable. The statistics are questionable because one or more of the following happened during export:
- A row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export,
- Only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table.
Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. If desired, import parameters can be supplied so that only
non-questionable statistics will be imported, and all questionable statistics will be recalculated.
You can remove the EXP-00091 error by exporting with "statistics="none" or by setting the client character set (e.g. export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1).
The Oracle docs note:
EXP-00091 Exporting questionable statistics
Cause: Export was able to export statistics, but the statistics may not be useable. The statistics are questionable because one or more of the following happened during export:
- A row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export,
- Only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table.
Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. If desired, import parameters can be supplied so that only
non-questionable statistics will be imported, and all questionable statistics will be recalculated.
2008/06/12
RULE Based Optimizer vs. COST Based Optimizer
RULE Based Optimizer
As its name implies the rule based optimizer uses a set of 15 rules to determine how to best process a query. The rule chosen is based strictly on query structure and pays no attention to the number of rows, partitions or other query specific features and statistics. The query optimization paths used by the cost based optimizer are:
Rank Path
1 Single row by ROWID
2 Single row by Cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite Index
9 Single Column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN on indexed column
14 ORDER BY indexed columns
15 Full table scan
The major benefit of the rule based optimizer was that for a specific query the execution plan was stable regardless of how big the table or its associated indexes grew as long as the index structure didn't change. The major disadvantages of the rule based optimizer are that the execution plan didn't change no matter how large the table or its indexes grew, and, it can't take advantage of new features as easily as a cost based optimizer.
COST Based Optimizer
The COST based optimizer was introduced in later versions of Oracle 7. The cost based optimizer uses a proprietary algorithm to assign a cost to each step of an execution plan for a query, and then, by looking at equivalent plans the optimizer can choose the one with the lowest cost for a specific query. The cost algorithm is dependent up on accurate table statistics in order to come up with accurate cost information. The table statistics are created and maintained by use of the ANALYZE command.
The major benefit of the cost based optimizer is that for changes in table and index sizes the optimizer can re-evaluate the execution plan and adjust to obtain an optimal performance profile. However, the cost based optimizer is only as good as the statistics fed to it, and, it tests a fully qualified DBA using the rule based optimizer can get better performance. However, unless you like to spend your free time using the tools tkprof, explain plan or third query party analyzers, I suggest jsut using the cost based optimizer and an "intelligent" table analyzer procedure.
As its name implies the rule based optimizer uses a set of 15 rules to determine how to best process a query. The rule chosen is based strictly on query structure and pays no attention to the number of rows, partitions or other query specific features and statistics. The query optimization paths used by the cost based optimizer are:
Rank Path
1 Single row by ROWID
2 Single row by Cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite Index
9 Single Column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN on indexed column
14 ORDER BY indexed columns
15 Full table scan
The major benefit of the rule based optimizer was that for a specific query the execution plan was stable regardless of how big the table or its associated indexes grew as long as the index structure didn't change. The major disadvantages of the rule based optimizer are that the execution plan didn't change no matter how large the table or its indexes grew, and, it can't take advantage of new features as easily as a cost based optimizer.
COST Based Optimizer
The COST based optimizer was introduced in later versions of Oracle 7. The cost based optimizer uses a proprietary algorithm to assign a cost to each step of an execution plan for a query, and then, by looking at equivalent plans the optimizer can choose the one with the lowest cost for a specific query. The cost algorithm is dependent up on accurate table statistics in order to come up with accurate cost information. The table statistics are created and maintained by use of the ANALYZE command.
The major benefit of the cost based optimizer is that for changes in table and index sizes the optimizer can re-evaluate the execution plan and adjust to obtain an optimal performance profile. However, the cost based optimizer is only as good as the statistics fed to it, and, it tests a fully qualified DBA using the rule based optimizer can get better performance. However, unless you like to spend your free time using the tools tkprof, explain plan or third query party analyzers, I suggest jsut using the cost based optimizer and an "intelligent" table analyzer procedure.
2008/06/02
Modify Parameter in RAC Environment
You can modify the value of your initialization parameters by using the ALTER SYSTEM SET command. This is the same as with a single-instance database except that you have the possibility to specify the SID clause in addition to SCOPE clause.
SQL>alter system set <dpname> scope=<svalue> sid='<sid|*>';
The default value for the sid clause is sid='*'.
Specify sid='*' or use default value, if you want to change the value of the parameter for all instances.
Specify sid='sid' if you want to change the value of the parameter only for the instance sid.
The <sid>.<pname> will override the value of <sid>.<pname>.
The sid='sid' of the ALTER SYSTEM RESET command allows you to override the precedence of a currently used <sid>.<pname> entry. The <sid>.<pname> entry will be deleted. Oracle use the value of *.<pname> for instance 'sid'.
SQL>alter system reset <dpname> sid='<sid>';
The following command will reset the value for instance * from instance sid.
SQL>alter system reset <dpname> scope='spfile' sid='*';
You must specify scope as 'spfile', because you can reset memory value for instance * from instance sid.
SQL>alter system set <dpname> scope=<svalue> sid='<sid|*>';
The default value for the sid clause is sid='*'.
Specify sid='*' or use default value, if you want to change the value of the parameter for all instances.
Specify sid='sid' if you want to change the value of the parameter only for the instance sid.
The <sid>.<pname> will override the value of <sid>.<pname>.
The sid='sid' of the ALTER SYSTEM RESET command allows you to override the precedence of a currently used <sid>.<pname> entry. The <sid>.<pname> entry will be deleted. Oracle use the value of *.<pname> for instance 'sid'.
SQL>alter system reset <dpname> sid='<sid>';
The following command will reset the value for instance * from instance sid.
SQL>alter system reset <dpname> scope='spfile' sid='*';
You must specify scope as 'spfile', because you can reset memory value for instance * from instance sid.
2008/05/25
Change Database into Archvielog Mode
If you create your Oracle9i database through DBCA, it is created in NOARCHIVELOG mode by default unless you select archivelog option during the creating process.
You can change Archive Mode when database is mounted but now opened.
You can enable/disable automatic archiving by changing parameter archive_log_start. This parameter is static.
The following command can show you database archive settings.
You can change Archive Mode when database is mounted but now opened.
SQL> alter database archivelog;
SQL> alter database noarchivelog;
You can enable/disable automatic archiving by changing parameter archive_log_start. This parameter is static.
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_start=FALSE scope=spfile;
The following command can show you database archive settings.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\BACKUP
Oldest online log sequence 34
Current log sequence 37
2008/05/23
Open Database in Restricted Mode
Restricted mode will only allow users with RESTRICTED SESSION privileges to access the database (typically DBA’s).
We use the startup restrict command to open the database in restricted mode as seen in this example.
-- Put the database in restricted mode.
We use the startup restrict command to open the database in restricted mode as seen in this example.
SQL> startup restrict;
-- Put the database in restricted mode.
SQL> alter system enable restricted session;
SQL> alter system disable restricted session;
SQL> select logins from v$instance;
SQL> alter system kill session 'session-id, session-serial';
Subscribe to:
Posts (Atom)