Saturday, September 29, 2012

Duplicate for standby from active database / Rman Duplicate for standby

Links to this post

steps to create STANDBY database.

--Put Primary db into logging mode.
--Modify standby side listener.
--Set init parameter on Primary db.
--Create DUMMY init.ora for standby db.
--Copy primary db password file on standby side.
--Create standby redo log (SRL) on primary side.
--start standby db in NOMOUNT mode, test connection from both side.
--RMAN duplicate target database for standby from active database
--start recovery process.

Also visit ...How to change Data guard Protection Mode.


*--Put Primary db into logging mode.

ALTER DATABASE ENABLE FORCE LOGGING;


*--Modify standby side listener & reload it.

Add below part into SID_LIST

(SID_DESC =
      (GLOBAL_DB_NAME=STDBY)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )

--Standby side Listner.ora

TEST:/u00/app/oracle/product/11.2.0/db_1/network/admin :TEST$cat listener.ora

SID_LIST_LISTENER_TEST =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DB_NAME=TEST)
      (SID_NAME = TEST)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
---Edit below part 
   (SID_DESC =
      (GLOBAL_DB_NAME=STDBY)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
  )
-----
LISTENER_TEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.101 )(PORT = 1524))
    )
  )


--Now reload listener.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$lsnrctl reload LISTENER_TEST

LSNRCTL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$lsnrctl status LISTENER_TEST

LSNRCTL for Linux: Version 11.2.0.1.0 - Production 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TEST
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u00/app/oracle/diag/tnslsnr/TEST/listener_test/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.101)(PORT=1524)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "STDBY" has 1 instance(s).
  Instance "STDBY", status UNKNOWN, has 1 handler(s) for this service...
Service "TEST" has 1 instance(s).
  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


--primary side Listener.ora

SID_LIST_LISTENER_PROD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DB_NAME=PROD)
      (SID_NAME = PROD)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
  )

LISTENER_PROD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.102 )(PORT = 1525))
    )
  )



--tnsnames.ora on Both side. 

TEST_INST=(description=(address=(protocol=tcp)(host= 192.168.100.101 )(port=1524))(connect_data=(SERVER=DEDICATED)(SID=TEST)))
TEST_SERV=(description=(address=(protocol=tcp)(host= 192.168.100.101 )(port=1524))(connect_data=(SERVER=DEDICATED)(service_name=TEST)))
PROD_INST=(description=(address=(protocol=tcp)(host= 192.168.100.102 )(port=1525))(connect_data=(SERVER=DEDICATED)(SID=PROD)))
PROD_SERV=(description=(address=(protocol=tcp)(host= 192.168.100.102 )(port=1525))(connect_data=(SERVER=DEDICATED)(service_name=PROD)))


*--Set init parameter on Primary db.

--primary is running on SPFILE. 

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u00/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora
                                                  

--below are parameter on primary. 
DB_NAME=PROD
DB_UNIQUE_NAME=PROD

--SET BELOW PARAMTER ON PRIMARY. 

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STDBY)' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/test/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=STDBY SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT=PROD SCOPE=BOTH;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='PROD','STDBY'
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

---ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/test/redo','/u01/stdby/redo' scope=both; --- CANT CHANGE ON FLY. 


*--Create DUMMY init.ora for standby db.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$cat initSTDBY.ora
DB_NAME=NOTREAL


*----Copy primary db password file on standby side.

PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$scp orapwPROD oracle@192.168.100.101:/u00/app/oracle/product/11.2.0/db_1/dbs


*--Create standby redo log (SRL) on primary side.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/test/redo/redo04.log' size 10m ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/test/redo/redo05.log' size 10m ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/test/redo/redo06.log' size 10m ;


*--start standby db in NOMOUNT mode, test connection from both side.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 16:07:01 2012

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

Connected to an idle instance.

@STDBY->startup nomount;
ORACLE instance started.

Total System Global Area            367439872 bytes
Fixed Size                            2213456 bytes
Variable Size                       318769584 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          4513792 bytes


--Test connections. You should be able to start/stop standby db from Primary side.

