Creating Snapshot StandBy (Report Server)

Beta version of the document. Needs to be tested on real servers! If anyone follows this version of the document, please report back on what works, what needs fixing.

Last done: ~ 2015-2016

Author:

Victor (Telegram @Vict0rGI)

One of the variants (Flashback) allows storing all changes during the day on the standby server in a temporary flash (size!!!) and at night roll forward to the current state, using only archive logs.

Create a database on the same server:

Stop StandBy FBID

Copy data to REPORT

Check

SELECT NAME FROM V$DATAFILE;

If the paths differ, fix them

SQL> alter database rename file 'D:\ORADATA\FBID\AFWW.DBF' to 'd:\oradata\report\afww.dbf'

initREPORT.ora

report.__db_cache_size=2399141888
report.__java_pool_size=16777216
report.__large_pool_size=16777216
report.__oracle_base='c:\app\oracle'#ORACLE_BASE set from environment
report.__pga_aggregate_target=2483027968
report.__sga_target=6106906624
report.__shared_io_pool_size=0
report.__shared_pool_size=3623878656
report.__streams_pool_size=0
*._dbms_sql_security_level=384
*._optimizer_null_aware_antijoin=FALSE
*._pga_max_size=536870912
*.audit_file_dest='C:\APP\ORACLE\ADMIN\REPORT\ADUMP'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='D:\ORADATA\REPORT\CONTROL01.DBF','D:\ORADATA\REPORT\CONTROL02.DBF','D:\ORADATA\REPORT\CONTROL03.DBF'
*.db_block_size=8192
*.db_domain=''
*.db_name='fbid'
*.db_unique_name='report'
*.db_recovery_file_dest='d:\oradata\report\fast_recovery_area'
*.db_recovery_file_dest_size=397284474880
*.diagnostic_dest='C:\APP\ORACLE'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=reportXDB)'
*.event=''
#*.fal_client='FBID'
#*.fal_server='FBID_PR'
*.job_queue_processes=30
#*.log_archive_dest_2='service=FBID_ST lgwr async NOAFFIRM optional reopen=180 valid_for=(online_logfiles, primary_role)'
#*.log_archive_dest_state_2='ENABLE'
#*.log_archive_min_succeed_dest=1
*.log_archive_dest_1='location=D:\oradata\report\fast_recovery_area\report\ARCHIVELOG'
#*.log_archive_dest_state_1='ENABLE' #
*.LOG_ARCHIVE_FORMAT='log_%t_%s_%r.dbf'
*.memory_target=32G  #8G#2181038080
*.nls_language='RUSSIAN'
*.nls_territory='RUSSIA'
*.open_cursors=2000
*.pga_aggregate_target=536870912
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=200
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='D:\oradata\utl_dir\fbid','D:\oradata\utl_dir\fbid\GC','D:\oradata\utl_dir\fbid\TMP'
*.db_file_name_convert='d:\oradata\fbid','d:\oradata\report'
*.log_file_name_convert='d:\oradata\fbid','d:\oradata\report'

Starting StandBy

Startup nomount
Alter database mount standby database exclusive;

Change the archive log path, pointing to the existing StandBy log location:

alter system set log_archive_dest_1='location=D:\oradata\fbid\fast_recovery_area\FBID\ARCHIVELOG' scope=memory;

// endless apply
recover managed standby database disconnect from session;

// up to a specific time
SQL> recover automatic standby database parallel 16 until time '2014-04-14:18:00:00';

After log recovery, switch to Snapshot mode, specify archive file path

alter system set log_archive_dest_1='location=D:\oradata\report\fast_recovery_area\report\ARCHIVELOG' scope=memory;

alter database flashback off;

alter database flashback on;

create restore point FBID guarantee flashback database;

Restore point created.

alter database activate standby database;
alter database open;

Test

select flashback_on from v$database;

Switch to archive log apply mode:

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 6.8413E+10 bytes
Fixed Size                  2272320 bytes
Variable Size            6.5767E+10 bytes
Database Buffers         2550136832 bytes
Redo Buffers               93585408 bytes
Database mounted.

SQL> flashback database to restore point FBID;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> startup mount force;

ORACLE instance started.
Total System Global Area 6.8413E+10 bytes
Fixed Size                  2272320 bytes
Variable Size            6.5767E+10 bytes
Database Buffers         2550136832 bytes
Redo Buffers               93585408 bytes
Database mounted.

SQL> drop restore point FBID;

Restore point dropped.

SQL> alter system set log_archive_dest_1='location=D:\oradata\fbid\fast_recovery_area\FBID\ARCHIVELOG' scope=memory;

System altered.

SQL> recover automatic standby database parallel 16 until time '2014-04-15:10:00:00';

Media recovery complete.

