Malaysian Premier Oracle Database blog

Innotiive Asia is Database Company now

2011-07-01T09:16:00.003+08:00

Innotiive Asia Sdn Bhd is now a database company that specialize in Oracle, MSSQL, MySQL and DB2.

If anybody has any queries or work to be done, please contact us at info@innotiiveasia.com

Thanks

Looking for Oracle DBAs

2010-05-20T18:55:00.002+08:00

Hi Guys,

So long no see. Innotiive Asia is expanding. Looking for Oracle DBAs.

With Experience. Remuneration comes with experience.

Please send your resumes to hr@innotiiveasia.com

Or call me for an interview. 012-2001973

Thanks

Oracle Blog from Innotiive Asia (M) Sdn Bhd

2009-10-02T08:02:00.002+08:00

A new oracle blog will be out somewhere on the 2nd week of October 2009 that will be maintained by Innotiive Asia's Oracle Consultants.

There will be alot of info for new and experienced DBAs. This blog is also used by Innotiive's DBAs to share their experience. You can also share your own experiences and also ask questions and there will a group of dedicated DBAs to answer your questions.

Please give your suggestions on how to improve this blog so that the DBAs can benefit, especially for Malaysians....

http://oracleinnotiive.wordpress.com

Why is excessive redo generated during an Online/Hot Backup

2009-08-10T11:07:00.001+08:00

Just a question asked by someone and here is the answer. There is no better person to answer this question than Tom Kyte...:-)

There is not excessive redo generated, there is additional information logged into

the online redo log during a hot backup the first time a block is modified in a

tablespace that is in hot backup mode.

in hot backup mode only 2 things are different:

o the first time a block is changed in a datafile that is in hot backup mode, the ENTIRE

BLOCK is written to the redo log files, not just the changed bytes. Normally only the

changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged

the FIRST TIME. This is because you can get into a situation where the process copying

the datafile and DBWR are working on the same block simultaneously. Lets say they are

and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time).

The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile --

DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right

now. The entire 8k block is rewritten. The backup program starts running again

(multi-tasking OS here) and reads the last 4k of the block. The backup program has now

gotten an impossible block -- the head and tail are from two points in time. We cannot

deal with that during recovery. Hence, we log the entire block image so that during

recovery, this block is totally rewritten from redo and is consistent with itself at

least. We can recover it from there.

o the datafile headers which contain the SCN of the last completed checkpoint are NOT

updated while a file is in hot backup mode. This lets the recovery process understand

what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one

tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon

as you have backed it up. This will reduce the number of blocks that may have to be

logged to the minimum possible.

Will be blogging again!!!

2009-02-28T11:10:00.003+08:00

Please accept my appologies for not blogging for 8 months...... Been busy being a new father....

Will be starting to blog very soon. Please lookup this blog for new and informative issues on Oracle Database.

Thank y0u and see you soon.....

INITIALIZATION PARAMETERS

2008-07-03T12:48:00.002+08:00

