Database Link Survival Guide

Overview

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

• You must have the CREATE DATABASE LINK system privilege.
• You must have the CREATE SESSION system privilege on the remote Oracle database.
• You must be able to connect to the remote database.
• Check GLOBAL_NAMES – to setup the name of the database link.

DB_NAME

The DB_NAME parameter is the SID of the database and is the name used when creating the database. It is specified within the INIT.ORA parameter file or in the CREATE DATABASE command.

select name, value from v$parameter where name = ‘db_name’;

DB_DOMAN

The DB_DOMAIN parameter is the value of the domain to which the database belongs. It can be the same as defined in the DNS – and is usually the company name. However you can set a DB_DOMAIN value which is not part of the DNS.

select name, value from v$parameter where name = ‘db_domain’;

GLOBAL_NAMES

GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are connecting. Usually GLOBAL_NAMES is set to TRUE.

select name, value from v$parameter where name = ‘global_names’;

Setting the Name of the Database Link

You can query GLOBAL_NAME on the remote database to find out how the name of the database name has to be on the local database.

CELLAR> sqlplus system/…@GENTIC

SQL> select global_name from global_name;

GENTIC.COMPANY.COM

So you have to set the database link name to GENTIC.COMPANY.COM if GLOBAL_NAMES is TRUE.

SQLNET

Setup your TNSNAMES.ORA so you can connect to the remote database.

GENTIC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gentic.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GENTIC.COMPANY.COM)
)
)

CELLAR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CELLAR.COMPANY.COM)
)
)

Refer to Remote Table(s)

Suppose you want to refer to the table scott.aTable on GENTIC from CELLAR. On CELLAR the same name scott.aTable should be used – so you don’t have to change any code.

sqlplus system/….@CELLAR

SQL> CREATE DATABASE LINK GENTIC.COMPANY.COM
CONNECT TO scott IDENTIFIED BY …..
USING ‘GENTIC‘;

Database link created.

SQL> DESC scott.aTable@GENTIC.COMPANY.COM;

Name                                      Null?    Type
—————————————– ——– ————–
LANGUAGE                                  NOT NULL VARCHAR2(2)
ACTIVE                                    NOT NULL NUMBER
TEXT                                               VARCHAR2(20)

SQL> SELECT * FROM scott.aTable@GENTIC.COMPANY.COM;

no rows selected

SQL> CREATE SYNONYM scott.aTable FOR scott.aTable@GENTIC.COMPANY.COM;

Synonym created.

SQL> SELECT * FROM scott.aTable;

no rows selected

SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘de’,1,’german’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘en’,1,’english’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘fr’,1,’french’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘it’,1,’italian’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘ru’,0,’russian’);
SQL> COMMIT;

SQL> SELECT * FROM scott.aTable;

LANGUA     ACTIVE TEXT
—— ———- ————————-
de              1 german
en              1 english
fr              1 french
it              1 italian
ru              0 russian

As you can see, accessing the remote table is fully transparent to the application. Exactly the same syntax can be used for the remote table if you create a synonym for the remote table.

Executing Remote Procedures

You can also execute remote procedures …

… here an example without synonym.

sqlplus system/….@CELLAR

SQL> DESC scott.aPackage@GENTIC.COMPANY.COM;

PROCEDURE aProcedure

Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
USER_ID                        NUMBER(10)              IN
A_NAME                         VARCHAR2(32)            IN
A_CONTENT                      VARCHAR2(4000)          IN
A_DEFAULT                      NUMBER(1)               IN

SQL> EXECUTE scott.aPackage.aProcedure@GENTIC.COMPANY.COM(1,'Test','Test',0);

PL/SQL procedure successfully completed.

And now the same with synonyms.

SQL> CREATE SYNONYM scott.aPackage for scott.aPackage@GENTIC.COMPANY.COM;

Synonym created.

