Pointless method of pfile recreation?



I've got a disaster recovery situation for you - pretty unlikely and pretty pointless but it again shows how useful cloud control can be....

So take the case that in some strange turn of disastrous events that we have everything we need to activate the database (datafile/redo etc etc) - the only thing missing is the lowly pfile/spfile that we need to start the instance up and get things going.

Somehow the pfile and spfile both got deleted, and somehow it's not been backed up at all with rman, bizarrely the alert log has also got removed (which could have told us what all of the settings were).

So we have nothing to start the instance.

It would be easy enough to just try and constuct one from scratch - all you really need is db_name and contro_files specified and you could just try and make up a lot of the the other settings until the system seemed OK again.

Is there another way though to get the exact file contents back as they were and save time rediscovering all the settings you had before by trial and error?

Well there is at least one - as long as you have cloud control set up.

The contents of the init file are uploaded to the repository and we can just query it we know where to look (which after some trial and error i do).

So if we know the db_name we are interested in we can just say

select init_param_name||'='||init_param_value
from MGMT$CS_DB_INIT_PARAMS p ,mgmt$target t
where t.target_name like '%YOUR_DB_NAME_HERE%'
and t.target_guid=p.target_guid
and p.INIT_PARAM_IS_DEFAULT='FALSE'

 (the number just comes from the fact i exported it in plsql developer which adds a row number column.
   INIT_PARAM_NAME||'='||INIT_PAR
1audit_file_dest=/oracle/admin/DBNAME/adump
2audit_trail=DB
3backup_tape_io_slaves=TRUE
4compatible=11.1.0.0.0
5control_file_record_keep_time=14
6control_files=/oracle/DBNAME/oradata/DBNAME/controlfile/o1_mf_87c072qk_.ctl, /oracle/DBNAME/recovery_area/DBNAME/controlfile/o1_mf_87c072t8_.ctl
7db_32k_cache_size=100663296
8db_block_checksum=full
9db_block_size=8192
10db_create_file_dest=/oracle/DBNAME/oradata
11db_domain=WORLD
12db_keep_cache_size=100663296
13db_name=DBNAME
14db_recovery_file_dest=/oracle/DBNAME/recovery_area
15db_recovery_file_dest_size=19327352832
16db_unique_name=DBNAME
17diagnostic_dest=/oracle/admin/DBNAME
18dispatchers=(PROTOCOL=TCP) (SERVICE=DBNAMEXDB)
19instance_name=DBNAME
20java_pool_size=268435456
21job_queue_processes=100
22log_archive_dest_1=LOCATION=USE_db_recovery_file_dest
23log_archive_format=DBNAME_%t_%s_%r.arc
24memory_target=0
25open_cursors=300
26pga_aggregate_target=314572800
27processes=300
28remote_login_passwordfile=EXCLUSIVE
29service_names=DBNAME, DBNAME.WORLD
30sessions=480
31sga_max_size=1577058304
32sga_target=1577058304
33shared_pool_size=268435456
34undo_tablespace=UNDOTBS1
35utl_file_dir=/oracle/utlfile/DBNAME

So you can see you can easily rebuild the exact file from that - just needs some quotes adding in the right places.......

Pretty pointless i know - but could be useful after a very unlikely series of events.......

Comments