Build an Oracle Parameter File – a quick way

Sometimes, we need to build a parameter file without copying an existing one.   After all you need to remember all the necessary parameters, typo errors, incorrect values etc. will kill your time.  You can easily build a template from the existing software installation, though you don’t have a database created.  Here you go.

I assume you have set all oracle environment values correct or else use  . oraenv to set the environment.  Then set a dummy ORACLE_SID

oracle@:/home/oracle>$ export ORACLE_SID=XYZ

Start the instance in RMAN  NOMOUNT state.

 oracle@:/home/oracle>$ rman target /

 Recovery Manager: Release 11.1.0.6.0 – Production on Thu Aug 18 18:52:51 2011

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

connected to target database (not started)

 

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/oradata/oracle/product/11g/dbs/initXYZ.ora’

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

 

Total System Global Area     158703616 bytes

Fixed Size                     2086736 bytes

Variable Size                 83888304 bytes

Database Buffers              67108864 bytes

Redo Buffers                   5619712 bytes

 

RMAN> exit

Recovery Manager complete.

 

Here Oracle is thinking you have started the instance to restore the parameter file! Well good, now you have an instance up and build your parameter file from there – all default values.

oracle@:/home/oracle>$ sqlplus

SQL*Plus: Release 11.1.0.6.0 – Production on Thu Aug 18 18:53:30 2011

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name ||’=’||value from v$parameter where value is not null;