SQL> EXECUTE scott.aPackage.aProcedure(1,’Test’,'Test’,0);

PL/SQL procedure successfully completed.

As you can see, executing remote procedures is fully transparent to the application. Exactly the same syntax can be used for the remote package if you create a synonym for the remote package.

Distributed / In-Doubt Transactions

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.

The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if the transaction fails because of a system or network error? The transaction becomes in-doubt.

Distributed transactions can become in-doubt in the following ways:

• A server machine running Oracle software crashes.
• A network connection between two or more Oracle databases involved in distributed processing is disconnected.
• An unhandled software error occurs.

The RECO process automatically resolves in-doubt transactions when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version of the data to display for a query.

Manually Committing an In-Doubt Transaction

Suppose, the database  server GENTIC crashes during a distributed transaction.

Prepare distributed transaction

SQL> CREATE SYNONYM scott.aTable FOR scott.aTable@GENTIC.COMPANY.COM;
SQL> SELECT * from scott.aTable;
SQL> CREATE TABLE scott.bTable (count NUMBER);

Start the distributed transaction

SQL> INSERT INTO scott.bTable (count) values (1);
SQL> INSERT INTO scott.bTable (count) values (2);
SQL> INSERT INTO scott.bTable (count) values (3);
SQL> INSERT INTO scott.bTable (count) values (4);
SQL> INSERT INTO scott.bTable (count) values (5);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘de’,1,’german’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘en’,1,’english’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘fr’,1,’french’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘it’,1,’italian’);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES (‘ru’,0,’russian’);
SQL> UPDATE scott.bTable SET count = 2 WHERE count = 1;

Now shutdown the distributed database

GENTIC> sqlplus / as sysdba;
shutdown abort;

Now try to COMMIT the distributed transaction

SQL> commit;

ERROR at line 1:
ORA-02054: transaction 1.37.5070 in-doubt
ORA-02068: following severe error from GENTIC
ORA-03113: end-of-file on communication channel

Now check the DBA_2PC_PENDING view for the LOCAL_TRAN_ID.

SET LINESIZE 500;
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A50
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
FROM DBA_2PC_PENDING
/

LOCAL_TRAN_ID GLOBAL_TRAN_ID                        STATE    MIX HOST       COMMIT#
————- ————————————- ——– — ———- ———
1.37.5070 CELLAR.COMPANY.COM.a05b1b48.1.37.5070 prepared no  cellar     15508150

Now manually commit or rollback force the in-doubt transaction as user SYS

SQL> exit;

sqlplus / as sysdba

SQL> COMMIT FORCE ‘1.37.5070′;

Commit complete.

Manually remove an entry from the data dictionary

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘1.37.5070′);

PL/SQL procedure successfully completed.

More Information to Distributed Transactions can be found here

Database Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the basic types of links.

Following are examples of private database links

SQL Statement	Result
CREATE DATABASE LINK link_name;	A private link using the global database name to the remote database.The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.
CREATE DATABASE LINK link_name CONNECT TO user IDENTIFIED BY ... USING 'service';	A private fixed user link to the database with service name service. The link connects to the remote database with the userid/password regardless of the connected user.
CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER USING 'service';	A private link to the database with service name service. The link uses the userid/password of the current user to log onto the remote database.

Following are examples of public database links

sdasd

SQL Statement	Result
CREATE PUBLIC DATABASE LINK link_name;	A public link to the remote database. The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.
CREATE PUBLIC DATABASE LINK link_name CONNECT TO CURRENT_USER USING 'service';	A public link to the database with service name service. The link uses the userid/password of the current user to log onto the remote database.
CREATE PUBLIC DATABASE LINK link_name CONNECT TO user IDENTIFIED BY ....;	A public fixed user link. The link connects to the remote database with the userid/password.

 

Automatically start ASM/Database/EM/LSNR services

In theory there is no need to start services as they are bought online by Oracle Clusterware
Shell Filedbora.txt

- Copy dbora at /etc/init.d directory
# cd /etc/init.d
# chmod 755 dbora
# chown root:root dbora
# ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
# ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

- Change oratab entries from “N” to “Y”

+ASM:/u01/app/oracle/product/10.1.0/db_1:Y
TESTDB:/u01/app/oracle/product/10.1.0/db_1:Y

- Manually edit /etc/inittab so that entry to respawn init.cssd comes before running the runlevel 3

Orignal /etc/inittab file: (…)
# System initialization.
l5:5:wait:/etc/rc.d/rc 5
l6:6:wait:/etc/rc.d/rc 6
(…)
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null

Modified /etc/inittab file: (…)
# System initialization.
si::sysinit:/etc/rc.d/rc.sysinit
l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
l3:3:wait:/etc/rc.d/rc 3

# +————————————————————————+
# | FILE : dbora |
# +————————————————————————+

