Thursday, February 22, 2007

Change DBID of database

When cloning a database, the database will get no new DBID. This makes it impossible to backup the database with RMAN as the DBID is no longer unique. A new DBID and optional a new DB_NAME can be created with nid.

Below the steps are detailed to change the DBID:

SQL> connect /as sysdba
Connected.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 641728512 bytes
Fixed Size 1304432 bytes
Variable Size 437000336 bytes
Database Buffers 201326592 bytes
Redo Buffers 2097152 bytes
Database mounted.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options


bash-3.00$ nid target=/
DBNEWID: Release 10.1.0.5.0 - 64bit Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database PINF1 (DBID=4250054534)

Connected to server version 10.1.0

Control Files in database:
/u03/oradata/PINF1/control01.ctl
/u04/oradata/PINF1/control02.ctl

Change database ID of database PINF1? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 4250054534 to 4266449216
Control File /u03/oradata/PINF1/control01.ctl - modified
Control File /u04/oradata/PINF1/control02.ctl - modified
Datafile /u03/oradata/PINF1/system001.dbf - dbid changed
Datafile /u04/oradata/PINF1/undotbs001.dbf - dbid changed
Datafile /u03/oradata/PINF1/sysaux001.dbf - dbid changed
Datafile /u03/oradata/PINF1/users001.dbf - dbid changed
Datafile /u03/oradata/PINF1/portal.dbf - dbid changed
Datafile /u03/oradata/PINF1/ptldoc.dbf - dbid changed
Datafile /u03/oradata/PINF1/ptlidx.dbf - dbid changed
Datafile /u03/oradata/PINF1/ptllog.dbf - dbid changed
Datafile /u03/oradata/PINF1/ias_meta01.dbf - dbid changed
Datafile /u03/oradata/PINF1/discopltm1.dbf - dbid changed
Datafile /u03/oradata/PINF1/discopltc1.dbf - dbid changed
Datafile /u03/oradata/PINF1/dcm.dbf - dbid changed
Datafile /u03/oradata/PINF1/oss_sys01.dbf - dbid changed
Datafile /u03/oradata/PINF1/wcrsys01.dbf - dbid changed
Datafile /u03/oradata/PINF1/ip_rt.dbf - dbid changed
Datafile /u03/oradata/PINF1/ip_dt.dbf - dbid changed
Datafile /u03/oradata/PINF1/ip_idx.dbf - dbid changed
Datafile /u03/oradata/PINF1/ip_lob.dbf - dbid changed
Datafile /u03/oradata/PINF1/oca.dbf - dbid changed
Datafile /u03/oradata/PINF1/uddisys01.dbf - dbid changed
Datafile /u03/oradata/PINF1/attrs1_oid.dbf - dbid changed
Datafile /u03/oradata/PINF1/battrs1_oid.dbf - dbid changed
Datafile /u03/oradata/PINF1/gcats1_oid.dbf - dbid changed
Datafile /u03/oradata/PINF1/gdefault1_oid.dbf - dbid changed
Datafile /u03/oradata/PINF1/svrmg1_oid.dbf - dbid changed
Datafile /u03/oradata/PINF1/temp001.dbf - dbid changed
Control File /u03/oradata/PINF1/control01.ctl - dbid changed
Control File /u04/oradata/PINF1/control02.ctl - dbid changed
Instance shut down

Database ID for database PINF1 changed to 4266449216.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

bash-3.00$ sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Feb 22 16:18:13 2007

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

SQL> connect /as sysdba
Connected to an idle instance.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 641728512 bytes
Fixed Size 1304432 bytes
Variable Size 437000336 bytes
Database Buffers 201326592 bytes
Redo Buffers 2097152 bytes
Database mounted.

SQL> alter database open resetlogs ;

Database altered.


AWR in a nuttshell

The AWR automatically creates snapshots of the database at certain moments in time and keep these in the AWR repository for a certain retention-period. To view the current settings of AWR, the following query should be executed:

SQL> Select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION
---------- --------------------------- ---------------------------------------
652048328 +00000 00:15:00.0 +00007 00:00:00.0


This means that a snapshot is taken every 15 minutes and will be kept for 7 days. To change these settings, the procedure DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings can be used:

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 10080, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 15); -- Minutes. Current value retained if NULL.
END;
/


