ORA-00001: unique constraint

Hello,

Today I got below error message in my replication env.

ORA-00001: unique constraint

As per my investication

1/ There is NO duplicate records in my base table & materialized view.

In my master & materialized view site having some database trigger to generate auto-increment values using trigger.

As per my trigger from mview site some values generated; the same values generated from master site.

That is the reason implemented DBMS_MVIEW.I_AM_A_REFRESH package from mview site.

For Example:

Master Site

SQL> create table tst_tri (a int);
Table created.

SQL> create or replace trigger tst_tri_after after insert on tst_tri
2 begin
3 delete from tst_tri;
4 end ;
5 /
Trigger created.

SQL> insert into tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tst_tri;

no rows selected

From Materialized View site:

SQL> create or replace trigger tst_tri_after after insert on babu.tst_tri
2 begin
3 if dbms_mview.i_am_a_refresh then
4 delete from babu.tst_tri;
5 end if;
6 end;
7 /

Trigger created.


SQL> select * from babu.tst_tri;

no rows selected

SQL> insert into babu.tst_tri values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from babu.tst_tri;

A
----------
1

SQL> drop trigger sys.tst_tri_after;

Trigger dropped.

ORA-06502: PL/SQL: numeric or value error

Hello,

Database Version: 10.2.0.3
Os: Sun Solaris 5.9

Recently we upgrated our production database 9.2 to 10.2.0.3 & I got below error in my production environment.

On the Application builder page, when attempting to click details to view the applications, the following error occurs:

ORA-06502: PL/SQL: numeric or value error: NULL index table key value

Permanent solution:Upgrade database to 10.2.0.4 or above

OR

Download and apply database Patch 5705795. If the version for your database is not available , create a new Service request from metalink under product RDBMS to request for a patch

Let me analyze something about partition:

Let me analyze something about partition:

Partitioning: Very large tables & indexes by letting you decompose them into smaller more manageable pieces called Partition.

Partition very useful for many different type of application particularly to handle very large volume data & OLTP environment.

Advantages:

1. Partition enables data management operation such as data loads, index creation, and rebuilding & Backup/recovery at the partition level rather then entire table.
2. Partition improves query performance.
3. Partition increases the availability of mission critical database.

Method of Partition:

1. Range Partition.
2. List Partition.
3. Hash Partition.
4. Composite Partition.

What is Rage Partition?

Range partition maps data to the partition based on ranges of partition key values that you establish based on partition. It’s most common type of we are using DATE column.

Ex:

….

…

..

PARTITION by RANGE (hiredate)

(

Partition jan2000 values less then (TO_DATE('02/01/2000','MM/DD/YYYY')),

Partition feb2000 values less then (TO_DATE('03/01/2000','MM/DD/YYYY')),

)

What is List Partition?

List partition enable explicitly control how rows map to the partition.

Example:

…

…

..

Partition by LIST (sales)

(

Partition sales_in values (‘INDIA’),

Partition sales_us values (‘USA’)

)

What is Hash Partition?

Hash partition enable easy partition of data that does not lend itself LIST & RANGE partition.

Composite Partition:

Composite partitioning partitions data using the range method, and within each partition, sub partitions it using the hash or list method

When To Partition Table:

Here are some pre-request to implement partition table.

1. Table size should be more than 2 Gb.
2. Tables contain Historical data’s, in which new data’s added in new partition.

Upgrade Oracle from 10.2.0.1 To 10.2.0.4

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba
Enter password:
Connected.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK
The OracleDBConsoleGOLDLINK service is stopping................
The OracleDBConsoleGOLDLINK service was stopped successfully.


C:\>net stop OracleServiceGOLDLINK
The OracleServiceGOLDLINK service is stopping.
The OracleServiceGOLDLINK service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN
SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ORA-00600: internal error code, arguments: [kxspoac : EXL 1]

Today we are faced the below error in our one of production database.