# +———————————+
# | PRINT HEADER INFORMATION |
# +———————————+
echo ” “
echo “+———————————-+”
echo “| Starting Oracle Database Script. |”
echo “| 0 : $0 |”
echo “| 1 : $1 |”
echo “+———————————-+”
echo ” “

# +—————————————————–+
# | ALTER THE FOLLOWING TO REFLECT THIS SERVER SETUP |
# +—————————————————–+

HOSTNAME=wygpreora01.wyg-asp.com
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
SLEEP_TIME=120
ORACLE_OWNER=oracle
DATE=`date “+%m/%d/%Y %H:%M”`

export HOSTNAME ORACLE_HOME SLEEP_TIME ORACLE_OWNER DATE

# +———————————————-+
# | VERIFY THAT ALL NEEDED SCRIPTS ARE AVAILABLE |
# | BEFORE CONTINUING. |
# +———————————————-+
if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]; then
echo ” “
echo “+————————————-+”
echo “| ERROR: |”
echo “| Oracle startup: cannot start |”
echo “| cannot find dbstart |”
echo “+————————————-+”
echo ” “
exit
fi

# +—————————+
# | START/STOP CASE STATEMENT |
# +—————————+
case “$1″ in

start)

echo ” “
echo “+—————————————-+”
echo “| ************************************** |”
echo “| >>>>>>>>> START PROCESS <<<<<<<<<< |”
echo “| ************************************** |”
echo “+—————————————-+”
echo ” “

echo “Going to sleep for $SLEEP_TIME seconds…”
sleep $SLEEP_TIME
echo ” “
su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbstart”

echo ” “
echo “+—————————————————+”
echo “| About to start EM DBCONSOLE |”
echo “| $ORACLE_HOME |”
echo “+—————————————————+”
echo ” “

su – $ORACLE_OWNER -c “emctl start dbconsole”

echo ” “
echo “+—————————————————+”
echo “| About to start the listener process in |”
echo “| $ORACLE_HOME |”
echo “+—————————————————+”
echo ” “

su – $ORACLE_OWNER -c “lsnrctl start listener”

touch /var/lock/subsys/dbora

;;

stop)

echo ” “
echo “+—————————————-+”
echo “| ************************************** |”
echo “| >>>>>>>>>> STOP PROCESS <<<<<<<<<< |”
echo “| ************************************** |”
echo “+—————————————-+”
echo ” “

echo “+—————————————————+”
echo “| About to stop EM DBCONSOLE |”
echo “| $ORACLE_HOME |”
echo “+—————————————————+”
echo ” “

su – $ORACLE_OWNER -c “emctl stop dbconsole”

echo ” “
echo “+——————————————————-+”
echo “| About to stop the listener process in |”
echo “| $ORACLE_HOME |”
echo “+——————————————————-+”
echo ” “
su – $ORACLE_OWNER -c “lsnrctl stop listener”

echo ” “
echo “+——————————————————-+”
echo “| About to stop all Oracle databases |”
echo “| running. |”
echo “+——————————————————-+”
echo ” “

su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbshut”

rm -f /var/lock/subsys/dbora

;;

*)

echo $”Usage: $prog {start|stop}”
exit 1

esac

echo ” “
echo “+———————-+”
echo “| ENDING ORACLE SCRIPT |”
echo “+———————-+”
echo ” “

exit

Flashback Query

Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

Prerequisites

Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments. The maximum time period that can be flashbacked to is defined using the UNDO_RETENTION parameter in the init.ora file. Alternatively, this parameter can be set using:

ALTER SYSTEM SET UNDO_RETENTION = <seconds>;

Using Flashback Query

Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time:

EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time(‘28-AUG-01 11:00:00′);

Once you’ve finished performing all your read-only operations you can turn off flashback query using:

EXECUTE Dbms_Flashback.Disable;

The flashback query is turned off when the session ends if there is no explicit call to the disable procedure. The current system change number can be returned using the Get_System_Change_Number function.
Example Data Recovery
Assuming all data was accidentally deleted from the EMPLOYEES table at 9:05AM we could recover it using:

DECLARE
CURSOR c_emp IS
SELECT *
FROM employees;
v_row c_emp%ROWTYPE;
BEGIN
Dbms_Flashback.Enable_At_Time('28-AUG-01 09:00:00');
OPEN c_emp;
Dbms_Flashback.Disable; 

