Protected: SQL 2005 Mirroring using the GUI

This post is password protected. To view it please enter your password below:


Restore n Recover Production DB to Point In Time on new machine

1.Install Oracle RDBMS Server Software on destination host.

2.Patch it to same release which is installed on source host.

3.Copy Datafile Backups , Archivelog Backups , Controlfile backup and spfile backup on to destination host ( test2 ) to same location as source host ( test1 )

4.Restore spfile from autobackup using following command.

$export ORACLE_SID=DEV
$export ORACLE_HOME=/apps1/oracle01/u11/app/oracle/product/10.2.0/db_1
$rman target / nocatalog
RMAN>set DBID=00000000 ( You can get DB ID for source db by querying v$database view )
RMAN>startup nomount;
RMAN>restore spfile from ‘/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp’;

Spfile will be restored in $ORACLE_HOME/dbs folder.

RMAN>shutdown immediate;
$sqlplus / as sysdba
SQL>create pfile from spfile;

pfile will be restored in $ORACLE_HOME/dbs folder.

Open pfile ( initDEV.ora ) and modify adump,bdump,cdump,udump and db_recovery_file_dest parameter.Also modify control_files parameter according to new path.

$sqlplus / as sysdba
SQL>startup nomount pfile=’$ORACLE_HOME/dbs/initDEV.ora’
SQL>create spfile from pfile;
SQL>startup nomount force;

5.Restore controlfile from autobackup using following command.

$rman target / nocatalog
RMAN>set DBID=00000000
RMAN>restore controlfile from ‘/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp’;
RMAN>alter database mount;

6.Restore datafiles.
If the directory structure is different than you need to use “set newname” command to change file name location else its not required.

Create RMAN script for restoring DB.

