ORA-14402: updating partition key column would cause a partition change

SQL> conn corporate/log

Connected.

updation script :

SQL> update STOCKVALUE set username=:username,modifiedon=to_date(:modifiedon,'dd/mm/yyyy hh24:mi:ss '),app_desc=:app_desc,app_level=:app_level,isrejected=:isrejected,qty= :qty,reservedforbranch= :reservedforbranch,batch=null,isreserved=:isreserved,amount= :amount,partyname=:partyname,gdocid=null,stock_value= :stock_value,rate= :rate,stock_qty= :stock_qty,trans_type= :trans_type,docdate=to_date(:docdate,'dd/mm/yyyy hh24:mi:ss '),docid=:docid,branch= :branch,location= :location,stocktrans_type= :stocktrans_type,postaccountflag=:postaccountflag,plusorminus=:plusorminus,expiry_date=null,packsize= :packsize,itemid= :itemid

 where STOCKVALUEid=200003000050517

status=ORA-14402: updating partition key column would cause a partition change

Solution : 

SQL> alter table STOCKVALUE enable row movement;

Table altered.

SQL> update STOCKVALUE set username=:username,modifiedon=to_date(:modifiedon,'dd/mm/yyyy hh24:mi:ss '),app_desc=:app_desc,app_level=:app_level,isrejected=:isrejected,qty= :qty,reservedforbranch= :reservedforbranch,batch=null,isreserved=:isreserved,amount= :amount,partyname=:partyname,gdocid=null,stock_value= :stock_value,rate= :rate,stock_qty= :stock_qty,trans_type= :trans_type,docdate=to_date(:docdate,'dd/mm/yyyy hh24:mi:ss '),docid=:docid,branch= :branch,location= :location,stocktrans_type= :stocktrans_type,postaccountflag=:postaccountflag,plusorminus=:plusorminus,expiry_date=null,packsize= :packsize,itemid= :itemid

 where STOCKVALUEid=200003000050517

1 row updated.

Oracle Data Pump in Oracle 11g (expdp and impdp)

Introduction

Oracle's export utility allows you to extract data from the database and write that data to the operating system file. The file to which you extract data when you use Oracle8i’s export utility is referred to as a dump file. Exports dump file contains both metadata and data. Metadata refers to the Data Definition Language (DDL) statements necessary to recreate the objects that have been exported.

To migrate data between oracle databases, within oracle databases, different tablespaces, to change ownership of objects, Oracle has provided with utilities called Exports and Imports. Exports and Imports are mainly used for data reorganization, which leads to greater performance. Exports and imports are also used as a logical backup tool.

New features in oracle 8i enable us to migrate data between different tablespaces. This utility is used to migrate data between higher versions, releases of Oracle used for repeating test runs (large sample data) for development environment, moving data from testing to development. Also used to archive large historical data, migrating data from one O/S platform to another O/S platform.

Some of the important uses of the export utility include the following:

1. Copying tables or entire schemas, from one database to another.
2. Reorganizing a table by exporting the data, recreating the table with different storage parameters, and reloading the data-all in the same database.
3. Storing data as secondary backup
4. Creating a logical backup that you can use to restore specific tables rather than the entire database.
5. Creating the temporary backup of objects that you are going to delete.

The various operations that are to be known in order to use effectively are:

• Starting the export utility
• Passing parameters to it
• Running it interactively
• Getting help when you need it
• Using its prerequisites

      Using Export prerequisites

To use the Export utility, a user must have the CREATE SESSION privilege on the target database. That’s all you need as long as you are executing objects that you own. To export tables owned by another user or to export the entire database, you must have the EXP_FULL_DATABASE role, and you must have it enabled. Typically, you will have the DBA role, which includes the EXP_FULL_DATABASE role, so you can export pretty much anything that you want to export.