NAME||’=’||VALUE
———————————
processes=100
sessions=115
timed_statistics=TRUE
timed_os_statistics=0
resource_limit=FALSE
license_max_sessions=0
license_sessions_warning=0
cpu_count=2
sga_max_size=159383552
pre_page_sga=FALSE
shared_memory_address=0
hi_shared_memory_address=0
use_indirect_data_buffers=FALSE
lock_sga=FALSE
shared_pool_size=0
large_pool_size=0
java_pool_size=0
streams_pool_size=0
shared_pool_reserved_size=2306867
java_soft_sessionspace_limit=0
java_max_sessionspace_size=0
instance_type=RDBMS
nls_language=AMERICAN
nls_territory=AMERICA
nls_comp=BINARY
nls_length_semantics=BYTE
nls_nchar_conv_excp=FALSE
filesystemio_options=asynch
disk_asynch_io=TRUE
tape_asynch_io=TRUE
dbwr_io_slaves=0
backup_tape_io_slaves=FALSE
resource_manager_cpu_allocation=2
file_mapping=FALSE
gcs_server_processes=0
sga_target=159383552
memory_target=0
memory_max_target=0
control_files=/oradata/oracle/product/11g/dbs/cntrlXYZ.dbf
control_file_record_keep_time=7
db_block_buffers=0
db_block_checksum=TYPICAL
db_ultra_safe=OFF
db_block_size=8192
db_cache_size=0
db_2k_cache_size=0
db_4k_cache_size=0
db_8k_cache_size=0
db_16k_cache_size=0
db_32k_cache_size=0
db_keep_cache_size=0
db_recycle_cache_size=0
db_writer_processes=1
db_cache_advice=ON
max_commit_propagation_delay=0
compatible=11.1.0.6.0
log_archive_start=FALSE
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_dest_state_3=enable
log_archive_dest_state_4=enable
log_archive_dest_state_5=enable
log_archive_dest_state_6=enable
log_archive_dest_state_7=enable
log_archive_dest_state_8=enable
log_archive_dest_state_9=enable
log_archive_dest_state_10=enable
log_archive_max_processes=4
log_archive_min_succeed_dest=1
standby_archive_dest=?/dbs/arch
log_archive_trace=0
log_archive_local_first=TRUE
log_archive_format=%t_%s_%r.dbf
log_buffer=5349376
log_checkpoint_interval=0
log_checkpoint_timeout=1800
archive_lag_target=0
db_files=200
db_file_multiblock_read_count=102
read_only_open_delayed=FALSE
cluster_database=FALSE
parallel_server=FALSE
parallel_server_instances=1
cluster_database_instances=1
db_recovery_file_dest_size=0
standby_file_management=MANUAL
thread=0
fast_start_io_target=0
fast_start_mttr_target=0
log_checkpoints_to_alert=FALSE
db_lost_write_protect=NONE
recovery_parallelism=0
_dummy_instance=TRUE
db_flashback_retention_target=1440
dml_locks=504
replication_dependency_tracking=TRUE
transactions=126
transactions_per_rollback_segment=5
undo_management=AUTO
undo_retention=900
fast_start_parallel_rollback=LOW
resumable_timeout=0
instance_number=0
db_block_checking=FALSE
recyclebin=on
db_securefile=PERMITTED
serial_reuse=disable
ldap_directory_access=NONE
ldap_directory_sysauth=no
os_roles=FALSE
max_enabled_roles=150
remote_os_authent=FALSE
remote_os_roles=FALSE
sec_case_sensitive_logon=TRUE
O7_DICTIONARY_ACCESSIBILITY=FALSE
remote_login_passwordfile=EXCLUSIVE
license_max_users=0
audit_sys_operations=FALSE
global_names=FALSE
distributed_lock_timeout=60
commit_point_strength=1
global_txn_processes=1
instance_name=XYZ
service_names=DUMMY
shared_servers=0
cursor_space_for_time=FALSE
session_cached_cursors=50
remote_dependencies_mode=TIMESTAMP
plsql_v2_compatibility=FALSE
plsql_native_library_subdir_count=0
plsql_warnings=DISABLE:ALL
plsql_code_type=INTERPRETED
plsql_debug=FALSE
plsql_optimize_level=2
plscope_settings=IDENTIFIERS:NONE
java_jit_enabled=TRUE
job_queue_processes=1000
parallel_min_percent=0
create_bitmap_area_size=8388608
bitmap_merge_area_size=1048576
cursor_sharing=EXACT
result_cache_mode=MANUAL
parallel_min_servers=0
parallel_max_servers=20
parallel_execution_message_size=2152
hash_area_size=131072
result_cache_max_size=819200
result_cache_max_result=5
result_cache_remote_expiration=0
shadow_core_dump=partial
background_core_dump=partial
background_dump_dest=/home/oracle/app/oracle/diag/rdbms/dummy/XYZ/trace
user_dump_dest=/home/oracle/app/oracle/diag/rdbms/dummy/XYZ/trace
core_dump_dest=/home/oracle/app/oracle/diag/rdbms/dummy/XYZ/cdump
audit_file_dest=/oradata/oracle/product/11g/rdbms/audit
object_cache_optimal_size=102400
object_cache_max_size_percent=10
session_max_open_files=10
open_links=4
open_links_per_instance=4
optimizer_features_enable=11.1.0.6
audit_trail=NONE
sort_area_size=65536
sort_area_retained_size=0
db_name=DUMMY
db_unique_name=DUMMY
open_cursors=50
sql_trace=FALSE
os_authent_prefix=ops$
optimizer_mode=ALL_ROWS
sql92_security=FALSE
blank_trimming=FALSE
star_transformation_enabled=FALSE
parallel_adaptive_multi_user=TRUE
parallel_threads_per_cpu=2
parallel_automatic_tuning=FALSE
parallel_io_cap_enabled=FALSE
optimizer_index_cost_adj=100
optimizer_index_caching=0
query_rewrite_enabled=TRUE
query_rewrite_integrity=enforced
sql_version=NATIVE
pga_aggregate_target=31876710
workarea_size_policy=AUTO
optimizer_dynamic_sampling=2
statistics_level=TYPICAL
skip_unusable_indexes=TRUE
optimizer_secure_view_merging=TRUE
ddl_lock_timeout=0
optimizer_use_pending_statistics=FALSE
optimizer_capture_sql_plan_baselines=FALSE
optimizer_use_sql_plan_baselines=TRUE
optimizer_use_invisible_indexes=FALSE
sec_protocol_error_trace_action=TRACE
sec_protocol_error_further_action=CONTINUE
sec_max_failed_login_attempts=10
sec_return_server_release_banner=FALSE
enable_ddl_logging=FALSE
client_result_cache_size=0
client_result_cache_lag=3000
aq_tm_processes=0
hs_autoregister=TRUE
xml_db_events=enable
dg_broker_start=FALSE
drs_start=FALSE
dg_broker_config_file1=/oradata/oracle/product/11g/dbs/dr1DUMMY.dat
dg_broker_config_file2=/oradata/oracle/product/11g/dbs/dr2DUMMY.dat
olap_page_pool_size=0
asm_power_limit=1
control_management_pack_access=DIAGNOSTIC+TUNING
sqltune_category=DEFAULT
diagnostic_dest=/home/oracle/app/oracle
max_dump_file_size=unlimited
trace_enabled=TRUE

214 rows selected.

 Your parameter file is ready and make necessary changes.  Even if you are not deleting the  other entries, is not an issue as those are the default values!