User Suvin Already have schema full of data which has to be exported.
Enter user-name: sys@test as sysdba
Enter password: <--(Password for User sys is given here)
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create or replace directory export_dest as 'D:\export\';
Directory created.
SQL> grant read,write on directory export_dest to suvin;
Grant succeeded.
SQL> grant read,write on directory export_dest to jretail;
Grant succeeded.
SQL> $expdp suvin@test directory=export_dest schemas=suvin dumpfile=jr.dmp logfile=jr.log;
Export: Release 10.2.0.1.0 - Production on Monday, 28 December, 2009 16:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password: <--(Password for User Suvin is given here)
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "JRETAIL"."SYS_EXPORT_SCHEMA_01": jretail/********@test directory=expo
rt_dest dumpfile=jr.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 56.62 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMA
P/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SUVIN"."POSTING" 7.871 MB 114815 rows
. . exported "SUVIN"."STOCKLEDGER" 6.185 MB 80000 rows
. . exported "SUVIN"."TAGHEADER" 4.956 MB 43622 rows
. . exported "SUVIN"."SALEITEMDETAIL" 3.865 MB 33755 rows
. . exported "SUVIN"."MAKINGWASTAGECHARGEDETAIL" 2.748 MB 62761 rows
. . exported "SUVIN"."SALEHEADER" 2.450 MB 22871 rows
. . exported "SUVIN"."SALEPAYMENTGENERAL" 1.102 MB 24014 rows
. . exported "SUVIN"."SALETAXSURCHARGEDETAIL" 1.365 MB 36806 rows
. . exported "SUVIN"."ITEMMASTERHEADER" 523.8 KB 3977 rows
. . exported "SUVIN"."VOUCHERDETAIL" 788.4 KB 10151 rows
. . exported "SUVIN"."CHITRECEIPTHEADER" 430.0 KB 6663 rows
. . exported "SUVIN"."STOCKAUDITINGTEMP" 574.3 KB 11368 rows
. . exported "SUVIN"."TEMP_JSTOCK" 551.4 KB 9785 rows
. . exported "SUVIN"."CHITMEMBERINSTALLMENTDETAIL" 354.9 KB 7445 rows
. . exported "SUVIN"."CHITRECEIPTDETAIL" 306.8 KB 6664 rows
. . exported "SUVIN"."PARTYLOCATIONDETAIL" 308.0 KB 2962 rows
. . exported "SUVIN"."ACCOUNTMASTER" 255.6 KB 3401 rows
. . exported "SUVIN"."PARTYMASTERHEADER" 250.3 KB 2946 rows
. . exported "SUVIN"."SALEORDERHEADER" 154 KB 1016 rows
. . exported "SUVIN"."TAGSTONEDETAIL" 198.3 KB 3326 rows
. . exported "SUVIN"."VOUCHERHEADER" 231.8 KB 4149 rows
. . exported "SUVIN"."PURCHASEHEADER" 139.9 KB 969 rows
. . exported "SUVIN"."SALEORDERDETAIL" 112.0 KB 1065 rows
. . exported "SUVIN"."BILLWISEADJUSTMENT" 125.3 KB 1188 rows
. . exported "SUVIN"."CHITMEMBER" 85.26 KB 870 rows
. . exported "SUVIN"."PURCHASEITEMDETAIL" 97.75 KB 1020 rows
. . exported "SUVIN"."STOCKAUDITDETAIL" 144.1 KB 3227 rows
. . exported "SUVIN"."TEMP_VITABLE" 132.3 KB 1226 rows
. . exported "SUVIN"."ACCOUNTOPENING" 89.46 KB 4543 rows
. . exported "SUVIN"."BILLWISEADJUSTMENTSCHEDULE" 50.39 KB 1188 rows
. . exported "SUVIN"."FORMATDETAIL" 76.24 KB 1267 rows
. . exported "SUVIN"."ITEMMASTERATTRIBUTESDETAIL" 63.53 KB 1571 rows
. . exported "SUVIN"."MAKINGWASTAGECHARGEHEADER" 88.5 KB 3306 rows
. . exported "SUVIN"."PRICEREVISIONDETAIL" 62.84 KB 1800 rows
. . exported "SUVIN"."PURCHASEPAYMENTGENERAL" 33.67 KB 577 rows
. . exported "SUVIN"."PURTAXSURCHARGEDETAIL" 46.18 KB 1003 rows
. . exported "SUVIN"."RATEITEMDETAIL" 60.92 KB 898 rows
. . exported "SUVIN"."SALEITEMSTONEDETAIL" 69.67 KB 1057 rows
. . exported "SUVIN"."SALEORDERPAYMENTGENERAL" 63.22 KB 1017 rows
. . exported "SUVIN"."SMITHRECEIPTHEADER" 34.41 KB 285 rows
. . exported "SUVIN"."SMITHRECEIPTITEMDETAIL" 33.70 KB 315 rows
. . exported "SUVIN"."AGEINGMASTER" 7.468 KB 6 rows
. . exported "SUVIN"."APPLICATIONCONFIGURATION" 11.53 KB 2 rows
. . exported "SUVIN"."BUDGETMASTERDETAIL" 8.187 KB 4 rows
. . exported "SUVIN"."BUDGETMASTERHEADER" 8.398 KB 1 rows
. . exported "SUVIN"."CALENDARMASTER" 8.039 KB 4 rows
. . exported "SUVIN"."CHITMASTER" 11.31 KB 10 rows
. . exported "SUVIN"."COMPANYMASTER" 13.10 KB 2 rows
. . exported "SUVIN"."DAYCLOSURE" 19.60 KB 454 rows
. . exported "SUVIN"."DIAMONDRATEDETAIL" 9.281 KB 2 rows
. . exported "SUVIN"."DIAMONDRATEMASTER" 7.796 KB 1 rows
. . exported "SUVIN"."EMPLOYEEMASTERHEADER" 24.80 KB 80 rows
. . exported "SUVIN"."EMPLOYEEROLEDETAILS" 13.56 KB 103 rows
. . exported "SUVIN"."FORMATHEADER" 10.26 KB 36 rows
. . exported "SUVIN"."INDENTDETAIL" 8.5 KB 2 rows
. . exported "SUVIN"."INDENTHEADER" 8.296 KB 1 rows
. . exported "SUVIN"."ITEMCATEGORYMASTER" 10.04 KB 5 rows
. . exported "SUVIN"."ITEMTYPEATTRIBUTESDETAIL" 9.828 KB 2 rows
. . exported "SUVIN"."ITEMTYPEMASTERHEADER" 10.07 KB 27 rows
. . exported "SUVIN"."LEDGERGROUP" 8.343 KB 48 rows
. . exported "SUVIN"."LOCATIONMASTER" 12.53 KB 2 rows
. . exported "SUVIN"."LOOKUPCODEMASTER" 27.70 KB 358 rows
. . exported "SUVIN"."LOOKUPTYPEMASTER" 10.32 KB 42 rows
. . exported "SUVIN"."LOTCHARGESDETAIL" 7.414 KB 1 rows
. . exported "SUVIN"."LOTHEADER" 11.17 KB 1 rows
. . exported "SUVIN"."MENUMASTER" 21.07 KB 184 rows
. . exported "SUVIN"."OBJECTMASTER" 21.25 KB 296 rows
. . exported "SUVIN"."OTHERCHARGEDETAIL" 9.015 KB 9 rows
. . exported "SUVIN"."OTHERCHARGEHEADER" 7.367 KB 3 rows
. . exported "SUVIN"."PARTYCOMMUNICATIONDETAIL" 15.13 KB 144 rows
. . exported "SUVIN"."PARTYLOCATIONCONTACTLINES" 15.26 KB 120 rows
. . exported "SUVIN"."PRICEREVISIONHEADER" 15.35 KB 138 rows
. . exported "SUVIN"."PURCHASEITEMSTONEDETAIL" 10.80 KB 4 rows
. . exported "SUVIN"."PURCHASEORDER" 12.25 KB 2 rows
. . exported "SUVIN"."PURCHASEORDERDETAIL" 12.95 KB 2 rows
. . exported "SUVIN"."PURCHASEPAYMENTDUESCHEDULE" 25.38 KB 377 rows
. . exported "SUVIN"."PURCHASEQUOTATION" 15.57 KB 1 rows
. . exported "SUVIN"."PURCHASEQUOTATIONDETAIL" 13.23 KB 1 rows
. . exported "SUVIN"."PURCHASERETURNDETAIL" 17.13 KB 36 rows
. . exported "SUVIN"."PURCHASERETURNHEADER" 19.62 KB 31 rows
. . exported "SUVIN"."PURORDTAXSURCHARGEDETAIL" 9.781 KB 2 rows
. . exported "SUVIN"."PURQUOTTAXSURCHARGEDETAIL" 9.75 KB 1 rows
. . exported "SUVIN"."PURRETTAXSURCHARGEDETAIL" 10.41 KB 19 rows
. . exported "SUVIN"."RATEFORMULAMASTER" 10.24 KB 3 rows
. . exported "SUVIN"."RATEMASTER" 19.47 KB 338 rows
. . exported "SUVIN"."ROLEMENUMASTER" 20.15 KB 526 rows
. . exported "SUVIN"."ROLEPRIVILAGEMASTER" 8.992 KB 31 rows
. . exported "SUVIN"."SALEORDERITEMSTONEDETAIL" 11.16 KB 4 rows
. . exported "SUVIN"."SALEORDERSAMPLEDETAIL" 12.60 KB 57 rows
. . exported "SUVIN"."SALEOTHERCHARGEDETAIL" 8.546 KB 4 rows
. . exported "SUVIN"."SALEPAYMENTDUESCHEDULE" 26.72 KB 406 rows
. . exported "SUVIN"."SALEQUOTATIONDETAIL" 22.77 KB 66 rows
. . exported "SUVIN"."SALEQUOTOTHERCHARGEDETAIL" 8.789 KB 11 rows
. . exported "SUVIN"."SECTIONISSUEHEADER" 8.398 KB 1 rows
. . exported "SUVIN"."SECTIONISSUEITEMDETAIL" 9.218 KB 1 rows
. . exported "SUVIN"."SIDETAIL" 20.53 KB 243 rows
. . exported "SUVIN"."SIHEADER" 25.71 KB 240 rows
. . exported "SUVIN"."SMITHCHARGEDETAIL" 9.062 KB 6 rows
. . exported "SUVIN"."SMITHCHARGEHEADER" 7.117 KB 3 rows
. . exported "SUVIN"."SMITHRECEIPTPAYMENTDUESCHEDULE" 14.67 KB 143 rows
. . exported "SUVIN"."SMITHRECEIPTPAYMENTGENERAL" 15.46 KB 137 rows
. . exported "SUVIN"."STOCKAUDITHEADER" 8.195 KB 4 rows
. . exported "SUVIN"."TAB1" 5.25 KB 4 rows
. . exported "SUVIN"."TAB2" 5.25 KB 4 rows
. . exported "SUVIN"."TAGCHARGEDETAIL" 7.773 KB 1 rows
. . exported "SUVIN"."TAXMASTER" 10.22 KB 8 rows
. . exported "SUVIN"."TDSCERTIFICATEDETAIL" 12.67 KB 30 rows
. . exported "SUVIN"."TDSCERTIFICATEHEADER" 9.171 KB 2 rows
. . exported "SUVIN"."TRANSACTIONORDER" 7.117 KB 10 rows
. . exported "SUVIN"."UOMMASTER" 8.015 KB 4 rows
. . exported "SUVIN"."VOUCHERTEMPLATEDETAIL" 9.601 KB 16 rows
. . exported "SUVIN"."VOUCHERTEMPLATEHEADER" 8.5 KB 4 rows
. . exported "SUVIN"."VOUCHERTYPE" 8.125 KB 8 rows
. . exported "SUVIN"."CALENDARMONTHDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GAIDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GAIHEADER" 0 KB 0 rows
. . exported "SUVIN"."GAIITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GRADETAIL" 0 KB 0 rows
. . exported "SUVIN"."GRAHEADER" 0 KB 0 rows
. . exported "SUVIN"."GRAITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GRTDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GRTHEADER" 0 KB 0 rows
. . exported "SUVIN"."GRTITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."GTODETAIL" 0 KB 0 rows
. . exported "SUVIN"."GTOHEADER" 0 KB 0 rows
. . exported "SUVIN"."GTOITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."NARRATIONMASTER" 0 KB 0 rows
. . exported "SUVIN"."PARTYRELATIONSDETAIL" 0 KB 0 rows
. . exported "SUVIN"."POSTINGTEMP" 0 KB 0 rows
. . exported "SUVIN"."POSTINGTEMP1" 0 KB 0 rows
. . exported "SUVIN"."PURCHASEORDERITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."PURCHASEPAYMENTMETAL" 0 KB 0 rows
. . exported "SUVIN"."PURCHASEQUOTATIONITEMSTONEDTL" 0 KB 0 rows
. . exported "SUVIN"."PURCHASERETURNITEMSTONEDTL" 0 KB 0 rows
. . exported "SUVIN"."PURCHASERETURNPAYMENTGENERAL" 0 KB 0 rows
. . exported "SUVIN"."PURCHASERETURNPAYMENTMETAL" 0 KB 0 rows
. . exported "SUVIN"."PURSTOCKLEDGER" 0 KB 0 rows
. . exported "SUVIN"."RATEHISTORYDETAILS" 0 KB 0 rows
. . exported "SUVIN"."RATEHISTORYMASTER" 0 KB 0 rows
. . exported "SUVIN"."REPAIRDELIVERYHEADER" 0 KB 0 rows
. . exported "SUVIN"."REPAIRDELIVERYPAYMENTGENRAL" 0 KB 0 rows
. . exported "SUVIN"."REPAIRORDERHEADER" 0 KB 0 rows
. . exported "SUVIN"."RETAGGING" 0 KB 0 rows
. . exported "SUVIN"."SALEHEADERPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEITEMDETAILPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEITEMSTONEDETAILPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEORDERPAYMENTDUESCHEDULE" 0 KB 0 rows
. . exported "SUVIN"."SALEOTHERCHARGEDETAILPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEPAYMENTDUESCHEDULEPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEPAYMENTGENERALPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALEQUOTATION" 0 KB 0 rows
. . exported "SUVIN"."SALEQUOTATIONSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SALEQUOTTAXSURCHARGEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNHEADER" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNOTHERCHARGEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNPAYMENTGENERAL" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNPAYMENTMETAL" 0 KB 0 rows
. . exported "SUVIN"."SALERETURNTAXSURCHARGEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SALETAXSURCHARGEDETAILPLUS" 0 KB 0 rows
. . exported "SUVIN"."SALRETURNDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SECTIONRELEASEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SECTIONRELEASEHEADER" 0 KB 0 rows
. . exported "SUVIN"."SECTIONTRANSFERDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SECTIONTRANSFERHEADER" 0 KB 0 rows
. . exported "SUVIN"."SISAMPLEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SISTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SMITHRECEIPTITEMSTONEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."SMITHRECEIPTPAYMENTMETAL" 0 KB 0 rows
. . exported "SUVIN"."SMITHVOUCHER" 0 KB 0 rows
. . exported "SUVIN"."STOCKADJUSTMENTDETAIL" 0 KB 0 rows
. . exported "SUVIN"."STOCKADJUSTMENTHEADER" 0 KB 0 rows
. . exported "SUVIN"."STOCKLEDGERPERIODWISE" 0 KB 0 rows
. . exported "SUVIN"."TAGMERGINGDETAIL" 0 KB 0 rows
. . exported "SUVIN"."TAGMERGINGHEADER" 0 KB 0 rows
. . exported "SUVIN"."TAXSURCHARGEDETAIL" 0 KB 0 rows
. . exported "SUVIN"."TDSACKNOWLEDGEMENT" 0 KB 0 rows
. . exported "SUVIN"."TEMP_SSTOCK" 0 KB 0 rows
. . exported "SUVIN"."TJRETAIL" 0 KB 0 rows
. . exported "SUVIN"."UOMCONVERSIONDETAIL" 0 KB 0 rows
Master table "SUVIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SUVIN.SYS_EXPORT_SCHEMA_01 is:
D:\EXPORT\JR.DMP
Job "SUVIN"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:06:39
SQL>
Monday, December 28, 2009
Monday, December 7, 2009
Oracle 10g - Manually Create a Physical Standby Database Using Data Guard
Step-by-step instructions on how to create a Physical Standby Database on Windows and UNIX servers, and maintenance tips on the databases in a Data Guard Environment.
Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line for a couple of years, and my latest experience with Data Guard was manually creating a Physical Standby Database for a Laboratory Information Management System (LIMS) half a year ago. I maintain it daily and it works well. I would like to share my experience with the other DBAs.
In this example the database version is 10.2.0.3.. The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.
I. Before you get started:
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
3. Test the Standby Database creation on a test environment first before working on the Production database.
II. On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
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:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
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=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
III. On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On UNIX, create the directory accordingly.
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.
4) Copy the online logs over.
2. 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 ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
(Note: Not all the parameter entries are listed here.)
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
- On UNIX:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).
12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
2) On Primary, force a logfile switch:
SQL>alter system switch logfile;
3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
IV. Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;
To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line for a couple of years, and my latest experience with Data Guard was manually creating a Physical Standby Database for a Laboratory Information Management System (LIMS) half a year ago. I maintain it daily and it works well. I would like to share my experience with the other DBAs.
In this example the database version is 10.2.0.3.. The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.
I. Before you get started:
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
3. Test the Standby Database creation on a test environment first before working on the Production database.
II. On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
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:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
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=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
III. On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On UNIX, create the directory accordingly.
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.
4) Copy the online logs over.
2. 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 ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
(Note: Not all the parameter entries are listed here.)
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
- On UNIX:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).
12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
2) On Primary, force a logfile switch:
SQL>alter system switch logfile;
3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
IV. Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;
To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
Subscribe to:
Posts (Atom)