Before using Export against a database, you must run the CATEXP.SQL script once to create views and tables that the Export utility requires. The EXP_FULL_DATABASE role is one of the items that CATEXP.SQL creates. The CATEXP.SQL script is run by CATALOG.SQL, so if you ran CATALOG.SQL when you first created the database, you are all set. If you find that you do need to run either of these scripts, you’ll find them in the  $ORACLE_HOME/RDBMS/ADMIN directory.

Create Directory

CONN SYS AS SYSDBA

SQL> CREATE OR REPLACE DIRECTORY EXPORTDR AS ‘E:\AXPDUMP’;

SQL> GRANT READ, WRITE ON DIRECTORY EXPORTDR TO CORPORATE;

Schema Export and Import

• Below is syntax for export & import individual Schema. 

expdp system/admin@orcl schemas=CORPORATE directory=EXPORTDR dumpfile=CORPORATE.DMP logfile=corporate.log

impdp system/admin@orcl schemas=CORPORATE directory=EXPORTDR dumpfile=CORPORATE.DMP logfile=corporate.log

Tables Export and Import

• Below is syntax for export & import individual tables.  

expdp corporate/corporate@orcl tables=stockvalue,saleinvoices directory=EXPORTDR dumpfile=stockvaluesaleinvoices.dmp logfile=stockvaluesaleinvoices.log

impdp corporate/corporate@orcl tables=stockvalue,saleinvoices directory=EXPORTDR dumpfile=stockvaluesaleinvoices.dmp logfile=stockvaluesaleinvoices.log

Table Export and Import From Full Backup With include and exclude

• The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import.

impdp SYSTEM/admin@db schemas=CORPORATE include=TABLE:"IN ('STOCKVALUE', 'SALESINVOICE')" directory=EXPORTDR dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log

impdp SYSTEM/admin@db schemas=CORPORATE exclude=TABLE:"= 'EMPLOYEE'" directory=EXPORTDR dumpfile=CORPORATE.dmp logfile=CORPORATE.log

Full Database Export and Import

• Below is syntax for export & import Full Database.  

expdp system/admin@orcl full=y directory=EXPORTDR dumpfile=fulldatabase.dmp logfile=fulldatabase.log

impdp system/admin@orcl full=y directory=EXPORTDR dumpfile=fulldatabase.dmp logfile=fulldatabase.log

Database Export and Import with Network_Link

Below are steps for Import & Export with Network_Link.Make sure network should be available till finish the Job.

SQL> create public database link "DEV" connect to SYSTEM

identified by "admin"

using 'DEV'

Database link created.

Import with Network_Link

impdp system/admin@test LOGFILE=15102013_2120-system.txt NETWORK_LINK=DEV schemas=CORPORATE directory=DATA_PUMP_DIR

;;; 

Import: Release 11.2.0.3.0 - Production on Tue Oct 15 21:20:05 2013

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

;;; 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@test LOGFILE=15102013_2120-system.txt NETWORK_LINK=PROD schemas=CORPORATE directory=DATA_PUMP_DIR 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 46.12 GB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"CORPORATE" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "CORPORATE"."MVW_SALESREGISTER"            21857513 rows

. . imported "CORPORATE"."STOCKVALUE"                   29229785 rows

. . imported "CORPORATE"."MVW_COSTOFGOODS_V2"           27764400 rows

. . imported "CORPORATE"."SALESINVOICEHDR"              12856752 rows

. . imported "CORPORATE"."SALESINVOICEDTL"              27722234 rows

. . imported "CORPORATE"."TB_COSTOFGOODS2_2011"         5650656 rows

. . imported "CORPORATE"."MVW_COSTOFGOODS_V3"           12301907 rows

. . imported "CORPORATE"."TB_COSTOFGOODS2_2012"         12301893 rows

. . imported "CORPORATE"."STOCKBATCHVALUE_V2"           15365374 rows

. . imported "CORPORATE"."MVW_STOCKBATCHVALUE_2012"     12248286 rows

. . imported "CORPORATE"."STOCKBATCHVALUE_2012"         12248288 rows