RUN
{
SET NEWNAME FOR DATAFILE 1 TO ‘/apps1/DEV/system01.dbf’;
SET NEWNAME FOR DATAFILE 2 TO ‘/apps1/DEV/undotbs01.dbf’;
SET NEWNAME FOR DATAFILE 3 TO ‘/apps1/DEV/sysaux01.dbf’;
SET NEWNAME FOR DATAFILE 4 TO ‘/apps1/DEV/users01.dbf’;
SET NEWNAME FOR DATAFILE 5 TO ‘/apps1/DEV/cs_dev_owner_01.dbf’;
SET NEWNAME FOR DATAFILE 6 TO ‘/apps1/DEV/admin_01.dbf’;
SET UNTIL TIME  = “to_date(‘2019-11-03:41:54:00′,’YYYY-MM-DD:HH24:MI:SS’)”;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
EXIT

7.Recover database to point in time.

$rman target / nocatalog
RMAN>recover database until time “to_date(”2019-11-03:41:54:00”,’YYYY-MM-DD:HH24:MI:SS’)”;

8.Rename redo logfile name before opening DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database rename file ‘/apps1/u14/oradata/DEV/redo01a.log’ to ‘/apps1/DEV/redo01a.log’;

Do same for all the redo files.( run select member from v$logfile to get list of redo logs )

9.Open DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database open resetlogs;

10.Create TEMP tablespace.

$sqlplus / as sysdba
SQL>create temporary tablespace temp1 tempfile ‘/apps1/DEV/temp.dbf’ size 10M;
SQL>alter database default temporary tablespace temp1;

Read Alert Log from External Table

step 1 : Create procedure
=========================
create or replace procedure ssas_externalalertlog(in_instance in varchar2) as
path_bdump varchar2(4000);
name_alert varchar2(100) := 'alert_' || in_instance || '.log';
begin
select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';

--select
--'alert_' || value || '.log' into name_alert
--from
--sys.v_$parameter
--where
--name = 'db_name';

execute immediate 'create or replace directory ssas_BDUMP as ''' ||
path_bdump || '''';
execute immediate
'create table ssas_ALERTLOG ' ||
' (MSG_line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory ssas_BDUMP ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' skip 0 ' ||
' READSIZE 1048576 ' ||
' FIELDS LDRTRIM ' ||
' REJECT ROWS WITH ALL NULL FIELDS ' ||
' (MSG_LINE (1:1000) CHAR(1000)) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ' ||
' noparallel nomonitoring ';
end;
/

step 2 : execute the procedure
==============================
execute ssas_externalalertlog('ORACLE_SID')

step 3 : issue this sql statement
=================================
col lineno noprint
col ora_error noprint
col msg_line format a132
set pages 0 lines 300 trimspool on trim on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set sql_trace=false;
break on thedate
prompt
prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
prompt =====================================
select "LINENO","THEDATE","ORA_ERROR","MSG_LINE"
from (
select *
from (
select lineno,
msg_line,
thedate,
max( case when (ora_error like 'ORA-%' or ora_error like 'PLS-%')
then rtrim(substr(ora_error,1,instr(ora_error,' ')-1),':')
else null
end ) over (partition by thedate) ora_error
from (
select lineno,
msg_line,
max(thedate) over (order by lineno) thedate,
lead(msg_line) over (order by lineno) ora_error
from (
select rownum lineno,
substr( msg_line, 1, 132 ) msg_line,
case when msg_line like '___ ___ __ __:__:__ ____'
then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' )
else null
end thedate
from ssas_alertlog
)
)
)
)
where ora_error is not null
and thedate >= (trunc(sysdate) - 3)
order by thedate
/

step 4 : drop the external table
================================
drop table ssas_alertlog
/

Redo Log Frequency Map

SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
       , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8
/

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don’t commit the insert.

CREATE TABLE lock_tab (
  id  NUMBER
);

INSERT INTO lock_tab VALUES (1);

Leave this session alone and in a new session, set the DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.

ALTER SESSION SET ddl_lock_timeout=30;

ALTER TABLE lock_tab ADD (
  description  VARCHAR2(50)
);

The session will wait for 30 seconds before failing.

ALTER TABLE lock_tab ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If we repeat the ALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.

ALTER TABLE lock_tab ADD (
  description  VARCHAR2(50)
);

Table altered.

SQL>

REMAP_TABLE & REUSE_DUMPFILES Feature of 11G DataPump

Often DBAs are needed to import the data of one table of first database to another table of another database.

With 10G DBAs had to export the table and import it to destination DB then rename the table.

But with 11G you can simply REMAP the table to new table name of destination Database.

As for Example,

Source DB : CHICAGO

$sqlplus dev/oracle@CHICAGO

SQL>create table dev as select rownum num from dual connect by rownum <= 1000000;

Check the size of table after its creating using following SQL.

col segment_name format a20
select segment_name,bytes “SIZE_BYTES”,ceil(bytes / 1024 / 1024) “SIZE_MB” from   dba_segments where  segment_name = ‘DEV’;

SEGMENT_NAME         ?SIZE_BYTES?  ?SIZE_MB?
——————–         ————         ———-
DEV                           13631488         13

  1. Export DEV table from DEV schema.

expdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y

Reuse_dumpfiles will re-create the dumpfile if the same file exists.This is mostly helpful in case of ASM where you need remove the dumpfile from SQLPLUS if you don’t have access to ASMCMD.

Export: Release 11.2.0.1.0 – Production on Tue Sep 8 12:01:23 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEV"."SYS_EXPORT_TABLE_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEV"."DEV"                                 8.568 MB 1000000 rows
Master table "DEV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEV.SYS_EXPORT_TABLE_01 is:
  /u01/app/dev/admin/CHICAGO/dpdump/exp.dmp
Job "DEV"."SYS_EXPORT_TABLE_01" successfully completed at 12:01:33

Destination DB : DUP

  1. Import the Dev table to Het table.Please note that Het table must not exists before you import it.

impdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het

Import: Release 11.2.0.1.0 – Production on Tue Sep 8 12:07:33 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEV"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEV"."SYS_IMPORT_FULL_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEV"."HET"                                 8.568 MB 1000000 rows
Job "DEV"."SYS_IMPORT_FULL_01" successfully completed at 12:07:38

Notice that the REMAP_TABLE option is designated as

[schema.]old_tablename[.partition]:new_tablename.

  • Check Het table created or not.

dev@shetal:~$ sqlplus  dev/oracle@DUP

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 12:08:10 2009

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc het
Name                       Null?    Type
—————————————– ——– —————————-
NUM                            NUMBER

SQL> select count(*) from het;

  COUNT(*)
———-
   1000000

Enjoy Madi…

How to add Dummy data into table

SQL>create table dev as select rownum num from dual connect by rownum <= 1000000;
Check the size of table after its creating using following SQL.

col segment_name format a20
select segment_name
,      bytes “SIZE_BYTES”
,      ceil(bytes / 1024 / 1024) “SIZE_MB”
from   dba_segments

where  segment_name like ‘DEV’

/
SEGMENT_NAME         SIZE_BYTES    SIZE_MB
——————– ———- ———-
DEV                    12582912         12

Materialized views

(Sources www.oracle.com and www.databasejournal.com)

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

Materialized views, which store data based on remote tables are also, know as snapshots.

Why Use Materialized Views?

You can use materialized views to achieve one or more of the following goals:

Ease Network Loads à If one of your goals is to reduce network loads, then you can use materialized views to distribute your corporate database to regional sites. Instead of the entire company accessing a single database server, user load is distributed across multiple database servers. Through the use of multi-tier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.

In addition to not requiring a dedicated network connection, replicating data with materialized views increases data availability by providing local access to the target data. These benefits, combined with mass deployment and data sub-setting (both of which also reduce network loads), greatly enhance the performance and reliability of your replicated database.

Create a Mass Deployment Environmentà Deployment templates allow you to pre-create a materialized view environment locally. You can then use deployment templates to quickly and easily deploy materialized view environments to support sales force automation and other mass deployment environments. Parameters allow you to create custom data sets for individual users without changing the deployment template. This technology enables you to roll out a database infrastructure to hundreds or thousands of users.

Enable Data Sub-settingà Materialized views allow you to replicate data based on column- and row-level sub-setting, while multimaster replication requires replication of the entire table. Data sub-setting enables you to replicate information that pertains only to a particular site. For example, if you have a regional sales office, then you might replicate only the data that is needed in that region, thereby cutting down on unnecessary network traffic.

Enable Disconnected Computingà Materialized views do not require a dedicated network connection. Though you have the option of automating the refresh process by scheduling a job, you can manually refresh your materialized view on-demand, which is an ideal solution for sales applications running on a laptop. For example, a developer can integrate the replication management API for refresh on-demand into the sales application. When the salesperson has completed the day’s orders, the salesperson simply dials up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.

Read-Only Materialized Views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or disabling the equivalent option in the Replication Management tool. Read-only materialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materialized view group. In addition, using read-only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience means that updates cannot be made at the remote materialized view site. The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees

AS

SELECT * FROM hr.employees@orc1.world

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Replication Management tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments

FOR UPDATE AS

SELECT * FROM hr.departments@orc1.world;

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

CREATE MATERIALIZED VIEW mv_emp_pk

REFRESH FAST START WITH SYSDATE

NEXT SYSDATE + 1/48

WITH PRIMARY KEY

AS

SELECT * FROM emp@remote_db;

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

CREATE MATERIALIZED VIEW LOG ON emp;

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

CREATE MATERIALIZED VIEW mv_emp_rowid

REFRESH WITH ROWID

AS

SELECT * FROM emp@remote_db;

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

CREATE MATERIALIZED VIEW mv_empdept

AS

SELECT *

FROM emp@remote_db e

WHERE EXISTS ( SELECT * FROM dept@remote_db d

WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE

[refresh [fast|complete|force]

[on demand | commit]

[start with date] [next date]

[with {primary key|rowid}]]

The refresh option specifies:

  • The refresh method used by Oracle to refresh data in materialized view
  • Whether the view is primary key based or row-id based
  • The time and interval at which the view is to be refreshed

Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view. You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

CREATE MATERIALIZED VIEW LOG ON emp;

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method – COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method – FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

  • Distinct or aggregate functions
  • GROUP BY Subqueries , Joins & Set operations

Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

CREATE MATERIALIZED VIEW mv_emp_pk

REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE + 2

WITH PRIMARY KEY

AS

SELECT * FROM emp@remote_db;

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Protected: How TO: Setup Multi-Master\Advanced Replication with Oracle 10g, 11g

This post is password protected. To view it please enter your password below:


Creating Logical Standby

Prerequisite

1 : Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database. See Appendix C of the dataguard documentation for a complete list of data type and storage type considerations.

2 : Ensure Table Rows in the Primary Database Can Be Uniquely Identified.

2.1 : Find Tables Without Unique Logical Identifier in the Primary Database.

Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:

SQL> 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’;

2.2 : If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. Use ALTER TABLE command to add a disabled primary-key RELY constraint.

The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

Creating a Logical Standby Database:

Step 1 Create a Physical Standby Database

Create a Physical Standby Database and make sure that there is no error in remote archiving to Standby from Primary Database.

Step 2 Make Sure that Physical Standby is in Sync with Primary Database

Use following query on Standby to check:

SQL>SELECT MAX(AL.SEQUENCE#) "LAST SEQ RECEIVED", MAX(LH.SEQUENCE#) "LAST SEQ APPLIED" FROM V$ARCHIVED_LOG AL, V$LOG_HISTORY LH;

There should not be any difference in Last Seq Received and Last Seq Applied on Physical Standby.

Step 3 Stop Redo Apply on the Physical Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 4 Set Parameters for Logical Standby in Primary

4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)

LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1′

4.2. Set LOG_ARCHIVE_DEST_3 for logs which will received on Standby from Primary

LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prim1′

LOG_ARCHIVE_DEST_STATE_3=ENABLE

Note: LOG_ARCHIVE_DEST_3 only takes effect when the primary database is transitioned to the logical standby role.

Step 5 Build a Dictionary in the Redo Data on Primary Database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Step 6 Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;

For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

Step 7 Create a New Password File for Logical Standby Database

$ORAPWD FILE=<filename> PASSWORD=<password> ENTRIES=<max_users>

This step is required in 10.2 only and should not be performed in 11g.

Step 8 Shutdown and Startup Logical Standby Database in Mount Stage

SQL> SHUTDOWN;
SQL> STARTUP MOUNT;

Step 9 Adjust Initialization Parameter on Logical Standby Database

LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1′
LOG_ARCHIVE_DEST_2= ‘SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1′
LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1′
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE

Step 10 Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 11 Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;