Server Parameters for Oracle 9i and 10g
Green = New in 10g
Red = Valid in 9i but dropped (or hidden) in 10g
Bold = Static Parameter - change in Pfile/SPfile
Normal= Dynamic Parameter - change in Pfile/SPfile/ALTER SYSTEM /SESSION
PARAMETER DESCRIPTION
------------------------------ ----------------------------------------
ACTIVE_INSTANCE_COUNT = int Active instances in the cluster
AQ_TM_PROCESSES = int Number of AQ Time Managers to start
ARCHIVE_LAG_TARGET = int Max no. seconds of redos the standby could lose
asm_diskgroups = string Disk groups to mount automatically
asm_diskstring = string Disk set locations for discovery
asm_power_limit = int Number of processes for disk rebalancing
AUDIT_FILE_DEST = 'directory' Directory in which auditing files are to reside
AUDIT_SYS_OPERATIONS = {TRUEFALSE}
AUDIT_TRAIL = {NONE FALSE DB TRUE OS} Enable system auditing 9i
AUDIT_TRAIL = {NONE DB DB_EXTENDED OS} Enable system auditing 10g
BACKGROUND_CORE_DUMP = {PARTIAL FULL}
BACKGROUND_DUMP_DEST = 'path or directory'
BACKUP_TAPE_IO_SLAVES = {TRUE FALSE} DEFERRED
BITMAP_MERGE_AREA_SIZE = int Memory for BITMAP MERGE
BLANK_TRIMMING = {TRUEFALSE}
CIRCUITS = int
CLUSTER_DATABASE = {TRUEFALSE} If TRUE startup in cluster database mode
CLUSTER_DATABASE_INSTANCES = int
CLUSTER_INTERCONNECTS = ipaddr [:ipaddr...] Interconnects for RAC use
COMMIT_POINT_STRENGTH = int
COMPATIBLE = release_number [CHAR: 9.2.0.0.0]
CONSTRAINT[S] = { IMMEDIATE DEFERRED DEFAULT }
CONTROL_FILE_RECORD_KEEP_TIME = int Time in Days
CONTROL_FILES =filename [,filename [...] ]
CORE_DUMP_DEST = 'text'
CPU_COUNT = int
CREATE_BITMAP_AREA_SIZE = int
CREATE_STORED_OUTLINES = {TRUE FALSE 'category_name' } [NOOVERRIDE]
CURSOR_SHARING = {SIMILAR EXACT FORCE}
CURSOR_SPACE_FOR_TIME = {TRUEFALSE}
CURRENT_SCHEMA = schema Change the current schema of the session
DB_2k_cache_size = int bytes Size of cache for 2K buffers
DB_4k_cache_size = int bytes Size of cache for 4K buffers
DB_8k_cache_size = int bytes Size of cache for 8K buffers
DB_16k_cache_size = int bytes Size of cache for 16K buffers
DB_32k_cache_size = int bytes Size of cache for 32K buffers
DB_BLOCK_BUFFERS = int Deprecated in favour of DB_CACHE_ SIZE
DB_BLOCK_CHECKING = {TRUE FALSE} DEFERRED
DB_BLOCK_CHECKSUM = {TRUE FALSE}
DB_BLOCK_SIZE = int [bytes] Do not alter after db creation
DB_CACHE_ADVICE = {ON READY OFF}
DB_CACHE_SIZE = int [bytes]
DB_CREATE_FILE_DEST = directory
DB_CREATE_ONLINE_LOG_DEST_n = directory (where n = 1-5)
Default locn for Oracle-managed control files and online redo logs.
DB_DOMAIN = domain_name Directory part of global database name
DB_FILE_MULTIBLOCK_READ_COUNT = int
DB_FILE_NAME_CONVERT = [(]'dbfile1' , 'dbfile2'...[)]
Datafile name convert patterns and strings
for standby/clone db [old string, new string]
DB_FILES = int
db_flashback_retention_target = int Max Flashback Database log retention (minutes)
DB_KEEP_CACHE_SIZE = int [bytes]
DB_NAME = database_name
db_recovery_file_dest = string Default database recovery file location
db_recovery_file_dest_size = int Database recovery files size limit
DB_RECYCLE_CACHE_SIZE = int [bytes]
db_unique_name = string Database Unique Name
DB_WRITER_PROCESSES = int Number of background database writer
processes to start
DBLINK_ENCRYPT_LOGIN = {TRUEFALSE} Enforce password encryption for distributed login
DBWR_IO_SLAVES = int
DDL_WAIT_FOR_LOCKS = {TRUEFALSE} Disable NOWAIT DML lock acquisitions
DG_BROKER_CONFIG_FILEn = filename (where n = 1 or 2)
DG_BROKER_START = {TRUEFALSE}
DISK_ASYNCH_IO = {TRUEFALSE}
DISPATCHERS = 'dispatch_clause' (see SQL ref manual for detail)(MTS_Dispatchers in Ora 8/9)
DISTRIBUTED_LOCK_TIMEOUT = int
DML_LOCKS = int One for each table modified in a transaction
DRS_START = {TRUEFALSE} Start DG Broker monitor (DMON process)
ERROR_ON_OVERLAP_TIME = {TRUE FALSE}
ENQUEUE_RESOURCES = int Resources for enqueues
EVENT = debug_string Debug event control
FAL_CLIENT = string Fetch archive log Client
FAL_SERVER = string Fetch archive log Server
FAST_START_IO_TARGET = int Upper bound on recovery reads(Deprecated)
FAST_START_MTTR_TARGET = int
FAST_START_PARALLEL_ROLLBACK = {FALSE LOW HIGH}
Max number of parallel recovery slaves
FILE_MAPPING = {TRUEFALSE}
FILEIO_NETWORK_ADAPTERS = char Network Adapters for File I/O
FILESYSTEMIO_OPTIONS = {none setall directIO asynch}
FIXED_DATE = {'YYYY_MM_DD_HH24_MI-SS' 'date in default format'}
Fix SYSDATE value for debugging
GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}'
RAC/OPS - lock granularity number of
global cache locks per file (DFS)
GCS_SERVER_PROCESSES = int Number of background gcs server processes to start
GLOBAL_CONTEXT_POOL_SIZE = {1 MB int MB}
GLOBAL_NAMES = {TRUE FALSE} Enforce that database links have same
name as remote database
HASH_AREA_SIZE = int Size of in-memory hash work area (Shared Server)
HASH_JOIN_ENABLED = {TRUEFALSE}
HI_SHARED_MEMORY_ADDRESS = int SGA starting address (high order 32-bits
on 64-bit platforms)
HS_AUTOREGISTER = {TRUE FALSE} Enable automatic server DD updates in HS
agent self-registration
IFILE = parameter_file_name Include file in init.ora
INSTANCE = int Connect to a different RAC instance
INSTANCE_GROUPS = group_name [,group_name ... ]
INSTANCE_NAME = instance_id
INSTANCE_NUMBER = int
INSTANCE_TYPE = {RDBMSASM} Type of instance to be executed
RDBMS or Automated Storage Management
ISOLATION_LEVEL = {SERIALIZABLE READ COMMITTED}
JAVA_MAX_SESSIONSPACE_SIZE = int [bytes]
JAVA_POOL_SIZE = int [bytes]
JAVA_SOFT_SESSIONSPACE_LIMIT = int
JOB_QUEUE_PROCESSES = int
LARGE_POOL_SIZE = int [bytes]
LICENSE_MAX_SESSIONS = int Maximum number of non-system user sessions
(concurrent licensing)
LICENSE_MAX_USERS = int Maximum number of named users that can be created
(named user licensing)
LICENSE_SESSIONS_WARNING = int Warning level for number of non-system
user sessions
LOCAL_LISTENER = network_name Define which listeners instances register with
LOCK_NAME_SPACE = namespace Used for generating lock names for standby/primary database
assign each a unique name space
LOCK_SGA = {TRUE FALSE}
LOG_ARCHIVE_CONFIG = [SENDNOSEND] [RECEIVENORECEIVE] [ DG_CONFIG]
LOG_ARCHIVE_DEST = string
LOG_ARCHIVE_DEST_n = {null_string
{LOCATION=local_pathname SERVICE=tnsnames_service}
[MANDATORY OPTIONAL] [REOPEN[=integer]]}
LOG_ARCHIVE_DEST_STATE_n = {ENABLE DEFER} (n = 1-10)
LOG_ARCHIVE_DUPLEX_DEST = string
LOG_ARCHIVE_FORMAT = string [CHAR: "MyApp%S.ARC"]
LOG_ARCHIVE_LOCAL_FIRST = {TRUEFALSE} Establish EXPEDITE attribute default value
LOG_ARCHIVE_MAX_PROCESSES = int
LOG_ARCHIVE_MIN_SUCCEED_DEST = int Minimum number of archive destinations
that must succeed
LOG_ARCHIVE_START = {TRUE FALSE}
LOG_ARCHIVE_TRACE = int Tracing level for Archive logs
LOG_BUFFER = int bytes Redo circular buffer size
LOG_CHECKPOINT_INTERVAL = int Checkpoint threshold, # redo blocks
LOG_CHECKPOINT_TIMEOUT = int Checkpoint threshold, maximum time interval between
checkpoints in seconds
LOG_CHECKPOINTS_TO_ALERT = {TRUEFALSE} Log checkpoint begin/end to alert file
LOG_FILE_NAME_CONVERT = ['old string','new string']
Convert patterns/strings for standby/clone db
LOG_PARALLELISM = int Number of log buffer strands
LOGMNR_MAX_PERSISTENT_SESSIONS = int Maximum no of threads to mine
MAX_DISPATCHERS = int Max number of dispatchers
MAX_DUMP_FILE_SIZE = {size bytesUNLIMITED} [DEFERRED]
MAX_ENABLED_ROLES = int Max number of roles a user can have enabled
MAX_ROLLBACK_SEGMENTS = int Max number of rollback segments in SGA cache
MAX_SHARED_SERVERS = int Max number of shared servers]
mts_circuits = int Max number of circuits (10g see CIRCUITS)
mts_dispatchers Specifications of dispatchers (10g see DISPATCHERS)
MTS_LISTENER_ADDRESS Address(es) of network listener [CHAR]
mts_max_dispatchers Max number of dispatchers (10g see MAX_DISPATCHERS)
mts_max_servers Max number of shared servers (10g see MAX_SHARED_SERVERS)
MTS_MULTIPLE_LISTENERS = {TRUEFALSE} Are multiple listeners enabled?
MTS_SERVERS = int Number of shared servers to start up [NUMBER]
mts_service = string Service supported by dispatchers [CHAR]
mts_sessions = int max number of shared server sessions [NUMBER]

nls_calendar ='string' NLS calendar system name (Default=GREGORIAN)
nls_comp = {BINARY ANSI} NLS comparison, Enterprise Edition
nls_currency ='string' NLS local currency symbol
nls_date_format ='format' NLS Oracle date format
nls_date_language =language NLS date language name (Default=AMERICAN)]
nls_dual_currency = currency_symbol
nls_iso_currency = territory Override the default set by NLS_TERRITORY
nls_language = language NLS language name (session default)
nls_length_semantics = {BYTECHAR}} Default when creating new columns
nls_nchar_conv_excp = {TRUEFALSE} Raise an exception instead of
allowing an implicit conversion
nls_numeric_characters ="decimal_character group_separator"
nls_sort = {BINARY linguistic_def} Case-sensitive or insensitive sort
linguistic_def may be BINARY, BINARY_CI, BINARY_AI,
GERMAN, GERMAN_CI, etc
nls_territory = territory Territory name (country settings)
nls_time_format =time_format Time format
nls_time_tz_format = time_format Time with timezone format
nls_timestamp_format = time_format Timestamp format
nls_timestamp_tz_format = time_format Timestamp with timezone format