ORA-00600: internal error code, arguments: [kxspoac : EXL 1]

Database Version: 10.2.0.1

OS Version: Windows

This is database bug in 10.2.0.1 to 10.2.0.3. Due to this error if a SQL statement fails with Ora-600 [kxspoac : EXL 1] when executed by a parallel execution slave and that SQL statement has numeric binds then you are probably seeing this bug.

Solution:

This bug fixed in oracle 10.2.0.4 & 11.1

Refer more details in : Metalink document: 389438.1

Character set Utilities

Database Character set utilities

The Database character set scanner access the feasibility of migrating an oracle database to new database character set. This database characterset scanner checks all character data in the database & tests for the effects & problem of changing the character set encoding.

The method of migrate the database's character sets.

1. Export & Import utilities.
2. CSALTER Script
3. CSALTER with Export & Import utilities on selected tables.

Scan modes in the Database character set scanner.

1. Full database scan

2. User Scan

3. Table Scan

4. Column Scan

How to Install Chractet set scanner:

1. Create a user named CSMIG

2. Assign the necessary privileges to CSMIG

3. Assign the Default ts to CSMIG

3. Create the character set scanner system tables under CSMIG

Use csminst.sql script to modify default tblespace for CSMIG.

% cd $ORACLE_HOME/rdbms/admin
% sqlplus sys/password as sysdba
SQL> @csminst.sql


C:\Users\Babu>csscan help=y


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:41:12 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:

Example: CSSCAN SYSTEM/MANAGER

Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:

Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes owner of tables to be scanned
TABLE yes list of tables to scan
COLUMN yes list of columns to scan
EXCLUDE list of tables to exclude from scan
TOCHAR yes new database character set name
FROMCHAR current database character set name
TONCHAR new national character set name
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

C:\Users\Babu>csscan system/sys tochar=utf8 user=scott log=D:\scott_scan.log


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:49:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 >

Enumerating tables to scan...

. process 1 scanning SCOTT.MASTER[AAAM7wAAEAAAAGhAAA]
. process 1 scanning SCOTT.DEPT[AAAMgxAAEAAAAAJAAA]
. process 1 scanning SCOTT.BONUS[AAAMg1AAEAAAAApAAA]
. process 1 scanning SCOTT.EMP[AAAMgzAAEAAAAAZAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

How a Database Is Quiesced

The database administrator uses the ALTER SYSTEM QUIESCE RESTRICTED statement to quiesce the database. Only users SYS and SYSTEM can issue the ALTER SYSTEM QUIESCE RESTRICTED statement. For all instances with the database open, issuing this statement has the following effect:

• Oracle Database instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.

• Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or terminate). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. If a query is carried out by multiple successive OCI fetches, Oracle Database does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch. Oracle Database also waits for all sessions (other than those of SYS or SYSTEM) that hold any shared resources (such as enqueues) to release those resources. After all these operations finish, Oracle Database places the database into quiesced state and finishes executing the QUIESCE RESTRICTED statement.

• If an instance is running in shared server mode, Oracle Database instructs the Database Resource Manager to block logins (other than SYS or SYSTEM) on that instance. If an instance is running in non-shared-server mode, Oracle Database does not impose any restrictions on user logins in that instance.

During the quiesced state, you cannot change the Resource Manager plan in any instance.
The ALTER SYSTEM UNQUIESCE statement puts all running instances back into normal mode, so that all blocked actions can proceed. An administrator can determine which sessions are blocking a quiesce from completing by querying the v$blocking_quiesce view.

Distributed Transaction (In-doubt Transaction Errors)

In-doubt Transaction Errors:

A transaction is In-Doubt when there is a failure during aspect of the two-phase commit. The following ways Distributed

Transaction Errors occurs.

• A network connection failure or slow between one or more oracle database.

• A server machine running Oracle Software crash.

• An un-handule software error.

You need to force commit or rollback of local, In-doubt transaction; this operation can generate some consistency

