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;