Show oracle internally recompiles object in certain cases

December 18, 2010 by Amin Jaffer · Leave a Comment
Filed under: General DBA

We had seen case when the DDL timestamp of a procedure was changed in the middle of the night and one reason we found that was Oracle will try to recompile the store procedure/function/package in certain condition.

Here is a scenerio which shows Oracle recompiles the object
– 1) Create a table named “X” and create a store procedure that references that table
SQL> create table x ( x number );

Table created.

SQL> create or replace procedure x_proc
as
l_var number;
begin
select x into l_var from x;
end;
/

Procedure created.

– 2) Get the status and last_ddl_time of the store procedure
SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
VALID 17-DEC-2010 20:55:22

– 3) Drop the table and the store procedure becomes invalid note last_ddl_time doesn't change of the store procedure
SQL> drop table x;

Table dropped.

– get last_ddl_time of the store procedure
SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:55:22

– 4) Table is being recreated, the LAST_DDL_TIME still remains the same
SQL> create table x ( x number );

Table created.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:55:22

SQL> insert into x values (1);

1 row created.

SQL> commit;

Commit complete.

– 5) Execute the store procedure and it runs successfully and Oracle will recompile the store procedure to see if it make it valid as you see the LAST_DDL_TIME has changed on the store procedure
SQL> exec x_proc;

PL/SQL procedure successfully completed.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
VALID 17-DEC-2010 20:57:14

It is also smart enough to know to try recompile once so i think it somehow keeps track of the dependency
– 6) Table dropped again which will make the store procedure invalid, the LAST_DDL_TIME does not change on the store procedure as expected
SQL> drop table x;

Table dropped.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:57:14

– 7) Execute the store procedure but will fail to execute as the table does not exists so check the LAST_DDL_TIME to verify it was recompiled
SQL> exec x_proc;
BEGIN x_proc; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.X_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:59:25

– 8) Try to execute the store procedure and this time it will not recompile the store procedure as the LAST_DDL_TIME hasn't changed
SQL> exec x_proc;
BEGIN x_proc; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.X_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:59:25

Tags: automatic, execution, last_ddl_time, procedure, recompile, store procedure

Scenario when the controlfile is lost and there was a datafile added

December 12, 2010 by Amin Jaffer · Leave a Comment
Filed under: Backup & Recovery, Control file, General DBA, RMAN

Scenario when the controlfile is lost and there was a datafile added to an existing table space after backup of the controlfile was taken so the controlfile does not know about the new datafile.

1) Start database in nomount state
SQL> connect /as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

2) Connect to rman to restore control file, to restore the controfile it requires setting DBID
$ $ORACLE_HOME/bin/rman

RMAN> SET DBID 1992878807

executing command: SET DBID

run {
restore controlfile from ‘/u01/oradata/TEST/rman/TEST_3llv5k9u_117′;
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 12-DEC-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101212
channel ORA_DISK_1: autobackup found: /u01/oradata/TEST/rman/c-1992878807-20101212-01
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oradata/TEST/control1.ora
output filename=/u01/oradata/TEST/control2.ora
output filename=/u01/oradata/TEST/control3.ora
Finished restore at 12-DEC-10

3) Mount database so one can recover the database
SQL> alter database mount;

Database altered.

4) Recover database (It will issue error about the controlfile does not know about the new datafile)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12645623 generated at 12/12/2010 11:16:24 needed for thread 1
ORA-00289: suggestion : /u01/oradata/TEST/arch/1_43_737334952.dbf
ORA-00280: change 12645623 for thread 1 is in sequence #43

Note here the archive log file specified by Oracle is 1_43_737334952.dbf but it does not exists so the file needed is the redo log so using ls find the redo log file needed in this case is redo3b.log so it is specified when restoring
$ ls -lrt
..
-rw-r—– 1 oracle oinstall 10087936 Dec 11 22:11 1_40_737334952.dbf
-rw-r—– 1 oracle oinstall 10087936 Dec 11 23:56 1_41_737334952.dbf
-rw-r—– 1 oracle oinstall 10087936 Dec 12 11:16 1_42_737334952.dbf

$ ls -lrt redo*.log
total 1518908
-rw-r—– 1 oracle oinstall 10486272 Dec 11 23:56 redo1b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 11 23:56 redo1a.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 11:16 redo2b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 11:16 redo2a.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 19:44 redo3b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 19:44 redo3a.log

