Does moving datafile in primary database affect a physical standby replication?

Good day!!

There could be tough situations where we mistakes unknowingly in a critical production databases which make us think a lot.

Lets look at the oracle 12c new feature –moving a datafile online

The database is RAC with physical standby dataguard.

In this scenario,we are going to add a datafile to a database in prod(consideration).

On Primary:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/ORCL/DATAFILE/system.257.1031501823
+DATA/ORCL/DATAFILE/system.265
+DATA/ORCL/DATAFILE/sysaux.256.1031501729
+DATA/ORCL/DATAFILE/undotbs1.259.1031501899
+DATA/ORCL/DATAFILE/example.265.1031501993
+DATA/ORCL/DATAFILE/users.258.1031501899
+DATA/ORCL/DATAFILE/users.272.1040745563

7 rows selected.

SQL> alter tablespace USERS add datafile '+DATA' size 500M;

Tablespace altered.

On Standby:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/ORCLDG/DATAFILE/system.260.1031954441
+DATA/ORCLDG/DATAFILE/system.265.1032181503
+DATA/ORCLDG/DATAFILE/sysaux.264.1031954497
+DATA/ORCLDG/DATAFILE/undotbs1.270.1031954543
+DATA/ORCLDG/DATAFILE/example.269.1031954557
+DATA/ORCLDG/DATAFILE/users.268.1031954621
+DATA/ORCLDG/DATAFILE/users.259.1040846285
+DATA/ORCLDG/DATAFILE/users.258.1040915343

8 rows selected.

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcldg

users.258.1040915343 is the datafile added on USERS.

The datafile is replicated to DR with the same filename

standby_file_management should be set to AUTO to manage datafiles automatically by oracle

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Now we move the datafile to different directory and rename the datafile online using oracle 12c feature

SQL> alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new';

Database altered.

Archived Log entry 2553 added for thread 1 sequence 1304 ID 0x5cf12ffb dest 1:
Wed May 20 15:38:31 2020
TT00: Standby redo logfile selected for thread 1 sequence 1305 for destination LOG_ARCHIVE_DEST_2
Wed May 20 15:54:11 2020
alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new'
Wed May 20 15:54:11 2020
Moving datafile +DATA/ORCL/DATAFILE/users.258.1031501899 (6) to +DATA/ORCL/users.new
Wed May 20 15:54:43 2020
Move operation committed for file +DATA/ORCL/users.new
Completed: alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new'

After moving the datafile to new location,a thought comes to your mind.

On PROD:

ASMCMD [+DATA/ORCL] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileorcl.ora
users.new    <---------------------------------

The datafile was moved in PROD but not on DR.

On DR:

ASMCMD [+DATA/orcldg/DATAFILE] > ls
EXAMPLE.269.1031954557
SYSAUX.264.1031954497
SYSTEM.260.1031954441
SYSTEM.265.1032181503
UNDOTBS1.270.1031954543
USERS.258.1040915343
USERS.259.1040846285
USERS.268.1031954621 <----------------------------------------

“”” Oh ! I forgot about physical standby database which replicate from primary”””

Don’t worry moving the datafile online in primary doesn’t affect the standby log shipping.

The filename is not renamed on the standby,but it works fine without disruption.

I switch the log for couple of times and generate some manual archives on primary

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1307
Next log sequence to archive   1309
Current log sequence           1309 <------

On DR:

As you can see the archive shipping is running smooth with SCN sync and not interrupted due to datafile move

Recovery of Online Redo Log: Thread 1 Group 10 Seq 1308 Reading mem 0
  Mem# 0: +FRA/ORCLDG/ONLINELOG/group_10.263.1031954629
RFS[1]: Selected log 11 for thread 1 sequence 1309 dbid 1559282684 branch 1031501951
Wed May 20 16:05:34 2020
Archived Log entry 1263 added for thread 1 sequence 1308 ID 0x5cf12ffb dest 1:
Wed May 20 16:05:34 2020
Media Recovery Waiting for thread 1 sequence 1309 (in transit)
Wed May 20 16:05:34 2020
Recovery of Online Redo Log: Thread 1 Group 11 Seq 1309 Reading mem 0
  Mem# 0: +FRA/ORCLDG/ONLINELOG/group_11.262.1031954629

MESSAGE
--------------------------------------------------------------------------------
ARC3: Completed archiving thread 1 sequence 1307 (0-0)
Media Recovery Waiting for thread 1 sequence 1308 (in transit)
ARC1: Beginning to archive thread 1 sequence 1308 (7744674-7744677)
ARC1: Completed archiving thread 1 sequence 1308 (0-0)
Media Recovery Waiting for thread 1 sequence 1309 (in transit)

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            1304          1
ARCH      CLOSING            1308          1
ARCH      CONNECTED             0          0
ARCH      CLOSING            1307          1
RFS       IDLE               1309          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       1309   <----  1
 

“””alter database move datafile“””

The above awesome feature was not available on older versions which was really a difficult time for DR.

Thanks

kishan

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: