Which three statements are true about snapshot standby …

In oracle documentation we can find this about snapshot standby: A snapshot standby database is a fully updatable standby database that provides disaster recovery and data protection benefits that are similar to those of a physical standby database.
The concept of snapshot standby is that we can temporary convert a physical standby to an updatable database for different purposes and then convert back to a physical standby. During the time that the database is a snapshot standby, it can be used as a normal read write database. And then after the flashback technology is used combined with archived logfiles to convert back the snapshot to a physical standby.
In this blog I have tested some common tasks on a snapshot database and I am describing below the results.
We show our configuration, oracle 12.2 is used.
ORCL_SITE: Primary
ORCL_SITE2: Physical Standby
ORCL_SITE2: Physical Standby
ORCL_SITE3: Logical Standby

DGMGRL> show configuration;
.
Configuration – ORCL_DR
.
Protection Mode: MaxPerformance
Members:
ORCL_SITE – Primary database
ORCL_SITE1 – Physical standby database
ORCL_SITE2 – Physical standby database
ORCL_SITE3 – Logical standby database
.
Fast-Start Failover: DISABLED
.
Configuration Status:
SUCCESS (status updated 42 seconds ago)
.
DGMGRL>

The first question we can ask is which type of standby can be converted to a snapshot database
1- Can we convert a logical standby to a snapshot standby
Let’s convert our logical standby to a snapshot standby

DGMGRL> CONVERT DATABASE ‘ORCL_SITE3’ TO SNAPSHOT STANDBY;
Converting database “ORCL_SITE3” to a Snapshot Standby database, please wait…
Error: ORA-16831: operation not allowed on this member
.
Failed.
Failed to convert database “ORCL_SITE3”
DGMGRL>
Answer: NO we cannot convert a logical standby to a snapshot standby
2- Can we convert a physical standby to a snapshot standby
Let’s convert our physical standby to a snapshot standby

DGMGRL> CONVERT DATABASE ‘ORCL_SITE2’ TO SNAPSHOT STANDBY;
Converting database “ORCL_SITE2” to a Snapshot Standby database, please wait…
Database “ORCL_SITE2” converted successfully
DGMGRL>

And we can verify the new status of the database ‘ORCL_SITE2′

DGMGRL> show database ‘ORCL_SITE2’;
.
Database – ORCL_SITE2
.
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 1 minute 33 seconds (computed 1 second ago)
Instance(s):
ORCL
.
Database Status:
SUCCESS
.
DGMGRL>
.

Answer: Yes we can convert a physical standby to a snapshot standby.
Now that the physical is converted to a snapshot let’s continue our quizz.
3- Can we open a snapshot standby on a read only mode
Let’s shutdown our standby snapshot and let’s open it on read only mode

SQL> startup open read only;
ORACLE instance started.
.
Total System Global Area 943718400 bytes
Fixed Size 8627440 bytes
Variable Size 348130064 bytes
Database Buffers 583008256 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

The status is now open read only

SQL> select db_unique_name,database_role,open_mode from v$database;
.
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
—————————— —————- ——————–
ORCL_SITE2 SNAPSHOT STANDBY READ ONLY
Answer: Yes a snapshot standby can be opened in a READ ONLY mode
4- Can we create a tablespace on a snapshot standby
Connected to the standby database let’s create a tablespace

SQL> create tablespace mytab_snap datafile ‘/u01/app/oracle/oradata/ORCL/mytab_snap01.dbf’ size 2M autoextend on maxsize 10M;
.
Tablespace created.

We can verify in the table dba_tablespaces

SQL> select tablespace_name from dba_tablespaces;
.
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
MYTAB_SNAP
TAB_TEST

