Home » RDBMS Server » Backup & Recovery » How to restore these changes to another cluster (11.2.0.4 SE, SLES 11 SP3)
How to restore these changes to another cluster [message #663083] Mon, 22 May 2017 02:57 Go to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear all,

how do I restore structural and dml changes from one cluster to another?

first of all, I'm was supposed to restore the database from one cluster to another.
but due to the fact that the database is huge, my customer wants me to restore the database to another cluster first before applying later changes in the original cluster to the destination cluster.

here's my restore log on the destination cluster


Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 18 15:00:24 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MIG (DBID=2932251289, not open)

RMAN> SET ECHO ON
2> 
3> run {
4> restore database;
5> restore archivelog from scn 1230422 until scn 1230481;
6> }
7> 
8> EXIT
echo set on

Starting restore at 2017-05-18 15:00:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=580 instance=mig1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/mig/datafile/system.265.942336403
channel ORA_DISK_1: restoring datafile 00002 to +DATA/mig/datafile/sysaux.260.942336403
channel ORA_DISK_1: restoring datafile 00003 to +DATA/mig/datafile/undotbs1.264.942336403
channel ORA_DISK_1: restoring datafile 00004 to +DATA/mig/datafile/users.263.942336403
channel ORA_DISK_1: restoring datafile 00005 to +DATA/mig/datafile/test_mig.266.942923305
channel ORA_DISK_1: reading from backup piece +FRA/mig/backupset/2017_05_03/nnndn0_mig_20170503_inc0_0
channel ORA_DISK_1: piece handle=+FRA/mig/backupset/2017_05_03/nnndn0_mig_20170503_inc0_0 tag=MIG_20170503_INC0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2017-05-18 15:01:20

Starting restore at 2017-05-18 15:01:20
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_DISK_1: reading from backup piece +FRA/mig/backupset/2017_05_03/annnf0_mig_20170503_arc_0
channel ORA_DISK_1: piece handle=+FRA/mig/backupset/2017_05_03/annnf0_mig_20170503_arc_0 tag=MIG_20170503_ARC
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2017-05-18 15:01:22

Recovery Manager complete.


at this stage at the destination cluster the database is only mounted and both the database and archivelog has been restored.

next I apply this structural changes at the source cluster

SYS@mig1>SET TRIMSPOOL ON
SYS@mig1>
SYS@mig1>SELECT sys_context('userenv','sid') FROM dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
62

SYS@mig1>
SYS@mig1>SELECT sys_context('userenv','server_host') FROM dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------------------
suse113-11204-ee-rac1

SYS@mig1>
SYS@mig1>SELECT to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF TZR') start_date_time FROM dual;

START_DATE_TIME
--------------------------------------------------------------
2017-05-22 09:28:55.580993 +08:00

SYS@mig1>
SYS@mig1>CREATE TABLESPACE MIG DATAFILE '+DATA';

Tablespace created.

SYS@mig1>
SYS@mig1>SELECT to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF TZR') end_date_time FROM dual;

END_DATE_TIME
--------------------------------------------------------------
2017-05-22 09:28:56.014030 +08:00

SYS@mig1>
SYS@mig1>SET ECHO OFF



and as well as this dml changes at the cluster database

SYS_CONTEXT('USERENV','SID')                                                    
--------------------------------------------------------------------------------
65                                                                              


SYS_CONTEXT('USERENV','SERVER_HOST')                                            
--------------------------------------------------------------------------------
suse113-11204-ee-rac1                                                           


START_DATETIME                                                                  
--------------------------------------------------------------                  
2017-05-22 10:14:40.994873 +08:00                                               

TEST_MIG@mig1>SET TRIMSPOOL ON
TEST_MIG@mig1>
TEST_MIG@mig1>SET SERVEROUTPUT ON
TEST_MIG@mig1>DECLARE
  2    TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  3  
  4    l_tab	t_forall_test_tab := t_forall_test_tab();
  5    l_start	NUMBER;
  6    --l_size   NUMBER	    := 1000000;
  7    l_size	NUMBER		  := 20;
  8  BEGIN
  9    -- Populate collection.
 10    FOR i IN 11 .. l_size LOOP
 11  	 l_tab.extend;
 12  
 13  	 l_tab(l_tab.last).id	       := i;
 14  	 l_tab(l_tab.last).code        := TO_CHAR(i);
 15  	 l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
 16    END LOOP;
 17  
 18    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
 19  
 20    -- Time bulk inserts.
 21    l_start := DBMS_UTILITY.get_time;
 22  
 23    FORALL i IN l_tab.first .. l_tab.last
 24  	 INSERT INTO forall_test VALUES l_tab(i);
 25  
 26    DBMS_OUTPUT.put_line('Bulk Inserts  : ' ||
 27  			    (DBMS_UTILITY.get_time - l_start));
 28  
 29    COMMIT;
 30  END;
 31  /
Bulk Inserts  : 0

PL/SQL procedure successfully completed.

TEST_MIG@mig1>
TEST_MIG@mig1>SELECT to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF TZR') end_datetime FROM dual;

END_DATETIME
--------------------------------------------------------------
2017-05-22 10:14:41.224680 +08:00

TEST_MIG@mig1>
TEST_MIG@mig1>SPOOL OFF


how do I propagate these changes to the destination cluster.

after this I'm will do a incremental backup as well as a archivelog backup on the source cluster.

how do I propagate these changes to the destination cluster.

many thanks in advance!

Re: How to restore these changes to another cluster [message #663088 is a reply to message #663083] Mon, 22 May 2017 06:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Easiest would be to create the database on your new cluster as a physical standby.
Re: How to restore these changes to another cluster [message #663460 is a reply to message #663088] Sun, 04 June 2017 21:09 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

with reference to http://docs.oracle.com/cd/E11882_01/server.112/e41134/rman.htm#SBYDB4878

how do i determine the incremental from scn on a restored non standby database?

on the restore cluster itself,

is this sql correct?

SELECT 'BACKUP AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN ' || MIN(CHECKPOINT_CHANGE#) || ' DATABASE FORMAT 
''/home/oracle/dba/backup/backupset/forrestore_%U'' TAG ''FOR mig RESTORE '
 || to_char(sysdate, 'YYYYMMDD') || ''';' lag_behind FROM V$datafile;


I cannot use v$database as when the restore non standby database is mounted, scn from v$database gives 0

SYS@ORAC01 mig1>SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
          0


with reference to https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1100.htm#REFRN30050

FIRST_NONLOGGED_SCN only applys to non standy database

thanks a lot!

Re: How to restore these changes to another cluster [message #663462 is a reply to message #663460] Mon, 05 June 2017 00:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you use Data Guard, then Oracle will work it out.
Re: How to restore these changes to another cluster [message #668126 is a reply to message #663462] Thu, 08 February 2018 02:48 Go to previous message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear John,

thanks for your solution,

--RMAN: RAC Backup, Restore and Recovery using RMAN (Doc ID 243760.1) To BottomTo Bottom


select max(sequence#) from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# and 
thread#=1;


select sequence#, thread#, first_change#, next_change#
from v$archived_log L, v$database D
where L.resetlogs_change# = D.resetlogs_change# and
sequence# in <answer in first query>
Previous Topic: Can restoring backup from tape to disk help me to restore spilfw
Next Topic: RMAN pushing same backups again and again to tape causing TSM running out of space
Goto Forum:
  


Current Time: Thu Mar 28 16:07:40 CDT 2024