ORA-00600: internal error code, arguments: [723], [248344], [248344], [memory leak]

Solution:

1. For few leaks size: Ignore this ORA-600 [723] as it is more of a housekeeping message and is not really a cause for concern.

OR

2. In order to avoid the ORA-00600[723] , you can set the 10262 event;
SEE: (Doc ID 39308.1) ORA-600 [723] "PGA memory leak"

As a workaround, use event 10262 to suppress the error check. Set event 10262 at a level greater than the size of the leaked memory, e.g.:

-- > your error message: ORA-00600: internal error code, arguments: [723], [248344], [248344], [memory leak], [], [], [], [] <<<<<< LEAK 248344 ... 250Ko.

SQL> connect / as sysdba
SQL> alter system set events '10262 trace name context forever, level 300000';

This prevents the leak being reported which is not serious and is only signalled on logging out the session before freeing the memory, and so no memory is wasted.

OR

3. Apply the latest Patchset 10G Release2 database version (102050) which is fixing this issue. 

Patch Link: https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=8202632

Troubleshooting ORA-600 [723] "PGA memory leak

PURPOSE: This article discusses the internal error "ORA-600 [723]", what it means and possible actions. The information here is only applicable to the versions listed and is provided only for guidance.

ERROR: ORA-600 [723] [a] [b]

VERSIONS: versions 6.0 to 11.1

DESCRIPTION: This is a memory leak in the Program Global Area (PGA) PGA is checked for Space leaks at logoff time and a leak was found. There is no data corruption with this error.

ARGUMENTS: Arg [a] Logoff PGA size in bytes Arg [b] "memory leak"

FUNCTIONALITY: MEMORY COMPONENT

IMPACT:

PROCESS FAILURE - but only during session delete so impact is minimal

NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS: Event 10262 can be set to safely ignore small memory leaks.

Set the following in init.ora for example to disable space leaks less than 4000 bytes

event = "10262 trace name context forever, level 4000"

and stop and restart the database. Repeated errors can be diagnosed further by sending the alert.log and trace files to Oracle Support Services.

Known Issues:

Bug# 6749617

See Note 6749617.8 OERI[723] of "Global Blockers" memory on disconnect

Fixed: 10.2.0.5

Bug# 5891737

See Note 5891737.8Dump (kcblsod) / OERI:723 / Memory leak in ASM/RAC

Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.3.P17

source : http://www.scribd.com/doc/19212023/troubleshooting-ora00600-argument-723-memory-leak

Table Fragmentation

http://www.orafaq.com/node/1936


"dbms_redefinition"

SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)
----------
100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb


SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb

SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

Empty Oracle Listener.log files

The listener.log file grows and it’s not possible to just move the file whilst the listener is up.  The tnslsnr process always holds this log file.

Procedure 1:

LSNRCTL>  set Log_status off

$ mv listener.log  listener_old.log

$  touch listener.log

LSNRCTL>  set Log_status on

Procedure 2: Create batch script like below and schedule to run once a week

$ more truncate_listener.sh

cd /u00/app/oracle/product/10.2.0/network/log/

cp listener.log /u00/app/oracle/product/10.2.0/network/log/backup/listener_`date '+%Y%m%d'`.log

echo > listener.log

Procedure 3: Stop Listener logs

set LOGGING_LISTENER=OFF in the listener.ora file to stop the listener logging.

Auditing FGA:

create user adlfga identified by adlfga;
grant resource,connect to adlfga;

BEGIN DBMS_FGA.ADD_POLICY(      object_schema => 'AE001T3',
object_name => 'ID_TICKET_DETAILS',
policy_name => 'TICKET_LPO_POLICY',
audit_condition => NULL,
audit_column => 'TICKET_LPO',
enable => true,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’
);
end;

conn adl_fga_handler/adl_fga_handler;
create table audit_event 2 (audit_event_no number);

create or replace procedure sp_audit(object_schema in varchar2,object_name in varchar2,policy_name in varchar2)ascount number;

begin
select nvl(max(audit_event_no),0) into count from audit_event;
insert into audit_event values (count+1); commit;

end;

