physical standby database managed/non-managed 2006-01-26 - By Chris Marquez
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
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>
|
|
