Rebuild Table as Partitioned Table

1. Check if tablespace has enough room for an extra copy of the table.
2. Drop any PK, Indexes, FK for the table.
3. Rename the table to a temporary table name.
4. Create new partitioned table with neccessary contraints.
5. Merge data from temporary table into the partitioned table.
6. Drop the partitioned table.

Cronjob Doesn't Source Profile By Default

The script runs perfectly in command line won't necessarily run in cronjob. The most possible reason for this is that 'PATH' is not set in the script because by default cronjob won't source '.profile'. And user won't notice this because if user runs the script in command line, he's already sourced his profile. You should include it in your crontab shells or run commands in a way they source profile.

Warning: no filemap entries available

The following warning occurs while doing opatch lsinventory:
"Warning: no filemap entries available"

Cause:
$ORACLE_HOME/inventory/oneoffs//etc/config/actios or $ORACLE_HOME/inventory/oneoffs//etc/config/inventory does not exist.

Solution:
1. Download the failed patch.
2. Unzip the downloaded patch.
3. cd /etc/config
4. cp inventory $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
5. cp actions $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
6. Run 'opatch lsinventory' again to verify the result.

Please note this is to fix an issue where "opatch lsinventory" lists an already installed patch this way

Loader Throughput Too High for Grid Control

Grid Control 10.2.0.4

Metric 'Loader Throughput(rows per second)' is alerted to exceed the critical threshold (3000) on Grid Control homepage. To solve this problem, shutdown grid control and set em.loader.threadPoolSize parameter in file 'emoms.properties' to a higher value (1 to 10). The file is located in $OMS_HOME/sysman/config/ directory. Then, restart Grid Control.

Recycle the listener.log File

The parameter 'LOGGING_LISTENER' in listener.ora file controls whether listener will be logged, and it defaults to LOGGING_LISTENER=ON. To turn off the log, you have to add LOGGING_LISTENER=OFF into listener.ora file.

If you try to rename or remove the listener.log file without shutting down listener on Windows platform, you will notice that Windows holds a lock on this file and returns an error. Even under Unix, the Oracle listener process holds an open handle to the file. You can remove the file, but Oracle will not re-create the file when it attempts to write it again.

Here is a solution for renaming or removing the listener.log file without having to stop and restart the listener:

lsnrctl set log_status off
mv listener.log listener.log.old
touch listener.log
lsnrctl set log_status on

Estimate size for undo tablespace

Sizing an UNDO tablespace requires three pieces of information:

UR: UNDO_RETENTION in seconds
UPS: Number of undo data blocks generated per second
DBS: Overhead varies based on extent and file size (db_block_size)

UndoSpace = (UR * (UPS * DBS) + DBS)
or, when the guesstime equates to zero, then add a multiplier (24) to the overhead (DBS) to derive more appropriate results:
UndoSpace = (UR * (UPS * DBS)) + (DBS * 24)

UNDO_RETENTION and DB_BLOCK_SIZE can be obtained from the initialization file. UPS can be acquired from the following query:

SELECT (sum(undoblks))/sum((end_time-begin_time)*86400)
FROM v$undostat;

How to Shutdown and Start Grid Control and All Its Components

Grid Control: 10.2.0.4

Shutdown Grid Control

1. $OMS_HOME/bin/emctl stop oms
2. $OMS_HOME/bin/emctl stop iasconsole
3. $OMS_HOME/opmn/bin/opmnctl stopall
4. $AGENT_HOME/bin/emctl stop agent
5. shutdown repository database
6. shutdown listener

Start Grid Control

1. start listener
2. start repository database
3. $OMS_HOME/bin/emctl start oms
4. $OMS_HOME/opmn/bin/opmnctl startproc ias-component=WebCache
5. $AGENT_HOME/bin/emctl start agent
6. $OMS_HOME/bin/emctl start iasconsole

Oracle Components Enabled

Oracle 10.2.0.2