SQL> alter system set log_archive_dest_1='location=D:\oradata\report\fast_recovery_area\report\ARCHIVELOG' scope=memory;

System altered.

Cycle back to open mode

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point FBID guarantee flashback database;

Restore point created.

SQL> alter database activate standby database;

Database altered.

SQL> alter database open;

Database altered.

Scripts for Snapshot StandBy operation

Run from local admin with ora_dba rights

// activation of physical StandBy, database recovery using latest archive logs up to a point in time
to_PhStBy.bat

set oracle_sid=report
set ddate=%date:.=-%:5:00:00
ECHO OFF
echo recover automatic standby database parallel 16 until time '%date:~6%-%date:~3,-5%-%date:~0,-8%:5:00:00'; > D:\oradata\REPORT\fast_recovery_area\recover.sql
rem sqlplus /nolog
@D:\oradata\REPORT\fast_recovery_area\to_PhStBy.sql

to_PhStBy.sql

set serveroutput on
set lines 200
spool D:\oradata\REPORT\fast_recovery_area\to_PhStBy.log

conn sys/sys as sysdba

startup mount force

conn sys/sys as sysdba

flashback database to restore point FBID;

alter database convert to physical standby;

startup mount force;

drop restore point FBID;

alter system set log_archive_dest_1='location=D:\oradata\fbid\fast_recovery_area\FBID\ARCHIVELOG' scope=memory;

@D:\oradata\report\fast_recovery_area\recover.sql

alter system set log_archive_dest_1='location=D:\oradata\report\fast_recovery_area\report\ARCHIVELOG' scope=memory;

spool off

exit;

// activation of SnapShot StandBy with FlashBack
to_PhStBy.bat

set oracle_sid=report
sqlplus /nolog @D:\oradata\REPORT\fast_recovery_area\to_SnStBy.sql
exit

to_PhStBy.sql

set serveroutput on
set lines 200
spool D:\oradata\REPORT\fast_recovery_area\to_SnStBy.log
conn sys/sys as sysdba
alter database flashback off;
alter database flashback on;
create restore point FBID guarantee flashback database;
alter database activate standby database;
alter database open;
spool off

exit;

Note:

During a non-regular backup of the primary database, it is necessary to copy the archive log for subsequent recovery if needed (with a different SID): (RestTest10Arch.sql script)

host ocopy D:\ORADATA\FBID\FAST_RECOVERY_AREA\FBID\ARCHIVELOG\LOG_1_44833_797622543.DBF \\S00000DPM001\ORACLE\FBID\ARCH\LOG_1_44833_797622543.DBF

RestTest10Arch.sql

host ocopy D:\ORADATA\XXXX\FAST_RECOVERY_AREA\XXXX\ARCHIVELOG\LOG_1_48611_797622543.DBF  \\S00000DPM001\ORACLE\XXXX\ARCH\LOG_1_48611_797622543.DBF

// at the backup completion time (apply).
t.bat:

set oracle_sid=fbid

sqlplus system/system @D:\oradata\fbid\fast_recovery_area\t.sql

sqlplus system/system @D:\oradata\fbid\fast_recovery_area\a.sql

c:\blat\blat -body "@" -subject *ORA*ArchNeedCopy_FBID -to gvi@fbid.ru

t.sql:

set lines 512
set pagesize 0 trimout on trimspool on verify off echo off feedback off
set serveroutput on

alter system archive log current;

spool D:\oradata\fbid\fast_recovery_area\a.sql