O7_DICTIONARY_ACCESSIBILITY = {TRUE FALSE} Allow Dictionary Access (as in Ora V7 )
OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED Space for application objects Max
OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED Space for application objects Min
OLAP_PAGE_POOL_SIZE =int bytes
OPEN_CURSORS = int Max # cursors per session
OPEN_LINKS = int Max # open links per Session
OPEN_LINKS_PER_INSTANCE = int Max # open links per instance
OPTIMIZER_DYNAMIC_SAMPLING = int
OPTIMIZER_FEATURES_ENABLE = {8.0.08.0.38.0.48.0.58.0.68.0.78.1.08.1.38.1.48.1.58.1.68.1.79.0.09.0.19.2.0}
Configure qry optimiser based on an Oracle release No.
OPTIMIZER_INDEX_CACHING = int Percent to cache (favour nested loop joins & IN-list)
OPTIMIZER_INDEX_COST_ADJ = int Adjust the cost of index vs FTS
OPTIMIZER_MAX_PERMUTATIONS = int Max join permutations per qry block
OPTIMIZER_MODE = [RULE CHOOSE FIRST_ROWS ALL_ROWS]
oracle_trace_collection_name =collection Name for use by Oracle TRACE
oracle_trace_collection_path =path Path to .cdf & .dat files (ORACLE_HOME/otrace/admin/cdf)
oracle_trace_collection_size =int bytes Max trace file size
oracle_trace_enable = {TRUEFALSE} Enable Oracle Trace
oracle_trace_facility_name ={ORACLED ORACLEE ORACLESM ORACLEC} TRACE event set
oracle_trace_facility_path =path TRACE definition files: ORACLE_HOME/otrace/admin/fdf/
OS_AUTHENT_PREFIX = prefix Prefix for auto-logon accounts [string]
OS_ROLES = {TRUEFALSE} Retrieve roles from the operating system
PARALLEL_ADAPTIVE_MULTI_USER = {TRUE FALSE} Tune degree of parallelism
PARALLEL_AUTOMATIC_TUNING = {TRUEFALSE} Automatic tuning
PARALLEL_EXECUTION_MESSAGE_SIZE = int bytes Message buffer size
PARALLEL_INSTANCE_GROUP = 'group' RAC: Limit instances used
PARALLEL_MAX_SERVERS = int
PARALLEL_MIN_PERCENT = int Min percent of threads required for
parallel query
PARALLEL_MIN_SERVERS = int
PARALLEL_SERVER = [TRUE FALSE] Startup in parallel server mode
PARALLEL_SERVER_instances = int No. of instances (used for sizing SGA)
PARALLEL_THREADS_PER_CPU = int
PARTITION_VIEW_ENABLED = {TRUEFALSE} Deprecated (use partition TABLES)
PGA_AGGREGATE_TARGET = int bytes Automatically size the SQL working area
plsql_code_type ={INTERPRETED NATIVE} Code-type
PLSQL_COMPILER_FLAGS = { [DEBUG NON_DEBUG] [INTERPRETED NATIVE] }
plsql_debug ={TRUE FALSE}
plsql_native_c_compiler
plsql_native_library_dir = ['Path_to_directory']
plsql_native_library_subdir_count = int
plsql_native_linker =path Path to linker
plsql_native_make_file_name =path Pathname of make file
plsql_native_make_utility =path Pathname of make utility
plsql_optimize_level Optimize level
PLSQL_V2_COMPATIBILITY = {TRUE FALSE} [DEFERRED]
plsql_warnings =string Compiler warnings settings
See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS
PRE_PAGE_SGA = {TRUEFALSE} Pre-page sga for process
PROCESSES = int User processes
QUERY_REWRITE_ENABLED = {FORCE TRUE FALSE} [DEFERRED NOOVERRIDE]
QUERY_REWRITE_INTEGRITY = {ENFORCED TRUSTED STALE_TOLERATED}
RDBMS_SERVER_DN = Distinguished Name
READ_ONLY_OPEN_DELAYED = {TRUE FALSE} Delay opening read_only files until first access
RECOVERY_PARALLELISM = int Server processes to use for parallel recovery
REMOTE_ARCHIVE_ENABLE = [RECEIVE[,SEND] FALSE TRUE]
Enable or disable sending archived redo logs to/from remote destinations
REMOTE_DEPENDENCIES_MODE = {TIMESTAMP SIGNATURE}
Remote-procedure-call dependencies mode
REMOTE_LISTENER =network_name
REMOTE_LOGIN_PASSWORDFILE ={NONE SHARED EXCLUSIVE} Use a password file
REMOTE_OS_AUTHENT = {TRUE FALSE} Allow non-secure remote clients to use
auto-logon accounts
REMOTE_OS_ROLES = {TRUE FALSE} Allow non-secure remote clients to use os roles
REPLICATION_DEPENDENCY_TRACKING = {TRUE FALSE}
RESOURCE_LIMIT = {TRUE FALSE} Master switch for resource limit
RESOURCE_MANAGER_PLAN = plan_name Turn on Resource Manager plan
resumable_timeout =seconds Set resumable_timeout
ROLLBACK_SEGMENTS = (rbs1 [, rbs2] ... )
ROW_LOCKING = [ALWAYS DEFAULT INTENT]
SERIAL_REUSE = {DISABLE SELECT DML PLSQL ALL} Cursor memmory management
SERVICE_NAMES = db_service_name [,db_service_name [,...] ]
SESSION_CACHED_CURSORS = int Session cursors to cache
SESSION_MAX_OPEN_FILES = int Max no. of BFiles (LOB) each session can open
SESSIONS = int Max no. of user and system sessions
SGA_MAX_SIZE =int bytes Initial SGA size
sga_target = int bytes Target size of SGA
SHADOW_CORE_DUMP = {PARTIAL FULL NONE} Include SGA in core file
SHARED_MEMORY_ADDRESS = int SGA starting address (platform specific)
SHARED_POOL_RESERVED_SIZE = int bytes Reserved area of shared pool
SHARED_POOL_SIZE = int Size of shared pool
SHARED_SERVERS = int Number of shared servers to start up (MTS)
SHARED_SERVER_SESSIONS = int Max number of shared server sessions
SKIP_UNUSABLE_INDEXES = {TRUE FALSE}
smtp_out_server = server_clause utl_smtp server and port configuration parameter
SORT_AREA_RETAINED_SIZE =int bytes [DEFERRED] UGA Memory to retain (Shared Server)
SORT_AREA_SIZE = int bytes [DEFERRED] In-memory sort work area (Shared Server)
SORT_MULTIBLOCK_READ_COUNT Obsolete in 9i
SPFILE =spfile_name Parameter file
sp_name =name Service Provider Name
SQL92_SECURITY = {TRUE FALSE} Require select privilege for update/delete
SQL_TRACE = {TRUE FALSE} Enable SQL trace
sqltune_category =category Qualifier for applying hintsets
SQL_VERSION =version Sql language version, for compatibility
STANDBY_ARCHIVE_DEST = 'filespec' Standby database archivelog destination
STANDBY_FILE_MANAGEMENT = {MANUAL AUTO}
Automate file mmanagement on standby DB
STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE TRUE FALSE}
STATISTICS_LEVEL = {ALL TYPICAL BASIC} Collect Statistics
streams_pool_size = int bytes Size of the streams pool
TAPE_ASYNCH_IO = {TRUE FALSE} Allow I/O requests to tape devices at the same time as CPU processing
THREAD =int Redo thread to use (RAC)
TIMED_OS_STATISTICS = int Gather OS statistics every x seconds
TIMED_STATISTICS = {TRUE FALSE} Collect time statistics
TIME_ZONE = '[+ -] hh:mm' LOCAL DBTIMEZONE 'time_zone_region'
TRACE_ENABLED = {TRUE FALSE} Trace execution path (Internal use only-Oracle support services)
TRACEFILE_IDENTIFIER = "traceid" Trace file custom identifier
TRANSACTION_AUDITING = {TRUE FALSE} [DEFERRED]
TRANSACTIONS = int Max. number of concurrent active transactions
TRANSACTIONS_PER_ROLLBACK_SEGMENT = int
UNDO_MANAGEMENT = {MANUAL AUTO} Undo space management mode (Manual=rollback segs)
UNDO_RETENTION = int Undo retention in second
UNDO_SUPPRESS_ERRORS = {TRUE FALSE} Suppress RBU errors in SMU mode
UNDO_TABLESPACE =undoname Select an undo tablespace
USE_INDIRECT_DATA_BUFFERS = {TRUEFALSE} Configure SGA Memory cache for >4Gb RAM
USE_PRIVATE_OUTLINES = {TRUE FALSE category_name }
USE_STORED_OUTLINES = { TRUE FALSE category_name} [NOOVERRIDE]
USER_DUMP_DEST = 'directory_name' User process dump directory
UTL_FILE_DIR Utl_file accessible directories list
UTL_FILE_DIR ='Path1', 'Path2'..
or
UTL_FILE_DIR ='Path1' # Must be
UTL_FILE_DIR ='Path2' # consecutive entries
WORKAREA_SIZE_POLICY = {AUTO MANUAL} Policy used to size SQL working areas

http://www.ss64.com/orasyntax/initora.html

ALTER TABLESPACE OFFLINE vs. ALTER DATABASE DATAFILE OFFLINE

2008-06-16T15:27:00.000+08:00

There is a difference between:
Taking the tablespace offline and taking the datafiles offline

