Tag: Alert log

Checkpoint Not Complete

Posted on by Osama Mustafa in Database
Error Will be In AlertSID.log Like This :

/u01/app/oracle/oradata/redo04.log
Thu Jan 14 22:12:55 2011
Thread 1 cannot allocate new log, sequence 46352
Checkpoint not complete

To Solve this Issue you Can Do More Than One Thing But All of the Solution is Simple :

Solution One :

Modify Database Parameter archive_lag_target Like This :

alter system set archive_lag_target=0 scope=both;

Solution two :

1-backup Full Database
2-Check Free Disk Space
3-Start Do the Following :

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#, status from v$log;

Now You need to Work Step by Step On Inactive Logs , and Stat Drop Them like the following :

alter database drop logfile group 1;

Re add the Log File

alter database add logfile group 1 ( ‘/Log-Name01.log‘,‘/Log-Name02.log’ ) size 75M

alter system switch logfile; / Alter System Checkpoint

 Do this For The Group that you have , and rearrange them again By Adding new Group contain more than one redo log inside them

Simple !!!

Enjoy
Osama Mustafa

Tracking Alert Log file by x$dbgalertext

Posted on by Osama Mustafa in Database
First i would thank CKPT for this amazing article that post in his blog .

Instead of reviewing whole alert log file for any Particular information, We can search using that key word from X$DBGALERTEXT Table.

Grep The word “Starting Up” of Alert log file from Instance. 



col ORIGINATING_TIMESTAMP for a40
col MESSAGE_TEXT for a80
set linesize 500
SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Starting up%';

Grep The word “Instance shutdown complete” of Alert log file from Instance.

SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Instance shutdown complete%';

again thank you CKPT for this article since its useful for all DBA’s , you can check CKPT Blog From the above link

Thank you
Osama mustafa
 

SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Posted on by Osama Mustafa in Database
Oracle Database Resource Manager (the Resource Manager) : 
enables you to optimize resource allocation among the many concurrent database sessions.

When database resource allocation decisions are left to the operating system, you may encounter the following problems:

  • Excessive overhead.

Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

  • Inefficient scheduling

 The operating system deschedules database servers while they hold latches, which is inefficient.

  • Inappropriate allocation of resources

The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Check The Resource Manager On your Database :

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN
                                                 TENANCE_PLAN

All this is Introduction about the Error that appear when you upgrade to 11g , you will getting the following messages in the alert.log.

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution to  Disable the resource manager , Steps to Do this :

1-set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both 

2-change the active windows to use the null resource manager plan 

    execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
    execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3-run :

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

You Can Also Disable SQL tuning By :

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


Thank you
Osama Mustafa