select 'host ocopy '||(select name from v$archived_log where
sequence# >=(select min(sequence#) from v$log_history where first_time>trunc(sysdate)+4/24+15/24/60)
and
sequence#<(
select min(sequence#) from v$log_history where  first_time>=(select completion_time from V$BACKUP_SET_DETAILS where controlfile_included='NO' and completion_time>trunc(sysdate) and
session_recid=(
select session_recid from V$RMAN_BACKUP_SUBJOB_DETAILS where  session_recid=(
select session_recid from V$BACKUP_SET_DETAILS where controlfile_included='NO' and completion_time>trunc(sysdate)
having bs_key=max(bs_key) group by session_recid,bs_key
) and status='COMPLETED' and input_type='DB FULL'
)
having bs_key=max(bs_key) group by completion_time,bs_key)
)
and dest_id=1)||' \\S00000DPM001\ORACLE\FBID\ARCH\'||
(select substr(name,instr(name,'\',-1)+1) from v$archived_log where
sequence# >=(select min(sequence#) from v$log_history where first_time>trunc(sysdate)+4/24+15/24/60)
and
sequence#<(
select min(sequence#) from v$log_history where  first_time>=(select completion_time from V$BACKUP_SET_DETAILS where controlfile_included='NO' and completion_time>trunc(sysdate) and
session_recid=(
select session_recid from V$RMAN_BACKUP_SUBJOB_DETAILS where  session_recid=(
select session_recid from V$BACKUP_SET_DETAILS where controlfile_included='NO' and completion_time>trunc(sysdate)
having bs_key=max(bs_key) group by session_recid,bs_key
) and status='COMPLETED' and input_type='DB FULL'
)
having bs_key=max(bs_key) group by completion_time,bs_key)
)
and dest_id=1) aa from dual;
select 'exit' from dual;
spool off
exit

a.sql:

host ocopy D:\ORADATA\FBID\FAST_RECOVERY_AREA\FBID\ARCHIVELOG\LOG_1_44833_797622543.DBF \\S00000DPM001\ORACLE\FBID\ARCH\LOG_1_44833_797622543.DBF
exit

DETAILED

goto_PhSTby.bat

set oracle_sid=XXXX
rem set ddate=%date:.=-%:5:00:00

ECHO ON

rem del /Q /F D:\oradata\XXXX\fast_recovery_area\recover.sql

rem echo recover automatic standby database parallel 16 until time '%date:~6%-%date:~3,-5%-%date:~0,-8%:3:00:00'; > D:\oradata\REPORT\fast_recovery_area\recover.sql

sqlplus /nolog @D:\oradata\XXXX\fast_recovery_area\goto_PhStBy.sql

exit

goto_PhStBy.sql

set serveroutput on
set lines 200
spool D:\oradata\REPORT\fast_recovery_area\to_PhStBy.log

conn sys/sys as sysdba

startup mount force

conn sys/sys as sysdba

flashback database to restore point XXXX;

alter database convert to physical standby;

startup mount force;

drop restore point FBID;

alter system set log_archive_dest_1='location=c:\oradata\XXXX\fast_recovery_area\FBID\ARCHIVELOG' scope=memory;

--column u_time new_value u_t
--select to_char(sysdate,'YYYY-MM-DD:HH24:MM:SS') u_time from dual;
--recover automatic standby database parallel 16 until time '&u_t'
--alter system set log_archive_dest_1='location=D:\oradata\report\fast_recovery_area\report\ARCHIVELOG' scope=memory;

recover managed standby database disconnect from session;
--run StandBy
spool off

exit;

open_test_FlashBack.bat

set oracle_sid=fbid
sqlplus /nolog @D:\oradata\XXX\fast_recovery_area\open_test_FB.sql
rem exit

open_test_SnStby.bat

set serveroutput on
set lines 200
spool D:\oradata\XXXX\fast_recovery_area\to_FB.log
conn sys/sys as sysdba
Startup nomount
Alter database mount standby database exclusive;
alter database flashback off;
alter database flashback on;
create restore point FBID guarantee flashback database;
alter database activate standby database;
alter database open;
spool off

exit;

purge.rman

run {
    crosscheck backup of database completed before "SYSDATE-2";
    crosscheck backup of controlfile completed before "SYSDATE-2";
    crosscheck backup of archivelog  from time  "SYSDATE-2";
    delete noprompt expired backup of controlfile completed before "SYSDATE-2";
    delete noprompt expired backup of database completed before "SYSDATE-2";
    delete noprompt expired backup completed before "SYSDATE-2";
    delete noprompt expired archivelog  from time  "SYSDATE-2";
    delete noprompt backup of archivelog  from time  "SYSDATE-2";
    delete noprompt force archivelog all completed before 'SYSDATE-2';
    report obsolete;
    DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 2 DAYS;
}

purgeAl.bat

set ORACLE_SID=XXXX

rman target sys/sys nocatalog cmdfile=d:\oradata\XXXX\fast_recovery_area\purgeAL.rman log d:\oradata\fbid\fast_recovery_area\purgeAL.log

rem del /Q /F \\S00000DPM01\ORACLE\XXXX\DB\RestTest10Arch.sql
rem del /Q /F D:\oradata\fbid\fast_recovery_area\RestTest10Arch.sql
rem del /Q /F \\S00000DPM01\ORACLE\XXXX\DB\RestTest10Rec.sql

purgeAL.rman

run {
    delete noprompt force archivelog all completed before 'SYSDATE-2';
}

to_SnStby.bat

set oracle_sid=report
sqlplus /nolog @D:\oradata\REPORT\fast_recovery_area\to_SnStBy.sql
exit

to_SnStby.sql
set serveroutput on
set lines 200
spool D:\oradata\REPORT\fast_recovery_area\to_SnStBy.log
conn sys/sys as sysdba
alter database flashback off;
alter database flashback on;
create restore point FBID guarantee flashback database;
alter database activate standby database;
alter database open;
spool off

exit;