PROD:/u00/app/oracle/product/11.2.0/db_1/network/admin :PROD$tnsping STDBY
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production 
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description=(address=(protocol=tcp)(host= 192.168.100.101)(port=1524))(connect_data=(SERVER=DEDICATED)(service_name=STDBY)))
OK (0 msec)

--Lets startup and stop standby from Primary side. 
 
PROD:/u00/app/oracle/product/11.2.0/db_1/network/admin :PROD$sqlplus sys/system@STDBY as sysdba

SQL*Plus: Release 11.2.0.1.0 Production 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size                  2213456 bytes
Variable Size             318769584 bytes
Database Buffers           41943040 bytes
Redo Buffers                4513792 bytes
SQL>


*--RMAN duplicate target database for standby from active database (full output is at end of the post)

RMAN will be invoked from Primary as below.

PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$rman target  sys/system@PROD_SERV AUXILIARY sys/system@STDBY

Recovery Manager: Release 11.2.0.1.0 - Production on 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=195900002)
connected to auxiliary database: NOTREAL (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert='PROD','STDBY','/u01/test/','/u01/stdby/'
set db_file_name_convert='/u01/test/','/u01/stdby/'
set log_file_name_convert='/u01/test/redo/','/u01/stdby/redo'
set 'db_unique_name'='STDBY'
nofilenamecheck;
}


--start recovery process on standby side by applying Real-time redo.

@STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--you can also check output from alert log on standby side. 

