Re: physical standby database managed/non-managed

From: Chris Marquez <marquezemail_at_gmail.com>
Date: Thu, 26 Jan 2006 14:18:01 -0500
Message-ID: <d494e9760601261118g412c95cenc7d12a31652d6c6f@mail.gmail.com>

Sandeep,

Like several have said, there is a way to tunnel DG arch log shipment over ssh.
I was on site where we did it (we did it for compression, not security). In the end it caused is problems so we dumped it for straight SQL*Net...Sorry don't remember the details. I think ssh encryption was making it to slow and the internal (asynchronous) arch buffer was filling...anyway...

>> Is there any way that standby recover to the last available archived
>> log and comes out cleanly? How can I query the last archived log file
>> applied on the standby database?

I do exactly what you talking about...a poor man's Standby. I use cron, shell, sql, and RMAN (and a scp or better shared storage area)

During a backup (shell script), I do this to gen recovery variables (for RMAN) in a new shell script;

[THIS IS SNIPS OF MY SCRIPT]
...
# - Backup archive logs and delete logs after backup is complete, ...

        BACKUP ARCHIVELOG ALL;
..

SET     sqlprompt #
SET     ECHO OFF
SET     FEEDBACK OFF
SET     HEADING OFF
SET     PAGESIZE 0
SET     line 200
SET     RECSEP OFF
SET     SERVEROUTPUT OFF
SET     TRIMSPOOL ON
SET     VERIFY OFF
SET     TERMOUT OFF

spool rman_restore_script_SQL_OUT.sql
select '#!/bin/sh' from dual;
select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual; alter system archive log current;
select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual; alter system archive log current;
select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual; select 'export logseq='||a.SEQUENCE#||'; export thread='||a.THREAD#||';' from sys.v_$archived_log a, sys.v_$instance i where a.THREAD# = i.THREAD#and COMPLETION_TIME > SYSDATE - 1/(60*24) order by COMPLETION_TIME desc;
select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual;

...
# - Backup archive logs and delete logs after backup is complete, ...

        sql "alter system archive log current";
        BACKUP ARCHIVELOG ALL;

...

I purposely don't attempt to restore to the very last log incase is was not archived and backed up.
But you see I manually switch logs to try and get everything I need. This works for me 100% of the time.

I use this SQL out in a new shell (RMAN Recvoery) script to rolls logs;

...
rman nocatalog msglog $log_dir/${log_file}_RMAN.log <<EOF >> $log_file 2>&1
#connect target $userid/$password;
connect target /;

shutdown abort;
startup nomount;
run {allocate channel c1 type disk; replicate controlfile from '${bkp_dir}/${date_time}_${DATABASE_NAME}_controlfile.ctl.bkp'; release channel c1; }
alter database mount;

run {
set until logseq ${logseq} thread ${thread}; allocate channel c2 type disk;
recover database;
sql 'alter database open read only';
release channel c2;
}

exit
EOF Simply my standby is just restoring from and RMAN backup all the time...well once per hour right now.
And I use SQL out put, to shell script, to RMAN syntax to make it happen.

hth

Chris Marquez
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 26 2006 - 13:18:01 CST