LOOP
FETCH c_emp INTO v_row;
EXIT WHEN c_emp%NOTFOUND;
INSERT INTO employees VALUES
(v_row.employee_id, v_row.first_name,
v_row.last_name, v_row.email,
v_row.phone_number, v_row.hire_date,
v_row.job_id, v_row.salary,
v_row.commission_pct, v_row.manager_id,
v_row.department_id, v_row.dn);
END LOOP;
CLOSE c_emp;
COMMIT;
END;
/

Notice that the Flashback Query session is disabled after the cursor is created so that the DML operations can be performed to recover the data.

Restrictions

* The server must be configured to use Automatic Undo Management.
* No DDL or DML can be issued when using Flashback Query.
* Flashback Query does not reverse DDL operations such as DROP, only DML alterations to the data.
* Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.

Oracle Undocumented Parameters

The following is a list of undocumented parameters.

1. _db_block_cache_protect
On VMS, the DB_BLOCK_CACHE_PROTECT mechanism has been made much faster. During normal use, having it turned on shouldn’t be noticeable (the degradation is less than 1%). Developers who link non-share will need PSWAPM privilege to use this feature.

When DB_BLOCK_CACHE_PROTECT is turned on, developers may either use the VMS mailboxes with the M (MEMORY_LOG) command  or they may just examine the ring buffer in the PGA (index SMPREI_, array SMPREB_) to determine what buffer requests have been made recently. DB_BLOCK_CACHE_PROTECT will prevent certain corruption from getting to disk; although, it may crash the foreground of the instance. It will help

catch stray writes in the cache. When you try to write past the buffer size in the sga, it will fail first with a stack violation. It seems that the db_block_cache_protect has a significant performance
overhead. Preliminary testing shows that it has considerable overhead (a single update took twice as long with the parameter set to TRUE).

2. _db_block_compute_checksums
There is another new init.ora parameter, DB_BLOCK_COMPUTE_CHECKSUMS, that controls whether a checksum is put into every block before the block is written to disk. The default is FALSE. A block read validates an exiting checksum whether or not this option is enabled. A block is marked
as corrupt if a checksum fails. It helps determine corruption due to hardware problems. The incarnation number and the sequence number are added to the end of the block to help
catch corruption. If the problem (corruption) is in the middle of the block this test will not detect it. To detect this problem a checksum may be generated in the block header before every write and verified on every read.

3. _db_block_hash_buckets= “Number of database block hash buckets”
The number of hash buckets is
a) by default to be set to a prime number;
b) coerced to a prime if there is an init.ora parameter setting.
The value, if not a prime number > 2, is rounded up to the next highest
prime. I would tend not to change it unless there is latch contention on the hash chains. raising it to equal the number of buffers would clearly remove any contention (basically, this is just saying that each buffer lives on its own hash chain). Having it set too small would mean that we might have to
scan over lots of buffers to find the one we want. I think the default is to make it 1/4 of the total number of buffers

4. _db_block_multiple_hashchain_latches “Use one latch per hash chain”

5. _db_handles “System-wide simultaneous buffer operations”

6. _db_handles_cached “Buffer handles cached each process”

7. _wait_for_sync ” Wait for sync on commit “
Wait_for_sync is an oracle generic parameter which, when set to false, will allow the system to complete commits without waiting for the redo-log buffer flushes to complete.

8. _db_block_max_scan_cnt “Maximum number of buffers to inspect when  looking for free buffer”
DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies the number of unavailable buffers a process should scan before signaling DBWR to write dirty buffers from the buffer cache to disk.

9. _db_writer_scan_depth
“Number of LRU buffers for dbwr to scan when looking for dirty buffers”

10a. _db_writer_scan_depth_increment
“Add to dbwr scan depth when dbwr is behind”

10b. _db_writer_scan_depth_decrement
Subtract from dbwr scan depth when dbwr is working too hard

11. _db_large_dirty_queue
“Number of buffers which force dirty queue to be written

12. _db_block_write_batch
Number of blocks to group in each DB Writer IO
specifies the no of blocks to be written to the disk in one write operation. Should be increased till write wait time and write complete waits starts to increase.
DBWR Free Low is the number of times DBWR is invoked because a user process found at least DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list. This parameter specifies the number of blocks which should be written to disk at one time. This parameter should only be increased until the statistics Write Complete Waits and Write Wait Time show growth. Write Complete
Waits is the number of times a process waited for DBWR to write a current block before making a change to a buffer.