STDBY:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :STDBY$tail -f alert_STDBY.log
--------
Primary database is in MAXIMUM PERFORMANCE mode
Tue Aug 14 14:44:27 2012
RFS[3]: Assigned to RFS process 6688
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 3141
RFS[2]: Selected log 4 for thread 1 sequence 9 dbid 195900002 branch 784012804
RFS[3]: Opened log for thread 1 sequence 8 dbid 195900002 branch 784012804
Archived Log entry 1 added for thread 1 sequence 8 rlc 784012804 ID 0xbacde77 dest 2:
RFS[2]: Selected log 5 for thread 1 sequence 10 dbid 195900002 branch 784012804
Tue Aug 14 14:44:28 2012
Archived Log entry 2 added for thread 1 sequence 9 ID 0xbacde77 dest 1:
TEST:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :TEST$
TEST:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :TEST$tail -f alert_STDBY.log
Primary database is in MAXIMUM PERFORMANCE mode
Tue Aug 14 14:44:27 2012
RFS[3]: Assigned to RFS process 6688
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 3141
RFS[2]: Selected log 4 for thread 1 sequence 9 dbid 195900002 branch 784012804
RFS[3]: Opened log for thread 1 sequence 8 dbid 195900002 branch 784012804
Archived Log entry 1 added for thread 1 sequence 8 rlc 784012804 ID 0xbacde77 dest 2:
RFS[2]: Selected log 5 for thread 1 sequence 10 dbid 195900002 branch 784012804
Tue Aug 14 14:44:28 2012
Archived Log entry 2 added for thread 1 sequence 9 ID 0xbacde77 dest 1:
Tue Aug 14 14:46:09 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBY)
Tue Aug 14 14:46:09 2012
MRP0 started with pid=30, OS id=6734
MRP0: Background Managed Standby Recovery process started (STDBY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/stdby/archive/1_8_784012804.dbf
Media Recovery Log /u01/stdby/archive/1_9_784012804.dbf
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 10 Reading mem 0
  Mem# 0: /u01/stdby/redoredo05.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION 




=====================full output of duplicate database==================================


PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$rman target  sys/system@PROD_SERV AUXILIARY sys/system@STDBY

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 14 14:45:13 2012

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

connected to target database: PROD (DBID=195900002)
connected to auxiliary database: NOTREAL (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert='PROD','STDBY','/u01/test/','/u01/stdby/'
set db_file_name_convert='/u01/test/','/u01/stdby/'
set log_file_name_convert='/u01/test/redo/','/u01/stdby/redo'
set 'db_unique_name'='STDBY'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=51 device type=DISK

allocated channel: prmy2
channel prmy2: SID=31 device type=DISK

allocated channel: prmy3
channel prmy3: SID=42 device type=DISK

allocated channel: stby1
channel stby1: SID=20 device type=DISK

Starting Duplicate Db at 14-AUG-12

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u00/app/oracle/product/11.2.0/db_1/dbs/orapwPROD' auxiliary format
 '/u00/app/oracle/product/11.2.0/db_1/dbs/orapwSTDBY'   targetfile
 '/u00/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora' auxiliary format
 '/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora'   ;
   sql clone "alter system set spfile= ''/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora''";
}
executing Memory Script

Starting backup at 14-AUG-12
Finished backup at 14-AUG-12

sql statement: alter system set spfile= ''/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora''

contents of Memory Script:
{
   sql clone "alter system set  control_files =
 ''/u01/stdby/data/control01.ctl'', ''/u01/stdby/data/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=/u01/stdby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/test/'', ''/u01/stdby/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u01/test/redo/'', ''/u01/stdby/redo'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STDBY'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =  ''/u01/stdby/data/control01.ctl'', ''/u01/stdby/data/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u01/stdby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/test/'', ''/u01/stdby/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/test/redo/'', ''/u01/stdby/redo'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''STDBY'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     367439872 bytes

Fixed Size                     2213456 bytes
Variable Size                247466416 bytes
Database Buffers             113246208 bytes
Redo Buffers                   4513792 bytes
allocated channel: stby1
channel stby1: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/stdby/data/control01.ctl';
   restore clone controlfile to  '/u01/stdby/data/control02.ctl' from
 '/u01/stdby/data/control01.ctl';
}
executing Memory Script

Starting backup at 14-AUG-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u00/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD.f tag=TAG20120814T144542 RECID=1 STAMP=791304343
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-AUG-12

Starting restore at 14-AUG-12

channel stby1: copied control file copy
Finished restore at 14-AUG-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/stdby/data/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/stdby/data/system01.dbf";
   set newname for datafile  2 to
 "/u01/stdby/data/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/stdby/data/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/stdby/data/users01.dbf";
   set newname for datafile  5 to
 "/u01/stdby/data/test.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/stdby/data/system01.dbf"   datafile
 2 auxiliary format
 "/u01/stdby/data/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/stdby/data/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/stdby/data/users01.dbf"   datafile
 5 auxiliary format
 "/u01/stdby/data/test.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/stdby/data/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-AUG-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/test/data/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/test/data/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00005 name=/u01/test/data/test.dbf
output file name=/u01/stdby/data/test.dbf tag=TAG20120814T144553
channel prmy3: datafile copy complete, elapsed time: 00:00:46
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/test/data/undotbs01.dbf
output file name=/u01/stdby/data/sysaux01.dbf tag=TAG20120814T144553
channel prmy2: datafile copy complete, elapsed time: 00:01:13
channel prmy2: starting datafile copy
input datafile file number=00004 name=/u01/test/data/users01.dbf
output file name=/u01/stdby/data/system01.dbf tag=TAG20120814T144553
channel prmy1: datafile copy complete, elapsed time: 00:01:28
output file name=/u01/stdby/data/users01.dbf tag=TAG20120814T144553
channel prmy2: datafile copy complete, elapsed time: 00:00:15
output file name=/u01/stdby/data/undotbs01.dbf tag=TAG20120814T144553
channel prmy3: datafile copy complete, elapsed time: 00:00:42
Finished backup at 14-AUG-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=791304219 file name=/u01/stdby/data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=791304219 file name=/u01/stdby/data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=791304219 file name=/u01/stdby/data/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=791304219 file name=/u01/stdby/data/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=791304219 file name=/u01/stdby/data/test.dbf
Finished Duplicate Db at 14-AUG-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: stby1

Tuesday, September 11, 2012

CTAS for partition tables / create table as select * from partition table

Links to this post
create table as select * from , for partition tables as below.

Here is Table DDL

CREATE TABLE CNS.COMM_CNS_dummy_data
(
  FILE_DT                         NUMBER(6),
  PARENT_HQ_NAME                  VARCHAR2(90 BYTE),
  PARENT_HQ_FIPS_COUNTRY_CODE     VARCHAR2(2 BYTE),
  PARENT_HQ_COUNTRY_CODE          VARCHAR2(3 BYTE),
  PARENT_HQ_STATE                 VARCHAR2(10 BYTE),
  HIERARCHY_CODE                  VARCHAR2(2 BYTE),
  DIAS_CODE                       VARCHAR2(9 BYTE),
  NUMBER_OF_FAMILY_MEMBERS        VARCHAR2(5 BYTE),
  FAMILY_UPDATE_DATE              VARCHAR2(8 BYTE),
  SIC1                            VARCHAR2(32 BYTE),
  SIC2                            VARCHAR2(32 BYTE),
  SIC3                            VARCHAR2(32 BYTE),
  SIC4                            VARCHAR2(32 BYTE),
  SIC5                            VARCHAR2(32 BYTE),
  SIC6                            VARCHAR2(32 BYTE),
  NATIONAL_IDENTIFICATION_NUMBER  VARCHAR2(20 BYTE),
  FILLER10                        VARCHAR2(6 BYTE),
  FILLER11                        VARCHAR2(6 BYTE),
  FIRST_EXECUTIVE_FIRST_NAME      VARCHAR2(13 BYTE),
  CENSUS_GEOCODE                  VARCHAR2(15 BYTE),
  CENSUS_GEOCODE_MATCH_FLAG       VARCHAR2(1 BYTE),
  CENSUS_2003_NECTA_DIV_FIPS      VARCHAR2(5 BYTE),
)
TABLESPACE CNS_DATA
PARTITION BY RANGE (FILE_DT)
(  
  PARTITION CNS_dummy_data_201105 VALUES LESS THAN (201106) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201106 VALUES LESS THAN (201107) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201107 VALUES LESS THAN (201108) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201108 VALUES LESS THAN (201109) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201109 VALUES LESS THAN (201110) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201110 VALUES LESS THAN (201111) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201111 VALUES LESS THAN (201112) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201112 VALUES LESS THAN (201201) TABLESPACE CNS_DATA,  
)
;


CTAS for above table will be

CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT)
(
  PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106),
  PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107),
  PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108),
  PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109),
  PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110),
  PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111),
  PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112),
  PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201)
  )
  AS SELECT * FROM 
  cns.COMM_cns_dummy_data PARALLEL;