You can check what components, like JVM or XML DB, are loaded into your Oracle Server by query view DBA_REGISTRY:

SQL> SELECT comp_name, status FROM dba_registry;

Enable archivelog mode in RAC

In 10g Release 1, the database must be mounted (not open) by an exclusive instance. In other words, set the CLUSTER_DATABASE parameter to FALSE. After executing the ALTER DATABASE SQL statement to change the archive log mode, shutdown the instance and restart it with the CLUSTER_DATABASE parameter set to TRUE before you restart the other instances.

1. Login to one of the nodes, disable the cluster instance parameter and set log archive destination:

$ sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile sid='';
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';

2. Shutdown all instances:

$ srvctl stop database -d 

3. Using the local instance, mount the database:

$ sqlplus / as sysdba
SQL> startup mount;

4. Enable archivelog mode

SQL> alter database archivelog;

5. Re-enable support for clustering from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='';

6. Shutdown the local instance:

SQL> shutdown immediate;

7. Bring all instances back up:

$ srvctl start database -d 

From 10g Release 2, you don't need to modify CLUSTER_DATABASE parameter to enable archivelog mode.

1. Login to one of the nodes, set log archive destination:

$ sqlplus / as sysdba
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';

2. Shutdown all instances:

$ srvctl stop database -d 

3. Using the local instance, mount the database:

$ sqlplus / as sysdba
SQL> startup mount;

4. Enable archivelog mode

SQL> alter database archivelog;

5. Shutdown the local instance:

SQL> shutdown immediate;

6. Bring all instances back up:

$ srvctl start database -d 

Memory Tuning Tips

Oracle 10gR2

You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS

If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE

Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.

SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.

ORA-04031 and SGA settings

Oracle 10.2.0.2
OS RHEL 4

Recently, I got error message from Grid Control for one of our production databases.

Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).

The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.

Another question I have during the research is that what's point of having both sga_target and sga_max_size.

Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.

Grid Control Agent Crash with 'too many open files' Error

Grid Control Agent 10.2.0.4
Database: 10.2.0.2
OS Platform: RHEL Release 4 64bit

Agent crashes a lot, intermittently with 'too many open files' error inside Grid Control. Trace file 'emagent.trc' gives 'health check' error lik this:

2008-03-24 15:24:52 Thread-4124650400 ERROR fetchlets.healthCheck: GIM-00105: file not found
2008-03-24 15:24:52 Thread-4124650400 ERROR engine: [oracle_database,,health_check] : nmeegd_GetMetricData failed : Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above.
2008-03-24 15:24:52 Thread-4124650400 WARN collector: Error exit. Error message:Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above

Cause: Bug 5872000 - Healthcheck Error Occurs fror 32Bit Database on 64Bit OS Due to Bug4526916 Fix.
The Healthcheck file, namely $ORACLE_HOME/dbs/hc_.cat file differs in size from the memory structure used by the Agent to read it. This file is created by the database on startup time, if not present.

This happens when the database is e.g. 10.2.0.4 and the agent is 10.2.0.3 and vice versa.

Possible solutions:

1. Apply Patch 5872000 to databases on 64-bit machine.
This needs to be applied on top of 10.1 -> 10.2.0.3, and 11.1.0.6 databases. THe file $ORACLE_HOME/dbs/hc_.dat may need to be removed before starting up the database after patch application. This file is created on database start up if not present. The agent uses this file for the Healthcheck metric. By recreating the file on start up after the patch application, the file is the correct one needed by the agent.
2.Disable the healthcheck metric per database in Grid Control.
Check 379423.1 in Metalink on 'How to edit or disable the Health Check Metric Collection in Grid Control 10.2'.

Note: If the second workaround is applied, then you have to redo it everytime you add new database target into Grid Control.

Reference Doc in Metalink:
564617.1
566607.1
379423.1
469227.1

Open File Limit on Linux

Oracle 10.2.0.2
3-node RAC
RHEL AS release 4