Answer: Yes we can see that the tablespace MYTAB_SNAP was created.
5- Can we drop a tablespace on a snapshot standby
Let’s drop a tablespace
SQL> drop tablespace TAB_TEST including contents and datafiles;
drop tablespace TAB_TEST including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace TAB_TEST on primary database due to
guaranteed restore points.
Answer: No due to guaranteed restore point, we cannot drop a tablespace on a snapshot database.
6- Can we extend a datafile on a snapshot standby
We are going to consider two types of datafiles.
• One from tablespace MYTAB_SNAP created on the snapshot standby
• Another from tablespace TAB_TEST created on the primary

FILE_NAME TABLESPACE_NAME
————————————————– ——————————
/u01/app/oracle/oradata/ORCL/users01.dbf USERS
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/ORCL/stab_test.dbf TAB_TEST
/u01/app/oracle/oradata/ORCL/mytab_snap01.dbf MYTAB_SNAP
Let’s extend first the datafile created on the snapshot standby

SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/mytab_snap01.dbf’ resize 20M;
.
Database altered.
And then let’s do the same operation on the datafile created on the primary

SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/stab_test.dbf’ resize 20M;
.
Database altered.
We can verify the new sizes

SQL> select FILE_NAME,sum(BYTES) from dba_data_files group by FILE_NAME;
.
FILE_NAME SUM(BYTES)
————————————————– ———-
/u01/app/oracle/oradata/ORCL/users01.dbf 5242880
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 73400320
/u01/app/oracle/oradata/ORCL/system01.dbf 870318080
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 587202560
/u01/app/oracle/oradata/ORCL/stab_test.dbf 20971520
/u01/app/oracle/oradata/ORCL/mytab_snap01.dbf 20971520
.
6 rows selected.
Answer: Yes we can extend datafiles on a snapshot standby.
Just note that when the snapshot standby is converted back to a physical, the datafile is shrinked until his previous size.
7- Can we reduce a datafile on a standby database
Let’s now reduce the size of the datafile created on the snapshot

SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/mytab_snap01.dbf’ resize 5M;
.
Database altered.

And let’s do the same operation on the datafile created on the primary

SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/stab_test.dbf’ resize 5M;
alter database datafile ‘/u01/app/oracle/oradata/ORCL/stab_test.dbf’ resize 5M
*
ERROR at line 1:
ORA-38883: Cannot shrink data file /u01/app/oracle/oradata/ORCL/stab_test.dbf
on primary database due to guaranteed restore points.
Answer: Yes we see that we can only reduce size for datafiles created on the snapshot standby.
8- Can we do a switchover to a snapshot standby
As a snapshot is a physical standby which was converted, one may ask if a switchover is possible to a snapshot standby.

DGMGRL> switchover to ‘ORCL_SITE2’;
Performing switchover NOW, please wait…
Error: ORA-16831: operation not allowed on this member
.
Failed.
Unable to switchover, primary database is still “ORCL_SITE”
DGMGRL>
Answer: No we cannot do a switchover to a snapshot standby.
9- Can we do a failover to a snapshot standby
The same question can be asked about failover.

DGMGRL> connect sys/root@ORCL_SITE2
Connected to “ORCL_SITE2”
Connected as SYSDBA.
DGMGRL> failover to ‘ORCL_SITE2’;
Converting database “ORCL_SITE2” to a Physical Standby database, please wait…
Operation requires shut down of instance “ORCL” on database “ORCL_SITE2”
Shutting down instance “ORCL”…
Connected to “ORCL_SITE2”
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance “ORCL” on database “ORCL_SITE2”
Starting instance “ORCL”…
ORACLE instance started.
Database mounted.
Connected to “ORCL_SITE2”
Connected to “ORCL_SITE2”
Continuing to convert database “ORCL_SITE2” …
Database “ORCL_SITE2” converted successfully
Performing failover NOW, please wait…
Failover succeeded, new primary is “ORCL_SITE2”
DGMGRL>
Answer: Yes, we can do a failover to a snapshot standby but the time of the failover is longer than if the failover was done to a physical standby. Indeed oracle has
• Convert the snapshot to physical standby one
• Applied archived logs to the physical standby
• And then do the failover