select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='TICKET_LPO_POLICY';

How to Check the version of and Update opatch utility

Checking the opatch utility version:
$ cd $ORACLE_HOME/OPatch
$ opatch lsinventory

Invoking OPatch 10.2.0.4.3
Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/10.2.0
Central Inventory : /u00/app/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/oui
Log file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2013-05-27_16-24-15PM.log
Lsinventory Output file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2013-05-27_16-24-15PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch  8350262      : applied on Thu Nov 22 14:55:53 GMT-04:00 2012
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262
--------------------------------------------------------------------------------
OPatch succeeded.



How to update the opatch utility?
Go to :
https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?release=80102000&plat_lang=46P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=13116067&patch_num=6880880&patch_num_id=979663&default_release=80102000&default_plat_lang=23P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0&sortcolpressed=&tab_number=

Description OPatch 10.2
Product Universal Installer
Select a Release : ORACLE 10.2.0.0.0
Platform or Language   : ORACLE SOLARIS  ON SPARC(64-BIT)
Last Updated 07-NOV-2010
Size 27M (28674793 bytes)
Entitlement Class   Software
Classification   General

Download the patch
Transfer the patch p6880880_102000_SOLARIS64.zip to remote server and unzip it

Backup the existing OPatch directory
$ mv OPatch/ opatch_backup

$ mv p6880880_102000_SOLARIS64.zip $ORACLE_HOME
$ unzip_sparc p6880880_102000_SOLARIS64.zip
bash-3.00$ cd OPatch/
bash-3.00$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/10.2.0
Central Inventory : /u00/app/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/oui
Log file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2013-05-27_16-33-16PM.log
Patch history file: /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2013-05-27_16-33-16PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch  8350262      : applied on Thu Nov 22 14:55:53 GMT-04:00 2012
Unique Patch ID:  13011839
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262
--------------------------------------------------------------------------------
OPatch succeeded.
bash-3.00$

Best Practices for Upgrading to Oracle Database 11g Release 2

http://www.oracle.com/technetwork/database/upgrade/best-practices-for-upgrading-11gr2-174946.pdf

Oracle terms and Ideas you need to know before beginning

Oracle Parser

It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it and building (or locating) an executable form of that statement.

Hard Parse

A hard parse occurs when a SQL statement is executed, and the SQL statement is either not in theshared pool, or it is in the shared pool but it cannot be shared. A SQL statement is not shared if the metadata for the two SQL statements is different i.e. a SQL statement textually identical to a preexisting SQL statement, but the tables referenced in the two statements are different, or if the optimizer environment is different.

Soft Parse

A soft parse occurs when a session attempts to execute a SQL statement, and the statement is already in the shared pool, and it can be used (that is, shared). For a statement to be shared, all data, (including metadata, such as the optimizer execution plan) of the existing SQL statement must be equal to the current statement being issued.

Cost Based Optimizer

It generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and then chooses the plan with the lowest cost. This approach is used when the data dictionary has statistics for at least one of the tables accessed by the SQL statements. The CBO is made up of the query transformer, the estimator and the plan generator.

EXPLAIN PLAN

A SQL statement that enables examination of the execution plan chosen by the optimizer for DML statements. EXPLAIN PLAN makes the optimizer to choose an execution plan and then to put data describing the plan into a database table. The combination of the steps Oracle uses to execute a DML statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables i.e. the join order with the appropriate join method.

Oracle Trace

Oracle utility used by Oracle Server to collect performance and resource utilization data, such as SQL parse, execute, fetch statistics, and wait statistics. Oracle Trace provides several SQL scripts that can be used to access server event tables, collects server event data and stores it in memory, and allows data to be formatted while a collection is occurring.

SQL Trace

It is a basic performance diagnostic tool to monitor and tune applications running against the Oracle server. SQL Trace helps to understand the efficiency of the SQL statements an application runs and generates statistics for each statement. The trace files produced by this tool are used as input for TKPROF.

TKPROF