Our application experienced performance downgrade today and user can not get response from application for around an hour. Not long after that, when we tried to log into the system, a message 'Too many open files' showed up and we were unable to login.

Searching a little bit, I found that Linux limits the number of open file handles. I recently installed Oracle Grid Control Agent on our RAC and created new database. The open file handles was increased up to the limit, and the system was unstable.

The file /proc/sys/fs/file-nr lists the number of current allocated file handles, available file handles in the allocated file handles and maximum file handles that can be opend for the whole system.
Oracle recommends that the file handles for the entire system be set to at lease 65536.
You can alter the default setting for the maximum number of file handles without rebooting the machine by making the changes directly to the /proc file system (/proc/sys/fs/file-max) using the following:

# sysctl -w fs.file-max=65536

You should then make this change permanent by inserting the kernel parameter in the /etc/sysctl.conf startup file:

# echo "fs.file-max=65536" >> /etc/sysctl.conf

The soft limit and hard limit of file handles for oracle can be configued inside /etc/security/limits.conf file.

cat >> /etc/security/limits.conf <<EOF
oracle soft mnproc 2047
oracle hard mnproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

nofile is the number of open file handles.
mnproc is hte number of processes available to a single user

Text Index Synchronization and Optimization

Oracle Text is a core component of iFS, Ultra Search, and XML DB. The indexing and searching abilities of Oracle Text are full-text retrieval against virtually any datatype (including all LOB types). They are not restricted to data stored in the database. It can index and search documents stored on the file system and index more than 150 document types, including a search on widget and find documents that contain the work gadget.

How to apply Oracle Text and Text Index can be found here:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54text.html

The Oracle Text indexes must be periodically synchronized so that new data is included in the indexes. This process can be scheduled to run on a periodic basis for Oracle 9i. The Procedure 'CTX_DDL.SYNC_INDEX' is used to synchronize text index. Oracle 10g supports an improved method of index synchronization over Oracle 9i. Oracle 10g has the ability to synchronize the Text indexes as data is saved. This method does not require background jobs to be configured.

In systems with frequent record modification or creation, the Text indexes will become fragmented over time. This fragmentation will lead to slow performance during index synchronization, saving data, and searching. The Procedure 'CTX_DDL.OPTIMIZE_INDEX' should be used to optimize the Text indexes periodically.

Text index types: CONTEXT, CTXCAT, or CTXRULE.

To make sure which indexes are Text indexes, log into user account and issue the following command:
SELECT idx_name
FROM ctxsys.ctx_indexes
WHERE idx_owner='';

Text Packages and associated information can be found in the Oracle official document 'Oracle Text Reference'.

Sql Tuning Advisor

Sql Tuning Advisor (STA) is a new feature introduced in Oracle 10g. This automates the entire SQL tuning process.

Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script

1. STA Through EM
The user must have been granted the SELECT_CATALOG_ROLE role.
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.

2. DBMS_SQLTUNE package
To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
Example can be found in metalink Doc 262687.1.

3. sqltrpt.sql script
Starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line.

Tuning 'log file sync' Event

What is a 'log file sync' Wait

At commit time, a process creates a redo record (containing commit opcodes) and copies that redo record into the log buffer. Then, that process signals LGWR to write the contents of log buffer. LGWR writes from the log buffer to the log file and signals user process back completing a commit. A commit is considered successful after the LGWR write is successful. In a nutshell, after posting LGWR to write, user or background processes wait for LGWR to signal back with a 1-second timeout. The user process charges this wait time as a 'log file sync' event.

The Root Causes of 'log file sync' Waits

1.LGWR is unable to complete writes fast enough for one of the following reasons:
a.Disk I/O performance to log files is not good enough.
b.LGWR is starving for CPU resource.
c.Due to memory starvation issues, LGWR can be paged out.
d.LGWR is unable to complete writes fast enough due to file system or Unix buffer cache limitations.
2.LGWR is unable to post the processes fast enough, due to excessive commits.
3.IMU undo/redo threads. With private strands, a process can generate few Megabytes of redo before commiting. LGWR must write the generated redo so far, and processes must wait for 'log file sync' waits, even if the redo generated from other processes is small enough.
4.LGWR is suffering from other database contention such as enqueue waits or latch contention.
5.Various bugs.