13. _db_block_cache_clone
“Always clone data blocks on get (for debugging)”
This parameter setting has a significantly adverse affect on performance
and we were told to run without it.

14. _controlfile_enqueue_timeout
/* use parameter value (default is 900) */
/* System Parameter: control file enqueue timeout in seconds */

15. _db_no_mount_lock
add hidden parameter to not acquire mount lock
If hidden int.ora parameter _db_no_mount_lock is set to TRUE then no mount locks are acquired when the the database is mounted exlusive. This allows two databases with the same name to be simultaneously mounted. Note that this circumvents the mechanism to prevent two simultaneous startups of the same database, and is thus a dangerous parameter to set. This only affects ports that ar compiled with the SYS_DFS option enabled (i.e. VMS only).

It does not get a mount lock on the database name during startup. This allows 2 instances with the same name to run on one machine

16. _log_checkpoint_recovery_check
Check redo log after checkpoints.
Add debugging code to check the red log after a checkpoint. This code is intended to help find a problm that is generating ora-600 [3020] during recovery. This code is enabed with a new init.ora parameter:
_log_checkpoint_recovery_check=XXX, where XXX is the number of redo blocks to check. This is called in LGWR after every checkpoint. If the init.ora parameter “_log_checkpoint_recovery_check” is zero (default) it does nothing. If it is a positive value then that many blocks of redo are scanned to see that the data file blocks on disk could be recovered if there was an immediate crash. This code was introduced to catch an elusive bug that results in OERI(3020) errors occasionally during crash recovery.

17. _switch_on_stuck_recovery
Check redo Log contents after checkpoint. This is called in LGWR after every checkpoint. If this parameter is zero (default) it does nothing. If it is a positive value then that many blocks of redo are scanned to see that the data file blocks on disk could be recovered if there was an immediate crash. This code was introduced to catch an elusive bug that results in OERI(3020) errors occasionally during crash recovery. Checkpoint recovery check: this is the number of redo blocks that kcracl will verify after every LGWR generated checkpoint. Defaults to zero for no checking. When opening the named offline log for redo application and to recalculate future change thread switching this parameter is used.

18. _log_io_size=redo log IO chunk size (blocks/write)
/* System Parameter: IO chunk size */
1. that the value is o/s dependent.
2. if left at 0, the value will be automatically determined for each log
file.

19. _log_buffers_debug
/* debugging: fill redo buffers with [well known] junk after writes */
“debug redo buffers (slows things down)”

20. _log_debug_multi_instance
/* debugging : pretend multi-instance */
“debug redo multi instance code”

21. _log_entry_prebuild_threshold
/* redo entry pre-build threshold */
/* this is a bad idea for a uniprocessor , and is only helpful for a
multiprocessor when there is latch contention */
LOG_ENTRY_PREBUILD_THRESHOLD determines the maximum size of a redo entry to prebuild before the copy to the log buffer. Increasing this parameter reduces the time that the redo copy latch is held. This parameter should not be modified if it is a single processor environment or there will be memory contention.

22. _disable_logging
If this is true, redo records will not be generated — no recovery is possible if the instance crashes. It is mainly used for getting good benchmarking  results. Default is false

23. _log_blocks_during_backup
TRUE value implies before any change is made to a db_block in the buffer cache, a *complete image* of the block is copied to the redo redo log. (This explains why excessive redo would be generated for datafiles excessive redo would be generated for datafiles in hot backup mode.) There is a new init.ora parameter, LOG_BLOCKS_DURING_BACKUP, that controls whether block images ar written to the redo log during hot backup.
Default is TRUE for VM, but port specific with the default defined in sparams.h. This may beset to FALSE if the Oracle block size equals the O/S physical sector sie or if it is otherwise ensured that hot backup reads consistent versios of blocks even if those blocks are being written at the time. Put anther way, this may be set to FALSE on machines that can guarantee the aomicity of a single block I/O request.
Default is true Problem is due to split blocks.

24. _allow_resetlogs_corruption
Added new secret init.ora parameter to override error 1138.  When set to TRUE the  resetlogs option will be allowed even if there are hot backups that need  more redo applied. Unless you are certain that absolutely all redo, includig  the online logs, has been applied, then a full export and import mst be done to insure the database is internally consistant.