problems; you can perform it as per following conditions.

Problem with Two-Phase Commit:

A user application that commits a differed transaction is informed problem area.

ORA-02050: transaction ID rolled back, some remote dbs may be in-doubt

ORA-02051: transaction ID committed, some remote dbs may be in-doubt

ORA-02054: transaction ID in-doubt

Action:

No action required by DBA of any node that has one or more in-doubt transaction due to System Failure or Network

Failures.

Oracle automatically fixes those errors once network or system failure problem resolved.

Transaction Time-outs;

A DML statement that require locks on remote database can be blocked by another transaction own locks on the request

data. If the locks continue to block transaction following reason we should be face below error message.

1. Timeout occurs

2. Oracle roll-back the statement

3. Oracle returns the error message.

ORA-02049: time-out: distributed transaction waiting for lock

Action:

As a DBA; should be check any DEAD LOCK occurs in application or any DML locks occurs in database.

Locks Perform In-doubt Transaction:

A query DML statement that require locks on local database can be blocked indefinitely due to in-doubt transaction then
only these errors occurs.



ORA-01591: lock held by in-doubt distributed transaction identifier

Action:

A user should be inform to DBA; because oracle rollback SQL statement immediately;

Manual Commit in In-doubt Transaction:

DBA_2PC_PENDING data dictionary view to gain information about the in-doubt transaction.

How to Disable RecycleBin in Oracle 10g R1 & R2

On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter "_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

From SYSDBA user:

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE

From BH user:

SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name FROM user_recyclebin;

ORIGINAL_NAME
--------------
T1


From SYSDBA user:

SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
no rows selected

There is no need to PURGE.

PS: Please avoid to user oracle hidden parameter. But before using hidden parameter please discuss with oracle support people.

In oracle 10g R2

On 10gR2 recyclebin is a initialization parameter and bydefault its ON. We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.

Find RAM Size in Sun Solaris

ne-ocadb02:COMMON()/>prtconf i grep -i mem
Memory size: 79872 Megabytes memory (driver not attached)
virtual-memory (driver not attached)
memory-controller, instance #16
etc...

ORA-16792

DataGuard Broker:

While connecting standby database in dataguard broker. I got the ORA-16792

It's due some missing configuration in standby database.

Error Message:

Warning: ORA-16792: configuration property value is inconsistent with database setting

Action:

DGMGRL> EDIT DATABASE "EDKSTBY" SET PROPERTY LsbyMaxEventsRecorded = 5000;
Property "lsbymaxeventsrecorded" updated

Monitoring Logical Standby Database

Monitoring Logical Standby Database:

DBA_LOGSTDBY_EVENTS:

It’s very interesting events that occurred during SQL Apply. By default, the view records most 100 events. If need needed we can changed using DBMS_LOGSTDBY_APPLY_SET()

DBA_LOSTDBY_LOG:

It’s providing dynamic information about archive logs being processed by SQL apply.

V$LOGSTDBY_STATE:

It’s provide fail-over characteristics about logical standby database

V$LOGSTDBY_PROCESS:

This view provides current state and various process involved with SQL apply. Including

· Identifying information (sid | serial# | spid)

· SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)

· Status of the process's current activity (status_code | status)

· Highest redo record processed by this process (high_scn)

V$LOGSTDBY_PROGRESS:

This view provide detailed information regarding process made by SQL apply.

V$LOGSTDBY_STATE:

This view provides a synopsis of the current state of SQL ApplY

V$LOGSTDBY_STATS:

This view provides SQL Apply statistics.

Purge_Master_Log

PURGE_MASTER_LOG

This procedure removes local messages in the DBA_REPCATLOG view associated with a specified identification number, source, or master group.

To purge all of the administrative requests from a particular source, specify NULL for the id parameter. To purge all administrative requests from all sources, specify NULL for both the id parameter and the source parameter.

Syntax:

DBMS_REPCAT.PURGE_MASTER_LOG (

id IN BINARY_INTEGER,

source IN VARCHAR2,

gname IN VARCHAR2);

Example:

SQL> select id,source,gname,timestamp from dba_repcatlog;

ID

----------

SOURCE

--------------------------------------------------------------------------------

GNAME TIMESTAMP

------------------------------ ---------

1801

SAMIDB.SAMIINDIA

SAMIQC 17-OCT-07

SQL> begin

dbms_repcat.purge_master_log (

id => 1801,

source => 'samidb.samiindia',

gname => 'samiqc' );

end;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select id,source,gname,timestamp from dba_repcatlog;

no rows selected

Transportable Tablespace (User Managed)

Performing User-Managed TSPITR with Transportable Tablespaces

Source Database:

1. Connect SQL*Plus to the auxiliary database with administrator privileges. For example:

% sqlplus 'SYS/oracle@aux AS SYSDBA'

2. Make the tablespaces in the recovery set read-only by running the ALTER TABLESPACE ... READ ONLY statement. For example, make users and tools read-only as follows:

ALTER TABLESPACE users READ ONLY;

3. Ensure that the recovery set is self-contained. For example:

EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('users',TRUE);

4. Query the transportable tablespace violations table to manage any dependencies. For example:

SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

This query should return no rows after all dependencies are managed. Refer to Oracle Database Administrator's Guide for more information about this table.

5. Generate the transportable set by running the Export utility as described in Oracle Database Administrator's Guide. Include all tablespaces in the recovery set, as in the following example:

% exp SYS/oracle TRANSPORT_TABLESPACE=y TABLESPACES=(users) TTS_FULL_CHECK=y file=C:\users.dmp

Destination Database:

In this step, you transport the recovery set tablespaces into the primary database.

1. Connect SQL*Plus to the primary database (not the auxiliary database). For example:

% sqlplus 'SYS/oracle@primary AS SYSDBA'

2. Drop the tablespaces in the recovery set with the DROP TABLESPACE statement. For example:

DROP TABLESPACE users INCLUDING CONTENTS;

3. Restore the recovery set datafiles from the auxiliary database to the recovery set file locations in the primary database. For example:

% copy C:\orcl\data\users01.dbf \

> D:\orcl\data\users01.dbf

4. Move the export file users.dmp to the primary host. For example, enter:

% copy C:\users.dmp \

> C:\users.dmp

5. Plug in the transportable set into the primary database by running Import as described in Oracle Database Administrator's Guide. For example:

% imp SYS/oracle TRANSPORT_TABLESPACE=y FILE=C:\users.dmp

DATAFILES=('D:\orcl\data\users01.dbf')

6. Make the recovered tablespaces read/write by executing the ALTER TABLESPACE READ WRITE statement. For example:

ALTER TABLESPACE users READ WRITE;

Data Guard

How to configure Logical Standby Database:

Database Version: Oracle 10.2.0.3.0

Operating System: Windows 2000

DB Mode: Archive log.

1. Pre-Request:

Before create logical standby database you must ensure primary database is properly configured or not.

A) Determine supported data types and objects

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database.

Unsupported data types in logical standby database:

BFILE
ROWID, UROWID
User-defined types
Collections (including VARRAYS and nested tables)
XML type
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)

Unsupported PL/SQL packages.

Oracle supplied pl/sql packaged that modify system metadata typically not supported by SQL apply.

Example:

DBMS_JAVA, DBMS_REGISTRY, DBMS_SPACE, DBMS_REFRESH, DBMS_REDEFINATION, DBMS_SCHEDULER and DBMS_AQ

Unsupported TABLES, VIEWS and SEQUENCES

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED;

Find out the following schema skipping in logical standby database:

SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

B) Ensure table’s rows in the primary key can be uniquely identified.

select owner,table_name from dba_logstdby_not_unique where (owner,table_name) not in ( select distinct owner,table_name from dba_logstdby_unsupported ) and bad_column='Y';