Identify the Root Cause

1.First make sure the 'log file sync' event is indeed a major wait event. Compare 'log file sync' wait time with CPU time.
2.Identify and break down LGWR wait events, and query wait events for LGWR.

Find sid for LGWR process.

SQL> SELECT sid 
SQL> FROM v$session
SQL> WHERE type='BACKGROUND' and program like '%LGWR%';

Find wait events for LGWR.

SQL> SELECT event, time_waited, time_waited_micro
SQL> FROM v$session_event
SQL> WHERE sid=
SQL> ORDER BY time_waited;

When LGWR is waiting for posts from the user sessions, that wait time is accounted as an 'rdbms ipc message' event. Normally, this event can be ignored.
It is worth noting that v$session_wait is a cummulative counter from the instance startup, and hence, can be misleading.

Get Table/View/Index Definition and Indexed Columns

1. Get View Definition

SQL> SELECT test 
SQL> FROM dba_views
SQL> WHERE owner='' AND view_name='';

2. Get Table Definition

SQL> SET PAGESIZE 0
SQL> SET LONG 100000
SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','','') FROM dual;

3. Get Index Definition

SQL> SET PAGESIZE 0
SQL> SET LONG 100000
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','','') FROM dual;

If you are only interested in the indexed column names, simply query COLUMN_NAME of table DBA_IND_COLUMNS.

SQL> SELECT column_name
SQL> FROM dba_ind_columns
SQL> WHERE index_owner='' AND index_name='';

Password Verification in Oracle

The default values for Oracle password security is very weak and special measures must be taken to strengthen Oracle password security. You can get an idea about scripting Oracle password security profiles by examing Oracle's utlpwdmg.sql script located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

To make password complex as you like, the following procedure should be followed:
1) Create a password verification function.
2) Assign this verification function to your desired profile or system default profile.
3) Assign this profile to the users to whom you want to impose rules.

Inside utlpwdmg.sql script, there is a sample PL/SQL script by which we can impose password complexity.

Securing Listener through a Password

Oracle 9i

When the listener is first created, there is no password by default. The password can be set by two methods: LSNRCTL comand and LISTENER.ORA parameter file.

1. LSNRCTL command

LSNRCTL> change_passord

2. LISTENER.ORA parameter file

passwords_listener =


If you save the change using save_config command, the first method will leave the encrypted password in the parameter file. The second method will always leave the unencrypted password in the parameter file. Once the passwords are changed, you have to supply the password before doing any administrative work. The password must be supplied differently depending on how the password is changed. If the password is changed through LSNRCTL command method, you must supply it like this:

LSNRCTL> set password
Password:
The command completed successfully

If the password is change through LISTENER.ORA parameter file, you must supply it like this:

LSNRCTL> set password

Otherwise, you will get this error:
TNS-01169: The listener has not recognized the password

However, when the password is changed through LSNRCTL command if it is already explicitly specified (unencrypted) in the LISTENER.ORA parameter file, the operation will never be successful. To change the password, you have to remove the line passwords_listener in the parameter file and then change the password.

Use LSNRCTL STATUS command to check if the password is set. If the 'Secrity' setting is shown as ON, then the password is set for the listener.

Reference:
http://www.rampant-books.com/art_nanda_dbazine_securing_listener.htm

Data Pump Export Data Consistency

How Data Pump Export Parameters Map to Those of the Original Export Utility

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref181

CONSISTENT parameter is obsoleted in Data Pump. By default, it will not be consistent export. But you can use FLASHBACK_SCN and FLASHBACK_TIME to keep export consistent. Check Oracle document on this parameters.

