Oracle SQL gurus' help needed

[Ralph Grothe]

Hi,

I have no notion of Oracle and their SQL dialect.
But I need to modify a routine (currently a mere shell script thought up by my ancestor admin of this box), such a simple task as relocating archived redo logs to another backup server.
I want to replace this script by an extendable Perl module, because the shell script kept blocking transfers, and IPC is easier and more tangible in Perl.

In the first step I need to retrieve those Oracle configuration parameters from a bulk of independent DB SIDs:

log_archive_dest
log_archive_format
log_archive_start

I wouldn't want to read them from the init*.ora files of the respective SIDs since their different locations seem to be a mess to me.

After a long search through the data dictionary views (remember I'm an Oracle dummy) I discovered the view from where to get at least log_archive_dest, so that I successfully can fetch it through a DBI statement such as

($arch_dest) =
$dbh->selectrow_array('select destination from v$archive_dest');

But I cannot find the views for the others.

A DBA told me that I could use
"show parameters log" from server manager.
From this I saw that the 1st, and 3rd fields are relevant to me.

So I prepared, and executed a DBI statement handle accordingly.
(you may find my poorman's DBI code of this private sub of my package attached)
But when I step over the DBI execute method in the debugger I get this error:

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCI
StmtExecute) at

looks like "show parameters logs" is no valid idiom in Oracle SQL.

Can you tell me either how to convert this into valid Oracle SQL, or name me the views of the data dictionary to retrieve the wanted Oracle parameters from?

Regards
Ralph

[Kamran Hussain · Accepted solution]

It is:

show parameter log

or

show parameter archive

or

you can select from v$parameter:

select name, value from v$parameter
where name like '%arch%'

[John Palmer]

select value from v$parameter where name = 'log_archive_format';

select value from v$parameter where name = 'log_archive_start';

v$archived_log may also be useful, it holds the filenames of all the archived logs. Something like
select name from v$archived_log
where rownum < 2;
gives you the full pathname of anarchived log.

Regards,
John

[BLADE_1]

hi,

u could also find this information in the parametr file $ORACLE_HOME/dbs/init.ora file...


rgds
nainesh