ALTER TABLESPACE (tablespace_name ) OFFLINE; does a checkpoint on all datafiles and then takes the datafiles offline.

ALTER DATABASE DATAFILE (datafile_path) OFFLINE;does not perform a checkpoint, so that if the database is open, you may need to perform media recovery when bringing it online.

That is the reason why:
You cannot do ‘alter database datafile (datafile_path) offline’ if you are in noarchivelog (but tablespace offline works)
You cannot do ‘alter tablespace (tablespace_name) offline’ if database is read-only (but datafile offline works)

Note that in both cases, you can check the STATUS column from v$datafile to see if the file is online, offline or needs recovery.

http://www.quest-pipelines.com/pipelines/dba/tips06.htm

System Change Number (SCN)

2008-06-11T16:45:00.002+08:00

The system change number (SCN) is an ever-increasing internal timestamp that uniquely identifies a committed version of the database. Every time a user commits a transaction, Oracle records a new SCN. You can obtain SCNs in a number of ways, for example,

a) alert log.
b) select dbms_flashback.get_system_change_number from dual;

The SCN plays an important role to determine if the database is in a consistent state or not when the database is brought up. The background process SMON checks the SCN in all datafile headers when the database is started. Everything is fine if all of these SCNs matches with the SCN found in the controlfile. They must be the same. If the SCNs are not same, the database is in an inconsistent state. Recovery is needed.

The SCN plays a vital role for providing consistent reads.

Basically, it works as follows: The query reads a db block. This block has as an attribute the SCN when it was last changed. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments/undo, we use it for our query.
When a (reading) query starts, it is important that the values of the rows selected are the same as when the query started, even if another session has changed those rows. This is refered to as read consistency. Read consistency is achieved through the SCN (system change number).

When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

The System Change Number (SCN) is how Oracle internally keeps track of the timing of events (i.e., transactions) in the database. The SCN does not have a time associated with it, but a higher SCN occurred after a lower SCN. There is no correlation between the SCN values and the time between when the SCNs were generated. Two SCNs that differ by a value of 10 could be generated one second apart or 10 minutes apart.

Since the online redo log files contain transaction information, it is useful to know when that transaction took place, relative to other transactions. So SCNs are written to the online redo log files. Any record written to the online redo log file has an associated SCN so the database will know when that change took place.

The SCN is stored in other places as well. One of the most important is the control files. The control file keeps track of changes to the database. As such, it needs to know the SCN.
The datafile headers also contain the SCN of the most recent transaction that changed the datafile’s contents. When you start Oracle, Oracle checks the SCN in the datafile with the SCN in the control file. If the SCN in the datafile is “older” than the SCN in the control file, Oracle knows that the file needs recovery. When you make a tablespace READ ONLY, all of its datafiles have the SCN frozen, but Oracle does not worry about this because it knows the files have not had changes made to it.
Another place the SCN is stored is in the data block. The SCN in the block will show when the block’s contents were last updated.

From
http://jenniferlinca.wordpress.com/category/backup-and-recovery/
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1188377,00.html

Oracle Index Monitoring

2008-04-10T14:14:00.001+08:00

The Oracle engine maintains all indexes whether it is used or not.There are so many applications out there that creates alot of indexes without knowing Maintenance of these indexes will take resources.Too many unused indexes may also slow down the system ecpecially on systems where there is write intensive.
Index monitoring is a way to identify unused indexes so that you can safely remove them.

TO START OR STOP INDEX MONITORING

Alter index INDEX_A MONITORING USAGE;
Alter inded INDEX_A NOMONITORING USAGE;

When you monitor these indexes, make sure you run this during the lifecycle of your applcation.Do not run this when your application is not running because you may not get the true picture.
The information on the monitoring of index usage will be displayed in

SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'INDEX_A'
ORDER BY index_name;

You must connect as the object owner to see the usage.

One flaw in this method is it simply isn't granular enough. All it will tell me is that an index has been used but doesn't give a picture of how often it is used.

There is a way to do it but it is another story...:-)

Optimizer_mode and statistics gathering in 10g

2008-03-27T11:56:00.003+08:00

The OPTIMIZER_MODE parameter has a new default value of ALL_ROWS in OracleDatabase 10g.
In Oracle Database 10g, the Optimizer uses dynamic sampling to get statistics for these tables and uses Cost Based Optimizer(CBO). The other two possible values are FIRST_ROWS_N and FIRST_ROWS.
The CHOOSE and RULE values are no longer supported by Oracle.

In Oracle Database 10g an automatic statistics gathering job is enabledby default when a database is created .This is because of the default setting STATISTICS_LEVEL=TYPICAL.
This is also true when you do database upgrade. Oracle will automatically collect statistics for alldatabase objects which are missing statistics by running an Oracle Scheduler job(GATHER_STATS_JOB) during a predefined maintenance window.
Statistics on a table are considered stale when more than 10% of the rows are changed.
The maintenance window by default is opens every night from 10:00 pm to 6:00 am and all day on weekends.This can be changed.

In 9i to gather statistics automatically on tables, you have to set MONITORING on when creating the table or using the alter table command.
In 10g MONITORING and NOMONITORING is no longer in use.It has been depreciated.

Monitoring tracks the approximate number of DMLs (INSERT, UPDATE, and DELETE)operations for the table since the last time statistics were gathered. information about how many rows are affected is maintained in the SGA, until periodically (about every 15 minutes.This can be changed) after that the background process called SMON flush the data into the data dictionary.(another use of SMON )
This data dictionary information is made AVAILABLE through the tablesDBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS or USER_TAB_MODIFICATIONS views.
Oracle uses these views to identify tables with stale statistics. Whenever there is 10% change in data in a table, Oracle considers the statistics to be stale.Then it will start to collect statiscts during the maintanence window (STATISTICS_LEVEL=TYPICAL. That is the default)
Since in 10g CBO is used, having up to date statistics is so important to generate good execution plans. Automatic statistics collection is the way to go in 10g.

Oracle Remote Diagnostic Agent (RDA)

2008-03-26T11:01:00.002+08:00

Remote Diagnostic Agent (RDA) is a set of command-line diagnostic scripts that are executed by an engine written in the Perl programming language.
RDA is a good tool to gather detailed information about an Oracle environment. The data gathered or the output is used to aid in problem diagnosisand also also for seeing the overall system configuration.

Oracle Support encourages the use of RDA because it provides a detail picture of the customer's environment. . RDA is designed to be as unobtrusive as possible; it does not modify Oracle or OS systems in any way. It collects useful data for diagnostic or to see the overall picture.

OS Platforms that are supported
IBM AIX
Intel Linux (Oracle Enterprise, RedHat, and SuSE)
HP-UX (10.* and 11.*)
HP Tru64 Linux PPC
Mac OS X/Darwin
Sequent Dynix/Ptx
Sun Solaris (2.6 - 2.10)
Windows XP Professional
Windows 2000 Workstation and Server
Windows 2003 Server
HP OpenVMS Alpha 7.3-2 and above
HP OpenVMS Itanium
ZLinux

Oracle Products that are supported
Oracle RDBMS Server (Standard and Enterprise Editions)
Oracle RAC Cluster (Single/Multiple Nodes, Automatic Storage Management, Oracle Cluster File System, Data Guard)
Oracle Application Server (iAS 1.0.2.x/9.0.x/10.1.2.x/10.1.3.x,10.1.4.x,
HTTP Server,WebCache,J2EE/OC4J)
Oracle Oracle Data Integrator
Oracle Management Server and Intelligent Agent (Grid Server, Agent Server, DB Control) OLAP Products (Express Server, Financial Analyzer, and Demand Planning Server)
Oracle Developer (Forms and Reports)
Oracle JDBC/PRO *C/ODBC and other development client related information
Oracle Collaboration Suites (Email Server,Calendar,Discussions,Content Services,Workspaces,WebClient, and Wireless)
Oracle Identity Management (Internet Directory and COREid)
Oracle Networking products
Oracle Ebusiness Suite 11i and 12
Oracle Portal Software 7.0, 7.2, and 7.3
Oracle Retail (Retek)