Create Physical standby database

A. Enable Force logging

In Primary database we want to enable FORCE LOGGING after database creation.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

PS: This statement can take a considerable amount of time to complete because it wall all in unlogged direct write in I/O to finish.

B. Create Password file in Primary database

Create password in your primary database. If already exist no problem if no create password file (using orapwd). Every Data Guard configuration must be need password file.

C. Configure standby redo log

D. Primary database initialization parameters.

In Primary database, we should be defining some initialization

Parameters, this parameters that control redo transport services while the database is primary role.

For Example:

Here I am using the below diagram.

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	WASIM	wasim.samiindia
Physical standby	BABU	babu.samiindia

Primary Database Initialization parameter:

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- -------------

db_name string wasim

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- -------------db_unique_name string wasim

SQL> show parameter service_names

NAME TYPE VALUE

------------------------------------ ----------- -------------

service_names string wasim.samiindia

SQL> alter system set log_archive_config = 'dg_config = (wasim,babu)' scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=

2 'LOCATION=E:\oracle\OraBak\Archive

3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

4 DB_UNIQUE_NAME=wasim';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=

2 'SERVICE=babu LGWR ASYNC

3 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

4* DB_UNIQUE_NAME=babu';

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set remote_login_passwordfile = exclusive scope=spfile;

System altered.

SQL>alter system set log_archive_max_processes=30 scope=spfile;

System altered.

Primary Initialization parameters for Standby database:

SQL> alter system set fal_server = babu ;

System altered.

SQL> alter system set fal_client = wasim ;

System altered.

SQL> alter system set db_file_name_convert =

'E:\oracle\product\10.2.0\oradata\wasim', 'E:\oracle\data\babu' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert =

‘E:\oracle\product\10.2.0\oradata\wasim\', ‘E:\oracle\data\babu\' scope=spfile;

System altered.

SQL> alter system set standby_file_management = auto scope=spfile;

System altered.

E. Physical Standby database configuration steps.

1. Take control file, primary database backups (only datafiles)

C:\>set oracle_sid=wasim

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:00:44 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Sys@Wasim.Samiindia> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289508 bytes

Variable Size 130024156 bytes

Database Buffers 33554432 bytes

Redo Buffers 2904064 bytes

Database mounted.

SQL> alter database create standby controlfile as 'C:\babu.ctl';

Database altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Prepare initialization parameters for standby database.

This step we need to create standby initialization parameters using primary database (init.ora).

babu.__db_cache_size=88080384

babu.__java_pool_size=4194304

babu.__large_pool_size=4194304

babu.__shared_pool_size=62914560

babu.__streams_pool_size=0

*.archive_lag_target=1800

*.audit_file_dest='d:\oracle\product\10.2.0\/admin/babu/adump'

*.background_dump_dest='d:\oracle\product\10.2.0\/admin/babu/bdump'

*.compatible='10.2.0.3.0'

*.control_files='e:\oracle\data\babu\control01.ctl'

*.core_dump_dest='d:\oracle\product\10.2.0\/admin/babu/cdump'

*.db_block_size=8192

*.db_domain='samiindia'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.db_name='wasim'

*.db_unique_name='babu'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=wasimXDB)'

*.fal_client='babu'

*.fal_server='wasim'

*.global_names=TRUE

*.job_queue_processes=10

*.log_archive_config='dg_config = (wasim,babu)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=E:\oracle\OraBak\Archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=babu'

*.log_archive_dest_2='SERVICE=wasim LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=wasim'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=30

*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.open_cursors=300

*.parallel_max_servers=20

*.parallel_min_servers=0

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='babu.samiindia'

*.sga_target=167772160

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='d:\oracle\product\10.2.0\/admin/babu/udump'

And need networking configuration both primary and standby database files system.

In TnsNames.ORA (Both Primary and Standby database)

BABU =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = babu.samiindia)

)

)