To view the available snapshots in the AWR repository:

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- -------------------------------------- ------------------------------
3558 18-FEB-07 06.00.18.000 AM 18-FEB-07 06.10.35.925 AM
3590 19-FEB-07 08.00.25.204 PM 19-FEB-07 09.00.37.533 PM
3607 20-FEB-07 08.00.26.773 PM 20-FEB-07 09.00.36.841 PM
3507 15-FEB-07 06.00.16.000 AM 15-FEB-07 06.10.34.753 AM
3525 16-FEB-07 06.10.33.378 AM 16-FEB-07 07.00.44.113 AM
3526 16-FEB-07 07.00.44.113 AM 16-FEB-07 08.00.57.048 AM
3527 16-FEB-07 08.00.57.048 AM 16-FEB-07 09.00.12.244 AM
3528 16-FEB-07 09.00.12.244 AM 16-FEB-07 10.00.23.455 AM
3529 16-FEB-07 10.00.23.455 AM 16-FEB-07 11.00.42.583 AM
3530 16-FEB-07 11.00.42.583 AM 16-FEB-07 12.00.53.854 PM
3531 16-FEB-07 12.00.53.854 PM 16-FEB-07 01.01.05.171 PM


Deletion of snapshot is done automatically by setting the retention-time. To manually delete snapshot-ranges, the following command can be used:

Exec DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (low_snap_id => 3506,
high_snap_id => 3608);


To create a manually snapshot:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot

To create a report on comparison of two snapshots:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Wednesday, February 21, 2007

Determine if PGA_AGGREGATE_TARGET is set big enough

Since Oracle9i, the dynamic initialisation parameter PGA_AGGREGATE_TARGET replaces parameters like sort_area_size and bitmap_merge_area_size. PGA_AGGREGATE_TARGET is set for all server-processes and not for each. If PGA_AGGREGATE_TARGET is set to 200mb, all server processes share these 200mb of memory. The minimum value of PGA_AGGREGATE_TARGET is 10mb.

To determine if PGA_AGGREGATE_TARGET is set high enough, the following query can be executed:

select name,value from v$sysstat where name like 'workarea executions%';

NAME VALUE
---------------------------------------------------------------- ----------
workarea executions - optimal 31570
workarea executions - onepass 0
workarea executions - multipass 0

In the above example all operations (all executions) have been execute in memory (optimal situation). When the operation was too big to be executed in memory, a part of the transaction was written to disk (onepass). If several swaps were needed, the Multipass counter will be increased.

In the example below, the PGA_AGGREGATE_TARGET is set too low:

NAME VALUE
---------------------------------------- ----------
workarea executions - optimal 510
workarea executions - onepass 1
workarea executions - multipass 4

The view v$pgastat give additional information about the use of the PGA:

select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 451537920 bytes
global memory bound 26214400 bytes
total PGA inuse 22575104 bytes
total PGA allocated 55227392 bytes
maximum PGA allocated 194516992 bytes
total freeable PGA memory 10682368 bytes
PGA memory freed back to OS 882114560 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 1022976 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 2193918976 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent

16 rows selected.

The second line (aggregate PGA auto target) should be just a little less then the target parameter (1ste line). The setting ‘over allocation count’ indicates the number of times Oracle has to allocate additional memory as Oracle cannot honor the PGA_AGGREGATE_TARGET setting. If optimal, this parameter should be close to zero. The line ‘cache hit percentage’ corresponds with the number of bytes executed in optimal buffers compared to the total number of bytes (see v$sysstat). Ideally the cache hit percentage should be close to 100%.


In case of the PGA_AGGREGATE_TARGET is set too small, the view V$PGA_TARGET_ADVICE can be used to determine the minimum setting for PGA_ALLOCATIE_TARGET considering the current workload:

select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
63 2197386240 0 100 5
125 2197386240 0 100 0
250 2197386240 0 100 0
375 2197386240 0 100 0
500 2197386240 0 100 0
600 2197386240 0 100 0
700 2197386240 0 100 0
800 2197386240 0 100 0
900 2197386240 0 100 0
1000 2197386240 0 100 0
1500 2197386240 0 100 0
2000 2197386240 0 100 0
3000 2197386240 0 100 0
4000 2197386240 0 100 0

