Showing posts with label Oracle Data Warehouse. Show all posts
Showing posts with label Oracle Data Warehouse. Show all posts

Wednesday, April 20, 2011

Load Slowly Changing Dimension Type 2 using Oracle Merge Statement

 Load Slowly Changing Dimension Type 2 using Oracle Merge Statement

By using Oracle Merge statement, we are able to perform an insert and update statements (sometime referred to as “upsert”) in one query. The merge statement primarily useful in data warehousing situations, especially when maintaining Type 2 Slowly changing dimension (SCD2), where large amounts of data are commonly inserted and updated

1. Tables

To explore how the merge statement works, let’s use the following set of tables

Product dimension table: dim_product.


CREATE TABLE DIM_PRODUCT
(
PRODUCT_SID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
PRODUCT_CATEGORY_ID NUMBER,
PRODUCT_NAME VARCHAR2(50 CHAR),
PRODUCT_DESCRIPTION VARCHAR2(200 CHAR),
PRICE NUMBER(8,2),
PRICE_EFFECTIVE_DATE DATE,
LAST_MODIFIED_DATE DATE,
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE,
IS_CURRENT_ROW VARCHAR2(1 CHAR)
CREATED_DATE DATE,
UPDATED_DATE DATE
);

Source stage table: stg_product.
This is a stage table use to store the newly modified product records from the product table.

CREATE TABLE STG_PRODUCT
(
PRODUCT_ID NUMBER NOT NULL,
PRODUCT_CATEGORY_ID NUMBER,
PRODUCT_NAME VARCHAR2(50 CHAR),
PRODUCT_DESCRIPTION VARCHAR2(200 CHAR),
PRICE NUMBER(8,2),
PRICE_EFFECTIVE_DATE DATE,
LAST_MODIFIED_DATE DATE,
CREATED_DATE DATE
);

Let’s also created a sequence to use for the dim_product dimension table ‘s surrogate keys.

CREATE SEQUENCE s_dim_product
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

2. SCD Logics and the Merge Query

A slowly changing dimension type 2 (SCD2) is defined to keep track changes of attributes in the underlying source table. While there are many attributes in a table, the business rules decide what changes to track. Here, for demonstration purpose, let’s choose to keep track of changes of each attribute in the underlying product source table, which is to keep history of production name, product description, and product category and price changes.

The merger statement provides the abilities to perform insert and update with only one select statement based upon a join condition. Rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery. The details of the merge statement can be found from the oracle documentation online.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm

An SCD2 normally has three additional columns. The data effective start and end date of the record, and a flag to indicate the current record. To maintain histories, a new record is created to store the changing attributes while we keep the exiting records as histories. To help achieve that, a small utility table with only tow records in it is created. In this utility table, one record indicates the insert flag and the other indicates the update flag. Here the table structure:

Utility table: scd_row_type.

CREATE TABLE SCD_ROW_TYPE
(
SCD_ROW_TYPE_ID NUMBER NOT NULL,
SCD_ROW_TYPE_DESCRIPTION VARCHAR2(20 CHAR)
);

After creating the table, insert the following records into the table:

Insert into scd_row_type(scd_row_type_id,scd_row_description) values(1,’For Insert’);
Insert into scd_row_type(scd_row_type_id,scd_row_description) values(2,’For Update’);

Now we are ready to construct the merge query
MERGE INTO dim_product p
USING ( SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)
                   ) mp
        ON  (p.product_sid = mp.product_sid)
when matched then
   update set P.EFFECTIVE_END_DATE = mp.LAST_MODIFIED_DATE, is_current_row = 'N', updated_date = sysdate