WASIM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = wasim.samiindia)

)

)

In SQLNET.ORA (Standby database)

Add the below line to broken connection detection on primary file system.

SQLNET.EXPIRE_TIME=2

3. Copy files from primary database to standby database.

In primary database, copy all the datafiles to standby machine. (Primary database already down)

Sys@Wasim.Samiindia> select file_name from dba_data_files;

FILE_NAME

-----------------------------------------------------

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\USERS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSAUX01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\UNDOTBS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSTEM01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EDK01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EXAMPLE01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\BABU01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\RMAN_TS.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\MIS01.DBF

4. Create standby database environment.

a) Create password file for standby database.

b) Create standby instance using oradim command.

               oradim -NEW -SID babu -INTPWD password -STARTMODE manual

c) I hope already created OFA.

5. Start the physical standby database.

C:\>set oracle_sid=babu

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:28:02 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

            Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289484 bytes

Variable Size 75498228 bytes

Database Buffers 83886080 bytes

Redo Buffers 7098368 bytes

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

This statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

6. Verification primary and standby database.

Logical Standby database Configuration

Before configuring logical standby database we need to configure physical standby database.

1. Pre-Request condition for creating logical standby database.

Before creating logical standby database we must ensure primary database properly configured of not. Determine supported data type, PL/SQL Packages and ensure tables rows in uniquely identified in primary database.

2. Enable Supplemental Logging.

Supplemental logging on the primary database generates additional information in the redo log. That info. Is then used during the redo application process in the standby database to make sure the correct rows are affected by the generated SQL.

We need to enable supplemental logging in primary and physical standby database.

In primary database:

System@Wasim.Samiindia> alter database add supplemental log data

2 (all) columns;

Database altered.

In Standby database:

SQL> alter database add supplemental log data

2 (all) columns;

Database altered.

3. Stop Redo apply in physical standby database.

You can run redo apply in physical standby database for any length of time It to a logical standby database. How ever before converting to logical standby database we need to stop redo apply on the physical standby database.

On Physical standby database.

SQL> alter database recover managed standby database cancel;

Database altered.

4. Prepare primary database to support a logical standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3

'LOCATION=E:\oracle\orabak2\wasim\archive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=WASIM';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = ENABLE;

System altered.

SQL> alter system set undo_retention=3600 scope=spfile;

System altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

5. Logical standby database configuration

This steps need to configure database using with out NID command. Using

SQL> alter database recover to logical standby babu;

Database altered.

If you using SPIFLE just execute the above command. If you using PFILE edit your parameter file and try to re-open your database.

Alert log:

*** DBNEWID utility started ***

DBID will be changed from 2126122585 to new DBID of 48127288 for database WASIM

DBNAME will be changed from WASIM to new DBNAME of BABU

Starting datafile conversion

Setting recovery target incarnation to 1

Datafile conversion complete

Database name changed to BABU.

Modify parameter file and generate a new password file before restarting.

Database ID for database BABU changed to 48127288.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open with RESETLOGS option.

Succesfully changed database name and ID.

*** DBNEWID utility finished succesfully ***

Completed: alter database recover to logical standby babu

6. New Password file

In logical standby database, we must re-create password file because

Database name changed.

7. Configure initialization parameter in logical standby database.

This steps logical database configuration steps. Before starting this steps need to re-start (in Mount stage only) your logical standy database.

log_archive_dest_1='location=E:\oracle\Orabak\archive valid_for=(All_logfiles,all_roles) db_unique_name=babu'

log_archive_dest_2='SERVICE=WASIM LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=WASIM'

log_archive_dest_3='location=E:\Oracle\Orabak\archive2 valid_for=(standby_logfiles,standby_role) db_unique_name=babu'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

log_archive_dest_state_3='ENABLE'

8. Open your standby database.

The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.

To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

               SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.

Issue the following statement to begin applying redoes data to the logical standby database. For example:

               SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;