Oracle FAQ Registration News

Hi Friends,

This is to inform you that recently we had registered our blog at Orafaq.com website which is known as Oralce FAQ.

- http://www.orafaq.com/aggregator/sources/429
- http://www.orafaq.com/wiki/Blogs

Thanks,
IngressIT

How to trace archive log

As mentioned in the below table Tracing the archive log file is done at system level.

Oracle Database Version	Data Type	Default Value	Alter Session	Alter System
9.0.1	Integer	0	No	Immediate
9.2.0	Integer	0	No	Immediate
10.1.0	Integer	0	No	Immediate
10.2.0	Integer	0	No	Immediate

We can generate the trace file with different level. 

Below table will provide you the description of different level.

Trace Level Description 0 Disabled (default) 1 Track archival of redo log file 2 Track status of each archivelog destination 4 Track archival operational phase 8 Track archivelog destination activity 16 Track detailed archivelog destination activity 32 Track archivelog destination parameter changes 64 Track ARCn process state activity 128 Track FAL server related activities 256 Track RFS Logical Client 512 Tracks LGWR redo shipping network activity 1024 Tracks RFS physical client 2048 Tracks RFS/ARCn ping heartbeat 4096 Tracks real-time apply activity 8192 Tracks Redo Apply activity (media recovery or physical standby)

I would like to request all the reader's to comment on the post and give me suggestion if you find so...

Thanks,

IngressIT

New Poll is Added to the Blog

Hi Friends,

New Poll is added to my blog http://imergegroup.blogspot.com on the New Year. I would like to request you to vote on it.

Thanks,
IngressIT

Happy New Year...

Happy new year to you and your family.

Please note 1 extra second is added to year 2008. So please adjust your watches / Desktop (other gadgets) timings today. (Earth revolving speed reduced may be because of recession - that means recession started showing its IMPACT on Solar system also !!!!) This may be good indication for all of us. Nature is giving us 1 extra second to plan/push ourselves and make our dream come true in the year 2009.

Thanks,
IngressIT

Flushing Shared Pool and Buffer Cache

Flushing SGA memory areas, Shared Pool and Buffer Cache are the uncommon task that comes to a DBA. Yesterday night when I was going through Oracle Documentation I found the command for Flushing the shared pool. And on the next moment I thought for Buffer cache.

Shared Pool flush

For Oracle 9i and 10g (Need to test it on 11g)
Alter system flush shared_pool

Buffer Cache flush

For Oracle 9i
Alter session set events = 'immediate trace name flush_cache';

Here I found the statement and tried it on my test database and it worked fine.

For Oracle 10g, the syntax is is as follows:
alter system flush buffer_cache;

I would like to thanks you all for reading and posting comments to my post.

Enhancement to DBCA in Oracle 11g

1) Support to configure new database options Starting from Oracle Database 11g; we can configure the following options using Database Configuration Assistant:

a) Oracle Application Express

b) Oracle Database Vault

c) Oracle Warehouse Builder.

These options can be selected while creating database using DBCA. As these options was not available in Oracle Database 10g.



2) Automatic memory management option ( Memory_Target) In Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You mention only the total memory size to be used by the instance and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. To do so, you set only a target memory size initialization parameter (MEMORY_TARGET). Typical is the default and it uses MEMORY_TARGET instead of specifying individual values for SGA_TARGET and PGA_AGGREGATE_TARGET. With Custom, you can select PGA and SGA individually.



3) Oracle Data Mining option is made default In Oracle Database 11g, you no longer configure this option through the Database Features screen (Database components) of Database Configuration Assistant. The data mining schema are created when you run the catproc.sql script as the SYS user. While in Oracle 10g, while creating a Custom database using DBCA we need to select the Oracle Data Mining option in the Database components section In Oracle 11g, we need not configure the Data mining option and the data mining schema are created when catproc.sql is executed.



4) ORACLE_BASE and Diagnostic Destination configuration The directory that you specify when you are prompted for ORACLE_BASE by Oracle Universal Installer is stored in the Oracle home inventory. Database Configuration Assistant uses this value to derive the default database locations and the DIAGNOSTIC_DEST parameter. The diagnostic destination location contains all ADR directories (diagnostic files such as Alert logs and so on). Starting with Oracle Database 11g Release 1 (11.1), the initialization parameter settings for background dump, user dump, and core dump destinations are replaced by the Diagnostic Destination. The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located. The structure of the directory specified by DIAGNOSTIC_DEST is as follows: /diag/rdbms//
This location is known as the Automatic Diagnostic Repository (ADR) Home.



5) Secure database configuration Oracle Database 11g has new defaults for audit and password profiles. Database Configuration Assistant has a new screen to enable the new security settings during the database creation and existing database configuration.

During Database creation, dbca gives the following 2 options:

- Keep the enhanced 11g default security settings(recommended)

- Revert to pre-11g default security settings Under second option( pre-11g settings), there are 2 categories,

-Revert Audit settings to pre-11g defaults

-Revert password profile settings to pre-11g defaults



You can choose any or both of these 2.



Audit settings:



Oracle recommends that you enable these default security settings. When you enable the default security settings, Oracle Database audits some of the security-relevant SQL statements and privileges. It also sets the AUDIT_TRAIL initialization parameter to DB. Oracle Database audits the AUDIT ROLE SQL statement by default. The privileges that are audited by default are as follows:



ALTER ANY PROCEDURE/TABLE

ALTER DATABASE/PROFILE/SYSTEM/USER

AUDIT ROLE BY ACCESS AUDIT SYSTEM

AUDIT SYSTEM BY ACCESS

CREATE SESSION/USER

CREATE ANY JOB/LIBRARY/PROCEDURE

CREATE ANY TABLE EXEMPT ACCESS POLICY

CREATE EXTERNAL JOB

CREATE PUBLIC DATABASE LINK

DROP PROFILE/USER

DROP ANY TABLE/PROCEDURE

GRANT ANY PRIVILEGE/ROLE

GRANT ANY OBJECT PRIVILEGE



Oracle Database also audits all privileges and statements BY ACCESS in one statement. If you are concerned that the auditing of these statements and privileges will adversely affect your applications, you can disable this auditing in the Security Settings window of Database Configuration Assistant.



6) Switching a database from Database Control to Grid Control configuration In previous releases, Database Configuration Assistant contained functionality to configure a database either with Database Control or with Grid Control. You could configure a database either while creating it or later. However, reconfiguring a database from Database Control to Grid Control required significant manual effort. With Oracle Database 11g, Database Configuration Assistant provides the Enterprise Manager Configuration plug-in, which automates the process to switch configuration of a database from Database Control to Grid Control.



7) Automatic Maintenance tasks
This option is added in 11g and comes in at the time of the database creation. Oracle Database 11g provides the ability to automatically manage maintenance tasks such as optimizer statistics collection and proactive advisor reports. These tasks are run in a predefined maintenance window and their CPU consumption is throttled to prevent them from interfering with normal user work. The default maintenance windows are 10:00 PM - 2:00 AM on weekdays. And all weekend long. These defaults can be changed using Enterprise Manager at any time.



I hope this will be helpful information to all. The above notes is prepared with the help of metalink