when NOT matched then
insert (P.PRODUCT_SID,P.PRODUCT_ID,P.PRODUCT_CATEGORY_ID, P.PRODUCT_NAME, P.PRODUCT_DESCRIPTION, P.PRICE, p.PRICE_EFFECTIVE_DATE,P.LAST_MODIFIED_DATE,
 p.effective_start_date,P.EFFECTIVE_END_DATE,is_current_row, created_date,
 updated_date )
 values (s_dim_product.nextval,mp.PRODUCT_ID,mp.PRODUCT_CATEGORY_ID,mp.PRODUCT_NAME,
 mp.PRODUCT_DESCRIPTION, mp.PRICE,mp.price_effective_date,
mp.LAST_MODIFIED_DATE,mp.last_modified_date,
 to_date('2099-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),’Y', sysdate,sysdate);
  commit;


3. How it works

To make it easier to understand how the merge statement work when we actually perform some tests with data. First, let’s issue the following insert statements to create two records in the stage table.

truncate table stg_product;

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID,  PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE,  LAST_MODIFIED_DATE,  CREATED_DATE)
        values (1,8,'Apple iPad 36 GB','Apple iPad 36 GB with case', 800,sysdate,sysdate,sysdate);

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID,  PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE,  LAST_MODIFIED_DATE,CREATED_DATE)
         values (2,7,'Canon 12 MPIX Digital Camera','Canon 12 MPIX Digital Camera, Zoon Len, Case', 150,sysdate,sysdate,sysdate);

commit;


Check the stage table to make sure the records are there.

Sqlplus>select * from stg_product;

Now run the merge statement which was created in section 2. Once the merge query statement was executed, run the following query to checked the records in the dim_product table.

Sqlplus>select * from dim_product;

There should be two rows in the dimension table with the column effective_start_date, effective_end_date and is_current_row populated. Now, let’s find out how the merge statement handles SCD Type 2 records. To begin, run the following queries against the stage table:

update stg_product set price = 700 , price_effective_date = sysdate, last_modified_date = sysdate where product_id = 1;

insert into stg_product (
PRODUCT_ID, 
PRODUCT_CATEGORY_ID,
PRODUCT_NAME,
PRODUCT_DESCRIPTION,
PRICE,
PRICE_EFFECTIVE_DATE,
LAST_MODIFIED_DATE,
CREATED_DATE)
values (3,6,'EPSon Color Printer',
'EPSon color print with high definition photo prints', 200,sysdate,sysdate,sysdate);
 commit;

The above statements updated a record where product_id equal to 1 to change it price to 700, and inserted one more new record, product_id 3, into the stg_product table. To help understand what the records set looks like from the using clause of the merge statement, let’s copy the query and execute it to get the record set.

SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id,
         s.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)


Examine the result set, it should look something like:

PRODUCT_SID PRODUCT_ID PRODUCT_CATEGORY_ID PRODUCT_NAME         PRODUCT_DESCRIPTION                                 PRICE   PRICE_EFFECTIVE_DATE LAST_MODIFIED_DATE  CREATED_DATE   SCD_ROW_TYPE_ID
-6789            1            8            Apple iPad 36 GB     Apple iPad 36 GB with the latest createst           700    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:10   2
62               1            8            Apple iPad 36 GB     Apple iPad 36 GB with the latest createst           700    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:10   2
                 3            6            EPSon Color Printer  EPSon color print with high definition photo prints 200    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:11   1

As you can see, we have two rows for product_id 1, and one row for the new record, product_id 3. Also, at the end of the row, you can also see that the scd_record_type_id column has two ids for product id 1. One is 2 and the other one is 1. for the one the that has a scd_record_type_id equql to 1, the product_sid is changed to –6789. Therefore, this record will be used for insert. The logic that define which record to update or insert was defined in the case statement:

(CASE
   WHEN dp.product_id IS NULL
     THEN  1
   WHEN (dp.product_category_id != sp.product_category_id
          OR dp.product_name != sp.product_name
          OR DP.PRODUCT_DESCRIPTION != sp.product_description
          OR dp.price != sp.price
          OR dp.price_effective_date != sp.price_effective_date)
     THEN  2
   ELSE    0
 END) AS scd_row_type_id