. . imported "CORPORATE"."SALESSUMMARY"                 5050365 rows

. . imported "CORPORATE"."STOCKBATCHVALUE"              9779245 rows

. . imported "CORPORATE"."MVW_STOCKBATCHVALUE"          9779245 rows

. . imported "CORPORATE"."SALESCLOSINGDTL"              12207626 rows

. . imported "CORPORATE"."STOCKBATCHVALUE_V2_2011"      5596522 rows

. . imported "CORPORATE"."RECVPKTS"                     1991476 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/JOB

Processing object type SCHEMA_EXPORT/REFRESH_GROUP

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 00:31:12

Export with Network_Link

expdp system/admin@test LOGFILE=15102013_2120-system.txt NETWORK_LINK=DEVschemas=CORPORATE directory=DATA_PUMP_DIR

;;; 

Export: Release 11.2.0.3.0 - Production on Thu Feb 6 20:00:01 2014

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

;;; 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  SYSTEM/********@TEST version=11.1.0 directory=DATA_PUMP_DIR dumpfile=06022014_2000-CORPORATE.dmp logfile=06022014_2000-CORPORATE.txt 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 39.61 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/JOB

Processing object type SCHEMA_EXPORT/REFRESH_GROUP

. . exported "CORPORATE"."MVW_COSTOFGOODS_V2"            4.576 GB 31590861 rows

. . exported "CORPORATE"."TB_COSTOFGOODS2_2011"          817.7 MB 5650656 rows

. . exported "CORPORATE"."SALESINVOICEHDR"               1.918 GB 6789553 rows

. . exported "CORPORATE"."MVW_SALES13"                   1.795 GB 13058630 rows

. . exported "CORPORATE"."SALESINVOICEDTL"               1.812 GB 14800698 rows

. . exported "CORPORATE"."TB_COSTOFGOODS2_2012"          1.765 GB 12301893 rows

. . exported "CORPORATE"."STOCKBATCHVALUE_2013"          1.305 GB 13628992 rows

. . exported "CORPORATE"."MVW_STOCKBATCHVALUE"           1.305 GB 13628992 rows

. . exported "CORPORATE"."STOCKBATCHVALUE"               1.305 GB 13628992 rows

. . exported "CORPORATE"."MVW_STOCKBATCHVALUE_2012"      1.171 GB 12248286 rows

. . exported "CORPORATE"."SALESSUMMARY_BKUP30122013"     1.047 GB 5636356 rows

. . exported "CORPORATE"."SALESSUMMARY_TEMP"             1.029 GB 5535489 rows

. . exported "CORPORATE"."SALESCLOSINGDTL"               437.1 MB 6599208 rows

. . exported "CORPORATE"."RECVPKTS"                      272.1 MB 2759249 rows

. . exported "CORPORATE"."MVW_SALESSUMMARY"              505.7 MB 5804784 rows

. . exported "CORPORATE"."MVW_STOCKVALUESUM"             444.1 MB 2720793 rows

. . exported "CORPORATE"."STOCKSUMMARY"                  404.1 MB 7191161 rows

. . exported "CORPORATE"."STOCKSUMMARY_OLD"              375.0 MB 6676708 rows

. . exported "CORPORATE"."MVW_STOCKSUMMARY"              339.4 MB 6653335 rows

. . exported "CORPORATE"."MVW_PENDINGCRFSTATUS"          304.3 MB  506551 rows

. . exported "CORPORATE"."SENDPKTS"                      85.88 MB  975402 rows

. . exported "CORPORATE"."ACCOUNTSHDR"                   123.6 MB  465284 rows

. . exported "CORPORATE"."TB_COSTOFGOODS2_2013"          268.4 MB 1930988 rows

. . exported "CORPORATE"."STOCKVALUE":"P0_2014"          221.8 MB 1398622 rows

. . exported "CORPORATE"."PRICELIST"                     217.7 MB 1370344 rows

.

.

.

.

.

.

Master table "CORPORATE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for CORPORATE.SYS_EXPORT_SCHEMA_01 is:

Job "CORPORATE"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:33:17

Please share your valuable Feedback...

ORA-00322: log 3 of thread 1 is not current copy

Below are steps to resolve an Issue

Error details:

SQL> startup

ORACLE instance started.

Total System Global Area 701046856 bytes

Fixed Size 454728 bytes

Variable Size 377487360 bytes

Database Buffers 321912832 bytes

Redo Buffers 1191936 bytes

Database mounted.

Errors in file e:\app\cabinda02\diag\rdbms\orcl\orcl\trace\orcl_m000_3660.trc:

ORA-00322: log 3 of thread 1 is not current copy

ORA-00312: online log 3 thread 1: 'E:\APP\CABINDA02\ORADATA\ORCL\REDO03.LOG'

Solution:

SQL> shut immediate

SQL> startup mount

ORACLE instance started.

Total System Global Area 2538741760 bytes
Fixed Size                  2257872 bytes
Variable Size             889195568 bytes
Database Buffers         1627389952 bytes
Redo Buffers               19898368 bytes

Database mounted.

SQL> recover database using backup controlfile;

Apply all the redolog files ( specify entire redolog file location path ) until success the media recovery.After Media recovery is completed open the database with RESETLOGS option.

SQL> ALTER DATABASE OPEN RESETLOGS;

Please share your feed back.

Database Manual Creation.

 Database Manual Creation

           Dear Friends follow the below steps for Database manual creation means without using DBCA command. Follow the below step by step without fail and change the file system as per your requirement.

           Install the Oracle Home.

For Installation of Oracle Home below steps to be followed.

Ø  Go to the DUMP location and click on setup.exe

 Should maintain the above directory structure.

Ø  Click NEXT…….

Ø   Click NEXT…..

Ø   Click on Install….

Ø  Wait For Some time to be finished.

Ø  Click on Exit.

Now Oracle Home is Ready to use. Based on this Oracle HOME we are going to be created Database manually. Below are steps for Database Manual Creation.

Manual DB Creation

Don’t for get to open the cmd prompt as Administrator.

Click on Start ->cmd ->Run as Administrator

ü  C:\>set ORACLE_HOME=E:\app\Administrator\product\11.1.0\db_1

ü  C:\>set PATH=%ORACLE_HOME%;%PATH%

ü  C:\>set ORACLE_SID=ORACLE

Create below DUMP Directories required …………

ü  C:\>mkdir E:\app\Administrator\admin\adump

ü  C:\>mkdir E:\app\Administrator\admin\dpdump

ü  C:\>mkdir E:\app\Administrator\admin\pfile

ü   C:\>mkdir E:\app\Administrator\diag

ü   C:\>mkdir E:\app\Administrator\flash_recovery_area

ü   C:\>mkdir E:\app\Administrator\oradata

ü  C:\>mkdir E:\app\Administrator\oradata\ORACLE

=======================================

Create the parameter file (Pfile)

=========================================

below are minimum requirement for  DB creation. Based on the oracle version we have to change compatible ='11.1.0'.In this scenario our oracle version 11.1.0. & make sure that file extension is initoracle.ora

db_name='ORACLE'

db_block_size=8192

memory_target=500m

processes=100

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

compatible ='11.1.0'

audit_trail ='db'

db_recovery_file_dest_size=5g

db_recovery_file_dest='E:\APP\ADMINISTRATOR\flash_recovery_area'

audit_file_dest='E:\APP\ADMINISTRATOR\admin\adump'

diagnostic_dest='E:\APP\ADMINISTRATOR\diag'

control_files = ('E:\APP\ADMINISTRATOR\oradata\control1.ctl', 'E:\APP\ADMINISTRATOR\oradata\control2.ctl', 'E:\APP\ADMINISTRATOR\oradata\control3.ctl')

========================================================================

=======================

Create a Windows service

==============================

C:\>oradim –new –sid oracle  startmode auto 

Instance created.

C:\>sc query oracleserviceORACLE

•  Connect to instance and create SPFILE

C:\>sqlplus

SQL> create spfile from pfile=’ E:\app\Administrator\admin\pfile\initoracle.ora’;

SQL> starup nomount;

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             314573352 bytes

Database Buffers          201326592 bytes


Redo Buffers                5832704 bytes

==========================================
 Execute the CREATE DATABASE Command

==========================================

CREATE DATABASE oracle
    USER sys IDENTIFIED BY cloud12c@
    USER system IDENTIFIED BY cloud12c@
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 500
LOGFILE
GROUP 1 ('E:\app\Administrator\oradata\oracle\redo01.log','E:\app\Administrator\oradata\oracle\redo02.log') SIZE 50M,
GROUP 2 ('E:\app\Administrator\oradata\oracle\redo03.log','E:\app\Administrator\oradata\oracle\redo04.log') SIZE 50M,
GROUP 3 ('E:\app\Administrator\oradata\oracle\redo05.log','E:\app\Administrator\oradata\oracle\redo06.log') SIZE 50M
DATAFILE 'E:\app\Administrator\oradata\oracle\system01.dbf' SIZE 300M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'E:\app\Administrator\oradata\oracle\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'E:\app\Administrator\oradata\oracle\undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\app\Administrator\oradata\oracle\temp01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8;

========================

Create data dictionary objects

========================

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> connect system/system

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Please share your valuable feedback....

Create New Database Using DBCA With Silent Option

Create New Database Using DBCA With Silent Option

Below are the steps for how to create Database Manual.

• Make sure you have no existing database entry in /etc/oratab file for the SID you intend to create
• Make sure you have no initSID.ora under the $ORACLE_HOME/dbs
• Construct the dbca command line:
• Make sure you have no existing Listener & TNS entries.
• Make to be created all the directory structure as required.

How to Configuring the FRA

Configuring the FRA is easy,by default Flash Recovery area is ORACLE_BASE.First you create the base directory of the FRA from the OS, and then you set the following parameters in any database that will use the FRA:

 

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

The DB_RECOVERY_FILE_DEST parameter defines the FRA base directory location. This

is the only directory you will need to create when configuring the FRA. You will need to

make sure that this directory is owned by the owner of the Oracle executable so that Oracle

can create other subdirectories beneath it.

The DB_RECOVERY_FILE_DEST_SIZE parameter defines the total amount of space for this

database instance is allowed to consume in the FRA. This is a logical limit, which can be

greater than or less than the actual physical limit of space on that device. For example, you

may have a file system with 500GB of space available on it. However, you may want to

indicate that your database can consume only up to 100GB of space within the FRA while

assigning the FRA to the 500GB file system.

You use the alter system command to configure the FRA. Note that DB_RECOVERY_

FILE_DEST is not dynamic, while DB_RECOVERY_FILE_DEST_SIZE is dynamic. To configure the FRA, do the following:

1. Create the base FRA directory:

E:\>mkdir \oracledb\flashback

E:\>

2. Log into SQL*Plus:

E:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 25 15:53:42 2014

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

Enter user-name: sys@test as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Now use the alter system command to set the parameter DB_RECOVERY_FILE_DEST to

\oracledb\flashback and DB_RECOVERY_FILE_DEST_SIZE to 400GB.

sql>alter system set db_recovery_file_dest_size=400GB;

sql>alter system set db_recovery_file_dest=’ \oracledb\flashback’ scope=spfile;

4. Now shut down and restart the database. Once the database has been restarted, the

FRA will become operational.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2538741760 bytes

Fixed Size                  2257872 bytes

Variable Size             889195568 bytes

Database Buffers         1627389952 bytes

Redo Buffers               19898368 bytes

Database mounted.

Database opened.

SQL>