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
