Dataguard - Revision history

Stuart: /* See how up-to-date a standby is */

2017-06-01T10:48:38Z

‎See how up-to-date a standby is

Stuart: /* See Dataguard error log messages */

2017-06-01T10:38:56Z

‎See Dataguard error log messages

Stuart: /* Check which logs are missing */

2017-06-01T10:35:58Z

‎Check which logs are missing

Stuart: /* (Re)Build a physical standby from an active primary database */

2017-02-20T12:23:05Z

‎(Re)Build a physical standby from an active primary database

Stuart: /* If flashback was not enabled, recreate old primary as a standby something like this */

2017-02-20T12:15:39Z

‎If flashback was not enabled, recreate old primary as a standby something like this

Stuart: /* If flashback was not enabled, recreate old primary as a standby something like this */

2017-02-20T12:11:57Z

‎If flashback was not enabled, recreate old primary as a standby something like this

Stuart: /* Failover */

2017-02-20T12:11:05Z

‎Failover

Stuart: /* Dataguard broker (dgmgrl) setup for management of physical standby databases */

2017-02-20T11:53:59Z

‎Dataguard broker (dgmgrl) setup for management of physical standby databases

Stuart: /* Dataguard broker managed standby (dgmgrl) not applying logs after restart */

2017-02-20T10:45:33Z

‎Dataguard broker managed standby (dgmgrl) not applying logs after restart

Stuart at 16:33, 15 February 2017

2017-02-15T16:33:57Z