25. _reuse_index_loop
“number of blocks being examine for index block reuse”
/* secret system parameter to control how agressive we should walk the free
** list when attempting to reuse block – default is 5.
** Set to 0 for fast index operation which is susceptible to growth,
** Set to > 5 for slower index op but more agressive in reusing blocks */ Controls the amount of work done when looking for a block to reusse for n index entry. The value determines the number of blocks to check on the freelist when looking for a reusable block.

26. _mts_load_constants
/* hidden init.ora to set server load balancing constants */
/* fill in load balancing parameters (from _mts_load_constants) */
* PID Controller – calculate control on number of servers using:
* control = Kp * err + Kd * delta(err) + Ki * sum(err)
* where Kp = proportional, Kd = derivative constant, Ki = integral constant
* Kp,Kd,Ki can be changed with the hidden parameter _mts_load_constants
* in order to tune the system as desired.
This values should only be changed after gathering enough information to determine that the mts is not optimal.

27. _mts_fastpath
/* hidden init.ora to enable dispatcher fastpath */
default is false
* Return TRUE if the system should implement the dispatcher network
* fastpath. When enabled, the dispatcher fastpath causes network i/o
* to be buffered and only flushed when absolutely necessary. When not
* enabled, flushes will occur for every dirty virtual circuit buffer.

28. _kgl_multi_instance_lock
Only for debugging. all the _kgl_multi_instance_xxxx
“whether KGL to support multi-instance locks”
Default is 0

29. _kgl_multi_instance_pin
“whether KGL to support multi-instance pins”
Default is 0.

30. _kgl_multi_instance_invalidation
“whether KGL to support multi-instance invalidations”
Default is 0.

31. _row_cache_instance_locks
Kernel SQL Row cache management component, number of row cache instance
locks
default is 100

32. _row_cache_buffer_size
“size of row cache circular buffer”
default is 200

33. _messages
” message queue resources – dependent on # processes “
The asynchronous message mechanism allows processes to send messages to each other. A process may send a message to a specified other process (always without waiting for a reply), may wait for a message to arrive on its queue, and may obtain the next message. Messages sent to detached processes are reliably delivered. Messages sent to foreground processes are reliably delivered as long as the process is active. The mechanism also permits sending of a simple “reply”, which is a one-byte message without queuing. It should normally be used to reply to asynchronous messages, and this is a safer technique than regular messages for responding to foreground processes. This mechanism is not used in single process mode.

34. _cpu_count
ksb – Kernel Service Background processes
“number of cpu’s for this instance”
CPU_COUNT has to be set on some platforms in order for Oracle to take advantage of multi-processor system, on others it does not have effect on performance since load balancing between processors is handled by the o/s.

