As you might know, Oracle doesn’t offer any disaster recovery solution for their Oracle Standard Edition databases. Oracle Data Guard ensures high availability, data protection and disaster recovery for Oracle Enterprise Edition database only. Dbvisit is a great disaster recovery solution for Oracle Standard Edition database, and you might be aware that you can now, as well, create snapshot databases on your Oracle Standby database through dbvisit. We have Reporting Replicas option and Test/Dev Snapshots option. This will give you the advantage to take benefit from your standby database for reporting application or for development team to have a refreshed production database to develop and test new application version. The only problem is that one of the requirement of Dbvisit snapshot is to have the Oracle database files stored on LVM file system. On an ODA the database files will be stored either directly on the ASM or on an ACFS file system. Thus this Dbvisit snapshot feature is for the moment not compatible with the ODA. What a pity! But ACFS is a layer up to the ASM having one advantage to be able to create snapshots. Let’s have a try then and have our Dbvisit standby Oracle database be used for an ACFS snapshot database!
My lab environment
For my test I will be using 2 ODAs :
- an ODA X5-2-HA with 2 nodes dbioda02 and dbioda03
- an ODA X8-2M with node named dbi-oda-x8
My primary database is an ACFS database:
[root@dbioda02 ~]# odacli list-databases | grep -i dbisnap 89b41944-b246-4afe-ad54-ec9fb3c235fd DBISNAP SI 19.15.0.0.220419 false OLTP odb1 ACFS CONFIGURED 5b0b3769-0c05-435e-b7c8-5d295c05934c
oracle@dbioda02:/home/oracle/ [DBISNAP] DBISNAP ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISNAP OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbioda02:/home/oracle/ [DBISNAP] df -h | grep -i dbisnap /dev/asm/datdbisnap-70 100G 2.6G 98G 3% /u02/app/oracle/oradata/DBISNAP oracle@dbioda02:/home/oracle/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 17 16:51:26 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- -------------------------------- db_create_file_dest string /u02/app/oracle/oradata/DBISNAP/
My standby database is an ACFS database as well:
[root@dbi-oda-x8 ~]# odacli list-databases | grep -i dbisnap f55ef7bc-219f-4abd-b942-0a82c9078718 DBISNAP SI 19.15.0.0.220419 false OLTP odb1 ACFS CONFIGURED c2e153aa-88af-45ce-98e1-2dab638453d2
oracle@dbi-oda-x8:/home/oracle/ [DBISNAP] DBISNAP ********* dbi services Ltd. ********* STATUS : MOUNTED DB_UNIQUE_NAME : DBISNAP_STD OPEN_MODE : MOUNTED LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbi-oda-x8:/home/oracle/ [DBISNAP] df -h | grep -i dbisnap /dev/asm/datdbisnap-265 100G 2.4G 98G 3% /u02/app/oracle/oradata/DBISNAP_STD /dev/asm/rdodbisnap-233 14G 3.4G 11G 25% /u04/app/oracle/redo/DBISNAP oracle@dbi-oda-x8:/home/oracle/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 17 16:54:02 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------------ db_create_file_dest string /u02/app/oracle/oradata/DBISNAP_STD/
And both are synchronized with dbvisit StandbyMP software:
oracle@dbioda02:/home/oracle/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 9914) dbvctl started on dbvisitvippri: Fri Feb 17 16:58:26 2023 ============================================================= Dbvisit Standby log gap report for DBISNAP at 202302171658: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 2554367 2023-02-17:16:58:27 +01:00 Destination 2552652 2023-02-17:16:04:04 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:54:23 Report for Thread 1 ------------------- SOURCE Current Sequence 8 Last Archived Sequence 7 Last Transferred Sequence 7 Last Transferred Timestamp 2023-02-17 16:04:32 DESTINATION Next Required Recovery Sequence 8 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 16:58:29 2023 =============================================================
Create the instance for the snapshot database
We first need to create an instance for the future snapshot database. Let’s call it SNAPDEV. On the ODA this will be performed with odacli create-database command and option -io (instance only). Neither database file nor srvctl service will be created at this point.
[root@dbi-oda-x8 ~]# odacli create-database -u SNAPDEV -n SNAPDEV -dh c2e153aa-88af-45ce-98e1-2dab638453d2 -r ACFS -io
Enter SYS and SYSTEM user password:
Retype SYS and SYSTEM user password:
Job details
----------------------------------------------------------------
ID: 2945b560-bd6b-40f0-b045-155fe7e3331f
Description: Database service creation with db name: SNAPDEV
Status: Created
Created: February 17, 2023 5:07:39 PM CET
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
[root@dbi-oda-x8 ~]# odacli describe-job -i 2945b560-bd6b-40f0-b045-155fe7e3331f
Job details
----------------------------------------------------------------
ID: 2945b560-bd6b-40f0-b045-155fe7e3331f
Description: Database service creation with db name: SNAPDEV
Status: Success
Created: February 17, 2023 5:07:39 PM CET
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Setting up ssh equivalance February 17, 2023 5:07:43 PM CET February 17, 2023 5:07:44 PM CET Success
Setting up ssh equivalance February 17, 2023 5:07:44 PM CET February 17, 2023 5:07:44 PM CET Success
Creating volume datSNAPDEV February 17, 2023 5:07:44 PM CET February 17, 2023 5:07:58 PM CET Success
Creating volume rdoSNAPDEV February 17, 2023 5:07:58 PM CET February 17, 2023 5:08:11 PM CET Success
Creating ACFS filesystem for DATA February 17, 2023 5:08:11 PM CET February 17, 2023 5:08:25 PM CET Success
Creating ACFS filesystem for RECO February 17, 2023 5:08:25 PM CET February 17, 2023 5:08:38 PM CET Success
Database Service creation February 17, 2023 5:08:38 PM CET February 17, 2023 5:08:56 PM CET Success
Auxiliary Instance Creation February 17, 2023 5:08:38 PM CET February 17, 2023 5:08:50 PM CET Success
Password file creation February 17, 2023 5:08:50 PM CET February 17, 2023 5:08:52 PM CET Success
Archive and redo log location creation February 17, 2023 5:08:52 PM CET February 17, 2023 5:08:52 PM CET Success
Updating the Database version February 17, 2023 5:08:52 PM CET February 17, 2023 5:08:55 PM CET Success
I have currently only an instance, and my database is started in nomount:
oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/local/dmk/etc/ [SNAPDEV] SNAPDEV ********* dbi services Ltd. ********* STATUS : STARTED VERSION : 19.15.0.0.0 *************************************
Get and adapt spfile from the standby database
Create a pfile from spfile on the standby database:
oracle@dbi-oda-x8:~/ [DBISNAP] DBISNAP *********************************** INSTANCE_NAME : DBISNAP DB_NAME : DBISNAP DB_UNIQUE_NAME : DBISNAP_STD STATUS : MOUNTED LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/3 DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO *********************************** Statustime: 2023-02-20 14:40:10 oracle@dbi-oda-x8:~/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 14:40:12 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> create pfile='/u01/app/odaorabase/oracle/admin/DBISNAP/pfile/initDBISNAP_STBY.ora' from spfile; File created.
Copy the created instance parameter file to the snapshot database admin directory:
oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/admin/DBISNAP/pfile/ [DBISNAP] cp -p initDBISNAP_STBY.ora /u01/app/odaorabase/oracle/admin/SNAPDEV/pfile/initSNAPDEV.ora
Update new instance parameter file for snapshot database:
- Remove all dynamique value DBISNAP.__parameter
- Adapt *.audit_file_dest with snapshot database entry
- Remove *.control_files
- Update OMF parameter db_create_file_dest and db_create_online_log_dest_X with snapshot database ACFS file system. Any new datafile value for snapshot database should go in its file system
- Update *.db_name and *.db_unique_name with snapshot database name
- Update *.dispatchers with snapshot database name
- Update any log_archive_dest_X with snapshot database name if needed
oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/admin/SNAPDEV/pfile/ [SNAPDEV] vi initSNAPDEV.ora oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/admin/SNAPDEV/pfile/ [SNAPDEV] diff initSNAPDEV.ora /u01/app/odaorabase/oracle/admin/DBISNAP/pfile/initDBISNAP_STBY.ora 0a1,13 > DBISNAP.__data_transfer_cache_size=0 > DBISNAP.__db_cache_size=3238002688 > DBISNAP.__inmemory_ext_roarea=0 > DBISNAP.__inmemory_ext_rwarea=0 > DBISNAP.__java_pool_size=33554432 > DBISNAP.__large_pool_size=16777216 > DBISNAP.__oracle_base='/u01/app/odaorabase/oracle'#ORACLE_BASE set from environment > DBISNAP.__pga_aggregate_target=2147483648 > DBISNAP.__sga_target=4294967296 > DBISNAP.__shared_io_pool_size=134217728 > DBISNAP.__shared_pool_size=805306368 > DBISNAP.__streams_pool_size=0 > DBISNAP.__unified_pga_pool_size=0 8c21 *.audit_file_dest='/u01/app/odaorabase/oracle/admin/DBISNAP_STD/adump' 11a25 > *.control_files='/u04/app/oracle/redo/DBISNAP/DBISNAP_STD/controlfile/o1_mf_kyz55gxr_.ctl'#Restore Controlfile 17,18c31,32 < *.db_create_file_dest='/u02/app/oracle/oradata/SNAPDEV/' *.db_create_file_dest='/u02/app/oracle/oradata/DBISNAP_STD/' > *.db_create_online_log_dest_1='/u04/app/oracle/redo/DBISNAP/' 22c36 *.db_name='DBISNAP' 25c39 *.db_unique_name='DBISNAP_STD' 27c41 *.dispatchers='(PROTOCOL=TCP) (SERVICE=DBISNAPXDB)' 34,35c48,49 < *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=SNAPDEV ALTERNATE=log_archive_dest_10' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=DBISNAP_STD ALTERNATE=log_archive_dest_10' > *.log_archive_dest_10='LOCATION=/u02/app/oracle/oradata/DBISNAP_STD/arc10 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBISNAP_STD ALTERNATE=log_archive_dest_1'
Create spfile from the new pfile.
oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/admin/SNAPDEV/pfile/ [SNAPDEV] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 15:16:50 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> create spfile='/u02/app/oracle/oradata/SNAPDEV/dbs/spfileSNAPDEV.ora' from pfile='/u01/app/odaorabase/oracle/admin/SNAPDEV/pfile/initSNAPDEV.ora'; File created.
Create init file in $ORACLE_HOME/dbs folder.
oracle@dbi-oda-x8:~/ [SNAPDEV] cd $ORACLE_HOME/dbs oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/ [SNAPDEV] vi initSNAPDEV.ora oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/ [SNAPDEV] cat initSNAPDEV.ora spfile='/u02/app/oracle/oradata/SNAPDEV/dbs/spfileSNAPDEV.ora'
Create missing folders.
oracle@dbi-oda-x8:~/ [SNAPDEV] mkdir /u01/app/odaorabase/oracle/admin/SNAPDEV/adump oracle@dbi-oda-x8:~/ [SNAPDEV] mkdir /u02/app/oracle/oradata/SNAPDEV/arc10 oracle@dbi-oda-x8:~/ [SNAPDEV] mkdir -p /u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog oracle@dbi-oda-x8:~/ [SNAPDEV] mkdir -p /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/
Startup the snapshot database in nomount with the spfile.
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 4294963264 bytes Fixed Size 8904768 bytes Variable Size 805306368 bytes Database Buffers 3439329280 bytes Redo Buffers 41422848 bytes
Copy password file
We will also copy the password file from the standby database to the new snapshot database. Remember that both databases are using same ORACLE_HOME.
oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/ [SNAPDEV] mv orapwSNAPDEV orapwSNAPDEV.old oracle@dbi-oda-x8:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/ [SNAPDEV] cp -p /u02/app/oracle/oradata/DBISNAP_STD/dbs/orapwDBISNAP ./orapwSNAPDEV
Stop dbvisit on standby side
We will stop dbvisit on the standby side and ensure there is no modification done on the database. So no new archive log transaction will be executed on the standby database.
Start the standby database in read only
We will start the standby database in read only to ensure the database is in a coherent status and perform next steps in that mode.
oracle@dbi-oda-x8:~/ [rdbms1900] DBISNAP *********************************** INSTANCE_NAME : DBISNAP DB_NAME : DBISNAP DB_UNIQUE_NAME : DBISNAP_STD STATUS : MOUNTED LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/3 DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO *********************************** Statustime: 2023-02-20 16:39:44 oracle@dbi-oda-x8:~/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 16:39:46 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter database open read only; Database altered.
And get the current SCN from the standby database.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2552651
We will use this SCN when recovering the snapshot database. We can get the timestamp running following query on the primary database.
SQL> select SCN_TO_TIMESTAMP(2552651) from dual; SCN_TO_TIMESTAMP(2552651) --------------------------------------------------------------------------- 17-FEB-23 04.04.03.000000000 PM
Create control file for the snapshot database
Generate a backup control file from the standby database.
oracle@dbi-oda-x8:~/ [DBISNAP] DBISNAP *********************************** INSTANCE_NAME : DBISNAP DB_NAME : DBISNAP DB_UNIQUE_NAME : DBISNAP_STD STATUS : OPEN READ ONLY LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/4 DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO *********************************** Statustime: 2023-02-20 16:40:47 oracle@dbi-oda-x8:~/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 16:40:49 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter database backup controlfile to trace as '/u01/app/odaorabase/oracle/admin/SNAPDEV/create/ctl_SNAPDEV.sql'; Database altered.
Update the generated control file as following:
- CREATE CONTROLFILE line should be :
CREATE CONTROLFILE SET DATABASE "SNAPDEV" RESETLOGS FORCE LOGGING ARCHIVELOG - Online log file should go in the new snapshot database redo log folder. Update
/u04/app/oracle/redo/DBISNAP/DBISNAP_STD/onlinelog/with/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/ - The path to the datafiles should now be the one from the future snapshot files. We will name the ACFS snapshot SNAPDEV. The path
/u02/app/oracle/oradata/DBISNAP_STD/DBISNAP_STD/datafile/needs then to be updated with/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/ - Temp file should go in the new snapshot database datafile folder. Update
/u02/app/oracle/oradata/DBISNAP_STD/DBISNAP_STD/datafile/with/u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/
The updated control file script is the following one:
oracle@dbi-oda-x8:/u01/app/odaorabase/oracle/admin/SNAPDEV/create/ [DBISNAP] cat ctl_SNAPDEV.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SNAPDEV" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_1_kyz56td7_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 2 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_2_kyz56tt9_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 3 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_3_kyz56vb6_.log' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_system_kyz55qq0_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_sysaux_kyz56798_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_undotbs1_kyz56gr5_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_users_kyz56j5h_.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 2
-- Replace * with correct password.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK MAXPIECESIZE 2 G');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u03/app/oracle/fast_recovery_area/SNAPDEV/archivelog/2023_02_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u03/app/oracle/fast_recovery_area/SNAPDEV/archivelog/2023_02_20/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/o1_mf_temp_kz72fnos_.tmp'
SIZE 250609664 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Registering these archivelog entries will help rebuild
-- information displayed by the V$ARCHIVED_LOG fixed view
Create the ACFS snapshot
We will now create the ACFS snapshot. We do not need to put the database in begin/end backup knowing the database is opened read only. There is no modification done on any data file.
There is no snapshot for the moment.
oracle@dbi-oda-x8:/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/ [DBISNAP] ls -al total 64 drwxr-xr-x 2 root root 32768 Feb 20 16:06 . drwxr-xr-x 5 root root 32768 Feb 17 15:25 ..
We will connect as grid user and create the snapshot named SNAPDEV. Here we will create a snapshot as read/write (option -w).
[grid@dbi-oda-x8 ~]$ acfsutil snap create -w SNAPDEV /u02/app/oracle/oradata/DBISNAP_STD acfsutil snap create: Snapshot operation is complete.
We now have a snapshot named SNAPDEV.
oracle@dbi-oda-x8:/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/ [DBISNAP] ls -al total 96 drwxr-xr-x 3 root root 32768 Feb 20 17:09 . drwxr-xr-x 5 root root 32768 Feb 17 15:25 .. drwxr-x--- 7 oracle oinstall 32768 Feb 20 17:09 SNAPDEV
We have our snapshot files.
oracle@dbi-oda-x8:/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/ [DBISNAP] ls -ltrh SNAPDEV/DBISNAP_STD/datafile/ total 2.2G -rw-r----- 1 oracle asmadmin 240M Feb 20 15:54 o1_mf_temp_kz72fnos_.tmp -rw-r----- 1 oracle asmadmin 1001M Feb 20 16:39 o1_mf_system_kyz55qq0_.dbf -rw-r----- 1 oracle asmadmin 871M Feb 20 16:39 o1_mf_sysaux_kyz56798_.dbf -rw-r----- 1 oracle asmadmin 5.1M Feb 20 16:39 o1_mf_users_kyz56j5h_.dbf -rw-r----- 1 oracle asmadmin 96M Feb 20 16:39 o1_mf_undotbs1_kyz56gr5_.dbf
Recover the snapshot database
We will use the updated control file script content to recover the snapshot database.
We will first create the control file.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV
*****************************
INSTANCE_NAME : SNAPDEV
DB_NAME : SNAPDEV
DB_UNIQUE_NAME : SNAPDEV
STATUS : STARTED
USERS/SESSIONS : 1/2
VERSION : 19.0.0.0.0
NLS_LANG : n/a
CDB_ENABLED : n/a
*****************************
Statustime: 2023-02-20 18:40:28
oracle@dbi-oda-x8:~/ [SNAPDEV] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 18:40:30 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> CREATE CONTROLFILE SET DATABASE "SNAPDEV" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_1_kyz56td7_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 2 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_2_kyz56tt9_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 3 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_3_kyz56vb6_.log' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_system_kyz55qq0_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_sysaux_kyz56798_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_undotbs1_kyz56gr5_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_users_kyz56j5h_.dbf'
CHARACTER SET AL32UTF8
18 ;
Control file created.
The database is now in mount status, and we can recover it. We will use the timestamp of the SCN taken at the beginning on the standby database and go one second beyond.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV
*****************************
INSTANCE_NAME : SNAPDEV
DB_NAME : SNAPDEV
DB_UNIQUE_NAME : SNAPDEV
STATUS : MOUNTED
LOG_MODE : ARCHIVELOG
USERS/SESSIONS : 1/2
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 19.0.0.0.0
CDB_ENABLED : NO
*****************************
Statustime: 2023-02-20 19:02:04
oracle@dbi-oda-x8:~/ [SNAPDEV] rmanh
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Feb 20 19:02:46 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: SNAPDEV (DBID=2598626450, not open)
RMAN> run {
2> SET UNTIL TIME "to_date('17/02/2023 16:04:04','DD/MM/YYYY HH24:MI:SS')";
3> recover database;
4> }
executing command: SET until clause
Starting recover at 20-FEB-2023 19:04:08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 20-FEB-2023 19:04:14
RMAN>
Start the snapshot database
We can now start the snapshot database in open read/write.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ***************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : MOUNTED LOG_MODE : ARCHIVELOG USERS/SESSIONS : 1/2 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ***************************** Statustime: 2023-02-20 19:11:22 oracle@dbi-oda-x8:~/ [SNAPDEV] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:11:31 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
The snapshot database is opened read/write!
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ********************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 1/2 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ********************************** Statustime: 2023-02-20 19:11:58
We can add a tempfile to the temp tablespace.
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files; no rows selected SQL> ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2G; Tablespace altered. SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/o1_mf_temp_kz8vltyf_.tmp TEMP
Start Dbvisit synchronisation again
We can put the standby database back to the MOUNT mode and synchronise it again with the primary database.
Starting the standby database back in mount mode will make the new archive log application possible again.
oracle@dbi-oda-x8:~/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:15:53 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 4288245824 bytes Fixed Size 8904768 bytes Variable Size 872415232 bytes Database Buffers 3372220416 bytes Redo Buffers 34705408 bytes Database mounted.
Create a few archive logs from the primary side.
oracle@dbioda02:/home/oracle/ [DBISNAP] DBISNAP ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISNAP OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbioda02:/home/oracle/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:19:17 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter system archive log current; System altered. SQL> / System altered. SQL> / System altered. SQL>
Send the new archive logs to the standby side.
oracle@dbioda02:/home/oracle/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP
=============================================================
Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 71904)
dbvctl started on dbvisitvippri: Mon Feb 20 19:20:42 2023
=============================================================
>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
Thread: 1 Archive log gap: 5. Transfer log gap: 5
>>> Sending heartbeat message... skipped
>>> Transferring Log file(s) from DBISNAP on dbvisitvippri to dbi-oda-x8:
thread 1 sequence 8 (o1_mf_1_8_kz7bc755_.arc)... done
thread 1 sequence 9 (o1_mf_1_9_kz7cjk2d_.arc)... done
thread 1 sequence 10 (o1_mf_1_10_kz7gfgjt_.arc)... done
thread 1 sequence 11 (o1_mf_1_11_kz7gfk1r_.arc)... done
thread 1 sequence 12 (o1_mf_1_12_kz7gfnop_.arc)... done
=============================================================
dbvctl ended on dbvisitvippri: Mon Feb 20 19:20:54 2023
=============================================================
Apply the last archive logs on the standby side.
oracle@dbi-oda-x8:~/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP
=============================================================
Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 85336)
dbvctl started on dbi-oda-x8: Mon Feb 20 19:21:54 2023
=============================================================
>>> Sending heartbeat message... skipped
>>> Applying Log file(s) from dbvisitvippri to DBISNAP on dbi-oda-x8:
thread 1 sequence 8 (1_8_1129044758.arc)... done
thread 1 sequence 9 (1_9_1129044758.arc)... done
thread 1 sequence 10 (1_10_1129044758.arc)... done
thread 1 sequence 11 (1_11_1129044758.arc)... done
thread 1 sequence 12 (1_12_1129044758.arc)... done
Last applied log(s):
thread 1 sequence 12
Next SCN required for recovery 2797961 generated at 2023-02-20:19:19:32 +01:00.
Next required log thread 1 sequence 13
=============================================================
dbvctl ended on dbi-oda-x8: Mon Feb 20 19:22:15 2023
=============================================================
And both primary and standby database are in sync.
oracle@dbioda02:/home/oracle/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 81759) dbvctl started on dbvisitvippri: Mon Feb 20 19:25:43 2023 ============================================================= Dbvisit Standby log gap report for DBISNAP at 202302201925: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 2798296 2023-02-20:19:25:43 +01:00 Destination 2798274 2023-02-20:19:25:24 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:00:19 Report for Thread 1 ------------------- SOURCE Current Sequence 19 Last Archived Sequence 18 Last Transferred Sequence 18 Last Transferred Timestamp 2023-02-20 19:25:29 DESTINATION Next Required Recovery Sequence 19 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Mon Feb 20 19:25:45 2023 =============================================================
Using the snapshot database
A development team has now the ability to use a database in read/write mode, which has just been refreshed from the production database, using the dbvisit standby database.
oracle@dbi-oda-x8:~/ [DBISNAP] SNAPDEV ********************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 1/2 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ********************************** Statustime: 2023-02-20 19:29:06 oracle@dbi-oda-x8:~/ [SNAPDEV] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:29:07 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> create table TESTSNAP (id integer, name varchar(10)) tablespace USERS; Table created. SQL> insert into TESTSNAP values (0, 'SCOTT'); 1 row created. SQL> commit; Commit complete. SQL> select * from TESTSNAP; ID NAME ---------- ---------- 0 SCOTT SQL>
And of course this table is not existing neither on the standby database, nor on the primary database.
Standby database.
oracle@dbi-oda-x8:~/ [SNAPDEV] DBISNAP
***********************************
INSTANCE_NAME : DBISNAP
DB_NAME : DBISNAP
DB_UNIQUE_NAME : DBISNAP_STD
STATUS : MOUNTED
LOG_MODE : ARCHIVELOG
USERS/SESSIONS : 2/3
DATABASE_ROLE : PHYSICAL STANDBY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 19.0.0.0.0
CDB_ENABLED : NO
***********************************
Statustime: 2023-02-20 19:32:09
oracle@dbi-oda-x8:~/ [DBISNAP] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:32:11 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> alter database open read only;
Database altered.
SQL> select * from TESTSNAP;
select * from TESTSNAP
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
Primary database.
oracle@dbioda02:/home/oracle/ [DBISNAP] DBISNAP
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : DBISNAP
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 19.15.0.0.0
CDB Enabled : NO
*************************************
oracle@dbioda02:/home/oracle/ [DBISNAP] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:33:34 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> select * from TESTSNAP;
select * from TESTSNAP
*
ERROR at line 1:
ORA-00942: table or view does not exist
Oracle restart database service
We can create an Oracle restart service for the snapshot database.
oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl add database -db SNAPDEV -oraclehome /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8 -instance SNAPDEV -spfile /u02/app/oracle/oradata/SNAPDEV/dbs/spfileSNAPDEV.ora -pwfile /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/orapwSNAPDEV -role primary -startoption OPEN -stopoption IMMEDIATE -dbname SNAPDEV -domain dbi-lab.ch -node dbi-oda-x8 -dbtype SINGLE -diskgroup "DATA" -acfspath "/u01/app/odaorahome,/u02/app/oracle/oradata/SNAPDEV,/u04/app/oracle/redo/SNAPDEV,/u03/app/oracle/,/u01/app/odaorabase0" oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl config database -d SNAPDEV Database unique name: SNAPDEV Database name: SNAPDEV Oracle home: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8 Oracle user: oracle Spfile: /u02/app/oracle/oradata/SNAPDEV/dbs/spfileSNAPDEV.ora Password file: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_8/dbs/orapwSNAPDEV Domain: dbi-lab.ch Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: DATA Mount point paths: /u01/app/odaorahome,/u02/app/oracle/oradata/SNAPDEV,/u04/app/oracle/redo/SNAPDEV/,/u03/app/oracle/,/u01/app/odaorabase0 Services: Type: SINGLE OSDBA group: dba OSOPER group: dbaoper Database instance: SNAPDEV Configured nodes: dbi-oda-x8 CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed
Let’s test it stopping and starting the snapshot database.
oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl status database -d SNAPDEV Instance SNAPDEV is running on node dbi-oda-x8 oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ********************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/5 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ********************************** Statustime: 2023-02-20 23:16:35 oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl stop database -d SNAPDEV oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl status database -d SNAPDEV Instance SNAPDEV is not running on node dbi-oda-x8 oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ************************** INSTANCE_NAME : SNAPDEV STATUS : DOWN ************************** Statustime: 2023-02-20 23:17:13 oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl start database -d SNAPDEV oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl status database -d SNAPDEV Instance SNAPDEV is running on node dbi-oda-x8 oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ********************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/6 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ********************************** Statustime: 2023-02-20 23:17:48
Easily refresh the snapshot database
Let’s see how easy it is to refresh the snapshot database.
Create some new data on the primary database
Let’s create a table with rows on the primary database.
oracle@dbioda02:/home/oracle/ [DBISNAP] DBISNAP ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISNAP OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbioda02:/home/oracle/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 19:33:34 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> create table REFRESHED (id integer not null PRIMARY KEY,operation varchar(30),refresh_date date) tablespace USERS; Table created. SQL> insert into REFRESHED values (0,'Primary data inserted',sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from REFRESHED; ID OPERATION REFRESH_DATE ---------- ------------------------------ ----------------------------- 0 Primary data inserted 20-FEB-2023 23:30:06 SQL>
Archive the current online log.
SQL> alter system archive log current; System altered.
Synchronise primary and standby databases with dbvisit.
oracle@dbioda02:/home/oracle/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP
=============================================================
Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 14841)
dbvctl started on dbvisitvippri: Mon Feb 20 23:32:54 2023
=============================================================
>>> Obtaining information from standby database (RUN_INSPECT=Y)... done
Thread: 1 Archive log gap: 1. Transfer log gap: 1
>>> Transferring Log file(s) from DBISNAP on dbvisitvippri to dbi-oda-x8:
thread 1 sequence 19 (o1_mf_1_19_kz7x6cxh_.arc)... done
=============================================================
dbvctl ended on dbvisitvippri: Mon Feb 20 23:32:59 2023
=============================================================
oracle@dbi-oda-x8:~/ [DBISNAP] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISNAP
=============================================================
Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 69578)
dbvctl started on dbi-oda-x8: Mon Feb 20 23:33:45 2023
=============================================================
>>> Applying Log file(s) from dbvisitvippri to DBISNAP on dbi-oda-x8:
thread 1 sequence 19 (1_19_1129044758.arc)... done
Last applied log(s):
thread 1 sequence 19
Next SCN required for recovery 2816170 generated at 2023-02-20:23:31:39 +01:00.
Next required log thread 1 sequence 20
=============================================================
dbvctl ended on dbi-oda-x8: Mon Feb 20 23:33:51 2023
=============================================================
The information can be seen on the standby database after been opened in read/only. Remember that as long as the standby database is opened read only, dbvisit can not apply any new change vector through the archive log files.
oracle@dbi-oda-x8:~/ [DBISNAP] srvctl stop database -d DBISNAP_STD oracle@dbi-oda-x8:~/ [DBISNAP] srvctl start database -d DBISNAP_STD -startoption "READ ONLY" oracle@dbi-oda-x8:~/ [DBISNAP] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 23:36:47 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS'; Session altered. SQL> select * from REFRESHED; ID OPERATION REFRESH_DATE ---------- ------------------------------ ------------------- 0 Primary data inserted 20/02/2023 23:30:06 SQL>
Of course this information can not been seen on the current snapshot database. It is an old snapshot which has not been refreshed so far.
oracle@dbi-oda-x8:~/ [DBISNAP] SNAPDEV
**********************************
INSTANCE_NAME : SNAPDEV
DB_NAME : SNAPDEV
DB_UNIQUE_NAME : SNAPDEV
STATUS : OPEN READ WRITE
LOG_MODE : ARCHIVELOG
USERS/SESSIONS : 2/7
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 19.0.0.0.0
CDB_ENABLED : NO
**********************************
Statustime: 2023-02-20 23:39:30
oracle@dbi-oda-x8:~/ [SNAPDEV] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 23:39:31 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> select * from REFRESHED;
select * from REFRESHED
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
Let’s refresh it!
Refresh snapshot database
Stop the snapshot database.
oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl stop database -d SNAPDEV
Remove the ACFS Snapshot with grid user.
[grid@dbi-oda-x8 ~]$ acfsutil snap delete SNAPDEV /u02/app/oracle/oradata/DBISNAP_STD acfsutil snap delete: Snapshot operation is complete.
We can also cleanup any datafile or tempfile present in the SNAPDEV datafile folder : /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile.
The standby database is already in read/only. Take the SCN from the standby database.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2816169
Using the primary, let’s get its timestamp.
SQL> select SCN_TO_TIMESTAMP(2816169) from dual; SCN_TO_TIMESTAMP(2816169) --------------------------------------------------------------------------- 20-FEB-23 11.31.37.000000000 PM
Take a new ACFS snapshot with grid user on the standby database.
[grid@dbi-oda-x8 ~]$ acfsutil snap create -w SNAPDEV /u02/app/oracle/oradata/DBISNAP_STD acfsutil snap create: Snapshot operation is complete.
The structure of the database did not change, thus we don’t need to create a new control file backup. We can use the previous one and the same commands as during the initial creation.
Start the snapshot database in NOMOUNT mode.
oracle@dbi-oda-x8:~/ [rdbms1900] SNAPDEV ************************** INSTANCE_NAME : SNAPDEV STATUS : DOWN ************************** Statustime: 2023-02-20 23:51:10 oracle@dbi-oda-x8:~/ [SNAPDEV] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 23:51:11 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 4288245824 bytes Fixed Size 8904768 bytes Variable Size 838860800 bytes Database Buffers 3405774848 bytes Redo Buffers 34705408 bytes
Remove the control file and restore it.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV
*****************************
INSTANCE_NAME : SNAPDEV
DB_NAME : SNAPDEV
DB_UNIQUE_NAME : SNAPDEV
STATUS : STARTED
USERS/SESSIONS : 2/3
VERSION : 19.0.0.0.0
NLS_LANG : n/a
CDB_ENABLED : n/a
*****************************
Statustime: 2023-02-20 23:53:17
oracle@dbi-oda-x8:~/ [SNAPDEV] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 20 23:53:19 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> !rm -f /u04/app/oracle/redo/SNAPDEV/SNAPDEV/controlfile/o1_mf_kz7d6mx1_.ctl
SQL> CREATE CONTROLFILE SET DATABASE "SNAPDEV" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_1_kyz56td7_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 2 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_2_kyz56tt9_.log' SIZE 1024M BLOCKSIZE 512,
GROUP 3 '/u04/app/oracle/redo/SNAPDEV/SNAPDEV/onlinelog/o1_mf_3_kyz56vb6_.log' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_system_kyz55qq0_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_sysaux_kyz56798_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_undotbs1_kyz56gr5_.dbf',
'/u02/app/oracle/oradata/DBISNAP_STD/.ACFS/snaps/SNAPDEV/DBISNAP_STD/datafile/o1_mf_users_kyz56j5h_.dbf'
CHARACTER SET AL32UTF8
18 ;
Control file created.
SQL>
The snapshot database is now in MOUNT mode. We will recover the database using the timestamp of the SCN taken previously on the standby database and go that time 2 seconds beyond, so until 20-FEB-23 23.31.39, otherwise I was getting RMAN-06555: datafile 1 must be restored from backup created before 20-FEB-2023 23:31:38 error. If needed, we could also, I guess, use the archive log files from the primary database during the recovering process.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV
*****************************
INSTANCE_NAME : SNAPDEV
DB_NAME : SNAPDEV
DB_UNIQUE_NAME : SNAPDEV
STATUS : MOUNTED
LOG_MODE : ARCHIVELOG
USERS/SESSIONS : 2/3
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 19.0.0.0.0
CDB_ENABLED : NO
*****************************
Statustime: 2023-02-20 23:57:49
oracle@dbi-oda-x8:~/ [SNAPDEV] rmanh
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Feb 20 23:57:53 2023
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: SNAPDEV (DBID=2598626450, not open)
RMAN> run {
2> SET UNTIL TIME "to_date('20/02/2023 23:31:39','DD/MM/YYYY HH24:MI:SS')";
3> recover database;
4> }
executing command: SET until clause
Starting recover at 21-FEB-2023 00:04:34
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-FEB-2023 00:04:40
RMAN>
We can start the database resetting the redo logs.
RMAN> SQL "ALTER DATABASE OPEN RESETLOGS"; sql statement: ALTER DATABASE OPEN RESETLOGS
And finally add the tempfile to the temp tablespace.
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files; no rows selected SQL> ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2G; Tablespace altered. SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ /u02/app/oracle/oradata/SNAPDEV/SNAPDEV/datafile/o1_mf_temp_kz8vltyf_.tmp TEMP
Checking the refreshed snapshot database
Let’s check if we have our REFRESHED table on the snapshot database.
oracle@dbi-oda-x8:~/ [SNAPDEV] SNAPDEV ********************************** INSTANCE_NAME : SNAPDEV DB_NAME : SNAPDEV DB_UNIQUE_NAME : SNAPDEV STATUS : OPEN READ WRITE LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/4 DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO ********************************** Statustime: 2023-02-21 00:12:05 oracle@dbi-oda-x8:~/ [SNAPDEV] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 21 00:12:09 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS'; Session altered. SQL> select * from REFRESHED; ID OPERATION REFRESH_DATE ---------- ------------------------------ ------------------- 0 Primary data inserted 20/02/2023 23:30:06 SQL>
Yes WE HAVE!!!
Do not forget to put the standby database back in the MOUNT status, so the standby can be kept in sync with the primary database through Dbvisit again.
oracle@dbi-oda-x8:~/ [DBISNAP] srvctl stop database -d DBISNAP_STD oracle@dbi-oda-x8:~/ [DBISNAP] srvctl start database -d DBISNAP_STD oracle@dbi-oda-x8:~/ [DBISNAP] DBISNAP *********************************** INSTANCE_NAME : DBISNAP DB_NAME : DBISNAP DB_UNIQUE_NAME : DBISNAP_STD STATUS : MOUNTED LOG_MODE : ARCHIVELOG USERS/SESSIONS : 2/4 DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.0.0.0.0 CDB_ENABLED : NO *********************************** Statustime: 2023-02-21 00:20:40
We will restart the snapshot database with srvctl as well.
oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl stop database -d SNAPDEV oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl status database -d SNAPDEV Instance SNAPDEV is not running on node dbi-oda-x8 oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl start database -d SNAPDEV oracle@dbi-oda-x8:~/ [SNAPDEV] srvctl status database -d SNAPDEV Instance SNAPDEV is running on node dbi-oda-x8
Summary
Of course creating a snapshot on the standby database is not as user friendly and easy as doing it with Dbvisit Central Console, but this solution is quite a good alternative to take benefit of the dbvisit standby database on the ODA. Remember, Dbvisit snapshot solution is today only possible with LVM, thus not compatible with the ODA. With ACFS snapshot we have a good solution to also use snapshot on Dbvisit standby database.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/MAW_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/ALN_web-min-scaled.jpg)
Marc Wagner
22.02.2023I had a discussion with my colleague Clemens Bleile who could explain my wondering why I had to set the until time 2s later for the refresh of the snapshot database instead of 1s. And this is coming from the precision issue of 3 seconds for the function SCN_TO_TIMESTAMP. You can read this here : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SCN_TO_TIMESTAMP.html
So it is definitively better to always, as long as possible, use the SCN during the recover (set until scn ). My run block for the recover during the refresh of the snapshot database, knowing the SCN was 2816169, would then be :
RMAN> run {
2> SET UNTIL SCN 2816170;
3> recover database;
4> }
Regards,
Marc Wagner