If the product_id is not existing in the dim_product table, it is a new record. Therefore, it is assigned a 1 and it will be inserted. If any of the changes occurred to an existing record, it is an update and is assigned a 2. Otherwise, any identical record will be assign a 0. The join statement:

JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)

takes only record with a scd_record_type_id that is 1 or greater since the scd_record_type table only have two records, scd_record_type_id 1 and 2. Therefore, any record that with a scd_record_type = 0 (identically records) will not be included in the final result set. To create a type 2 record, which is a existing record that has one or more new attributes, we change the existing product_sid to a dummy number, -6789, by using the decode statement:

DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid

We choose this number because we had define the surrogate key of the dim_product table to only use positive value for keys. Hence, it will be inserted into the dimension. The "when matched" and "when not matched" clauses in the merge statement are self-explanatory. We matched the record set with the dim_product table on the product_sid key. If it matched, we set the is_current_row flag to ‘N’ and sent the effective_end_date to the last_modified_date. If it was not matched, records will be insert into the dimension table. Now, run the merge statement in section 2 to load the data into the dimension table.

4. Summary

As it is stated in the Oracle documentation, the MERGE statement can be used to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. Therefore, it is helpful in loading data into a slowly changing dimension type 2 table.

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement. Oracle Database does not implement fine-grained access control during MERGE statements.

 If you are using the fine-grained access control feature on the target table or tables, then use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.

Reference:


Oracle Online Documentations

Oracle Database 11g The Complete Reference,Kevin Loney, Oracle Press

The Data Warehouse toolkit, Ralph Kimball and Margy Ross

The Data Warehouse ETL Toolkit, Ralph Kimball and Joe Caserta

Other Reference on the Web

Related Note:

SQL Outer Join

Thursday, December 02, 2010

OLAP

OLAP and Business Intelligence





Reference:


Saturday, September 18, 2010

Setup Oracle Physical Standby Database Using Dataguard On Linux

1. INTRODUCTION

This document summarizes the steps of creating an Oracle Physical Standby Database on Linux servers using Oracle Data Guard features. Oracle 10g Data Guard supports both Logical and Physical Standby databases.

The database version that is used here is 10.2.0.3. The Primary database server is pcdb-1.databob.net and Standby database server is pcdb-2.databob.net.The Primary database (db_name) is called PCDB and the Standby database is called PCDBS.
Oracle 10g documentation regarding Data Guard Physical Standby Database can be found here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm


2. CONFIGURATIONS
2.1 Prerequisites
Following are the prerequisites need to be met before creating the standby database.

1. Make sure that the operating system and platform architecture on the primary and standby systems are the same. Oracle user was created in both servers and ssh is configured for Oracle user on both servers
2. Install Oracle database software on the standby server and patch it if necessary. We are using Oracle 10g, release 10.2.0.3. Make sure the same Oracle software release is used on the Primary and Standby databases server, and Oracle home paths are identical on both servers.
3. Test the Standby Database creation and make sure the server was installed properly. Also make sure that all the directories for data and logs are the same on both servers.

2.2 Planning
During the configuration processes, it requires restarting the primary database, creating backup copy of data and log files in the primary database, and restoring them in the standby database. Therefore, if the primary database is a production database, proper planning is needed to limit downtime.

Also, this document separates primary and standby database configuration tasks for clarity reason. In fact, some of these steps can be rearranged to limit database downtime.

2.3 Configure Primary Database Server, pcdb-1.databob.net
Before you create a standby database you must first ensure the primary database is properly configured. After the primary database is set up and running, these are additional configuration tasks need to complete:

1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Additional Primary Database Initialization Parameters

Please note that during the configuration processes, it requires to restart the primary database. Proper planning is needed to reduce down time if the primary database is already in production.

You need to be the Oracle sys user to perform most the configuration tasks. To become the sys user, do the following:

#> sqlplus /nolog
SQL>connect sys/password as sysdba

Following are steps to configure Primary Database

1. Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.
Create a password file if it dose not exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

1) To check if a password file already exists, run the following command:

SQL> select * from v$pwfile_users;
no rows selected
2) Create an Oracle password file in Linux:
Password files are created with the Oracle orapwd tool which is located in $ORACLE_HOME/bin directory. To create the password file, execute the following commands:
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDB password= force=y

Replace with the password of the SYS user. The password file needs to be named exactly in orapw format in Linux. For example, if the sid is ‘PCDB’ then the password file name needs to be named as orapwPCDB.
The password file is located in $ORACLE_HOME/dbs directory.
3) Check again if the password file was created and perform the following query. You should get the same result as show bellow for sys user.
SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER
-----------------------------------------
SYS TRUE TRUE

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files, run the following query:

SQL> select bytes from v$log;
BYTES
--------------
10485760
10485760
10485760
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile:
GROUP# MEMBER
--------------------------------------------------------------------------------
1 /u0/oracle/log/PCDB/redo/PCDB_redo01_01.log
2 /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
3 /u0/oracle/log/PCDB/redo/PCDB_redo03_01.log

3) Create standby Redo log groups.
Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. Here, we are going to create same number log groups for standby log as of data redo log group.
Before creating the standby log groups, please make sure that:
• log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
• Verify related database parameters and settings.
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.
To create the standby log groups, user the following commands:

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 10M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS
---------- ---------- ---------- --------- ------------------------------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

4. Set Primary Database Initialization Parameters
Add (or edit) the following parameters to pFile, initPCDB.ora if they are not already in the pFile. If a pFile dose not exist, you can use the following command to create one from the existing spFile:
SQL>create pfile=’<$ORACLE_HOME>/dbs/initPCDB.ora’ from spfile;
(Note: replace oracle home with the full path.).
Add and verify if the following parameters are in the pFile, initPCDB.ora:
db_name=PCDB
db_unique_name=PCDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’


5. Create spfile from pfile, and restart primary database using the new spfile and enable archive log.
Data Guard must use SPFILE. Create the SPFILE and restart database.
SQL> shutdown immediate;
SQL>create spfile from pfile=’/u0/oracle/admin/PCDB/scripts/initPCDB.ora’;


6. Create a Control File for the standby database:

On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’;
Database altered.


7. Make a backup copy of the primary database data file.
1) If the database is running, shutdown it.
SQL>shutdown immediate;
2) Make a copy of the data files and temp files and online log files and scp them over to the standby database server.
You can copy the file manually or follow the following steps to create scp scripts:
SQL> set pages 50000 lines 300 head off veri off flush off ti off
SQL> spool ‘/tmp/cpfiles_prim.sh’
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_data_files;
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_temp_files;
SQL> select 'scp -p '||member||' ${stan_host}:’||file_name from v$logfile;
SQL> spool off;
Edit the file /tmp/cpfiles_prim.sh to define the ${stan_host} variable. To do so, add these tow lines to the top of the script:
#!/bin/bash
stan_host=

Replace hostname with the hostname or IP address for the standby server.
#>/tmp/cpfiles_prim.sh
#!/bin/bash
stan_host= pcdb-2.databob.net
scp -p /u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf ${stan_host}:/u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf
…..
Execute the script, /tmp/cpfiles_prim.sh, to scp the file over to the standby database server. Note, depend on the files size and the network traffic, it may take some time. Therefore, you may want to change script to copy the files to a temp directory is the primary server, tar and gzip the files and scp the gziped files to the standby server. Then, copy the files to the pproper directories.

Before copying the files, verify that standby server file system setting are the same as the primary server. This means that all the directories need to be the same.
#>chomd 766 /tmp/cpfiles_prim.sh
#>./tmp/cpfiles_prim.sh
3)Copy the standby control file that was generated in step 6 above to the /tmp directory in the standby server
#>scp ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’ standby_server:/tmp


8. Restart the Primary database using the newly created spfile and enable archive log.
SQL>Startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 343
Next log sequence to archive 345
Current log sequence 345
SQL>


Now the primary database is setup for transmitting data to standby database server.

2.4 Configure Standby Database Server, pcdb-2.databob.net

