Environment Details:
Database: Oracle Database 19.0.0.0 EE
Operating System: Windows 11 Pro
Database Role: Snapshot Standby
In last post Snapshot Standby database in Oracle 19c, we have seen how to configure physical standby database into snapshot standby database and convert back into physical standby database. The snapshot standby database is required to perform read-write testing and once testing is done then all the changes are discarded before converting back to physical standby database.
Suppose, during the read-write testing, if you need to add some space in existing datafiles or need to create new tablespace then what will happen. Can you do that? Let's see how it works.
|
Check the database role. Execute below command in snapshot
standby database. SQL>
select name,open_mode,database_role,controlfile_type from v$database; NAME
OPEN_MODE DATABASE_ROLE CONTROL
----
------------ ---------------- -------
PR
READ
WRITE
SNAPSHOT STANDBY CURRENT
Capture the data file details with tablespace names before
performing any testing. SQL> col name for a60
SQL> select ts#,file#,name
File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1;
TS# FILE# FILE_NAME
FILE_SIZE_MB
--- -----
--------------------------------------------- ------------
0 1
D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF
1180
1 3
D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF
990
2 4
D:\RUPESH\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF
440
4 7
D:\RUPESH\SETUPS\STANDBY\DB\DR\USERS01.DBF
5 SQL> select ts#,name from
v$tablespace order by 1; TS# NAME
--- ---------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
3 TEMP
4 USERS
|
|
Now
create new tablespace TEST_FLB in snapshot standby database
and add some data files in this tablespace or any existing tablespace. You
can also resize existing data files or create any table.
SQL> create tablespace TEST_FLB datafile
'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g;
Tablespace created.
SQL> alter
tablespace TEST_FLB add datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g;
Tablespace altered.
SQL> alter
database datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g;
Database altered.
SQL> create table customer (cust_id
number(10), cust_name varchar2(14)) tablespace TEST_FLB;
Table created. SQL> insert into
customer values(1,'A');
1 row created.
SQL> insert
into customer values(2,'B');
1 row created.
SQL> insert
into customer values(3,'C');
1 row created.
SQL> insert
into customer values(4,'D');
1 row created. SQL> insert
into customer values(5,'E'); 1 row created. SQL> commit; Commit complete. SQL> select * from
customer;
CUST_ID
CUST_NAME
----------
--------------
1 A
2 B
3 C
4 D
5 E Snapshot Standby Alert Log File: 2023-09-02T20:53:49.598851+05:30 create tablespace TEST_FLB datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g 2023-09-02T20:53:51.052190+05:30 Completed: create tablespace TEST_FLB datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g 2023-09-02T20:53:53.860701+05:30 alter tablespace TEST_FLB add datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g 2023-09-02T20:53:55.967650+05:30 Completed: alter tablespace TEST_FLB add datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g 2023-09-02T20:53:57.391041+05:30 alter database datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g 2023-09-02T20:53:59.654427+05:30 Resize operation completed for file# 2, fname D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF, old size 1048576K, new size 3145728K Completed: alter database datafile 'D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g 2023-09-02T20:54:26.785953+05:30 You can check the data file and tablespace details by querying below command: SQL> col name for a60 SQL> select ts#,file#,name File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1; TS# FILE# FILE_NAME FILE_SIZE_MB --- ----- --------------------------------------------- ------------ 0 1 D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF 1180 1 3 D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF 990 2 4 D:\RUPESH\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF 440 4 7 D:\RUPESH\SETUPS\STANDBY\DB\DR\USERS01.DBF 5 6 5 D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF 1024 6 2 D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF 3072 6 rows selected. SQL> select ts#,name from v$tablespace order by 1; TS# NAME --- ----------- 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 3 TEMP 4 USERS 6 TEST_FLB 6 rows selected. |
|
Now suppose, your testing is done and your plan is to convert back the snapshot standby database into physical standby database then perform below steps. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size 9030464 bytes Variable Size 452984832 bytes Database Buffers 1677721600 bytes Redo Buffers 7745536 bytes Database mounted. SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR DR MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT SQL> alter database convert to physical standby; Database altered. Once you execute the above "convert to physical standby" command, it will delete the data files and drop the tablespaces created in snapshot standby database which means all the changes are discarded once you execute the "convert to physical standby" command. Snapshot Standby Database Alert Log File: 2023-09-02T20:58:39.261800+05:30 alter database convert to physical standby 2023-09-02T20:58:39.482013+05:30 Flashback Restore Start Deleted file D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF Flashback: deleted datafile #2 in tablespace #6 of pdb #0 from control file. Deleted file D:\RUPESH\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF Flashback: deleted datafile #5 in tablespace #6 of pdb #0 from control file. Flashback: dropped tablespace #6: 'TEST_FLB' of pdb #0 from the control file. Flashback Restore Complete Drop guaranteed restore point Stopping background process RVWR 2023-09-02T20:58:41.228496+05:30 Deleted Oracle managed file D:\RUPESH\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LH6NTGC1_.FLB Deleted Oracle managed file D:\RUPESH\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LH6NTKCV_.FLB Guaranteed restore point dropped 2023-09-02T20:58:41.275867+05:30 .... (PID:18848): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8969] Clearing standby activation ID 2841025717 (0xa9569cb5) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200; .... (PID:18848): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8974] .... (PID:18848): RT: Role transition work is not done .... (PID:18848): Redo network throttle feature is disabled at mount time Physical Standby Database mounted. 2023-09-02T20:58:41.386798+05:30 In-memory operation on ADG is currently only supported on Engineered systems and PaaS. inmemory_adg_enabled is turned off automatically. Please contact our support team for EXADATA solutions CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby Completed: alter database convert to physical standby |
|
You can also verify by executing below query to ensure newly created tablespace or added data files are not a part of the database now. SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR DR MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY SQL> col name for a60 SQL> select ts#,file#,name File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1; TS# FILE# FILE_NAME FILE_SIZE_MB --- ----- -------------------------------------------- ------------ 0 1 D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF 1180 1 3 D:\RUPESH\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF 990 2 4 D:\RUPESH\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF 440 4 7 D:\RUPESH\SETUPS\STANDBY\DB\DR\USERS01.DBF 5 SQL> select ts#,name from v$tablespace order by 1; TS# NAME --- ---------- 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 3 TEMP 4 USERS |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Thank you for your comment !