← Older revision		Revision as of 10:48, 1 June 2017
Line 363:		Line 363:
	/		/

		+	</pre>
		+	===See how up-to-date a standby is (another version)===
		+	<pre>
		+	PRIMARY_SQL>
		+	select thread#
		+	, max(sequence#) "Last Primary Seq Generated"
		+	from v$archived_log val
		+	, v$database vdb
		+	where val.resetlogs_change# = vdb.resetlogs_change#
		+	group by thread#
		+	order by 1
		+	/
		+
		+	STANDBY_SQL>
		+	select thread#
		+	, max(sequence#) "Last Standby Seq Received"
		+	from v$archived_log val
		+	, v$database vdb
		+	where val.resetlogs_change# = vdb.resetlogs_change#
		+	group by thread#
		+	order by 1
		+	/
		+
		+	STANDBY_SQL>
		+	select thread#
		+	, max(sequence#) "Last Standby Seq Applied"
		+	from v$archived_log val
		+	, v$database vdb
		+	where val.resetlogs_change# = vdb.resetlogs_change#
		+	and val.applied in ('YES', 'IN-MEMORY')
		+	group by thread#
		+	order by 1
		+	/
	</pre>		</pre>

@@ Line 61: / Line 61: @@
+/
 </pre>
 ===Check which logs are missing===
 Assuming dest_2 is the standby

@@ Line 47: / Line 47: @@
 </pre>
 ===Check which logs are missing===
 Assuming dest_2 is the standby

@@ Line 545: / Line 545: @@
 ===(Re)Build a physical standby from an active primary database===
-Run from the standby server
+Run from the standby server. Trivial mods to parameterise it soon!
 <pre>
 #!/usr/bin/ksh

@@ Line 472: / Line 472: @@
 <pre>
 echo "*.db_name='db'" > /tmp/initdb_primary.ora
-export ORACLE_SID=db_primary
+export ORACLE_SID=db
 sqlplus / as sysdba
 startup nomount pfile='/tmp/initdb_primary.ora';

@@ Line 469: / Line 469: @@
 mkdir -p /oracle/<SID>/fra/
 mkdir -p /oracle/<SID>/admin/adump
 <pre>
 echo "*.db_name='db'" > /tmp/initdb_primary.ora

@@ Line 455: / Line 455: @@
 reinstate database db_primary;
 </pre>
-If flashback was not enabled, recreate old primary as a standby something like this
+====If flashback was not enabled, recreate old primary as a standby something like this====
 <pre>
 sqlplus / as sysdba
@@ Line 461: / Line 461: @@
 exit;
 </pre>
 <pre>
 rm $ORACLE_HOME/dbs/*<SID>.ora
@@ Line 470: / Line 469: @@
 mkdir -p /oracle/<SID>/fra/
 mkdir -p /oracle/<SID>/admin/adump
+<pre>
 echo "*.db_name='db'" > /tmp/initdb_primary.ora
 export ORACLE_SID=db_primary
@@ Line 476: / Line 475: @@
 startup nomount pfile='/tmp/initdb_primary.ora';
 exit
 rman target sys/sys@db_standby auxiliary sys/sys@db_primary
@@ Line 487: / Line 488: @@
      set log_file_name_convert='/old/logfile/dir1/','/new/logfile/dir1/','/old/logfile/dir2/','/new/logfile/dir2/'
      nofilenamecheck;
+</pre>
+<pre>
 dgmgrl sys/sys@db_standby
 enable database db_primary;

@@ Line 397: / Line 397: @@
 ===Dataguard broker (dgmgrl) setup for management of physical standby databases===
 Example: server1 - db_primary, server2 - db_standby<br />
 On both systems...
 <pre>
 alter system set dg_broker_start=true;
 </pre>
 From server1
 <pre>
@@ Line 412: / Line 415: @@
 add database db_standby as connect identifier is db_standby maintained as physical;
 </pre>
-Configuration complete, enable it
+====Configuration complete, enable it====
 <pre>
 enable configuration;
 </pre>
-Show configuration
+====Show configuration====
 <pre>
 show configuration;
 </pre>
-Show individual database information
+====Show individual database information====
 <pre>
 show database db_primary;
 show database db_standby;
 </pre>
-Switchover primary to standby and vice versa<br />
+====Switchover primary to standby and vice versa====
 From server1
 <pre>
@@ Line 431: / Line 434: @@
 switchover to db_standby;
 </pre>
-To switch back to the original situation...<br />
+====Switch back to the original situation====
 From server2
 <pre>
 dgmgrl sys/sys@db_standby
 switchover to db_primary;
 </pre>

@@ Line 395: / Line 395: @@
   where  1=1
   and    st.group# = lf.group#
 ===Dataguard broker managed standby (dgmgrl) not applying logs after restart===

← Older revision		Revision as of 16:33, 15 February 2017
Line 477:		Line 477:

	EORMAN		EORMAN
		+
		+	</pre>
		+
		+	===Recover a physical standby from an gap using SCN based incremental (catch up) backup===
		+	Essential steps using nfs mounted filesystem (/shared_backups) shared between both primary and secondary nodes to avoid scp copies
		+	<pre>
		+	Primary
		+	SQL> alter system set log_archive_dest_state_2=defer scope=both;
		+
		+	Standby
		+	SQL> select min(checkpoint_change#) lowest_scn from v$datafile_header order by 1;
		+
		+	Primary
		+	RMAN> backup incremental from scn <lowest_scn> database format '/shared_backups/catchup_backup_%U';
		+
		+	Standby
		+	SQL> alter database recover managed standby database cancel;
		+
		+	Standby
		+	RMAN> catalog start with '/shared_backups';
		+	RMAN> recover database noredo;
		+
		+	Primary
		+	SQL> backup current controlfile for standby format '/shared_backups/catchup_control.ctl';
		+
		+	Standby
		+	RMAN> report schema;
		+	RMAN> shutdown immediate;
		+	RMAN> startup nomount;
		+	RMAN> restore standby controlfile from '/shared_backups/catchup_control.ctl'
		+	SQL> alter database mount;
		+	RMAN> report schema;
		+	RMAN> run {
		+	set newname for datafile 1 to '+DATA/<standbySID>/data1/system.dbf';
		+	set newname for datafile 2 to '+DATA/<standbySID>/data1/sysaux.dbf';
		+	set newname for datafile 3 to '+DATA/<standbySID>/data2/users.dbf';
		+	set newname for datafile 4 to '+DATA/<standbySID>/data3/undotbs.dbf';
		+	switch datafile all;
		+	}
		+	RMAN> run {
		+	set newname for tempfile 1 to '+DATA/<standbySID>/data2/temptbs.dbf';
		+	switch tempfile all;
		+	}
		+	RMAN> report schema;
		+
		+	Primary
		+	SQL> alter system set log_archive_dest_state_2=enable scope=both;
		+
		+	Standby
		+	SQL> alter database recover managed standby database cancel;
		+	SQL> alter database recover managed standby database using current logfile disconnect from session;
		+	SQL> select inst_id, process, thread#, sequence#, blocks, status from gv$managed_standby where process like '%MRP%';

	</pre>		</pre>