To configure the standby database server, pcdb-2 , db_unique_name PCDBS, these configuration tasks need to complete:

1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly

Following are steps to configure Standby Database.


1. Install the Primary database data files on the Standby Server:

If you had not copy the data files which you created in section 2.3, step 7, please do so to copy the files to the proper directories from the backup copy.

2. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy initPCDB.ora from Primary server to Standby server,
2) Rename it to initPCDBS.ora, and modify the file as follow.
Note that most the parameters here do not need to change since they will be the same as those mentioned in section 2.3, step 4.
*.audit_file_dest=/u0/oracle/admin/PCDB/adump
*.background_dump_dest=/u0/oracle/admin/PCDB/bdump
*.core_dump_dest=/u0/oracle/admin/PCDB/cdump
*.user_dump_dest=/u0/oracle/admin/PCDB/udump
*.compatible='10.2.0.3.0'
control_files = (/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl)
db_name='PCDB'
db_unique_name=PCDBS
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’

3 Copy the standby control file,which was scp’ed over from the primary server in section 2.3, step 7
to the following directories and filename ;
/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

4. Create the password file for the standby server.
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDBS password= force=y

5. Configure listeners for both the primary and standby databases.
Create or edit the listener.ora files on both primary and standby server. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary server, pcdb-1, edit the listener.ora file for PCDB (primary) and PCDBS(standby).
# On primacy server, pcdb-1, PCDB
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDB)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDB)
)
)
2) On Standby server, pcdb-2, edit the listener.ora file for PCDBS(standby).
# On primacy server, pcdb-2, PCDBS
LISTENER_PCDBS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDBS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDBS)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDBS)
)
)

6. Configure Oracle Net service names for both primary and standby servers.


Configure tnsnames.ora on both servers to hold entries for both databases. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary database server, pcdb-1, edit the tnsnames.ora
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)
PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2) On Standby server, pcdb-2, edit the tnsnames.ora
LISTENER_PCDBS=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
7. Start the listener and check tnsping on both nodes to both services

1) Restart listeners on primary servers
oracle@pcdb-1> lsnrctl stop
lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 15-SEP-2010 10:26:32

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
oracle@pcdb-1> lsnrctl start
2)Restart listener on standby server
oracle@pcdb-2> lsnrctl stop
oracle@pcdb-2> lsnrctl start

3)use tnsping to test both servers, PCDB and PCDBS

On primary server:
oracle@pcdb-1>tnsping PCDB
oracle@pcdb-1>tnsping PCDBS

On standby:
oracle@pcdb-2>tnsping PCDB
oracle@pcdb-2>tnsping PCDBS

8.Verify that the Oracle environment variables are set correctly on the standby server.

#>sudo – su oracle
#>env
ORACLE_SID=PCDB
ORACLE_BASE=/u0/oracle/product/10.2.0
….

9. Start up nomount the standby database and generate a spfile.

SQL> connect sys/password as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes

SQL> create spfile from pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

10. Startup mount the standby database and perform recovery
1) Startup mount the instance
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes
Database mounted.

2) Start apply the logs
Issue the following command to start apply the logs.

SQL>alter database recover managed standby database disconnect from session;
Database altered.

Now, if everything is setup correctly, the standby database should start to apply the logs from the primary database.

11. Verify the Physical Standby Database Is Performing Properly

1) Check alert log of the standby server to see if the operations taking place or there is any error

#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDBS.log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 15 15:03:16 2010
Attempt to start background Managed Standby Recovery process (PCDBS)
MRP0 started with pid=56, OS id=6637
Wed Sep 15 15:03:16 2010
MRP0: Background Managed Standby Recovery process started (PCDBS)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 3 processes
Media Recovery Waiting for thread 1 sequence 360
Wed Sep 15 15:03:22 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

2) Check the alert log of the primary server to see if the operations taking place or there is any error