When PGA_AGGREGATE_TARGET is set to 63mb, the estimate hit percentage is 5. This means 5% of transactions are executes as onepass or multipass. In the above situation, PGA_AGGREGATE_TARGET can be set to 125mb. In this situation there would be no over allocation and the hit ration would be 100%

Block change tracking in Oracle10

The time needed to create a RMAN incremental backup can be decreased by using the Block Change Tracking (BCT). When BCT is activated, Oracle doesn't have to scan all blocks in datafiles to determine whether a datablock has been changed. Information about changed datablocks are written to an external file.

To activate the recording of Block change tracking, execute the following command:

alter database enable block change tracking
using file ‘/u01/oradata/orcl/change_tracking.f’;

Is the file already exists, the REUSE-clause can be added to the command.

When the OMF-option is used, Oracle will automatically create and name the BCT-file:

alter database enable block change tracking;

The BCT-mode can be disabled by:

alter database disable block change tracking;

The BCT-file will also be deleted by this command.

Information about the BCT-mode can be found in the view

desc v$block_change_tracking
Name Null? Type
------------------- -------------- ------------------
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER

The percentage of blocks read for an incremental backup can be determined by issuing the following query:

select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as “% read for backup”
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = ‘YES’
group by file#
order by file#;

When the BCT-mode is activated or deactivated, a message is written to the alert.log file. The tracking file can be renamed by 'alter database rename file'-command. The BCT-file cannot be backuped by using RMAN.

Tuesday, February 20, 2007

SPFILE

The good thing about the old pfile was that you could easily commenting out some parameter-values (resetting them to their original value) or add comments on changes made to the pfile.

In the SPFILE you can do the same:

To comment out a parameter-value (reset it to its original value) you can you:


ALTER SYSTEM RESET sort_area_size SCOPE=SPFILE SID='*';

To change a parameter-value and add a comment to it, use:


ALTER SYSTEM SET timed_statistics=TRUE COMMENT=Change due error 123' SCOPE=BOTH;


Parameters starting with an underscore should be placed between quotes

ALTER SYSTEM SET "_collect_undo_stats"=FALSE SCOPE=SPFILE;

Changing the Time Zone of the database (Oracle10g)

To show the current setting for the time zone in Oracle, execute the following query:

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

To change the Time Zone to Europe/Amsterdam execute the following ALTER-DATABASE-command:

ALTER DATABASE SET TIME_ZONE='Europe/Amsterdam';

In my case, this command was executed unsuccessfully:

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

This means that there are tables using a date data-type with ‘local time zone’. To determine how many tables have this data-type, a next query can be executed:

SQL> select count (*) from dba_tab_columns
2 where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE' ;

COUNT(*)
----------
1

In this case there is only one table. The owner and name can be determined by:

SQL> select owner, table_name from dba_tab_columns
2 where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE';

OWNER TABLE_NAME
------------------------------ ------------------------------
OE ORDERS


SQL> desc oe.orders ;
Name Null? Type
------------------------------------ -------- ----------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)


This oe.orders is just a table in the example schema of OE so this colums can be dropped.

SQL> alter table OE.ORDERS drop column ORDER_DATE ;

Table altered.

Now the Time Zone can be altered:

SQL> ALTER DATABASE SET TIME_ZONE='Europe/Amsterdam';

Database altered.

If the dbtimezone is queried, the change is not yet reflected:

SQL> select dbtimezone from dual ;

DBTIME
------
+00:00


A database restart is necessary to activate the Time Zone change:

SQL> connect /as sysdba ;
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1248428 bytes
Variable Size 142607188 bytes
Database Buffers 113246208 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual ;

DBTIMEZONE
----------------
Europe/Amsterdam

Determine current SCN in an Oracle Database

A SCN can be useful to determine to which point in time an imcomplete recovery must be execute. Prior to a test or something dangerous, you can query your SCN so you can recovery or flashback to this SCN discarding changes to your database. The current SCN can be queried as follows:

SQL> select current_scn from v$database ;
CURRENT_SCN

-----------
927043