35. _debug_sga
/* Debug SGA, don’t make the SGA a global section so we can set watchpoints

36. _enqueue_locks
ksq1 – Kernal Service enQueues (1)
Definitions for enqueues client objects, “locks for managed enqueues”

37. _enqueue_hash
“enqueue hash table length”

38. _enqueue_debug_multi_instance
“debug enqueue multi instance”
KST is a trace facility used for “realtime” tracing of events. Below are defined KST macros that will enable the tracing of such things as latch operations, memory assignments, etc. Tracing is done to a per process circular buffer stored in the SGA. Access to these buffers is via fixed tables. Tracing is enabled for classes of events, particular events, and ranges of events.

The tracing state may be dynamically changed with the following syntax
“ALTER TRACING”
- “ON”
- Processes begin logging events using the current enabled events
- “OFF”
- Processes stop tracing
- “ENABLE”
- Add in the events indicated in to those which are
being traced.
- “DISABLE”
- No longer trace events specified by

39._trace_buffers_per_process
Note that by default, tracing is NOT enabled. In order to enable tracing
at instance startup time, add _trace_buffers_per_process = 1

40. _trace_block_size
_trace_xxxxx (xxxxx = buffers_per_process, block_size, archive_start,
flushing, enabled, get_time_every, archive_dest etc.)
These parameters are only there for debugging purposes. Customers
or support will never have to use them.

41. _trace_archive_start
“start trace process on SGA initialization”

42. _trace_flushing
“TRWR should try to keep tracing buffers clean”

43. _trace_enabled
By default, tracing is NOT enabled. In order to enable tracing,
_trace_enabled = true

44. _trace_events
If you wish to enable tracing of waits at instance startup time, you can  either
add the line ‘_trace_events = “WAT,ALL”‘ to init.ora or execute  ‘alter tracing enable “WAT,ALL”‘ in a sqldba session.
If you wish to limit tracing to specific events, you can use the  the following syntax:
alter tracing enable “WAT,,…”  where “id” is either a specific event number, or an event range (event number 1 – event number 2).

45. _trace_archive_dest “trace archival destination”

46. _trace_file_size “trace file size”  default is 10000 blocks

47. _trace_write_batch_size “trace write batch size”  default is 32

48. _rollback_segment_initial “starting undo segment number”
Default is 1. DO NOT SPECIFY 0 AS THAT HAPPENS TO BE THE SYSTEM ROLLBACK

49. _rollback_segment_count “number of undo segments”  default is 0

50. _offline_rollback_segments
If a rollback segment is not accessible because the file it is in is offline or corrupted, one can force the system to come up without the rollback segment by specifying the rollback segment in init.ora
paramater ‘_offline_rollback_segments’. The system will come up by estimating the current system commit time since it cannot access transaction tble in the rollback segment. The system commit number is a conservative guess based on current time, the database creation time and the assumed transaction rate of 8000 tps. ONE MUST MAKE SURE THAT THE SYSTEM TIME IS SET CORRECTLY WHEN FORCING THE SYSTEM UP USING ‘_OFFLINE_ROLLBACK_SEGENTS’. A trace is written with information
about the estimated system commit number.

51. _corrupted_rollback_segments Mark a rollback segment as corrupted.

52. _label_tag_cache_size
/* hidden size of the SGA label tag comparison cache (bitcache) */
“number of tags in the label tag comparison cache”
default is 200

53. _trace_get_time_every
“Number of trace sequence numbers per call to slgcs()”
default is 200

54. _vms_bg_priority
“VMS process priority to give to background processes”
default is 4

55. _sort_use_os_files_as_temporaries
Use O/S files rather than temp segments for sorting.

56. _log_checkpoints_to_alert
Whether checkpoint messages should be written to alert.log or not. Turned
off in benchmarks.

57. _large_memory_system :
Used in internal benchmarks. Doesn’t concern us.
“Configure system to use memory and save cpu cycles, paging, etc
default is false

58. _wakeup_timeout
This is WMONs sleeptime between checks of it’s queue of processes to wake.

59. _latch_wait_posting
enable posting of processes that may be waiting for a latch after a process frees the same latch (set this parameter to a value greater than one this parameter to a value greater than one for it to take effect).

60. _sql_connect_capability_code
allows database links from a pre-7.0.15 release to work with release 7.1. It is necessary to set this parameter for database links from a V6 ?

Displays partition information for specified index

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF

SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM   dba_ind_partitions a
WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,'ALL’,a.index_name,’BRANCH_STATS_IX’)
ORDER BY a.index_name, a.partition_name;

Replace ‘BRANCH_STATS_IX’ with valid index_name

---

select ‘Alter Index ‘|| index_owner ||’.'||index_name ||’ Rebuild Partition ‘ || partition_name ||’  Online; ‘ from dba_ind_partitions a WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,'ALL’,a.index_name,’BRANCH_STATS_IX’) ORDER BY a.index_name, a.partition_name

Will create script like …
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060201 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060202 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060203 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060204 Online;

How to rebuild corrupt partitions…

Problem : Oracle reported block corruption on data file id 21,22 mapped for ABC,XYZ tablespaces.

Get list of all partitions mapped into corrupt tablespaces

SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.tablespace_name in ('ABC','XYZ')
ORDER BY a.index_name, a.partition_name

Get a script to rebuild all these partitions
SELECT ‘Alter Index ‘|| index_owner ||’.'||index_name ||’ Rebuild Partition ‘ || partition_name ||’ Online; ‘
FROM dba_ind_partitions a
WHERE a.tablespace_name in (‘ABC,’XYZ)
ORDER BY a.index_name, a.partition_name

;

Kill DBMS job before database shutdown

A long running dbms job will delay shutdown immediate unless you opt for abort. It’s better to kill all running jobs before initiating a “shutdown immediate”.

SQL> select /*+ RULE */ sid,job from dba_jobs_running where job=13;

SID        JOB
———- ———-
279        13

