Bugman ( RMAN ) validate restore “bug”

August 28, 2009 by oraclue 1 Comment

If you are using RMAN (BUGMAN) like I do then you probably used VALIDATE command.Validate command is used to verify integrity of backups
without performing backups etc.

There are some “issues” with validate command.Actually Oracle intentionally did it but nevertheless it can be potential problem if you do not pay attention to it.

Let me start with two validate command:

restore database validate and  restore database validate check logical

First command validate physical backups while second command check logical corruption only.

If you are validating your backups using these commands then you are missing files needed for database recovery or archivelogs.

Restore database validate command only validates that all DATAFILES  that can be restored.ARCHIVELOGS ARE NOT INCLUDED.

Archivelog files are used for recovery and this command does not validate them. Read more of this post

Filed under Backup&Recovery

Tracking DDL changes in 11g

August 20, 2009 by oraclue 6 Comments

As you know Oracle provides DDL triggers to track changes to the database.Data from these triggers are used for auditing, change controls etc.

But Oracle 11g also provides specific event for tracking DDL changes.It uses target called SQL_DDL.

These events can be enabled on session or system level.This is shortest command:

SQL> alter session set events ‘trace[SQL_DDL]‘;

and to turn off:

SQL>  alter session set events ‘trace[SQL_DDL] off’;

System level:

SQL> alter system set events ‘trace[SQL_DDL]‘;

System altered.

SQL>  alter system set events ‘trace[SQL_DDL] off’;

This command can be extended to trace disk , memory or use different resolution etc.List is below:

[disk=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[memory=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[get_time=DISABLE | DEFAULT | SEQ | HIGHRES | SEQ_HIGHRES ]

[get_stack=DISABLE | DEFAULT | ENABLED ]

For my test I have created simple logon trigger  whic will trace only my userid after logon on database:

CREATE OR REPLACE TRIGGER SYS.trace_ddl
after logon on database
begin
if user like ‘MILADIN’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set tracefile_identifier=”MILADIN”’;
execute immediate ‘alter session set events ”trace [SQL_DDL]”’;
end if;
end;
/

Here are few examples.

First trace is coming from  create statement.These traces can be used not only to track DDL changes but they are also very
educational especially if you interested into Oracle internals. Read more of this post

Filed under Internals

Issues with upgrade to 11g

August 13, 2009 by oraclue 5 Comments

I was doing  manual upgrade from version 10.2.0.3 to 11.1.0.7 .Really  did not expect any big issues since I did it many times.So after doing all pre-upgrade steps ( or I taught I did all of them)

I started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2160112 bytes
Variable Size            1946159632 bytes
Database Buffers         4429185024 bytes
Redo Buffers               36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>#
   FROM registry$database
        *
ERROR at line 2:
ORA-00942: table or view does not exist

Upgrade failed! Since I hit same error in the past I knew where is the problem and expected to fix it quickly.

This  error is related to timezone file  which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.

Query to check timezone file  is:

SQL> select * from v$timezone_file;

FILENAME        VERSION
———— ———-
timezlrg.dat          4

so I pointed database to old oracle home and started it:
-sh-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Aug 11 11:12:41 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2082376 bytes
Variable Size            1577060792 bytes
Database Buffers         4848615424 bytes
Redo Buffers               14692352 bytes
ORA-00201: control file version 11.0.0.0.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file:
‘/oracle/demo/control/copy_1/control01.ctl’   Read more of this post

Filed under Troubleshooting