for more information on how to download,install and run RDA,please see metalink Note: 330363.1

What a DBA shouldn't do....

2008-03-17T15:20:00.002+08:00

Here is a list of things that a DBA shouldn't do.I got this info
from an Oracle expert Rachel Carmichael of Dragonfly Consulting LLC.She presented this topic in an event organized by International Oracle Users Group (IOUG).i think it will benefit you all.

1. Dont backup your database - This is one BIG MISTAKE you can
do.Database is slow or fast, but it is still running.When data is
gone, you are gone too.

2.Do not test the recovery features - Whe you do backup, please test
the recovery features, this is the best way to know whether you are
backing up properly.

3.If you do backup,you do not need to export - Sometimes export can
help you to recover from certain issues.A good dba must always think
ways to lower MTTR (Mean Time To Recover).

4.Do not monitor the database.Anything wrong user will complain- To
be a good DBA, always be PROACTIVE not REACTIVE.

5.Use cache hit ratios instead of wait events to monitor performance.- If older systems yes.9i and 10g BETTER use wait events.More accurate.

6.Got a performance problem? Increase your shared pool.Please investigate what the real issue is.Sometimes increasing memory can halt your system.

7.Index every single column in every single table just in case-the more index you have,the more upkeep Oracle has to do.Please always justify your index creation

8.Don't analyze tables or generate stats!- Oracle becomes more cleverer if you generate stats and also please test first.

9.Fix your space problems by turning autoextend on every datafile in every tablespace. -This is not the best way or the right way.You won't know when you are going to hit a big problem.

10.Go ahead -- apply every critical fix without testing them first.- Please test sometimes it can back fire.

11.When new releases come out, upgrade ASAP and use all the new features in each release.- Please know whether you need it or your application support it first.Always let others test first before you do it :-)

12.Grant everyone "connect," "resource" and "DBA" privileges.- This is the worse thing you can do.Give access to users who need it only.

Enjoy thinking.......

Basic Database security

2008-02-11T16:54:00.000+08:00

As a DBA, it is your responsibility to secure your organization's database from unauthorized access.

In this article I am going to suggest some guidelines that is mandatory to have for all oracle databases.
1. Data Integrity:

Always keep a check on integrity of the data in the database. Before adding any sort of data whether important or unimportant your system must ensure that the data is valid and correct. Data Integrity prevents data from becoming invalid always returns correct results.

2. Use Access Control System:

Always grant enough or appropriate privileges to users and those privileges should be given to only those users who require them to accomplish their tasks. Excessive grant of unnecessary privileges can compromise the security of your database.

3. Role Based Authentication:

You can also grant role based authentication. You can segregate the roles into administrator, programmer, operator, user and so on. Permission to perform a certain task should be based on the privileges granted to that role. Also maintain a log of each role to keep track of each role's activities.

4. Password Protection:
Make your database root account password protected. Also maintain password for each user account in the database.

5. Use Oracle Views:

Views provide a powerful and flexible security mechanism for the database. By using views you can hide parts of database from certain users. The user is not aware of the parts missing from the view.

6. Back Up your database appropriately:
Backup your database and archive logs correctly, so that you can recovery it in case of failure. Do test your backup from time to time.

7. Maintain Log Files:
Maintain your database log files if you can.This will help you keep track of client's activities.

8. Disable Remote Access:
If it is feasible for your company, ensure that no users can get remote access to your database system. This will reduce the risk of network attacks from unauthorized or malicious users.

Opening a Oracle 10gR2 standby database in Read-Write mode for DML, DDL operations without affecting the primary database.

2008-01-28T12:14:00.000+08:00

Task

Opening the Standby Database in Read-Write mode, do a test and bring it back to a standby database without recopying of datafiles, logfiles or recreating the standby controlfile. Then showing that the test did not have an effect on primary db.

Summary

Before proceeding, one must have a working dataguard configuration. Make sure that logs are being passed from the primary db to the secondary db. The task here is to open the standby database in read-write mode, insert some data and then revert it back to its previous consistent state with the primary db without recopying of datafiles or recreating the standby controlfile. This is similar to the snapshot feature available in 11g.

Benefit

This is helpful when one has a working dataguard configuration and wants to test some scripts on live data. After the tests, data can be reverted to its original state. This also avoids the need to create a test database or perform export or import to get the live data to test on as this is time consuming.

I. STEPS

NOTE: Make sure that the dataguard is working

NOTE: The following steps are performed on the PRIMARY

Create a table and insert some values
SQL> create table mytest(id number);
SQL> insert into mytest values(1);
SQL> insert into mytest values(2);
SQL> insert into mytest values(3);
SQL> commit;

NOTE: The following steps are performed on the STANDBY

Stop the Dataguard
a. SQL> alter database recover managed standby database cancel;

Enable Flashback
a. Check whether flashback is enabled
i. SQL> select flashback_on from v$database;
ii. If no, then
· SQL> alter database flashback on;

Create restore point
SQL> create restore point before_open guarantee flashback database;

NOTE: The following steps are performed on the PRIMARY

Archive the current log file
a. SQL> alter system archive log current;

Stop logs being shipped to the standby
a. SQL> alter system set log_archive_dest_state_2=DEFER

NOTE: The following steps are performed on the STANDBY

Activate the standby database
a. SQL> alter database activate standby database

Open the standby database
a. SQL> alter database open

Query mytest table and insert some values
a. SQL> select * from mytest; (Here you will get 3 rows)
b. SQL> insert into mytest values(4);
c. SQL> insert into mytest values(5);
d. SQL> insert into mytest values(6);
e. SQL> select * from mytest; (Here you will get 6 rows)

NOTE: The following steps are performed on the STANDBY

To revert the standby back to its previous configuration, use the restore point we created. This would means any changes to the done to the data while the standby was open is lost.
a. SQL> shutdown immediate;
b. SQL> startup mount;
c. SQL> flashback database to restore point before_open;

Switch it back to a standby database
a. SQL> alter database convert to physical standby;

NOTE: The following steps are performed on the PRIMARY

Enable logs to be shipping from primary again
a. SQL> alter system set log_archive_dest_state_2=ENABLE;

NOTE: The following steps are performed on the STANDBY

Drop the restore point
a. SQL> drop restore point before_open;

Activate the Dataguard
a. SQL> alter database recover managed standby database disconnect from session;

NOTE: The following steps are performed on the PRIMARY

Query the mytest table
a. SQL> select * from mytest (Here you will get the 3 original rows)

Check whether the logs are being shipped from the primary to the standby
a. SQL> archive log list;
b. SQL> alter system switch logfile;

NOTE: The following steps are performed on the STANDBY

Check whether the standby is receiving the logs
a. SQL> select sequence#, applied, archived from v$archived_log;

Oracle Secure Backup and Oracle Secure Backup Express

2007-12-14T11:53:00.000+08:00

Now ORACLE provides a free Media Management layer(MML) as part of its Oracle Secure Backup server software.

It offers a easy and convinent way to implement a tape backup statergy without using a third party MML where you must pay aditional licence fees.

If you have a single server, you can use Oracle Secure Backup Express.It is limited to a single host with one direct-attached tape drive.

Oracle Secure Backup Express is the replacement product for Legato Storage Manager (LSM) and Legato Single Server Version (LSSV).
One distinct advantage of OSB Express over LSSV is that OSB Express can do file system backup.
Oracle Secure Backup Express is bundled with the Oracle Database and its free.

Oracle's technical support organization will provide technical support, phone support, or updates to you for Oracle Secure Backup Express when it is used to back up another Oracle Product that has a valid Customer Support Identifier (CSI).

If you have a big environment you can use the full fledge Oracle Secure Backup.
Oracle Secure Backup is licensed by the number of tape drives utilized (clients are unlimited).
You can user OSB with Oracle Database 9i,10g and 11g.

RMAN and Online Redo logs

2007-12-10T18:53:00.000+08:00

As you all know RMAN doesnt backup online redo logs

The online redo logs files are never backed up by RMAN or any hot backup method.

The online redo logs files contain the latest data or redo generated by the database.(The data comes from redo log buffer written by LGWR).