SQL> select sid,serial# from v$session where sid=279;

SID    SERIAL#
———- ———-
279      28683

SQL>  alter system kill session ‘279,28683′;

System altered.

SQL> select sid,serial# from v$session where sid=279;

no rows selected

SQL> select /*+ RULE */ sid,job from dba_jobs_running where job=13;

no rows selected

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

View which Job is currently running

SQL> select /*+ rule */ sid,job,this_date,this_sec  from dba_jobs_running
SID        JOB THIS_DATE THIS_SEC
———- ———- ——— ——–
27        173 18-DEC-08 16:44:42
43        172 18-DEC-08 16:44:33

 

SQL> select /*+ rule */ what,last_date,last_sec,this_date,this_sec from dba_jobs where job in (select /*+ rule */job from dba_jobs_running);

WHAT
——————————————————————————–
declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>’repA’, stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end;

ASM Command

Here following the summary of ASM Command :

cd Changes the current directory to the specified directory.

du Displays the total disk space occupied by ASM files in the specified ASM directory and all its subdirectories, recursively.

exit Exits ASMCMD.

find Lists the paths of all occurrences of the specified name (with wildcards) under the specified directory.
ASMCMD> find +dgroup1 undo* +dgroup1/SAMPLE/DATAFILE/UNDOTBS1.258.555341963 +dgroup1/SAMPLE/DATAFILE/UNDOTBS1.272.557429239

The following example returns the absolute path of all the control files in the
+dgroup1/sample directory.ASMCMD> find -t CONTROLFILE +dgroup1/sample * +dgroup1/sample/CONTROLFILE/Current.260.555342185 +dgroup1/sample/CONTROLFILE/Current.261.555342183

ls Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups.

lsct Lists information about current ASM clients.

lsdg Lists all disk groups and their attributes.

mkalias Creates an alias for a system-generated filename.

mkdir Creates ASM directories.

pwd Displays the path of the current ASM directory.

rm Deletes the specified ASM files or directories.

rmalias Deletes the specified alias, retaining the file that the alias points to.

 

Make Read Only Table

This script is used for protect table from modification (read only) :

CREATE TRIGGER tab_readonly
BEFORE DELETE OR INSERT OR UPDATE
ON emp
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20201, ‘Table Status: READ ONLY.’);

END;

Compile an Invalid Object

This scripts can be used to recompile all invalid DB Object :

set heading off;
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set tab on;
set scan off;
set verify off;
–
SPOOL gen_inv_obj.sql;
select
decode (OBJECT_TYPE, ‘PACKAGE BODY’,
‘alter package ‘ || a.OWNER||’.'||OBJECT_NAME || ‘ compile body;’,
‘alter ‘ || OBJECT_TYPE || ‘ ‘ || a.OWNER||’.'||OBJECT_NAME || ‘
compile;’)
from dba_objects a,
(select max(level) order_number, object_id from public_dependency
connect by object_id = prior referenced_object_id
group by object_id) b
where A.object_id = B.object_id(+)
and STATUS = ‘INVALID’
and OBJECT_TYPE in (‘PACKAGE BODY’, ‘PACKAGE’, ‘FUNCTION’, ‘PROCEDURE’,'TRIGGER’, ‘VIEW’)
order by
order_number DESC,OBJECT_TYPE,OBJECT_NAME;
SPOOL off;
@gen_inv_obj.sql;

spool comp_all.tmp

select decode (OBJECT_TYPE, ‘PACKAGE BODY’,
‘alter package ‘ || OWNER ||’.'||OBJECT_NAME || ‘ compile body;’,
‘alter ‘ || OBJECT_TYPE || ‘ ‘ || OWNER||’.'||OBJECT_NAME || ‘ compile;’
)
from   DBA_OBJECTS A,
SYS.ORDER_OBJECT_BY_DEPENDENCY B
where  A.OBJECT_ID = B.OBJECT_ID (+)
and    A.STATUS = ‘INVALID’
and    A.OBJECT_TYPE in (‘PACKAGE BODY’,'PACKAGE’,'FUNCTION’,'PROCEDURE’,'TRIGGER’,'VIEW’)
order by B.DLEVEL desc,
A.OBJECT_TYPE,
A.OBJECT_NAME;
–
spool off;
set heading on;
set feed on;
set scan on;
set verify on;
–
@comp_all.tmp