The alert log of the primary will show how it recognize the standby and start shipping archived logs
#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDB.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 364 for destination LOG_ARCHIVE_DEST_2
Wed Sep 15 17:05:24 2010
Thread 1 advanced to log sequence 365
Current log# 2 seq# 365 mem# 0: /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
Wed Sep 15 17:05:25 2010
LNS: Standby redo logfile selected for thread 1 sequence 365 for destination LOG_ARCHIVE_DEST_2

3). Check the Standby severer:
3.1). Check database unique_name and database name.
SQL> show parameters db_unique_name;
NAME TYPE VALUE
---------------------------------------------------------------------------
db_unique_name string PCDBS

SQL> SELECT NAME FROM V$DATABASE
NAME
---------------------------
PCDB
3.2). Check archived redo log on Standby
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------------
367 15-SEP-10 15-SEP-10
368 15-SEP-10 15-SEP-10
369 15-SEP-10 15-SEP-10
370 15-SEP-10 16-SEP-10
371 16-SEP-10 16-SEP-10
372 16-SEP-10 16-SEP-10
3.3)Check archived redo applied on Standby
SQL>select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
367 YES
368 YES
369 YES
370 YES
371 YES
372 YES

3.4) Check standby manager status
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CLOSING 363
ARCH CLOSING 364
….
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 373
….
RFS IDLE 0
RFS IDLE 373
The above show MRP0 is waiting for log 372 from the primary server.
3.5) Switch log files on Primary.
Login to primary database and perform the following command to force a log switch.
SQL> alter system switch logfile;

System altered.

SQL>archive log list
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 372
Next log sequence to archive 374
Current log sequence 374

3.6) In Standby Database server, repeat verification steps, 3.2, 3.3 and 3.4.

You show notice that log 373 had been applied and MRP0/LNS is waiting for log 374 (step 3.4).
12. Errors and Troubleshoot
1) Error: ORA-19527: physical standby redo log must be renamed (in Standby server)
If you have the above error message in the alert log after you issued “alter database recover managed standby database using current logfile disconnect;” statement, it was due to the fact that the standby database file structure is exactly the same as the primary database server. To resolve that, do the following:

SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

Now, you can start the recover again by issue the following statement:
SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;

Check the alert log again and you should not see the same error again. If you see error, “ORA-27037: unable to obtain file status”, try to shutdown the server again one more time and startup mount, you will not see any more error.

3)ORA-16191: Primary log shipping client not logged on standby

ORA-16191: Primary log shipping client not logged on standby

PING[ARCr]: Heartbeat failed to connect to standby 'PCDBS'. Error is 16191.
Wed Sep 15 16:50:49 2010
Errors in file /u0/oracle/admin/PCDB/bdump/PCDB_arcr_29633.trc:
ORA-01017: invalid username/password; logon denied
Wed Sep 15 16:50:49 2010
Error 1017 received logging on to the standby

The error was caused, most likely, the missing of password file or the password for sys user was not entered correctly in the password file. Run the following query to check if the password file was created in both servers (primary and standby) and the password was correctly. You can always recreate the password files.(see step 2, section 2.3)

SQL> select * from v$pwfile_users;

2.5 Useful Commands and Queries

• Check archive log status on standby database:
SQL>select sequence#, first_time, next_time,applied from v$archived_log;
• Start archive log recover (log apply service):
SQL>alter database recover managed standby database using current logfile disconnect;
• Start real-time apply on Standby
SQL> alter database recover managed standby database using current logfile disconnect;
• Stop archive log recover:
SQL> alter database recover managed standby database cancel;
• Check dataguard messages
SQL> select error_code, timestamp, message from v$dataguard_status;
• Check standby messages
SQL> select process,status,sequence# from v$managed_standby;
• Enable archivelog
SQL>alter database archivelog;
• Show current archive log list
SQL>archive log list;
• Force a log switch on primary
SQL>alter system switch logfile
• Check log file size
SQL>select bytes from v$log;
• Check current log group
SQL>select group#, member from v$logfile;
• Many more here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm


Related Notes:

REFERENCES