The contents of the current redo logs are not archived until a log switch occurs.
This means if all your members in a redo log group fails, you would lose the latest information.

To minimize these failures, here are some suggestions that you can implement to safeguard your online redo logs
1. Multiplex your groups to have redundancy members
2. Do not put same members of the same group in one disk controller
3. Use RAID for your online redo logs
4. Make sure switching happens at regular intervals (use archive_lag_target parameter)

Only Oracle Dataguard Implementation with Maximum Protection Mode can save all committed data in the event you lose all members of the current online redolog group

Oracle Background Processes

2007-11-29T16:18:00.000+08:00

Hi,Sorry for not posting any info for some time because I have been really busy.

Here is an article on Background processes.

Background processes for Orcacle9i

Database writer (DBWn)
· The database writer writes modified blocks from the database buffer cache to the datafiles.
· Oracle allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj).
· The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.
· Oracle selects an appropriate default setting for this initialization parameter (or might adjust a user specified setting) based upon the number of CPUs and the number of processor groups.

Log writer (LGWR)
· The log writer process writes redo log entries to disk.
· Redo log entries are generated in the redo log buffer of the system global area (SGA), and LGWR writes the redo log entries sequentially into an online redo log file.
· If the database has a multiplexed redo log, LGWR writes the redo log entries to a group of online redo log files.

Checkpoint (CKPT)
· At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint.

· The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

System monitor (SMON)
· The system monitor performs crash recovery when a failed instance starts up again.
· In a cluster database (Oracle9i Real Application Clusters), the SMON process of one instance can perform instance recovery for other instances that have failed.
· SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.
· SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate.

Process monitor (PMON)
· The process monitor performs process recovery when a user process fails.
· PMON is responsible for cleaning up the cache and freeing resources that the process was using.
· PMON also checks on the dispatcher processes and server processes and restarts them if they have failed.

Archiver (ARCn)
· One or more archiver processes copy the online redo log files to archival storage when they are full or a log switch occurs.

Recoverer (RECO)
· The recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.
· At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

New Background process for Oracle10g

Memory Monitor (MMON)
MMON performs various manageability-related background tasks, for example:
· Issuing alerts whenever a given metrics violates its threshold value.
· Taking snapshots by spawning additional process (MMON slaves)
· Capturing statistics value for SQL objects which have been recently modified

Recovery Writer (RVWR)
Enabling Flashback Database starts a new RVWR background process.
This process is similar to the LGWR (log writer) process.
The new process writes Flashback Database data to the Flashback Database logs.

Memory Manager (MMAN)
Memory Manager (MMAN) coordinates the sizing of different memory components within SGA.
MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage.
This process is used only when you enable Automatic Shared Memory Management.

Memory Monitor Light (MMNL)
· The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed.
· Performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
· It works with Automatic Workload Repository (AWR)

Please post if you have any questions.Thanks

Flashback Recovery

2007-08-30T12:52:00.000+08:00

A new feature in Oracle 10g. With Oracle 10g, flashback database enables to rollback the database either in full restore or partially to a specific point-in-time. Flashback feature is an easy and fast way to restore the database to a previous state without hassle.

This feature in Oracle 9i was called a flashback query. Restore is dependant on the size of the undo tablespace retention period. The bigger the undo area the longer Oracle keeps data.

Basic implementation of flashback recovery.

1) Enable archivelog:

SQL> startup mount;
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17

2) Setting required parameters:

* mounted mode

SQL> alter system set db_flashback_retention_target=1200;

System altered.

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size big integer 2G

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

- db_flashback_retention_target = seconds the flashback data is kept.
- db_recovery_file_dest_size = size of the flashback data.
- db_recovery_file_dest = destination of the flashback data.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

3) Verifying the flashback statistics:

SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0

4) Testing the flashback recovery:

creating a tablespace:
SQL> create tablespace test datafile
2 'd:\oracle\product\10.2.0\oradata\TIME\t1.dbf' size 1m
3 extent management local uniform size 100k;

Tablespace created

creating user:
SQL> create user flash identified by back
2 default tablespace test
3 quota 200k on test
4 temporary tablespace temp;

User created

SQL> grant connect, resource to flash;

Grant succeeded.

5) Creating a table:

SQL> conn flash/back
Connected.

SQL> create table time (start_tag NUMBER(5), end_tag NUMBER(5));

Table created.

SQL> insert into time VALUES ('1.001','1.010');

1 row created.

SQL> insert into time VALUES ('1.003','1.010');

1 row created.

SQL> commit;

Commit complete.

SQL> drop table time;

Table dropped.

SQL> desc time;
ERROR:
ORA-04043: object time does not exist

6) Running flashback on table level:

SQL> flashback table "TIME" to before drop;

Flashback complete.

SQL> desc time;
Name Null? Type
----------------------------------------- -------- ----------------------------
START_TAG NUMBER(5)
END_TAG NUMBER(5)

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
144445440

7) Running flashback database recovery:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('23-08-2007 01:59', 'DD-MM-YYYY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select username from user_users where username='FLASH';

no rows selected

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
318155 23-AUG-07 1200 8192000
0

FLASH RECOVERY AREA 10g

2007-07-24T16:11:00.000+08:00

The Flash Recovery Area, a new feature in 10g, is a specific area of disk storage that is set aside for retention of backup components such as :-
datafiles
image copies
archived redo logs
control file autobackup copies and etc

Features include:

Centralized Backup Files location - All backup components can be stored in one consolidated disk. The Flash Recovery Area can be configured for use by multiple database instances if so desired.
Automated Disk-Based Backup and Recovery -Once the Flash Recovery Area is configured, all backup components are managed automatically by the Oracle server.

Flashback Logs. The Flash Recovery Area is also used to store and manage flashback logs, which are used during Flashback Backup operations to quickly restore a database to a prior desired state.( I will discuss this in another article)

Oracle recommends that the Flash Recovery Area should be sized large enough to include all files required for backup and recovery. Just make sure you have enough continuous disk space.

When sizing, you need to take into account the RMAN backup retention policy, storage device type that you are using and number of data block changes.

A warning server alert is issued when the flash recovery area is 85% full.
A critical server alert is issued when the flash recovery area is 97% full.
(These percentages can be changed )

You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.
If the flash recovery area becomes full, an error will be issued.

Setting Up the Flash Recovery Area.
These involve setting up 2 main initialization parameter.

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area.( when reached maximum, it can be changed by alter system)
This parameter has to be set first

DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flashback Recovery Area will be stored. Oracle recommends that this be a separate location from the database's datafiles, control files, and redo logs

ALTER SYSTEM SET db_file_recovery_dest_size = '5G' SCOPE=BOTH;
and then set DB_FILE_RECOVERY_DEST and DB_FLASHBACK_RETENTION_TARGET

ALTER SYSTEM SET db_file_recovery_dest = 'c:\oracle\innotiive\flash' SCOPE=BOTH;
ALTER SYSTEM SET db_flashback_retention_target = 2880;( in minutes)
(It is for retention of flasback logs ).

Renaming the existing database

2007-06-16T17:23:00.000+08:00

Here is a step by step guide on how to change an existing database name. Ths guide is done by one of the Innotiiveasia's consultant.I hope it helps you all and if you have any comments, feel free to do it in this blog.Hapy reading.

Renaming the existing database

Connect to the database we wanted to change.

SQL> select name from v$database;

NAME
---------
PROD

The existing database name is prod, changing the database name to test. First force a log switch.

SQL> alter system switch logfile;

System altered.

Then backup the controlfile to trace

SQL> alter database backup controlfile to trace resetlogs;

Database altered.
Note: This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.

4. shutdown the database using shutdown immediate command.
SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

5. Find the trace file in udump and modify it as below

cd $ORACLE_HOME/admin/prod/udump
[oracle@localhost udump]$ ls -lrt
total 1024

-rw-r----- 1 oracle dba 695 Jun 12 13:41 prod_ora_6567.trc
-rw-r----- 1 oracle dba 2094 Jun 12 13:41 prod_ora_6569.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:42 prod_ora_6604.trc
-rw-r----- 1 oracle dba 666 Jun 12 13:42 prod_ora_6580.trc
-rw-r----- 1 oracle dba 795 Jun 12 13:42 prod_ora_6605.trc
-rw-r----- 1 oracle dba 638 Jun 12 13:43 prod_ora_6619.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:43 prod_ora_6642.trc
-rw-r----- 1 oracle dba 6161 Jun 12 15:29 prod_ora_6644.trc
-rw-r----- 1 oracle dba 695 Jun 12 16:25 prod_ora_7693.trc
-rw-r----- 1 oracle dba 666 Jun 12 16:25 prod_ora_7668.trc
-rw-r----- 1 oracle dba 10260 Jun 12 16:40 prod_ora_7694.trc

6. Check the latest trace file and use vi to edit the file. Delete all the statement in trace file until the file set 2 STARTUP NOMOUNT.

Below is the trace file, which i deleted the unnecessary statement until the startup nomount. STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top

7. Edit the trace file. Change the word 'REUSE' to 'set' and the 'olddbname' to ‘newdbname’.

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS

This is how the file will look after modification

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M

-- STANDBY LOG FILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top

Note: SAVE the modified trace file to a new name.sql such as “rename.sql”

8. Connect to sqlplus and startup the database in nomount stage and check whether the database using spfile or pfile.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218944 bytes
Variable Size 79693440 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes

SQL> show parameter spfile;
NAME TYPE VALUE
----------- ------------------------------
spfile string

SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string prod

9. Change the db_name in spfile to the new database name

SQL> alter system set db_name=test scope=spfile;
System altered.

SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string test
10. Shutdown the database, rename the existing controlfile and run the script

[oracle@localhost udump]$ cd /u02
[oracle@localhost u02]$ cd oradata/prod
[oracle@localhost prod]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
control03.ctl redo03.log temp01.dbf
[oracle@localhost prod]$ mv control01.ctl con1.ctl
[oracle@localhost prod]$ mv control02.ctl con2.ctl
oracle@localhost prod]$ mv control03.ctl con3.ctl

