By Mouhamadou Diaw
Dbvisit snapshot option is a new feature available starting from version 9.0.06. I have tested this option and in this blog I am describing the tasks I have done.
The configuration I am using is following
dbvist1 : primary server
dbvist 2 : standby server
orcl : oracle 19c database
We suppose that the dbvisit environment is already set and the replication is going fine. See previous blog for setting up dbvisit standby
First there are two options
-Snapshot Groups
-Single Snapshots
Snapshot Groups
This option is ideal for companies that are using Oracle Standard Edition (SE,SE1,SE2) where they would like to have a “database that is read-only, but also being kept up to date” . It allows to create 2 or more read-only snapshots of the standby at a time interval. The snapshot will be opened in a read-only mode after its creation. A new service which will point to the latest snapshot is created and will be automatically managed by the listener.
The use of the feature is very easy. As it required managing filesystems like mount and umount, the user which will create the snapshot need some admin privileges.
In our case we have configured the user with full sudo privileges, but you can find all required privileges in the documentation .
1
2
3
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] sudo grep oracle /etc/sudoers oracle ALL=(ALL) NOPASSWD:ALL oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] |
The use of the option can be done by command line or using the graphical dbvserver console. But It is highly recommended to use the graphical tool.
When the option snapshot is available with your license, you will see following tab in the dbvserver console
To create a snapshot groups we just have to choose the option NEW SNAPSHOT GROUP
And then fill the required information. In this example
-We will create a service named snap_service
-We will generate a snapshot every 15 minutes (900 secondes)
There will be a maximum of 2 snapshots. When the limit is reached, the oldest snapshop is deleted. Just note that the latest snapshot will be deleted only when the new snapshot is successfully created. That’s why we can have sometimes 3 snapshots
-The snapshots will be prefixed by MySna
After the snapshots are created without errors, we have to start the snapshots generation by clicking on the green button
And then we can see the status
On OS level we can verify that the first snapshot is created and that the corresponding instance started
1
2
3
4
5
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon oracle 2300 1 0 09:05 ? 00:00:00 ora_pmon_orcl oracle 6765 1 0 09:57 ? 00:00:00 ora_pmon_MySna001 oracle 7794 1892 0 10:00 pts /0 00:00:00 grep --color=auto pmon oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] |
We can also verify that the service snap_service is registered in the listener. This service will be automatically pointed to the latest snapshot of the group.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2020 10:01:49 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbvisit2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 16-JAN-2020 09:05:07 Uptime 0 days 0 hr. 56 min. 42 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/network/admin/listener .ora Listener Log File /u01/app/oracle/diag/tnslsnr/dbvisit2/listener/alert/log .xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbvisit2)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) … … … Service "snap_service" has 1 instance(s). Instance "MySna001" , status READY, has 1 handler(s) for this service... The command completed successfully oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] |
To connect to this service, we just have to create an alias like
1
2
3
4
5
6
7
8
|
snapgroup = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = snap_service) ) ) |
15 minutes later we can see that a new snapshot was generated
1
2
3
4
5
6
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon oracle 2300 1 0 09:05 ? 00:00:00 ora_pmon_orcl oracle 6765 1 0 09:57 ? 00:00:00 ora_pmon_MySna001 oracle 11355 1 0 10:11 ? 00:00:00 ora_pmon_MySna002 oracle 11866 1892 0 10:13 pts /0 00:00:00 grep --color=auto pmon oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] |
Note that we can only open the snapshot in a read only mode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
oracle@dbvisit1: /home/oracle/ [orcl (CDB$ROOT)] sqlplus sys/*****@snapgroup as sysdba SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED SQL> alter pluggable database all open ; alter pluggable database all open * ERROR at line 1: ORA-65054: Cannot open a pluggable database in the desired mode. SQL> alter pluggable database all open read only; Pluggable database altered. |
Of course you have probably seen that you can stop, start, pause and remove snapshots from the GUI.
Single Snapshot
This option allows to create read-only as well read-write snapshots for the database.
To create a single snapshot , we just have to choose the NEW SINGLE SNAPSHOT
In the following example the snapshot will be opened in a read write mode
At this end of the creation we can see the status
We can verify that a service SingleSn was also created
1
2
3
4
5
6
7
8
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] lsnrctl status | grep Singl Instance "SingleSn" , status READY, has 1 handler(s) for this service... Instance "SingleSn" , status READY, has 1 handler(s) for this service... Service "SingleSn" has 1 instance(s). Instance "SingleSn" , status READY, has 1 handler(s) for this service... Instance "SingleSn" , status READY, has 1 handler(s) for this service... Instance "SingleSn" , status READY, has 1 handler(s) for this service... Instance "SingleSn" , status READY, has 1 handler(s) for this service |
And that the instance SinglSn is started
1
2
3
4
5
|
oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon oracle 3294 1 0 16:04 ? 00:00:00 ora_pmon_SingleSn oracle 3966 1748 0 16:08 pts /0 00:00:00 grep --color=auto pmon oracle 14349 1 0 13:57 ? 00:00:00 ora_pmon_orcl oracle@dbvisit2: /home/oracle/ [orcl (CDB$ROOT)] |
We can also remark that at OS level, a filesystem is mounted for the snap. So there must be enough free space at the LVM that host the database.
1
2
3
|
oracle@dbvisit2: /home/oracle/ [MySna004 (CDB$ROOT)] df -h | grep snap /dev/mapper/ora_data-SingleSn 25G 18G 6.3G 74% /u01/app/dbvisit/standby/snap/orcl/SingleSn oracle@dbvisit2: /home/oracle/ [MySna004 (CDB$ROOT)] |
Using the alias
1
2
3
4
5
6
7
8
|
singlesnap = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SingleSn) ) ) |
We can see that new snapshot is opened in read write mode
1
2
3
4
5
6
7
|
SQL> select name,open_mode from v $database; NAME OPEN_MODE --------- -------------------- SINGLESN READ WRITE SQL> |
Conclusion
What we will retain is that a snapshot groups is a group of snapshots taken at a regular time interval. These kinds of snapshots can only be opened in a read only mode.
The single snapshot can be created in a read only or read write mode. When opened in read write mode, it can be compared maybe to Oracle snapshot standby.
The option dbvisit snapshot required a license and is only supported on linux.