Oracle 10g export - Export dump in 10g Real time procedure

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>

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.

Checking Growth of Oracle Database in Oracle 10g

Step : 1 Calculate total Size of tablespace
SELECT sum(bytes)/1024/1024 "TOTAL SIZE (MB)"
FROM dba_Data_files

Step : 2 Calculate Free Space in Tablespace
SELECT sum(bytes)/1024/1024 "FREE SPACE (MB)"
FROM dba_free_space

Step : 3 Calculate total size , free space and used space in tablespace

SELECT t2.total "TOTAL SIZE",
t1.free "FREE SPACE",
(t1.free/t2.total)*100 "FREE (%)" ,
(1-t1.free/t2.total)*100 "USED (%)"
FROM (SELECT sum(bytes)/1024/1024 free FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total FROM dba_Data_files) t2

Step : 4 Create table which is store all free/use space related information of tablespace

CREATE TABLE db_growth
AS
SELECT *
FROM(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE% "
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)

INSERT INTO db_growth
SELECT *
FROM (
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)

CREATE VIEW v_db_growth
AS SELECT *
FROM
(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view

INSERT INTO db_growth select *
FROM v_db_growth

Step : 8 Check everything goes fine.

SELECT * FROM db_growth;

Check Result
Step : 9 Execute following SQL for more time stamp information

ALTER SESSION SET nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.

Step : 10 Create a DBMS jobs which execute after 24 hours
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.submit(
jobno, 'BEGIN INSERT INTO db_growth SELECT * FROM v_db_growth;COMMIT:END;', sysdate, 'SYSDATE+ 24', TRUE);
COMMIT;
END;

PL/SQL procedure successfully completed.

Step: 11 View your dbms jobs and it's other information
SELECT * FROM user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
EXEC DBMS_JOB.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 12 Finally all data populated in db_growth table
SELECT * FROM db_growth

How to Drop All Objects in a Schema in Oracle 10g

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.

If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects



Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;



This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Importing the whole User Schema (All Objects Included) in SQL Plus from one user to another user with different name

Before Importing Create a user

SQL> create user jretail1 identified by jretail*****;

User created.

Grant privileges to the user

SQL> grant resource,connect,create view to jretail1;

Grant succeeded.

Then start the import process


SQL> $imp jretail1@test file=c:\jretail1120am.dmp log=c:\jretail1120am.log fromuser=jretail touser=jretail1;

Import: Release 10.1.0.2.0 - Production on Thu Oct 22 14:58:32 2009

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

Password:

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

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by JRETAIL, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "ACCOUNTMASTER" 3203 rows imported
. . importing table "ACCOUNTOPENING" 4345 rows imported
. . importing table "AGEINGMASTER" 6 rows imported
. . importing table "APPLICATIONCONFIGURATION" 2 rows imported
. . importing table "BILLWISEADJUSTMENT" 462 rows imported
. . importing table "BILLWISEADJUSTMENTSCHEDULE" 462 rows imported
. . importing table "BUDGETMASTERDETAIL" 4 rows imported
. . importing table "BUDGETMASTERHEADER" 1 rows imported
. . importing table "CALENDARMASTER" 4 rows imported
. . importing table "CALENDARMONTHDETAIL" 0 rows imported
. . importing table "CHITMASTER" 10 rows imported
. . importing table "CHITMEMBER" 850 rows imported
. . importing table "CHITMEMBERINSTALLMENTDETAIL" 7107 rows imported
. . importing table "CHITRECEIPTDETAIL" 6351 rows imported
. . importing table "ITEMTYPEATTRIBUTESDETAIL" 2 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL>

Importing the whole User Schema (All Objects Included) in SQL Plus from one user to another user with the same name

Before Importing Create a user

SQL> create user visynapse identified by visynapse*******;

User created.

Grant privileges to the user

SQL> grant resource,connect,create view to visynapse;

Grant succeeded.

Then start the import process



SQL> $imp visynapse@test file=E:/uploaddb_19_10_09.dmp log=E:/implogvisyn.log;

Import: Release 10.1.0.2.0 - Production on Mon Oct 19 12:50:41 2009

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

Password:*****

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

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing VISYNAPSE's objects into VISYNAPSE
. . importing table "CALCULATION_SECURITY" 199 rows imported
. . importing table "CALCULATION_TABLE" 207 rows imported
. . importing table "COLOUR" 1 rows imported
. . importing table "CUBE" 8 rows imported
. . importing table "DASHBOARD" 2 rows imported
. . importing table "DASHBOARD_METRIC" 8 rows imported
. . importing table "DASH_TABLE" 3 rows imported
. . importing table "DATA_DICTIONARY" 48 rows imported
. . importing table "DIMENSION_26" 295 rows imported
. . importing table "DIMENSION_27" 432 rows imported
. . importing table "DIMENSION_28" 295 rows imported
. . importing table "DIMENSION_SECURITY" 19 rows imported
. . importing table "DOMAIN" 1 rows imported
. . importing table "FILTERS" 0 rows imported
. . importing table "GRAINS" 6148 rows imported
. . importing table "HEADER_FOOTER" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR" 0 rows imported
. . importing table "KEY_PERFORMANCE_INDICATOR_CS" 0 rows imported
. . importing table "KPI_DIMENSION" 0 rows imported
. . importing table "KPI_DIMENSION_CS" 0 rows imported
. . importing table "LINKMETRIC" 0 rows imported
. . importing table "METRIC" 8 rows imported
. . importing table "METRIC_CALCULATION_SET" 207 rows imported
. . importing table "METRIC_CUBE_SET" 8 rows imported
. . importing table "REPORT_CHANNEL" 0 rows imported
. . importing table "REPORT_CONFIG" 23 rows imported
. . importing table "REPORT_HEADER" 0 rows imported
. . importing table "SCORECARD" 0 rows imported
. . importing table "SELECTION_DIMENSION" 0 rows imported
. . importing table "SELECTION_FILTERCRITERIA" 0 rows imported
. . importing table "SELECTION_FORMULA" 0 rows imported
. . importing table "SELECTION_SHAREDREPORT" 0 rows imported
. . importing table "SELECTION_TABLE" 0 rows imported
. . importing table "STATIC_REPORT_TABLE" 8 rows imported
. . importing table "SUMMARY_CONFIG" 0 rows imported
. . importing table "TIME_FREQUENCY" 6 rows imported
. . importing table "USER_DASHBOARD_SECURITY" 2 rows imported
. . importing table "USER_GROUP" 1 rows imported
. . importing table "USER_PROFILES" 1 rows imported
. . importing table "USER_TARGETS" 8 rows imported
. . importing table "VALUE_SECURITY" 0 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL>

Exporting the whole User Schema (All Objects Included) in Oracle SQL Plus

SQL> $exp visynapse@uploaddb file=E:/uploaddb_19_10_09.dmp log=E:/uploadlog.log;


Export: Release 10.1.0.2.0 - Production on Mon Oct 19 11:18:48 2009

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

Password:******

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user VISYNAPSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user VISYNAPSE
About to export VISYNAPSE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VISYNAPSE's tables via Conventional Path ...
. . exporting table CALCULATION_SECURITY 199 rows exported
. . exporting table CALCULATION_TABLE 207 rows exported
. . exporting table COLOUR 1 rows exported
. . exporting table CUBE 8 rows exported
. . exporting table DASHBOARD 2 rows exported
. . exporting table DASHBOARD_METRIC 8 rows exported
. . exporting table DASH_TABLE 3 rows exported
. . exporting table DATA_DICTIONARY 48 rows exported
. . exporting table DEFINITIONS_21 21 rows exported
. . exporting table DEFINITIONS_22 12 rows exported
. . exporting table DEFINITIONS_23 17 rows exported
. . exporting table DEFINITIONS_24 30 rows exported
. . exporting table DEFINITIONS_25 22 rows exported
. . exporting table DEFINITIONS_26 25 rows exported
. . exporting table DEFINITIONS_27 23 rows exported
. . exporting table DEFINITIONS_28 28 rows exported
. . exporting table DIMENSION_SECURITY 19 rows exported
. . exporting table DOMAIN 1 rows exported
. . exporting table FILTERS 0 rows exported
. . exporting table GRAINS 6148 rows exported
. . exporting table HEADER_FOOTER 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR 0 rows exported
. . exporting table KEY_PERFORMANCE_INDICATOR_CS 0 rows exported
. . exporting table KPI_DIMENSION 0 rows exported
. . exporting table KPI_DIMENSION_CS 0 rows exported
. . exporting table LINKMETRIC 0 rows exported
. . exporting table METRIC 8 rows exported
. . exporting table METRIC_CALCULATION_SET 207 rows exported
. . exporting table METRIC_CUBE_SET 8 rows exported
. . exporting table REPORT_CHANNEL 0 rows exported
. . exporting table REPORT_CONFIG 23 rows exported
. . exporting table REPORT_HEADER 0 rows exported
. . exporting table SCORECARD 0 rows exported
. . exporting table SELECTION_DIMENSION 0 rows exported
. . exporting table SELECTION_FILTERCRITERIA 0 rows exported
. . exporting table SELECTION_FORMULA 0 rows exported
. . exporting table SELECTION_SHAREDREPORT 0 rows exported
. . exporting table SELECTION_TABLE 0 rows exported
. . exporting table STATIC_REPORT_TABLE 8 rows exported
. . exporting table SUMMARY_CONFIG 0 rows exported
. . exporting table TIME_FREQUENCY 6 rows exported
. . exporting table USER_DASHBOARD_SECURITY 2 rows exported
. . exporting table USER_GROUP 1 rows exported
. . exporting table USER_PROFILES 1 rows exported
. . exporting table USER_TARGETS 8 rows exported
. . exporting table VALUES_28_CONTROL 196 rows exported
. . exporting table VALUE_SECURITY 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>