But Data Pump has bug #5472500. Even those parameters are be used, it can't guarantee consistency in some rare cases. It happens: when a table has rows at the SCN picked for the consistent export, but does not have rows when Data Pump is actually invoked, that tables's dump will not be consistent because it will get exported with zero rows. This is a test post for it:

http://forums.oracle.com/forums/thread.jspa?threadID=623510

Since it's rare cases, we can record the row number for each table before export and the row number after export and make a comparison. It they are the same, the export will be consistent.

FTP: 426 Connection closed; transfer aborted

Referenced from here:
http://www.chilkatsoft.com/p/p_182.asp

If you get this error when doing FTP transfers in passive mode, the problem could be that there are multiple IP addresses on the external interface. (You may alternatively see the error "425 Can’t open data connection.".

A Microsoft knowledge base article explains it: http://support.microsoft.com/kb/817829

"When the client establishes a passive mode FTP connection to an FTP server, the client must establish both connections. The FTP server returns the TCP port used for the FTP data connection. The FTP server expects this FTP data connection to come from the same IP address as the FTP control channel. If the connection comes from a different IP address, the FTP server may reject or close the connection because this may be a passive mode port theft attempt.

When ISA has more than one IP address bound to the external interface, the source IP address for the FTP data connection may not be the primary IP address. Instead, the source IP address may be one of the secondary IP addresses bound to the external interface. With the hotfix installed, ISA always uses the primary IP address for both types of connections."

The solution: "You must install ISA Server Service Pack 1 (SP1) before you install the following hotfix."

Prepare for EXP/IMP Using Data Pump

1. Create directory object in source and target database.

SQL> CREATE DIRECTORY directory_name as 'directory_path';

2. Create user account for source and target database.

SQL> CREATE USER user IDENTIFIED BY pwd
SQL> DEFAULT TABLESPACE users QUOTA unlimited ON users
SQL> TEMPORARY TABLESPACE temp;

3. Grant privileges to the user to exp/imp.

For source database:

SQL> GRANT READ, WRITE ON DIRECTORY directory_name TO user;
SQL> GRANT create session TO user;
SQL> GRANT create table TO user;
SQL> GRANT exp_full_database TO user;

For target database:

SQL> GRANT READ, WRITE ON DIRECTORY directory_name TO user;
SQL> GRANT create session TO user;
SQL> GRANT create table TO user;
SQL> GRANT imp_full_database TO user;

Note: If a user with the EXP_FULL_DATABASE role is exporting a table that is owned by a different schema and that table has as SYS.XMLTYPE column, then the Export DataPump job will fail with ORA-31693 and ORA-01031. The solution is to explicitly grant the FLASHBACK privilege to the schema that runs the Export DataPump job. The privilege can be granted on specific objects (privilege: FLASHBACK), or on all tables (privilege: FLASHBACK ANY TABLE).

How to Check Character Set in Oracle

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Kill Session in RAC Database

You may get ORA-01940 even after you make sure that no user is connected by checking v$session.

If you are operating on RAC database, you probably should check gv$session and kill session on different instance. What Oracle will do is check sessions on the current instance you are connecting. So you will find nothing active there.

After you find the user connecting on another insntance through gv$session, you can kill that session by connecting to that instance if you are in versions before 11.
For Oracle 11, you can kill session in any instance by using this command:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial#, @instance_id';

Recover Database Using Backup Controlfile Until Cancel

Referenced from here:
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1248488,00.html

If you were to recover the database with a current control file, Oralce will know the last SCN of the last checkpoint. So Oracle can use the information in the archived and online redo logs to apply recovery up to that SCN. This is called a "complete" recovery.

If you do not have the current control file, your option is to use a backup controlfile. Which means Oracle does not know the SCN to stop applying recovery. So you tell Oracle that you are using a "backup controlfile" and that you will tell it when to stop applying redo by replying "cancel." When Oracle starts recovery, it looks at the datafiles to know the last time a checkpoint was performed on the datafile. Oracle now knows to start applying recovery to the datafile for all SCNs after the SCN in the datafile header. Oracle rolls forward transactions. But Oracle does not know when to stop, and eventually, Oracle applies recovery in all of your archived redo logs. You can then tell Oracle to use the redo in the online redo logs. Oracle will ask you where to find more redo. At this point, you tell it to quit applying redo by replying CANCEL.

Hopefully, you never have to use this command. This is because you multiplex your control files and have three copies of the control file, all on different disk units. So if you lose a disk unit, you use the control files on the other disk units. The only time you need this command is when you lose all of your control files. In this case, either use a binary backup of the control file or you re-create the control file with the CREATE CONTROLFILE command and then recover with the backup control file.

Oracle Text

Introduction to Oracle Text can be found at Oracle documentation website named 'Text Reference' for different versions.

Oracle Text installation should be completed during the Oracle Installtion using OUI.

Using the following procedure to confirm Oracle Text installation:

Login to an account with DBA privileges and confirm that the CTXSYS user exists.

SELECT username FROM dba_users WHERE username='CTXSYS';

If there is no CTXSYS user, then it is not installed. It is best to use OUI to install it.

Doc 275689.1 in metalink is also a good reference for manual installation of Oracle Text 9.2.

Installing Oracle Text for 10gR2:
@$ORACLE_HOME/ctx/admin/catctx.sql ctxsys DRSYS TEMP NOLOCK
where ctxsys is the install schema, DRSYS is the default tablespace, Temp is well - temp tablespace for that user, and NOLOCK instructs the script to not lock the account when the install is complete (which by the way, you should lock).

If DRG-10700 comes up, check doc 107626.1 in metalink.

A good post for Oracle Text Indexing is here:
http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php

Check Which Port is Listening or Open on Linux

1. cat /etc/services | grep port#
2. netstat -nan | grep port#
3. lsof -i -n -P | grep port#
4. nmap -sS -O ip
5. telnet ip port#

Designated PFILE in Registry on Windows

On windows platform, sometimes there is an entry inside registry to specify which pfile to start oracle. The entry is located at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ORA__PFILE. If a pfile is specified, oracle will use this file to start the instance.

If you want to start oracle instance using spfile, there are workarounds for this problem.
1.You can remove the pfile entry ORA__PFILE from the registry.
2.In your init.ora you can add an entry spfile=.

Automating Database Startup and Shutdown on Linux

http://www.oracle-base.com/articles/linux/AutomatingDatabaseStartupAndShutdownOnLinux.php

Unregister Database from Catalog When Database is Already Deleted

The following steps can help unregister database when database is already deleted.

1. Connect to catalog database in SQL*Plus using the user having the catalog schema and find out what databases are in there.

SQL> SELECT db_id FROM db;

2. Connect to catalog database in RMAN using the user having the catalog schema and unregister the database.

$ RMAN CATALOG user/pwd@catalog

If you know which dbid is the database you want to unregister, using this command:

RMAN> RUN {
2> SET DBID 1334531173;
3> UNREGISTER DATABASE;
4> }

If you are not sure which dbid, you can try all the dbid is 'db' table in the first step and RMAN will show you database name and ask you if you want to unregister the database.

9i Agent and 10g Agent on the Same Host

OS: windows 2003
Database: 9.2.0.5

Currently, some problem happened when both 9i Agent and 10g Agent are running on the
same host. The database is configured in both OEM 9i and Grid Control 10g. The database
was originally monitored by OEM 9i. There was a backup job schedule inside OEM 9i.
After installing agent 10g and configuring the database under Grid Control 10g, the
new backup job inside Grid Control is fine. But the backup job inside OEM 9i stays
'started' status while the job is actually successfully executed. Rescheduling job in
OEM 9i can not always resolve the problem. The resule is random.

No idea about the reason!

RMAN 9i Fails selecting from v$session_longops

Oracle Database: 9.2.0.5

Error:
RMAN-10006: error running SQL statement: select sofar, context, start_time
from v$session_longops where (start_time > nvl(:1, sysdate-100) or
start_time = nvl(:2, sysdate+100)) and sid = :3 and serial#
= :4 and opname like 'RMAN:%' order by start_time desc, context
desc
RMAN-10002: ORACLE : ORA-01041: internal error. hostdef extension doesn't exist

Cause:
It is possible for longop statistics to be corrupted and possibly
cause internal errors or core dumps. This problem can also result in NULL values in V$SESSION_LONGOPS.

Solution:
1) Restart instance may solve the problem.
2) Bug 3123232 is fixed in 9.2.0.6 so apply 9206 PSR.
3) Use the workaround:
Add to the init.ora/spfile of the target database. Switch off long-op statistics by setting the following (9.2 specific) settings in the (s)pfile:

optimizer_features_enable=9.2.0.1
_subquery_pruning_mv_enabled=false

Instance will have to be restarted.

RMAN-20242 While Trying To Restore ArchiveLogs on RAC

Oracle DB RAC: 10.2.0.2

On RAC database, when you want to restore archivelog files from backup using RMAN,
always remember to specify which thread you want to restore. Otherwise, you will have
the following error:

RMAN-20242: specification does not match any archive log in the recovery catalog

For example, if you want to restore archivelog logseq=9455 for thread 2, then insread of using

RMAN> resoter archivelog logseq=9455;

you shold use

RMAN> resoter archivelog logseq=9455 thread 2;

Unsecure AS Control

OAS 10.1.2.0.2

1. Stop the AS Control

$ emctl stop iasconsole

2. Update AS Control's standalone OC4J container to support unsecure port in $ORACLE_HOME/sysman/j2ee/config/emd-web-site.xml.

Change the following line:

<web-site host="ALL" port="<port>" display-name="Oracle Enterprise Manager iAS Console Website" secure="true">

Into:

<web-site host="ALL" port="<port>" display-name="Oracle Enterprise Manager iAS Console Website" secure="false">

3. In the same file (emd-web-site.xml), you also need to modify the ssl-config section at the bottom of the file.

Change the following line:

<ssl-config needs-client-auth="true" keystore="server/keystore.test" keystore-password="%EMD_KEYSTORE_PASSWORD%" />

Into:

<ssl-config needs-client-auth="false" keystore="server/keystore.test" keystore-password="%EMD_KEYSTORE_PASSWORD%" />

4. Update AS Control Local Management Agent to support the unsecure HTTP protocol.
The EMD_URL property in $ORACLE_HOME/sysman/config/emd.properties needs to get switched back to the HTTP (non-secure) protocol.

Change:

EMD_URL=https://<hostname>:<port>/emd/main

Into:

EMD_URL=http://<hostname>:<port>/emd/main

5. Update the standAloneConsoleURL property of oracle_ias target. Edit the file $ORACLE_HOME/sysman/emd/targets.xml to change the StandaloneConsoleURL property of oracle_ias target to switch back to the HTTP (non-secure) protocol.

Change the following lines:

<Target TYPE="oracle_ias" NAME="EnterpriseManager.<hostname>" ...> ... <Property NAME="StandaloneConsoleURL" VALUE="https://<hostname>:<port>/emd/console"/>

Into:

<Target TYPE="oracle_ias" NAME="EnterpriseManager.<hostname>" ...> ... <Property NAME="StandaloneConsoleURL" VALUE="http://<hostname>:<port>/emd/console"/>

6. Start the AS Control Application:

$ emctl start iasconsole

Configuring Database for RMAN Operations

1. Create RMAN user with sysdba privilege and default tablespace.

SQL> CRAETE USER <user> IDENTIFIED BY <password>
SQL> DEFAULT TABLESPACE users;
SQL> GRANT sysdba TO <user>;

2. Create password file so that RMAN user can log in from remote via Oracle Net.
3.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='exclusive';