So restoring specifying the redo log file redo3b.log

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/TEST/redo3b.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: ‘/u01/oradata/TEST/test_04.dbf’

Note the recovery didn’t start as the control file found a datafile it does not know about.

ORA-01112: media recovery not started

5) As datafile is in mount state V$datafile can be queried it shows the new datafile is called /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/oradata/TEST/system_01.dbf
/u01/oradata/TEST/undo_01.dbf
/u01/oradata/TEST/sysaux_01.dbf
/u01/oradata/TEST/test_01.dbf
/u01/oradata/TEST/test.dbf
/u01/oradata/test_02.dbf
/u01/oradata/TEST/test_03.dbf
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008

6) Shows the datafile does not physical exists
$ ls -l /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
ls: /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008: No such file or directory

7) Trying to recover database again it reports the filename needs to be updated in the controfile before proceeding
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008′
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008′

8) Create datafile from UNNAMED00008 to update controfile
SQL> alter database create datafile ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008′ as ‘/u01/oradata/TEST/test_04.dbf’;

Database altered.

9) Check v$datafile after the datafile is created note the control file knows about the datafile
SQL> select name from v$datafile;

10) Recover the database
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12654692 generated at 12/12/2010 19:43:13 needed for thread 1
ORA-00289: suggestion : /u01/oradata/TEST/arch/1_43_737334952.dbf
ORA-00280: change 12654692 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/TEST/redo3b.log
Log applied.
Media recovery complete.

11) Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.

Tags: added, alter database create datafile, CONTROLFILE, datafile, ORA-01244, recover databse, restore, restore controlfile, V$datafile

Restoring controlfile scenerio?

December 7, 2010 by Amin Jaffer · Leave a Comment
Filed under: Backup & Recovery, RMAN

Scenario when all control files for some reason have been lost. The following scenario shows how to restore controlfile and open the database.

– 1) Try to shutdown database which will fail as the control file(s) doesn’t exists so one needs to perform shutdown abort
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oradata/TEST/control1.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

– 2) Start database in nomount to restore the controlfile as controlfile is missing so database can only be started in mount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

– 3) Start rman and connect to target database and set DBID
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Dec 7 21:08:53 2010

RMAN> connect target /

connected to target database: TEST (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

– 4) Restore controlfile from autobackup
RMAN> restore controlfile from autobackup;

Starting restore at 07-DEC-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101207
channel ORA_DISK_1: autobackup found: c-1992878807-20101207-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oradata/TEST/control1.ora
output filename=/u01/oradata/TEST/control2.ora
output filename=/u01/oradata/TEST/control3.ora
Finished restore at 07-DEC-10

– 5) Open database in mount
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

– 6) Recover database
RMAN> recover database;

Starting recover at 07-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/oradata/TEST/redo3a.log
archive log filename=/u01/oradata/TEST/redo3a.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 07-DEC-10

– 7) Database has to be open with resetlogs
RMAN> alter database open resetlogs;

database opened

Tags: Backup & Recovery, General DBA, RMAN

-->

Categories
- Backup & Recovery
  - Flashback
  - RMAN
- Dataguard
- Datatypes
  - LOB
- dbms_auto_task_admin
- DBMS_JOBS
- DBMS_METADATA
- DBMS_SQLTUNE
- General DBA
  - AIX
  - Control file
  - Internal
  - Monitor
  - Parallel Query
  - role
  - Scheduler
  - Scheduler
- install
- Materialized view
- MySQL
  - JDBC
- OEM – Grid Control
- OpenWorld
- ORA Errors
- Oracle RAC
- Parameters
  - Initialization
  - spfile
- PL/SQL
  - Debug
- Real Application Testing
- Scripts
- Security
  - Password
- sga
  - Shared Pool
- shared pool
- SQL Server
  - Backup
  - Cursor
  - Grant
  - Jobs
  - osql
  - Profiler
  - Tunning
- SQL*Plus
- startup
- tablespace
- trace
  - Auditing
  - oradebug
- Transaction
- Tuning
  - AWR
  - Statistics
  - Statspack
- Unix
  - Shell
- vi
- windows
  - Batch
Archives
Links
Meta
- Log in
- WordPress
- XHTML
Recent Posts

Oracle DBA Daily Experiences

Show oracle internally recompiles object in certain cases

Scenario when the controlfile is lost and there was a datafile added

Restoring controlfile scenerio?

Categories

Archives

Links

Meta

Recent Posts