[oracle@localhost prod]$ lscon1.ctl con3.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf control2.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

11. Run The Script
SQL>@$ORACLE_HOME/admin/prod/udump/rename

ORA-00308: cannot open archived log 'ARCH:
'ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested filename AUTO CANCEL}

11. When we encounter the statement above apply the logfiles path

Specify log: {=suggested filename AUTO CANCEL}
/u02/oradata/prod/redo02.log
Log applied.
Media recovery complete.

12. Once the media recovery complete, open the database with resetlogs
SQL> alter database open resetlogs;
Database altered.

13. The global database name may also need to be changed

SQL> alter database rename global_name to test;
Database altered.

14. To confirm the database name already been changed,

select the database name from v$database.

SQL> select name from v$database;
NAME
---------
TEST

Segment Shrink in 10g

2007-05-31T12:28:00.000+08:00

DML activities like updates and deletes will make segements have chunks of free space within their data blocks.This will make the particular object fragmented
This can lead to very poor performance because select statements that does full table scans will have to scan more blocks to retrieve data.

In oracle 9i, to reclaim free space we can do the following
1.Drop the table,recreate it and then load back the data or
2.Use the 'ALTER TABLE MOVE' command to move the table to a new tablespace
3.Online table reorganization

In Oracle 10g, you are able to shrink segments with commands directly.

During this operation
-Segment data is compacted.
-High-water mark (HWM) is brought down.
-Unused space is released back to the tablespace containing the segment.

e.g
-- Enable row movement.
ALTER TABLE ABU.ORDERS ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE ABU.ORDERS SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE ABU.ORDERS SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place.
For these operations to work, the objects needs to be in a tablespace with automatic segment-space management enabled.
You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.

Temporary tablespace groups in 10g

2007-05-28T13:39:00.000+08:00

The main idea behind temporary tablespace groups is to improve the scalability of operations that require more temporary space. This is a new feature introduced in 10g.

There must be a minimum of one tablespace in a temporary tablespace group and groups cannot exist without at least one temporary tablespace.

The group will not exist when the last tablespace member is removed from it. For a user to be able to use the group, assign the tablespace group name when assigning temporary tablespace to users.

There is no explicit limit on the maximum number of tablespaces that are contained in a group.

EXAMPLES
– create a temporary tablespace without a group

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP ‘’; -- same as not specifying a group

– create a temporary tablespace group with one tablespace (TEMP1) in it:

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP TEMPGRP1;

If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:

– user TEMPGRP1 as the default temporary tablespace for all new users in the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGRP1;

- Make user ALI use the temporary tablespace group

ALTER USER ALI TEMPORARY TABLESPACE TEMPGRP1;

Roles

2007-04-28T23:33:00.000+08:00

Before I talk about roles, there is a few points that I want the readers to understand before I begin.

All Oracle objects (e.g.tables, indexes, etc) are owned by users who created them.
- only the user who created a table will be able to see its data.To allow other to see rows, the owner/user must grant access privileges.
- Oracle has system privileges that allow global access rights.
- Oracle has object privileges that allow grants to specific tables.

Any big organization will face difficulties to manage their database users in the form of managing their system and objects privileges.

In these companies staffs come and go plus they may have selected privileges on many objects.
Roles will be most helpful to manage this problem.

A role is a set or group of privileges that can be granted to users or roles. This is a great way for DBA to save time and effort.

To be able to create a role, you must have at least the CREATE ROLE system privileges.

The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;

If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

Example:
CREATE ROLE abc;
This 1st example creates a role called abc.

CREATE ROLE abcIDENTIFIED BY abc123;
This 2nd example creates the same role called abc, but now it is password protected with the password of abc123.

The syntax for granting privileges on a table is:
grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called emp to a role named abc, you would execute the following statement:

grant select, insert, update, delete on emp to abc;

You can also use the all keyword to indicate that you wish all permissions to be granted.

For example:
grant all on emp to abc;

Revoke Privileges (on Tables) to Roles.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called emp from a role named abc, you would execute the following statement:
revoke delete on emp from abc;

If you wanted to revoke all privileges on a table, you could use the all keyword.
For example:
revoke all on emp from abc;

Granting the Role to a User
GRANT role_name TO user_name;

For example:
GRANT abc to bakar;
This example would grant the role called abc to the user named bakar.

The user bakar will automatically gets all the privileges that was in the role abc

The SET ROLE statement allows you to enable or disable a role for a current session.

When a user logs into the database, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:
SET ROLE( role_name [ IDENTIFIED BY password ] ALL [EXCEPT roleA, roleB, ... ] NONE );
The role_name phrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles for the current session. (including all default roles)

Example
SET ROLE abc IDENTIFIED BY abc123;
This example would enable the role called abc with a password of abc123.

