Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have lots of small transaction like with OLTP, use a smaller block size. With fewer but larger transactions, like with a DSS application, use a larger block size.
If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).
Back to top of file
How does one coalesce free space?
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then only for a short period of time.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE ... COALESCE; command, until then use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
Back to top of file
How does one prevent tablespace fragmentation?
Always set PCTINCREASE to 0 or 100.
Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.
Back to top of file
Where can one find the high water mark for a table?
There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
Back to top of file
How is extents allocated to a segment?
Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that's exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. This is why Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.
Back to top of file
Can one rename a database user (schema)?
No, this is listed as Enhancement Request 158508. Workaround:
Do a user-level export of user A
create new user B
import system/manager fromuser=A touser=B
drop user A
Back to top of file
Can one rename a tablespace's name?
No, this is listed as Enhancement Request 148742. Workaround:
Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects back in
Back to top of file
Can one resize tablespaces and data files?
You can manually increase or decrease the size of a datafile from Oracle 7.2 using the
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
command.
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.
Also, datafiles can be allowed to automatically extend if more space is required. Look at the following command:
CREATE TABLESPACE pcs_data_ts
DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
Back to top of file
How does one create a standby database?
While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!!
On your standby database, issue the following commands:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'file_name';
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;
On systems prior to Oracle 8i, write a job to copy archived redo log files from the primary database to the standby system, and apply the redo log files to the standby database (pipe it). Remember the database is recovering and will prompt you for the next log file to apply.
Oracle 8i onwards provide an "Automated Standby Database" feature which will send archived log files to the remote site via NET8, and apply then to the standby database.
When you need the standby database stop the recovery process and activate it:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Back to top of file
How does one give developers access to trace files (required as input to tkprof)?
The "alter session set sql_trace=true" command generates trace files in USER_DUMP_DEST that is used by developers as input to tkprof. On Unix the default file mask for these files are "rwx r-- ---".
There is an undocumented INIT.ORA parameter that will allow everybody to read (rwx r-- r--) this trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your database for it to take effect.
Thanks to Erlie Flynn
Back to top of file
How does one see what the uptime for my database is?
column STARTED format a18 head 'STARTUP TIME'
select C.INSTANCE,
to_date(JUL.VALUE, 'J')
|| to_char(floor(SEC.VALUE/3600), '09' )
|| ':'
|| substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
|| '.'
|| substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC,
V$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
Marco Bergman provided the following simpler example:
SELECT to_char(logon_time,'Dy dd Mon HH24:MM:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
Back to top of file
How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.
If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8i, one can just query SYS.v$sort_usage. Look at this example:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.addr = u.session_addr
/
Index Search Add FAQ Ask Question
Oracle Database Administration FAQ
$Date: 15-Apr-2000 $
$Revision: 1.31 $
$Author: Frank Naudé $
The only thing to fear is bear itself
Topics
What database block size should I use?
How does one coalesce free space?
How does one prevent tablespace fragmentation?
Where can one find the high water mark for a table?
How is extents allocated to a segment?
Can one rename a database user (schema)?
Can one rename a tablespace's name?
Can one resize tablespaces and data files?
How does one create a standby database?
How does one give developers access to trace files (required as input to tkprof)?
How does one see what the uptime for my database is?
How can one see who is using a temporary segment?
Back to Oracle DBA Topics
What database block size should I use?
Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have lots of small transaction like with OLTP, use a smaller block size. With fewer but larger transactions, like with a DSS application, use a larger block size.
If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).
Back to top of file
How does one coalesce free space?
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then only for a short period of time.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE ... COALESCE; command, until then use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
Back to top of file
How does one prevent tablespace fragmentation?
Always set PCTINCREASE to 0 or 100.
Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.
Back to top of file
Where can one find the high water mark for a table?
There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
Back to top of file
How is extents allocated to a segment?
Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that's exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. This is why Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.
Back to top of file
Can one rename a database user (schema)?
No, this is listed as Enhancement Request 158508. Workaround:
Do a user-level export of user A
create new user B
import system/manager fromuser=A touser=B
drop user A
Back to top of file
Can one rename a tablespace's name?
No, this is listed as Enhancement Request 148742. Workaround:
Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects back in
Back to top of file
Can one resize tablespaces and data files?
You can manually increase or decrease the size of a datafile from Oracle 7.2 using the
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
command.
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.
Also, datafiles can be allowed to automatically extend if more space is required. Look at the following command:
CREATE TABLESPACE pcs_data_ts
DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
Back to top of file
How does one create a standby database?
While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!!
On your standby database, issue the following commands:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'file_name';
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;
On systems prior to Oracle 8i, write a job to copy archived redo log files from the primary database to the standby system, and apply the redo log files to the standby database (pipe it). Remember the database is recovering and will prompt you for the next log file to apply.
Oracle 8i onwards provide an "Automated Standby Database" feature which will send archived log files to the remote site via NET8, and apply then to the standby database.
When you need the standby database stop the recovery process and activate it:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Back to top of file
How does one give developers access to trace files (required as input to tkprof)?
The "alter session set sql_trace=true" command generates trace files in USER_DUMP_DEST that is used by developers as input to tkprof. On Unix the default file mask for these files are "rwx r-- ---".
There is an undocumented INIT.ORA parameter that will allow everybody to read (rwx r-- r--) this trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your database for it to take effect.
Thanks to Erlie Flynn
Back to top of file
How does one see what the uptime for my database is?
column STARTED format a18 head 'STARTUP TIME'
select C.INSTANCE,
to_date(JUL.VALUE, 'J')
|| to_char(floor(SEC.VALUE/3600), '09' )
|| ':'
|| substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
|| '.'
|| substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC,
V$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
Marco Bergman provided the following simpler example:
SELECT to_char(logon_time,'Dy dd Mon HH24:MM:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
Back to top of file
How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.
If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8i, one can just query SYS.v$sort_usage. Look at this example:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.addr = u.session_addr
/
Index Search Add FAQ Ask Question
Oracle Performance Tuning FAQ
$Date: 23-Apr-2000 $
$Revision: 1.10 $
$Author: Frank Naudé $
Proper Preparation Prevents Piss Poor Performance
Topics
When is cost base optimization triggered?
How can one optimize %XYZ% queries?
Where can one find I/O statistics per table?
My query was fine last week and now it is slow. Why?
Why is Oracle not using the damn index?
How does one tune Oracle Wait events?
Back to Oracle DBA Topics
When is cost base optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
1. Change statistics of objects by doing an ANALYZE;
1. Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).
Back to top of file
How can one optimize %XYZ% queries?
It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.
If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
Back to top of file
Where can one find I/O statistics per table?
The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O.
The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.
Back to top of file
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.
Back to top of file
Why is Oracle not using the damn index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:
USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index decreases.
USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN).
There are many other factors taht affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.
Back to top of file
How does one tune Oracle Wait events?
Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:
Event Name: Tuning Recomendation:
db file sequential read Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
buffer busy waits Increase DB_BLOCK_BUFFERS/ Alalyze contention from SYS.V$BH
log buffer space Increase LOG_BUFFER parameter or move log files to faster disks
Index Search Add FAQ Ask Question
Oracle Backup and Recovery FAQ
$Date: 28-Jan-2000 $
$Revision: 1.10 $
$Author: Frank Naudé $
The wise one once said: Backup early and often... database crashes can happen at any time for any reason!
Topics
When and why should I backup my database?
What is the difference between online and offline backups?
How does one get a database in ARCHIVELOG mode?
Does oracle write to data files in hot backup mode?
My database was terminated while in BACKUP MODE, do I need to recover?
I've lost my REDOLOG files, how can I get my DB back?
I've lost some Rollback Segments, how can I get my DB back?
What are EBU and RMAN?
Common RMAN errors with solutions
What third party tools can be used with Oracle EBU/ RMAN?
Back to Oracle DBA Topics
When and why should I backup my database?
BOTTOM LINE: You loose the companies data, you could loose your job.
Most company won't spend the money of an elaborate backup system by a 3rd party vendor. If your company is one of these, then resort the old fashion way, TAPE using tar, dump, cpio, bar, dd. Get that data backed up!
If you cannot take your database down for a cold (off-line) backup. Switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
Back to top of file
What is the difference between online and offline backups?
A hot backup is a backup performed while the database is online and available for read/write. This can only be done if your database is in ARCHIVELOG mode.
A cold backup is a backup performed while the database is off-line.
For questions regarding logical backups see the Import/ Export FAQ.
Back to top of file
How does one get a database in ARCHIVELOG mode?
It is necessary to enable ARCHIVELOG mode before you start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script:
SVRMGR> connect internal;
SVRMGR> startup mount exclusive;
SVRMGR> alter database archivelog;
SVRMGR> archive log start;
SVRMGR> alter database open;
NOTE1: ARCHIVELOG mode was introduced with Oracle V6 and is essential for database point-in-time recovery.
NOTE2: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement.
Back to top of file
Does oracle write to data files in hot backup mode?
Oracle will continue to write to the database data files even if a tablespace is in backup mode. If a tablespace is in backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo log files. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
Back to top of file
My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE ... BEGIN BACKUP;), it will tell you that media recovery is needed when you attempt to start it up. The DBA is then required to recover the database and apply all archived logs to the database.
However, from Oracle7.2, you can simply take the individual datafiles out of backup mode. Eg: ALTER DATABASE DATAFILE '...' END BACKUP; and restart the database. You can do a select from V$BACKUP to see which datafiles were in backup mode. This will save a significant amount of database downtime.
Back to top of file
I've lost my REDOLOG files, how can I get my DB back?
The following INIT.ORA parameter may be required if your current redologs are corrupted or blown away. Caution is advised when using it as you might end-up with lost or inconsistent data!!!
_allow_resetlogs_corruption = true
Back to top of file
I've lost some Rollback Segments, how can I get my DB back?
Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch. Caution is advised when using it as it will mark uncommitted transactions are committed and you will end-up with lost or inconsistent data!!!
_corrupted_rollback_segments = (rbs01,rbs01,rbs03,rbs04)
Back to top of file
What are EBU and RMAN?
Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle databases. It is sometimes pronounced OEBU for Oracle Enterprise Backup Utility.
The Oracle Recovery Manager (RMAN) is the Oracle8 equivalent of EBU.
Back to top of file
Common RMAN errors with solutions
RMAN-20242: Specification does not match any archivelog in the recovery catalog.
Add to RMAN script: sql 'alter system archive log current';
RMAN-06089: archived log xyz not found or out of sync with catalog
Execute from RMAN: change archivelog all validate;
Back to top of file
What third party tools can be used with Oracle EBU/ RMAN?
The following Media Management Software Vendors have integrated their media management software packages with Oracle8 Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management Vendors will provide first line technical support for the integrated backup/recover solutions.
Veritas NetBackup
EMC DataManager
HP OMNIBack II
IBM's Tivoli Storage Manager - formerly ADSM
Legato Networker
Sterling Software's SAMS:Alexandria - formerly from Spectralogic
etc...
Index Search Add FAQ Ask Question
Oracle Security FAQ
$Date: 28-May-2000 $
$Revision: 1.00 $
$Author: Frank Naudé $
Secure your database and sleep well at night
Topics
How does one switch to another user in Oracle?
Is it true that OPS$ accounts are a security risk in a C/S environment?
Who created all these users in my database?
Back to Oracle DBA Topics
How does one switch to another user in Oracle?
Any DBA can switch from one database user to another. Of course it is not advisable to bridge Oracle's security, but look at this example:
SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.
Note: Also see the su.sql script in the Useful Scripts and Sample Programs Page.
Back to top of file
Is it true that OPS$ accounts are a security risk in a C/S environment?
If you allow people to log in with OPS$ accounts from Windows95, you cannot tell who that really is. With terminals, you can rely on passwords, with Win95, you cannot.
If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle Assumes that the remote OS has authenticated the user.
If REMOTE_OS_AUTHENT is set to FALSE, you have no exposure from remote clients - you also won't be able to connect from a remote client without a password (recommended). IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you're using OPS$ as your prefix, you'll be able to log on locally with or without a password, regardless of whether you've identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.
Procedure to enable SQL trace for users on your database: =========================================================
1. Get the SID and SERIAL# for the process you want to trace. SQL select sid, serial# from sys.v_$session where ... SID SERIAL# ---------- ---------- 8 13607
1. Enable tracing for your selected process: SQL ALTER SYSTEM SET TIMED_STATISTICS = TRUE; SQL execute dbms_system.set_sql_trace_in_session(8,13607, true);
1. Ask user to run just the necessary to demonstrate his problem.
1. Disable tracing for your selected process: SQL execute dbms_system.set_sql_trace_in_session(8,13607, false); SQL ALTER SYSTEM SET TIMED_STATISTICS = FALSE;
1. Look for trace file in USER_DUMP_DEST $ cd /app/oracle/admin/oradba/udump $ ls -ltr total 8 -rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc
1. Run TKPROF to analyse trace output $ tkprof ora_9294.trc x EXPLAIN=monitor/oramon SYS=NO
1. View/print output
Index Search Add FAQ Ask Question
Oracle8 Database Administration FAQ
$Date: 01-Jun-1997 $
$Revision: 1.0 $
$Author: Frank Naudé $
Great 8: The database for network computing
Topics
What is new in Oracle8?
How does one upgrade to Oracle8?
Can one exp from Oracle8 and imp into Oracle7?
How does one create partitioned tables?
How does one create an object?
Back to Oracle DBA Topics
What is new in Oracle8?
Oracle8 will still support the standard relational stuff as in Oracle7, but some new features and object extensions (cost option!) were added. To list a few:
A new object-relational database model
New object extensions (create type, external libraries, new in built datatypes)
Support for the ANSI SQL3 standard
Addresses the large database market (512 petabytes theoretical limit up from 4 terabytes in Oracle7)
Table and Index partitioning (store a table in multiple tablespaces)
Up to 1000 columns per table (up from 254 in Oracle7)
Provides deferred constraints (executed at commit time)
LONG field size increased from 2GB to 4GB; can have many LOBs per table; can be replicated; can be stored in separate tablespace
Replication through C-code built into the server instead of handling it thru DB triggers
Server Managed Backup and Recovery
Supports more concurrent users
Connection Pooling (utilizes idle connections to support more user)
Shared Database Links
Oracle8 Data Cartridges (database plug-in's to creating new data types)
Client-side Object Cache
The ROWID field format and size changed from 6 to 10 bytes
Server-side JAVA support from Oracle 8.1
SQL*Net now called Net8
Tablespace point-in-time recovery
Back to top of file
How does one upgrade to Oracle8?
If you are running Oracle 7.1, 7.2 or 7.3, you can use the Oracle8 Migration Utility to upgrade the data dictionary. The amount of time it takes to do the migration depends on the number of tables you have in the database, and not on the size of the database. Note that you cannot perform a migration after a patch set was installed. Migrate first, then apply the necessary patch sets.
For versions prior to Oracle 7.1, export your data and import it into a newly created Oracle8 database.
For more information, refer to the "Oracle8 Server Migration Release 8.0" manual.
Back to top of file
Can one exp from Oracle8 and imp into Oracle7?
Run catexp.sql and catexp7.sql from Server Manager on your Oracle8 database. Both these files can be found in the $ORACLE_HOME/rdbms/admin directory.
You can now run the Oracle7 exp utility against your Oracle8 database and import the resulting dump file back into an Oracle7 database.
Back to top of file
How does one create partitioned tables?
Oracle8's partitioning scheme will give DBA's the ability to perform maintenance on partitions without having to bring down an entire table (import/ export/ load/ etc). The SQL optimizer will bypass partitions that don't contain data pertinent to the query being solved. Look at the following example:
CREATE TABLE emp
( id NUMBER(5) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
address VARCHAR2(50),
phone VARCHAR2(15),
email VARCHAR2(100) )
PARTITION BY RANGE ( name )
( PARTITION p1 VALUES LESS THAN ('L') TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )
Notes: Oracle8 sorts nulls greater than all other values except MAXVALUE.
Back to top of file
How does one create an object?
CREATE TYPE employee_type as OBJECT (
name varchar2(30),
ssn varchar2(11),
salary number),
MEMBER FUNCTION get_id RETURN number);
CREATE TYPE BODY emp_type (
MAP MEMBER FUNCTION get_id RETURN number is
BEGIN
RETURN ascii_to_num(ssn);
END;);
CREATE TABLE emp OF employee_type;
Index Search Add FAQ Ask Question
DBA Tasks, Responsibilities and Skills Required
$Date: 23-Mar-1998 $
$Revision: 1.1 $
$Author: Frank Naudé $
DILBERT'S LAWS OF WORK:
Don't be irreplaceable, if you can't be replaced, you can't be promoted.
Topics
DBA Responsibilities
Required Skills
Qualifications
Typical Interview Questions
Back to Oracle DBA Topics
DBA Responsibilities:
The job of the DBA seems to be everything that everyone else either doesn't want to do or doesn't have the ability to do. We get the wonderful task of figuring out all the things too hard for everyone else to figure out.
More seriously though, there is a list of typical DBA responsibilities:
Installation, configuration and upgrading of Oracle server software and related products
Evaluate Oracle features and Oracle related products
Establish and maintain sound backup and recovery policies and procedures
Take care of the Database design and implementation
Implement and maintain database security (create and maintain users and roles, assign priviledges)
Do database tuning and performance monitoring
Do application tuning and performance monitoring
Setup and maintain documentation and standards
Plan growth and changes (capacity planning)
Work as part of a team and provide 7x24 support when required
Do general technical trouble shooting and give consultation to development teams
Interface with Oracle Corporation for technical support.
Back to top of file
Required Skills:
Good understanding of the Oracle database, related utilities and tools
A good understanding of the underlying operating system
A good knowledge of the physical database design
Ability to perform both Oracle and operating system performance tuning and monitoring
Knowledge of ALL Oracle backup and recovery scenarios
A good knowledge of Oracle security management
A good knowledge of how Oracle acquires and manages resources
A good knowledge Oracle data integrity
Sound knowledge of the applications systems at your site
Experience in code migration, database change management and data management through the various stages of the development life cycle
A sound knowledge of both database and system performance tuning
A DBA should have sound communication skills with management, development teams, vendors and systems administrators
Provide a strategic database direction for the organisation
A DBA should have the ability to handle multiple projects and deadlines
A DBA should possess a sound understanding of the business
Back to top of file
Qualifications:
Must be certified as an Oracle DBA
Preferably a BS in computer science or related engineering field
Lots and lots of EXPERIENCE
Back to top of file
Typical Interview Questions
General Questions
Tell us about yourself/ your background.
What are the three major characteristics that you bring to the job market?
What motivates you to do a good job?
What two or three things are most important to you at work?
What qualities do you think are essential to be successful in this kind of work?
What courses did you attend? What job certifications do you hold?
What subjects/courses did you excel in? Why?
What subjects/courses gave you trouble? Why?
How does your previous work experience prepare you for this position?
How do you define 'success'?
What has been your most significant accomplishment to date?
Describe a challenge you encountered and how you dealt with it.
Describe a failure and how you dealt with it.
Describe the 'ideal' job... the 'ideal' supervisor.
What leadership roles have you held?
What prejudices do you hold?
What do you like to do in your spare time?
What are your career goals (a) 3 years from now; (b) 10 years from now?
How does this position match your career goals?
What have you done in the past year to improve yourself?
In what areas do you feel you need further education and training to be successful?
What do you know about our company?
Why do you want to work for this company. Why should we hire you?
Where do you see yourself fitting in to this organization . . .initially? . . .in 5 years?
Why are you looking for a new job?
How do you feel about re-locating?
Are you willing to travel?
What are your salary requirements?
When would you be available to start if you were selected?
General Oracle Questions
Did you use online or off-line backups?
What version of Oracle were you running?
Haw many databases and what sizes?
If you have to advise a backup strategy for a new application, how would you approach it and what questions will you ask?
If a customer calls you about a hanging database session, what will you do to resolve it?
Compare Oracle to any other database that you know. Why would you prefer to work on one and not on the other?
Index Search Add FAQ Ask Question
Oracle Parallel Query Option FAQ
$Date: 13-Dec-1996 $
$Revision: 1.0 $
$Author: Frank Naudé $
Topics
What is the Oracle Parallel Query Option?
How does one invoke the Parallel Query Option?
How does one disable the Parallel Query Option?
What parameters can be set to control the Query Option?
How does one monitor Parallel Query Execution?
Back to Oracle FAQ Index
What is the Oracle Parallel Query Option?
The Oracle Parallel Query Option (PQO) allows one to parallise certain SQL statements so it can run on different processors on a multi-processor box. Typical operations that can be run in parallel: full table scans, sorts, sub-queries, data loading etc.
This option is mainly used for performance reasons and is commonly seen in Decision Support and Data Warehousing applications.
Back to top of file
How does one invoke the Parallel Query Option?
After setting the INIT.ORA parameters necessary for PQO to work, do the following:
ALTER your table (or index) and indicating that it is a parallel table
ALTER TABLE TAB_XXX PARALLEL (DEGREE 7);
putting hints in your SQL statement to indicate that it should be executed in parallel
SELECT --+ PARALLEL(table_alias, degree, nodes) * FROM table ...
Back to top of file
How does one disable the Parallel Query Option?
ALTER TABLE TAB_XXX PARALLEL (DEGREE 1 INSTANCES 1); or
ALTER TABLE TAB_XXX NOPARALLEL;
ALTER INDEX IND_XXX PARALLEL (DEGREE 1 INSTANCES 1); or
ALTER INDEX IND_XXX NOPARALLEL;
You can also take out the INIT.ORA parameters that allow PQO to work.
Back to top of file
What parameters can be set to control the Query Option?
The following INIT.ORA parameters can be set to control Parallel Query execution:
PARALLEL_MIN_SERVERS
PARALLEL_MAX_SERVERS
etc.
Back to top of file
How does one monitor Parallel Query Execution?
Prior to Oracle 8i:
select * from sys.v_$pq_sysstat;
Oracle 8i onwards:
select * from v_$pq_sysstat;
select * from v_$px_process;
select * from v_$px_sesstat;
select * from v_$px_process_sysstat;
Back to top of file
Index Search Add FAQ Ask Question
Oracle Parallel Server Option FAQ
$Date: 01-Dec-1998 $
$Revision: 2.01 $
$Author: Frank Naudé $
Topics
What is the Oracle Parallel Server Option?
What are the benefits for running in Parallel Server mode?
Are all applications suited for the Parallel Server?
How does one setup an Oracle Parallel Server?
How does one start an Oracle Parallel Server?
How can I test if a database is running is shared (parallel) mode?
How can I keep track of active instances?
How can one see how many PCM locks are allocated per instance?
How can one see how many PCM locks are allocated per data file?
What is pinging and why is it so bad?
How does one monitor PCM lock activity?
How does one setup a common SQL*Net connect string for all OPS instances?
How does one trace DLM activity?
Any other tips for using the Parallel Server?
Back to Oracle FAQ Index
What is the Oracle Parallel Server Option?
A normal Oracle installation consists of a single Oracle instance that access a database on the same computer system. With the parallel server option (PSO), multiple instances on different computer systems (nodes) can access the same database files simultaneously.
The OPS option is mainly used for high availability reasons. The parallel server can provide improved performance by using CPU resources across multiple computer systems. However, performance can be worse if your data is not partitioned correctly.
In Oracle Parallel Server installations, multiple instances mount the same database files. Communication between instances are managed by the Distributed Lock Manager (DLM). Note that the DLM is hardware and operating system dependent.
To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to ten additional background processes, named LCK0 through LCK9, to lock a resource in use by an instance.
The PSO is normally used on DEC VAX Clusters, UNIX Clusters, IBM's MVS SYSPLEX etc.
Back to top of file
What are the benefits for running in Parallel Server mode?
Running Oracle in parallel mode can benefit you in the following ways:
High availability and limited failover support from Oracle8.
Speedup (increase transaction response time) - can be achieved for DSS applications.
Scaleup (increase the transaction volume) - can be achieved for OLTP applications.
Back to top of file
Are all applications suited for the Parallel Server?
Applications that can be partitioned based on function or data are perfect candidates for the Oracle Parallel Server.
Applications with 'hot' rows (the same row being accessed by processes on different nodes) will not work well. This is because data will constantly be moved (pinged) from one Oracle Instance to another.
Back to top of file
How does one setup an Oracle Parallel Server?
1. Shut Down your Database:
SVRMGR> CONNECT INTERNAL
SVRMGR> SHUTDOWN NORMAL
1. Enable the Parallel Server Option. On Unix this is done by relinking the Oracle software.
1. Make the software available on all computer systems the Parallel Server will run on. This can be done by copying the software to all systems or to a shared disk.
1. Each instance requires its own set of Redo Log Files. Create additional log files:
SVRMGR> CONNECT INTERNAL
SVRMGR> STARTUP EXCLUSIVE
SVRMGR> ALTER DATABASE ADD LOGFILE THREAD 2
SVRMGR> GROUP G4 ('RAW_FILE1') SIZE 500k,
SVRMGR> GROUP G5 ('RAW_FILE2') SIZE 500k,
SVRMGR> GROUP G6 ('RAW_FILE3') SIZE 500k;
SVRMGR> ALTER DATABASE ENABLE PUBLIC THREAD 2;
1. Each instance requires its own set of rollback segments. Add Rollback Segments for New Nodes:
SVRMGR> CREATE ROLLBACK SEGMENT RB2 TABLESPACE RBS;
1. Edit the INIT.ORA files and number the instances 1, 2,...:
INSTANCE_NUMBER = 1
THREAD = 1
ROLLBACK_SEGMENTS = (r01, r02, r03, r04)
PARALLEL_SERVER = TRUE
# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.
# Set LM_PROCS to the number of nodes * PROCESSES
# etc....
1. Create the dictionary views needed for the PSO:
SVRMGR> START ?/RDBMS/ADMIN/CATPARR.SQL
1. On all the computer systems, startup the instances:
SVRMGR> CONNECT INTERNAL;
SVRMGR> STARTUP PARALLEL; or STARTUP SHARED;
Back to top of file
How does one start an Oracle Parallel Server?
Do the following from each node:
SVRMGR> connect internal
SVRMGR> set retries 5
SVRMGR> startup parallel retry
You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).
Back to top of file
How can I test if a database is running is shared (parallel) mode?
begin
if dbms_utility.is_parallel_server then
dbms_output.put_line('Running in SHARED/PARALLEL mode.');
else
dbms_output.put_line('Running in EXCLUSIVE mode.');
end if;
end;
/
Back to top of file
How can I keep track of active instances?
You can keep track of active parallel server instances by executing one of the following queries:
SELECT * FROM SYS.V_$ACTIVE_INSTANCES;
SELECT * FROM SYS.V_$THREAD;
Back to top of file
How can one see how many PCM locks are allocated per instance?
select count(*) "Number of hashed PCM locks"
from v$lock_element where bitand(flags, 4) != 0
/
select count(*) "Number of fine grain PCM locks"
from v$lock_element where bitand(flags, 4) = 0
/
Back to top of file
How can one see how many PCM locks are allocated per data file?
The following statement shows how many PCM locks are allocated per file and what the ping rate on those files are:
col file_name format a29
col tablespace format a12
col blocking format 9999999
col nlocks format 99999
col start_lk format 9999999
select l.file_id || ' ' || l.file_name file_name,
l.ts_name "TABLESPACE",
start_lk, nlocks, blocking, frequency "PING COUNT"
from sys.file_ping p, sys.file_lock l
where l.file_id = p.file_id
order by l.file_id
/
Back to top of file
What is pinging and why is it so bad?
Pinging is the process whereby one Oracle Instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. To move a data block from one instance's SGA to another is a slow process.
The challenge of tuning an Oracle Parallel Server is to minimize pinging.
Back to top of file
How does one monitor PCM lock activity?
Summary of PCM lock activity for the current instance:
select * from sys.v$lock_activity;
/
PCM lock activity per database object:
col table format a40
select file#, kind||' '||username||'.'||name "TABLE", sum(xnc) pings
from sys.v$false_ping p, sys.dba_users u
where u.user_id = p.owner#
group by file#, kind||' '||username||'.'||name, xnc
order by xnc desc
/
Back to top of file
How does one setup a common SQL*Net connect string for all OPS instances?
Define all Oracle System Identifiers (SIDs) to be the same on all nodes of the system. If you already have your SIDs defined, you can rename them by:
Shutting down all instances of the database;
Change the ORACLE_SID environment variable to the common name you have chosen;
Copy the current initOLDSID.ora file to initCOMMON.ora;
Restart the Oracle instances.
This is a sample TNSNAMES entry to connect to one of two instances in a parallel server:
rprd = (DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=node_1)
(PORT=1526)
)
(ADDRESS=
(PROTOCOL=tcp)
(HOST=node_2)
(PORT=1526)
)
)
(CONNECT_DATA=(SID=rprd))
)
Back to top of file
How does one trace DLM activity?
Look at this example:
SVRMGR> REM Dump Parallel Server DLM locks
SVRMGR> oradebug lkdebug -a convlock
SVRMGR> oradebug lkdebug -a convres
SBRMGR> oradebug lkdebug -r
Execute the "ORADEBUG HELP" command from svrmgrl for a list of valid commands. See the Internals FAQ for more tracing events.
Back to top of file
Any other tips for using the Parallel Server?
Run the ?/rdbms/admin/catparr.sql script regularly, it creates a table called EXT_TO_OBJ which needs to be current.
Re-create your tables with the FREELISTS and FREELIST GROUPS storage parameters.
Know your DLM (Distributed Lock Manager) and its capacity limits.
Use a blocking factor (!n) when setting up the GC_FILES_TO_LOCKS parameter. This will allocate n contiguous new blocks to a specific instance (and FREELIST GROUP) when table blocks are allocated.
Choose the right mix of HASH LOCKS (gc_db_locks) and FINE GRAIN LOCKS (gc_releasable_locks). Normally HASH locking is faster but FINE GRAIN locking scales better with multiple instances.
On some systems you can only create raw devices on the master node. For example, on Sequent you need to issue "vxdctl -c mode" to get the master from where to allocate database files from.
Back to top of file
Index Search Add FAQ Ask Question
Oracle Replication FAQ
$Date: 27-Nov-1999 $
$Revision: 1.12 $
$Author: Frank Naudé $
Topics
Can sequences be replicated?
I get "NO DATA FOUND" errors. How does one handle this?
How does one delete all local def errors?
Any replication notes?
Where can I get more info about replication?
Back to Oracle FAQ Index
Can sequences be replicated?
No, the best way to handle sequences, assuming you are using them as primary key values, is to concatenate then with something unique to the site. For example, use a sequence number concatenated with the database name, site name or something similar. One could also start the sequences at one site as odd numbers (1, 3, 5, etc) and the other site as even numbers (2, 4, 6 etc).
Back to top of file
I get "NO DATA FOUND" errors. How does one handle this?
If the application is updating primary key values, you will get lots of "NO DATA FOUND" errors. Primary keys should NEVER be updated. If you do update primary keys, conflict resolution becomes extremely difficult.
Back to top of file
How does one delete all local def errors?
From sqlplus say:
SQL> spool delete_errors.sql
SQL> select 'exec dbms_defer_sys.delete_error(''' || deferred_tran_id ||''','''||
SQL> destination || ''')'
SQL> from deferror;
SQL> spool off
SQL> @delete_errors
Back to top of file
Any replication notes?
Must have 1 master definition site for each replication group
Ensure triggers don't fire during replication by testing DBMS_SNAPSHOT.I_AM_A_REFRESH before executing a trigger body.
There are 12 conflict resolution methods. None will be enabled by default.
Back to top of file
Where can I get more info about replication?
Visit the Oracle Replication Special Interest Group and join their excellent mailing list.
With Redolog based replication, transactions are replicated even before they are committed in Oracle? Thought this is not possible? Visit Quest Software and look around for information about their SharePlex for Oracle product.
Ever wondered how Advanced Replication works? Check out how easy it is with Nico Booyse's replication scripts.
Back to top of file
No comments:
Post a Comment