4. Set CONTROL_FILE_RECORD_KEEP_TIME Parameter to a value no less than your selected database backup retention period.
5. Configure RMAN default settings.

Some Notes about Password File and OS Authentication

1. In order to let remote user login database as sysdba, password file needs to be
created and REMOTE_LOGIN_PASSWORDFILE must be set to a value that is not NONE.

2. In 9i, optional values for REMOTE_LOGIN_PASSWORDFILE are NONE, SHARED and EXCLUSIVE. In 10g, EXCLUSIVE still works for backwards compatibility, but now it behaves just like SHARED. This is the quote from the documentation:

9i
# NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.
# SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.
# EXCLUSIVE
The password file can be used by only one database and the password file can contain names other than SYS.

10g
# NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.
# SHARED
One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.

3. "GRANT sysdba TO user" will add an entry (username and password) into the password file, so that remote user can log into database as sysdba. Remote users in the password file can be found in V$pwfile_users. Oracle checks the password
in password file to verify remote user's identity. Everytime user's password is set,
new password will be added into password file and substitute the old password.

4. ANYONE in the dba group (whatever group you used upon install) is permited to
connect locally "as sysdba".

5. OS authentication allows Oracle to pass control of user authentication to the operating system. Non-priviliged OS authentication connections take the following form.

sqlplus /
sqlplus /@service

Use the following command to enable OS authentication for a user.
CREATE USER ops$username IDENTIFIED EXTERNALLY;
*ops$ can be check by "SHOW PARAMETER os_authent_prefix"

Refer to "Problem to connect as SYSDBA" on asktom.oracle.com for detail explanation.

Changing Hostname, Domain Name and ias_admin password for Oracle Application Server

Application Server: 10.1.2.0.2
OS: Windows Server 2003 R2

Recently, I took over an Oracle application server machine which was cloned from a VM
machine. To set it up, I need to change the hostname, domain name and ias_admin
password since I have no credentials for administration.

Refer to "Oracle Application Server Admininstrator's Guide 10g Release 2"->Chapter 8
Changing Network Configurations->Changing the Hostname, Domain Name, or IP Address
for how to change hostname and domain name.

After that, application server was running and I can access to application server
control page. Since I don't have password for ias_admin, I can't log into this page.
I need to update the password using command line.

Refer to the following steps for updaing ias_admin password using command line:
1. Log in as the user who installed the Oracle Application Server instance
2. Stop the Application Server Control.
On UNIX systems, enter the following command:
ORACLE_HOME/bin/emctl stop iasconsole
On Windows systems, use the Services control panel to stop the Application Server Control
service.
3. Locate and open the following file in a text editor:
ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
4. Locate the line that defines the credentials property for the ias_admin user.
The section is located in tag name->users->user->credentials
5. Replace the existing encrypted password with the new password.
Be sure to prefix the password with an exclamation point (!). For example:
!mynewpassword123
See Also:
"The ias_admin User and Restrictions on its Password" in the Oracle Application
Server 10g Installation Guide
6. Start the Application Server Control.
After the restart, the Application Server Control will use your new Administrator
(ias_admin) password, which will be stored in encrypted format within the
jazn-data.xml file.

Keep an Eye on CONTROL_FILE_RECORD_KEEP_TIME

Oracle Database: 10.2.0.2

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable
record in the control file can be used. In the event a new record needs to be added to a reusable section and the oldest record not aged enough, the record sectione expands.

You don't have to worry about this parameter if you use recovery catalog. Otherwise,
it is suggested that the DBA set this parameter to a value no less than the database
backup retention period.

This is the problem I met due to inappropriate settings.
The parameter is set to default (7 days), and the retention for backup is set to 8
days. During backup procedure, old archivelog records can not be found in controlfile
because the space is reused, but the archivelog files are still on disk. Everytime
the job is supposed to delete the files older than 8 days, it didn't because the
controlfile have no records for those fils. So, after a couple of days, the space for
archivelog is filled up.