A default role means that the role is always enabled for the current session when the user logs on. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:
ALTER USER user_nameDEFAULT ROLE( role_name ALL [EXCEPT roleA, roleB, ... ] NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.
The role_name phrase is the name of the role that you wish to set as DEFAULT.
The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles as DEFAULT.

Example:
ALTER USER bakarDEFAULT ROLE abc;
This example would set the role called abc as a DEFAULT role for the user called bakar.

ALTER USER bakar DEFAULT ROLE ALL;
This example would set all roles assigned to bakar as DEFAULT.

ALTER USER bakarDEFAULT ROLEALL EXCEPT abc;
This example would set all roles assigned to bakar as DEFAULT, except for the role called abc.

How to be a successful Oracle Database Professional

2007-04-12T20:58:00.000+08:00

I read this article in a blog from a very famous Oracle Guru, Tom Kyte (http://tkyte.blogspot.com)

I thought of sharing this with you all of what he thinks to become a successful Oracle Database Professional.

Ways to go to become a successful Oracle Database Professional

1. Be involved, participate. I cannot stress that one enough.

2. Understand. It is not good enough to think you know how something should work according to you. You have to understand how it actually does work. What you believe to be the way something is implemented could be far from the truth as to how it actually is implemented.

3.Ask why whenever you don't know why. We could just shorten that to "ask". If you don't ask, you'll never know. If you are answering questions and don't ask some yourself - you are doing it wrong.

4.Trust but Question Authority. If you see something written as a statement of fact without any supporting evidence either ignore it entirely or if you feel it could be beneficial to you if true develop a method to test the concept and see if it holds true.

5.If you want to be successful, you'll be communicating. Be precise in that communication. It really does make a difference. Clarity is necessary and preciseness is mandatory for clarity.

6.When you participate and start giving your own answers, your own input - do it right

I hope this sayings from Tom Kyte motivates you all.

orakill on windows

2007-04-02T08:46:00.000+08:00

This utility is provided only with Oracle databases on WINDOWS platforms.Ths executable allows a DBA to kill sessions in Oracle withuot connecting to the database.That is you can do it on DOS command prompt.(orakill.exe)

Unix system are processes based while windows environment are thread based.
In Unix, every oracle sessions can be seen individually by using the ps comand but in windows
based system all sessions are in the oracle.exe executables.That means you won't be able to see
sessions individually.

The orakill will do the same thing in windows what kill -9 does in UNIX.
C:\oracle>orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill

The thread id should be retrieved from the spid column of a query such as:

select spid, osuser, s.program
from v$process p, v$session s
where p.addr=s.paddr;

to get a better view, you can user the below query to replace the above.

select b.spid, a.osuser, a.username
from v$process b, v$session a
where b.addr=a.paddr
and a.username is not null;
e.g
SPID OSUSER USERNAME
------------ ------------------------------ -------------------
2508 MURALI\innotiive PRASANA
2644 MURALI\innotiive ALI

C:\oracle>orakill PROD 2644
Kill of thread id 2644 in instance PROD successfully signalled.

SQL>select b.spid, a.osuser, a.username from
v$process b, v$session a where b.addr=a.paddr
and a.username is not null;
SPID OSUSER USERNAME
------------ ------------------------------ --------------
2508 MURALI\innotiive PRASANA

You can also kill a user session from the Oracle databasee.g.alter system kil session (sid,serial#);

You can get the value of sid and serial# from the v$session view.
One of the reason to use orakill instead of alter system kill session is orakill will clear
any locks that exist but alter system kill session will remain connected until it times out and
then it release the locks.

The orakill utility should be used as a last resort only. If the session cannot be killed more
gracefully (via alter system kill session), or the instance is inaccessible via SQL, then
orakill should be used to terminate the offending session.

Be careful not to kill a background process as this may cause the database to be down.

Bigfile Tablespace in 10g

2007-03-24T00:16:00.000+08:00

Database tables nowdays holds alot of data. This is of because data is important for companies to make decisions.As you are aware the concept of BI (Business Intelligence), it is becoming famous nowdays. BI needs alot of data to make better decisions.

To address this craving,Oracle has come up with th Bigfile Tablespace concept.

Bigfile tablespaces(BFT) are tablespaces with a single large datafile(this means you can only have 1 datafile for bigfile tablespace). In contrast to normal (smallfile) tablespaces can have several datafiles(max 1022 files), but each is limited in size (the size of the disk partiton).

This concept is introduced in 10g.

Bigfile tablespaces(BFT) must be locally managed with automatic segment-space management.

Temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces are exceptions to this rule.

e.g. of creating a bigfile tablespace

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G;

OR

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

DBA_TABLESPACES AND V$TABLESPACE contain a new column called BIGFILE that allows administrators to determine if the tablespace is a Smallfile or Bigfile tablespace.

Before you even consider creating one of these things, make sure that your environment is able to support its expansion

For using BFT,the underlying operating system should support Large File.In other words the file system should have Large File Support(LFS).

Bigfile tablespaces will have a future in many Oracle environments but they must only be implemented with much forethought.

Oracle Recycle Bin

2007-03-16T01:14:00.000+08:00

This is a new feature in Oracle10g.Prior to 10g,when you issue the DROP command
e.g. DROP table EMP;

The particular table is dropped and also its dependent objects (indexes,costraints and triggers,etc).
In Oracle 10g it will be renamed to a system generated string and logically placed in the recycle bin.Each dropped object will be placed in the recycle bin which is in the same tablespace the schema from which it was dropped from.

The recycle bin is actually a new data dictionary table(SYS.RECYCLEBIN$) that records information about dropped tables.When an object is dropped,Oracle just renames the table and all its associated objects (indexes, triggers,etc) to a system-generated name that begins with BIN$.
There are two recyclebin views, USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

Users will retain all writes and privileges to the recyclebin object just as before the object was dropped.
There is no special storage for these dropped objects because it will still remain in the tablespace they were dropped from.
Dropped objects is still counted in the users's quota.

Here are some examples.Have fun!!!

SQL> create table t (name varchar2(8));
Table created.

SQL> insert into t values ('ali');
1 row created.

SQL> insert into t values ('abu');
1 row created.

SQL> select * from t;
NAME
--------
ali
abu

SQL> drop table t;
Table dropped.

SQL> select * from t;select * from t
*ERROR at line 1:ORA-00942: table or view does not exist

SQL> select object_name,original_name,ts_name,droptime from recyclebin;

OBJECT_ NAME ORIGINAL_NAME TS_NAME DROPTIME
---------------------- ------------------- ------------ ---------------
BIN$DgYaFTOBTrScJ+2ba85IXA==$0 T USERS 2007-03-15:10:14:22

You can also type

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME-------------- --------------- --------------------- --------------- -------------
T BIN$DgYaFTOBTrScJ+2ba85IXA==$0 TABLE 2007-03-15:10:14:22

To query the content of a table in recyclebin

SQL> select * from "BIN$DgYaFTOBTrScJ+2ba85IXA==$0";
NAME
--------
ali
abu

to recover the dropped table
SQL> flashback table t to before drop;
Flashback complete.

SQL> select * from t;
NAME
--------
ali
abu

SQL> select * from recyclebin;
no rows selected

Some other commands
1.to recover the table to a different name
SQL> flashback table t to before drop rename to t1;

2. To drop permanently(do not go to recyclebin)
SQL> DROP table t purge;

When you issue the DROP TABLESPACE....INCLUDING CONTENTS,all objects will be drop and won't go to the recyclebin and all objects in the recyclebin will be removed.

Usually indexes,constraints and triggers should retain the original name after the dropped table is recovered but now the indexes has system generated names not original names.This is a bug in Oracle.

SPFILE or PFILE

2007-03-09T14:23:00.000+08:00

Someone ask me the other day the differences between spfile and pfile

Here are my input:

PFILE or SPFILE are file that is used to store database intialization parameters.

The pfile or spfile is read during instance startup to get instance specific settings.

SPFILE was implemented or introduced from 9i only.In 10g pfile is still supported
You only can use one at a time but both files can exist in the default location.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use the spfile.

Changes to pfile can be made by editing the file and do a shutdown startup but for spfile, changes are made using the alter system command.

To create a server parameter file from a parameter file
login as sys
create spfile from pfile;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

By using spfile changes can be made in 3 ways as specified below
SCOPE = SPFILE
e.g. alter system set sql_trace=true scope =spfile;
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)

SCOPE = MEMORY
e.g alter system set undo_tablespace=undotbs1 scope=memory;
(For dynamic parameters, changes are applied in memory only. No static parameter changes is allowed.)

SCOPE = BOTH
e.g alter system set log_archive_dest_1 =/u01/arch2 scope=both;
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameters changes is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

We can also use the server parameter file to create a pfile
This would be useful for:
1) Creating backups of the spfile.
2) For diagnostic purposes
3) Modifying the spfile by first exporting it, editing the output file, and then recreating it.

The following example creates a pfile from spfile
login as sys
CREATE PFILE FROM SPFILE;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

Fell free to comment or ask questions....Thanks

Welcome all Malaysian Oracle Users

2007-03-05T23:51:00.000+08:00

I am starting this blog to help Malaysian Oracle Users, so that they can share their experiences on Oracle Databases (7,8,8i,9i,10g)

I will try my best to post new/fresh/old experiences that I have encountered as a Database consultant.

Please give your comments if there is any mistakes or how to make this blog better.

Hope to do it weekly. WATCH OUT!!!