Monday, April 16, 2007

Oracle Implict and Explict Cursors

The difference between implict and explict cursors:

Implicit Cursor are declared and used by the oracle internally. Moreover implicitly cursors are no need to declare. Oracle creates and process and closes automatically.

Explicit cursors are declared and used by the user. Explicit cursor should be declared and closed by the user. Moreover Explicit Cursors were used to retrieve values from two or more rows.

Tuesday, August 29, 2006

Oracle For Data Warehouse: Oracle Bitmap Index




1. Structure of A Bitmap Index

* Any type of index is designed to help find rows that contain certain key values. A regular (B*-tree) index does this by pairing the ROWID for a row with its key value, and then sorts those pairs into a B-tree structure. The ROWID serves as a pointer to a row with that value.
* A bitmap index has a very different structure of which no ROWIDs are stored. Bitmap index entries contain bitmap/bit-vector, instead of list of rowids (B*-tree). Each bitmap header stores start and end ROWIDs. Each position in a bitmap maps to a potential row in the table. The contents of that position in the bitmap for a particular value indicate whether that row have that value in the bitmap columns. 1-bit is stored is the value match the bitmap condition; otherwise, 0-bit is stored.
* A mapping function converts the 1-bit position to an actual row. A compression function compresses the long sequence of '0's in the Bitmap.
* When a bitmap index is created, a series of bitmaps are created. Each bitmap corresponding to a particular value of the columns involved, for example, customer_id. If the table contains 300 distinct values for the customer_id, 300 bitmaps will be built. If later an other customer_id is added, a bitmap for that value will be create.
* If a bitmap index involves more that one column, then there will be a bitmap for every possible combination.

2. Characteristics Of A Bitmap Index

* Bitmap indexing is a query execution optimization technique in Data Warehousing environments
* Bitmap indexes are very space efficient, which allow more entries per leaf block. A bitmap can hold many bits pointing to many rowids and have significantly fewer index block processing and disk I/O.
* Bitmap indexes are fully updateable. When a bitmap index entry is locked, many rows stay locked (in OLTP). In a B*-tree index, a list of rowids are stored for each index entry in a leaf block and, when an index entry is locked, it will not impact many table rows. * With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
* With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
* Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them-either the combination of all 10 columns or sometimes a single column-creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. The AND_EQUAL hint provides this functionality for B-tree indexes, but no more than five indexes can be used by a query. This limit is not imposed with bitmap indexes.




3. When to use a Bitmap index

* The column has a low distinct cardinality (not just low cardinality), meaning that there are few possible values for the column. For example, sales regions.
* Columns that are frequently used in complex conditions, such as AND, OR or XOR, in the WHERE clause of queries or aggregate queries that contain SUM, COUNT, or other aggregate functions.
* Tables that has many rows. On a table with 1 million rows, even 10,000 distinct possible values would be acceptable.
* Data warehouse that supports many ad hoc queries and few concurrent DML changes.

4. Advantages of Bitmap Index

* Reduced response time for many ad hoc queries
* Substantial reduction of space usage compared to other indexing techniques. Bitmap index can be created individually and then combined efficiently at run time.
* Dramatic performance gains.

5. Star Transformation

* Use Star Transformation in data warehouse that utilize star schema with the following case:
-- Number of dimension tables is large, making the Cartesian product of the dimension tables too expensive.
-- Fact table is sparse
-- Not all dimensions have constraining predicate

* To enable star transformation, set STAR_TRANSFORMATION_ENABLED initialization parameter to TRUE. This parameter is also dynamic and can be set to TRUE at the session level.

6 Index for Dimension and Fact Table

* For Fact table, create bitmap indexes for all the foreign keys. For example, if you fact table contains foreign keys for customer key, sales_person_key, order_key and product_key, which are foreign keys to the dimension, table coustomer_dim, sales_person_dim, order_dim and product_dim. Create a bitmap index for each of these four foreign keys.
* Create an unique b*-tree index on the primary key for each of the dimension tables.