Oracle Database Link

This is how I create a database link or dblink in Oracle. Using the following format, be sure to use the host name or use an IP address of the desired database server. This is the best way to insure it will connect. You need to update the text in RED to your specific environment.

CREATE PUBLIC DATABASE LINK NAME.DOMAIN.COM
CONNECT TO USERID
IDENTIFIED BY PASSWORD
USING '(description=(address=(protocol=TCP)
(host=hostdb_server.com)(port=1521))
(connect_data=(sid=SID)))'
/

The domain.com should match your db_domain parameter and is required if this parameter is set.
SQL> show parameters db_domain
NAME TYPE VALUE
------------- --------- ------------
db_domain string domain.com

Disassociating Physical Standby Database from the Primary Database

I will be getting a new database server to use as a physical standby. My production database currently has an existing physical standby database associated with it. I will turn my current standby database into a test standby instance before configuring a new standby for the production instance.

These are the steps to disassociating a physical standby database from the Primary Database:

Login to the production instance.

sqlplus
SQL> create pfile from spfile;
SQL> quit

-- save original Standby configuration init file
$ORACLE_HOME/dbs/cp initSID.ora initSID.date

sqlplus
SQL> alter system set log_archive_dest_state_2=DEFER scope=both;
SQL> alter system set log_archive_dest_2='LOCATION=NULL' scope=both;
SQL> alter system set log_archive_config='NODG_CONFIG' scope=both;
SQL> alter system set fal_client=NULL scope=both;
SQL> alter system set fal_server=NULL scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/arch' scope=both;

SQL> alter system switch logfile;

SQL> create pfile from spfile;

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> shutdown immediate

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

ORA-32017: failure in updating SPFILE

ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

Modifying log_archive_dest_n will return this error due to invalid syntax. This parameter is dynamic and can be updated using the ALTER SYSTEM statement.

Here is an example of an error raised when trying up modify the parameter log_archive_dest_2 to NULL.

SQL> alter system set log_archive_dest_2=NULL scope=both;
alter system set log_archive_dest_2=NULL
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

To update this parameter to NULL or to a valid location use the following syntax:
SQL> alter system set log_archive_dest_2='LOCATION=NULL' scope=both;
or
SQL> alter system set log_archive_dest_2='LOCATION=/u02/oradata/arch' scope=both;

Creating an NFS mount for use with RMAN

I needed more disk space on my database server for exports and RMAN backups (possible Flashback space). My colleague has an available Network File System (NFS), as he calls it, Network-attached storage (NAS). I have been given a few terabytes for use with my database. I will mount this same NAS on my production and development database servers for use with exports and RMAN backups.

Once the share is available:
1. Edit /etc/vfstab and add the mount.
Adding mount to vfstab

2. Add the directory to the server.
#cd /
#mkdir oracle_data

3. #mount -a

4. #chown oracle:oinstall oracle_data

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

RMAN error

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2

I am using an NFS share for RMAN backups. After mounting the file system and trying testing a backup using RMAN I received the ORA-27054 error. This is due to the fact that mount was not established correctly. The following is the correct syntax for mounting NFS for RMAN to utilize. Since I am mounting two server versions of Solaris; 5.8 and 5.10 to the same NFS share.

Oracle 10gR2 on UNIX,

For SUN Solaris 5.8 sparc 64 or Solaris 5.9
mount rw,bg,intr, hard,timeo=600, wsize=32768, rsize=32768

Actual entry (all one line) in the vfstab file (where bigshare is the server):
bigshare:/export/oracle - /oracle_backups nfs - yes rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768

For Solaris 5.10 use
mount -F nfs -o hard, bg,proto=tcp, vers=3, suid, rsize=32768, wsize=32768, intr,noac

Actual entry (all one line) in the vfstab file (where bigshare is the server):
bigshare:/export/oracle - /oracle_backups nfs - yes hard, bg,proto=tcp, vers=3, suid, rsize=32768, wsize=32768, intr,noac

Indexing - Using functions in the WHERE Clause

Using a function on an indexed column in the WHERE clause will force the optimizer to bypass the desired index (does not apply to function-based indexes).
Common functions used in the WHERE clause are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR.
The one I am guilty of is using TRUNC on a date column, as in (assuming there is an index on insert_date):

WHERE TRUNC(insert_date) = TRUNC(sysdate);
instead use something like:
WHERE insert_date >= TRUNC(sysdate) and insert_date <>
By taking the function TRUNC off of the indexed column, insert_date, the index on insert_date will be used.

Example:
SQL> set timing on

On our first run the data has not been cached and it take 5 seconds.
SQL> select count(last_updated) "Total" from barcodes where trunc(last_updated) > trunc(sysdate-60);

Total
-------------------
2114
Elapsed: 00:00:05.10

Same SQL but the data now in cache it takes 2.5 seconds.
SQL> select count(last_updated) "Total" from barcodes where trunc(last_updated) > trunc(sysdate-60);

Total
-------------------
2114
Elapsed: 00:00:02.53

Rewrite query to use the index on last_updated and the query takes 0.0 seconds.
SQL> select count(last_updated) "Total" from barcodes where last_updated > trunc(sysdate)-60 and last_updated <>

Total
-------------------
2114
Elapsed: 00:00:00.00

You can't always assume you will use an index just becuase it is available.

ORA-00354:, ORA-16014:

ORA-00354:
How to recover from
ORA-00354: corrupt redo log block header
also
ORA-00312: online log 1 thread 1: '/u02/oradata/DEV/redo01.log'
ORA-16038: log 1 sequence# 2530 cannot be archived
ORA-00354: corrupt redo log block header
also

SQL> alter database mount;
SQL> alter database open resetlogs;
SQL> alter database open;

ERROR at line 1:
ORA-16014: log 1 sequence# 2530 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u02/oradata/DEV/redo01.log'

I have not tested with the database open. I crashed this instance and could not open due to the corrupted redo log. In mount state issue the following commands for your specific redo logs with corruption.

SQL> alter database clear unarchived logfile '/u02/oradata/DEV/redo01.log';
SQL> alter database clear unarchived logfile '/u03/oradata/DEV/redo02.log';
SQL> alter database clear unarchived logfile '/u04/oradata/DEV/redo03.log';
SQL> alter database open;


I restarted to be sure everything was good.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.