Thursday, September 6, 2012

Active Dataguard vs Goldengate.

Links to this post

Few days back i have provided Recommendation on what should use for DR. 

Active Data Guard or Oracle Goldengate. 

Primary Functional Comparison .

Oprations
Active Data Guard 
  GoldenGate V11.2
Suitable
Disaster recovery /  Read-only Reporting.
Fine-grain Replication / Object Transformation
Security
User  grant/privileges are same on Target & Source
User access can restricted as per requirement.
Data type support
All data-type supported by Primary
*  (Attached separately)
Replication
·      Physical block-to-block copy of whole DB on Target.  (Carried out by Redo logs). 
·      Target will be read only  (Can be Open temporary for read/write)
·      Support Parallel DML on RAC span on Multiple instances.

·      No object-Transformation allowed
·      Support compression.
·      Only Required Table are subject for Replication  (More controlled). (carried out by Trailfiles) 
·      Target will be in read/write all Time.
·      Does Not support Parallel DML on RAC  spawn from Multiple instances
·      Object-Transformation Allowed.
·      Not-support Compression. 
Maintenance.
Automatic Outage Gap resolution.
Low-Maintenance
Manual Invention needed for Troubleshoot.
Moderate - Maintenance.
Clustered DB
Only One instance will Perform recovery  on Target.
All instance will perform recovery on Target side.  (Load balance)


Other things to look for . 

·         Do you need Copy of source database or  Set of tables required to be Replicate
·         SLA for lag/latency.
·         Amount of Archive log Generated / day by database  ( Per Instance for RAC )
·         Is source & target are in different Time-zone.
·         No of tables with LOB/CLOB data to be replicate.
·         Network bandwidth.