It is also a diagnostic tool to monitor and tune applications running against the Oracle Server. TKPROF primarily processes SQL trace output files and translates them into readable output files, providing a summary of user-level statements and recursive SQL calls for the trace files. It also shows the efficiency of SQL statements, generate execution plans, and create SQL scripts to store statistics in the database.

Object Statistics Are Locked

ORA-38029 "Object Statistics Are Locked" - Possible Causes [ID 433240.1]

Cause: Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.

SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true);

BEGIN dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 13437

ORA-06512: at "SYS.DBMS_STATS", line 13457

ORA-06512: at line 1

This query shows that the table statistics are locked.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Unlock it

Sql> exec dbms_stats.unlock_schema_stats('AE1T3OBY');

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Now gather the table statistics

SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => TRUE);

PL/SQL procedure successfully completed.

Gathering statistics sample:

begin
dbms_stats.gather_table_stats( ownname=> 'AE1T3', tabname=> 'ID_NUMBER_GEN_SOURCE' , estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=> DBMS_STATS.AUTO_CASCADE, degree=> null, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;

Oracle Database 10g: Analyze Index Vs. Gather_Index_Stats

 One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command it updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10185	2515695

SQL> analyze index STK_UKEY estimate statistics;

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10184	2515448

SQL> EXEC DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');

PL/SQL procedure successfully completed.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	9428	2320245

Conclusion:

Action	INDEX_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
SQL Query	STK_UKEY	2	10185	2515695
analyze index STK_UKEY estimate statistics;	STK_UKEY	2	10184	2515448
DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');	STK_UKEY	2	9428	2320245

Now if we see the numbers of leaf blocks reported are 9428. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So it’s better to use gather_index_stats rather than analyze index.

Hope this helps!!

Reclaiming Segment Space

alter table ae1t3.ID_TICKET_STOCK_MASTER enable row movement;
alter table ae1t3.ID_TICKET_STOCK_MASTER shrink space compact cascade;
alter table ae1t3.ID_TICKET_STOCK_MASTER disable row movement;

Drop 10g Database using RMAN

SQL> startup mount;

SQL> alter system enable restricted session;

$ rman target sys/dupdb@dupdb catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 22 16:53:41 2013

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

connected to target database: DUPDB (DBID=3693079167, not open)

connected to recovery catalog database

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "DUPDB" and DBID is 3693079167

Do you really want to drop all backups and the database (enter YES or NO)? yes

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

251149  251145  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_13_1_813321003

251150  251146  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_14_1_813321069

251170  251167  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_15_1_813321073

251244  251240  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_16_1_813421585

251245  251241  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_17_1_813421589

251246  251242  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_18_1_813421655

251264  251261  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_19_1_813421660

251490  251486  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_1_1_813429205

251491  251487  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_2_1_813429213

251492  251488  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_3_1_813429215

251493  251489  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_4_1_813429281

251514  251512  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_5_1_813429285

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_13_1_813321003 recid=13 stamp=813321007

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_14_1_813321069 recid=14 stamp=813321069

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_15_1_813321073 recid=15 stamp=813321074

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_16_1_813421585 recid=16 stamp=813421586

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_17_1_813421589 recid=17 stamp=813421593

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_18_1_813421655 recid=18 stamp=813421655

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_19_1_813421660 recid=19 stamp=813421661

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_1_1_813429205 recid=1 stamp=813429206

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_2_1_813429213 recid=2 stamp=813429214

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_3_1_813429215 recid=3 stamp=813429216

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_4_1_813429281 recid=4 stamp=813429281

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_5_1_813429285 recid=5 stamp=813429286

Deleted 12 objects

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Archived Log Copies

Key     Thrd Seq     S Low Time  Name

------- ---- ------- - --------- ----

251481  1    694     A 21-APR-13 /zone/drprod/arch1/drprod_1_694_800019715.arc

251482  1    695     A 21-APR-13 /zone/drprod/arch1/drprod_1_695_800019715.arc

251483  1    696     A 22-APR-13 /zone/drprod/arch1/drprod_1_696_800019715.arc

251484  1    697     A 22-APR-13 /zone/drprod/arch1/drprod_1_697_800019715.arc

251460  1    1       A 21-APR-13 /zone/dupdb/arch1/dupdb_1_1_813327814.arc

251467  1    2       A 21-APR-13 /zone/dupdb/arch1/dupdb_1_2_813327814.arc

251473  1    3       A 22-APR-13 /zone/dupdb/arch1/dupdb_1_3_813327814.arc

251485  1    4       A 22-APR-13 /zone/dupdb/arch1/dupdb_1_4_813327814.arc

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_694_800019715.arc recid=4 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_695_800019715.arc recid=5 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_696_800019715.arc recid=6 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_697_800019715.arc recid=7 stamp=813429204

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_1_813327814.arc recid=1 stamp=813327994

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_2_813327814.arc recid=2 stamp=813429138

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_3_813327814.arc recid=3 stamp=813429201

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_4_813327814.arc recid=8 stamp=813429283

Deleted 8 objects

database name is "DUPDB" and DBID is 3693079167

database dropped

database name is "DUPDB" and DBID is 3693079167

database unregistered from the recovery catalog

RMAN>

Alert.log will write the below logs

DROP DATABASE

Mon Apr 22 16:54:02 2013

Deleted file /u01/app/oracle/oradata/dupdb/system01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/undotbs01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/users

Deleted file /u01/app/oracle/oradata/dupdb/redo01.log

Deleted file /u01/app/oracle/oradata/dupdb/redo02.log

Deleted file /u01/app/oracle/oradata/dupdb/redo03.log

Deleted file /u01/app/oracle/oradata/dupdb/redo04.log

Instance terminated by USER, pid = 20258

Deleted file /u01/app/oracle/oradata/dupdb/control01.ctl

Deleted file /u01/app/oracle/oradata/dupdb/control02.ctl

Deleted file /u01/app/oracle/oradata/dupdb/control03.ctl

Completed: DROP DATABASE

Mon Apr 22 16:54:08 2013

Shutting down instance (abort)

License high water mark = 3

Clone / Duplicate a database with RMAN

1.  create parameter file.

SQL> create pfile from spfile;

$ cp initdrprod.ora initadil.ora

2. edit parameter file and replace OLD SID with NEW SID

$ vi initadil.ora

adil.__db_cache_size=176160768 adil.__java_pool_size=4194304 adil.__large_pool_size=4194304 adil.__shared_pool_size=411041792 adil.__streams_pool_size=8388608 *.audit_file_dest='/u01/app/oracle/admin/adil/adump' *.background_dump_dest='/u01/app/oracle/admin/adil/bdump' *.compatible='10.2.0.3.0' *.control_files='/u01/app/oracle/oradata/adil/control01.ctl',                               '/u01/app/oracle/oradata/adil/control02.ctl',                               '/u01/app/oracle/oradata/adil/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/adil/cdump' *.db_block_size=8192 *.db_domain='omeir.global' *.db_file_multiblock_read_count=16 *.db_name='adil' *.dispatchers='(PROTOCOL=TCP) (SERVICE=adilXDB)' *.job_queue_processes=10 *.local_listener='LISTENER_ADIL' *.log_archive_dest_1='location=/zone/archive/adil/' *.log_archive_format='adil_%t_%s_%r.arc' *.open_cursors=300 *.pga_aggregate_target=203423744 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=612368384 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/adil/udump' *.db_file_name_convert='/u01/app/oracle/oradata/drprod/','/u01/app/oracle/oradata/adil/' *.log_file_name_convert='/u01/app/oracle/oradata/drprod/','/u01/app/oracle/oradata/adil/'

3. Create password file:

orapwd file=orapw$ORACLE_SID password=adil entries=50;

====

4. Create dump files:

$ cd /u01/app/oracle/admin/adil/

$ mkdir adump bdump cdump udump dpdump pfile  -- dump directory location

$ cd /u01/app/oracle/oradata/

$ mkdir adil                                                       -- controlfiles/datafiles/relofiles location

$ cd /zone

$ mkdir adil/arch                                             -- archive location.

====

5. Update listener and tnsnames.ora files

====

6.

set ORACLE_SID and start the database in nomount state.

===

7. execute the following duplicate database command ( run from auxiliary db console ‘oracle_sid’)

connect target sys/drprod@drprod

connect catalog rman/rman@catdb

connect auxiliary sys/adil@adil 

run {

set newname for datafile 1 to '/u01/app/oracle/oradata/adil/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/adil/undotbs01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/adil/sysaux01.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/adil/users';

allocate channel c2 type disk;

set until sequence 2 thread 1;

duplicate target database to adil

logfile

  GROUP 1 ('/u01/app/oracle/oradata/adil/redo01.log') SIZE 5M  REUSE,

  GROUP 2 ('/u01/app/oracle/oradata/adil/redo02.log') SIZE 5M  REUSE,

  GROUP 3 ('/u01/app/oracle/oradata/adil/redo03.log') SIZE 5M  REUSE,

  GROUP 4 ('/u01/app/oracle/oradata/adil/redo04.log') SIZE 5M  REUSE;

}

8. Change passwords of sys/system/sysman/dbsnmp (if required)

9. Register database with catalog ( if required)

$ rman target sys/adil@adil catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 22 16:09:37 2013

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

connected to target database: ADIL (DBID=2245812828)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> backup database plus archivelog;

10 Create Enterprise manager Db controle

$ emca -repos drop

$ emca -repos create

$ emca -config dbcontrol db

Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

SOURCE:

 http://oracleinsider.wordpress.com/2009/05/09/encrypting-data-in-oracle-10g-transparent-data-encryption-ted/

Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.

Using any HEX editor e.g. Ultraedit software allows the portion of data to be viewed present in the datafiles which ultimately breaches the security of sensitive company information. The short guide presented below provides steps for setting up a simple encryption strategy on sensitive data columns to prevent access of data from operating system e.g. using hex editors etc.

Steps of setting up Data Encryption

1. Setting up a Wallet
         a. Creating and Opening a Wallet
         b. Closing a Wallet to prevent access
2. Encrypting the desired Data Column
          a. Encrypting with Salt
          b. Encrypting with Non-Default Algorithm
          c. Encrypt for External Tables
          d. Generating a New key for the column
          e. Turning Off Column Encryption
3. Additional Resources

1. Setting up a Wallet

Creating and Opening a Wallet

In order to hold the encryption key of the data columns, a wallet must be created before any column could be encrypted. The parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora file provides the location of where the wallet is to be found. The default location of the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet.

The following command creates and opens up the wallet (using sys account)

• Connect sys/pwd as sysdba
• ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “AnyPassword”;

To reopen the wallet, issue the following command
           • ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “AnyPassword”;

Closing the Wallet to prevent Access

In order to prevent access to sensitive data, the wallet must be closed using the following command

              • ALTER SYSTEM SET WALLET CLOSE;

2. Encrypting the desired Data Column

The default encryption method used in oracle is AES192. In order to encrypt the desired columns, use the ENCRYPT clause after every column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT
) tablespace employees;

If one requires to use any encryption method other than ‘AES192’, then use ‘ENCRYPT USING’ clause within the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT USING ‘3DES168’
) tablespace employees;

ENCRYPT with SALT

For increased encryption security, use the ‘ENCRYPT SALT’ for Salt or ‘ENCRYPT NO SALT’ for No Salt command to specify random encrypted string in the generated key for the desired column.

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT NO SALT,
Pwd varchar2(10) ENCRYPT SALT,
SSN number ENCRYPT
) tablespace employees;

ENCRYPT for External Tables

Specify Encrypt identification key by using ‘ENCRYPT IDENTIFIED BY’ clause in the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN varchar2(11) ENCRYPT IDENTIFIED BY ‘xc7If3t9’
) tablespace employees;

Generating a New key for the column

To re-generate a fresh key for the column, use the following command

• ALTER TABLE employee REKEY
OR
• ALTER TABLE employee REKEY USING ‘3DES168’;

Turning Off Column Encryption

By using the ‘DECRYPT’ clause, the column encryption can later be turned off as below:

• Alter table employees modify(ssn varchar2(11) DECRYPT);

3. For more information visit

• http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php
• http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm

Hope this effort helps in any manner!