What if I add data files or create tablespace in snapshot standby database Oracle 19c
Note : new tablespace or datafile will b droped if you change standby to physical database
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:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF 1180
1 3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF 990
2 4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF 440
4 7 D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g;
Tablespace created.
SQL> alter tablespace TEST_FLB add datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g;
Tablespace altered.
SQL> alter database datafile 'D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g
2023-09-02T20:53:51.052190+05:30
Completed: create tablespace TEST_FLB datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g
2023-09-02T20:53:53.860701+05:30
alter tablespace TEST_FLB add datafile 'D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g
2023-09-02T20:53:57.391041+05:30
alter database datafile 'D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF, old size 1048576K, new size 3145728K
Completed: alter database datafile 'D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF 1180
1 3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF 990
2 4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF 440
4 7 D:\TESTDB\SETUPS\STANDBY\DB\DR\USERS01.DBF 5
6 5 D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF 1024
6 2 D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF
Flashback: deleted datafile #2 in tablespace #6 of pdb #0 from control file.
Deleted file D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LH6NTGC1_.FLB
Deleted Oracle managed file D:\TESTDB\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:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF 1180
1 3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF 990
2 4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF 440
4 7 D:\TESTDB\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
No comments:
Post a Comment