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;
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
Sandeep,<br> <br> Like several have said, there is a way to tunnel DG arch log shipment over ssh. <br> I was on site where we did it (we did it for compression, not security).<br> In the end it caused is problems so we dumped it for straight SQL*Net...Sorry don't remember the details.<br> I think ssh encryption was making it to slow and the internal (asynchronous) arch buffer was filling...anyway...<br> <br> >> Is there any way that standby recover to the last available archived <br> >> log and comes out cleanly? How can I query the last archived log file <br> >> applied on the standby database?<br> <br> I do exactly what you talking about...a poor man's Standby.<br> I use cron, shell, sql, and RMAN (and a scp or better shared storage area)<br> <br> During a backup (shell script), I do this to gen recovery variables (for RMAN) in a new shell script;<br> <br> [THIS IS SNIPS OF MY SCRIPT]<br> ...<br> # - Backup archive logs and delete logs after backup is complete,<br> ...<br> BACKUP ARCHIVELOG ALL;<br> ..<br> <br> SET sqlprompt #<br> SET ECHO OFF<br> SET FEEDBACK OFF<br> SET HEADING OFF<br> SET PAGESIZE 0<br> SET line 200<br> SET RECSEP OFF<br> SET SERVEROUTPUT OFF<br> SET TRIMSPOOL ON<br> SET VERIFY OFF<br> SET TERMOUT OFF<br> spool rman_restore_script_SQL_OUT.sql<br> select '#!/bin/sh' from dual;<br> select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual;<br> alter system archive log current;<br> select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual;<br> alter system archive log current;<br> select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual;<br> 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;<br> select '# '||to_char(SYSDATE,'DD-MM-YYYY_HH24:MI') from dual;<br> <br> ...<br> # - Backup archive logs and delete logs after backup is complete,<br> ...<br> sql "alter system archive log current";<br> BACKUP ARCHIVELOG ALL;<br> ...<br> <br> <br> I purposely don't attempt to restore to the very last log incase is was not archived and backed up.<br> But you see I manually switch logs to try and get everything I need.<br> This works for me 100% of the time.<br> <br> <br> I use this SQL out in a new shell (RMAN Recvoery) script to rolls logs;<br> <br> ...<br> rman nocatalog msglog $log_dir/${log_file}_RMAN.log <<EOF > ;> $log_file 2>&1<br> #connect target $userid/$password;<br> connect target /;<br> <br> shutdown abort;<br> startup nomount;<br> run {allocate channel c1 type disk; replicate controlfile from '${bkp_dir}/${date_time}_${DATABASE_NAME}_controlfile.ctl.bkp'; release channel c1; }<br> alter database mount;<br> <br> run {<br> set until logseq ${logseq} thread ${thread};<br> allocate channel c2 type disk;<br> recover database;<br> sql 'alter database open read only';<br> release channel c2;<br> }<br> <br> exit<br> EOF<br> <br> <br> Simply my standby is just restoring from and RMAN backup all the time...well once per hour right now.<br> And I use SQL out put, to shell script, to RMAN syntax to make it happen.<br> <br> hth<br> <br> Chris Marquez<br> Oracle DBA<br>
