About ODA Snapshot Database
The Source Database must meet the following requirement to create Snapshot Database
Important Note
Steps to Create Snapshot Database Using oakcli utility
[root@odanode1 ~]# oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
orcldb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
odatest RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
SQL> select name, open_mode, database_role, log_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
ODATEST READ WRITE PRIMARY ARCHIVELOG
ODATEST READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 53
Current log sequence 54
SQL> set lines 400 pages 200
SQL> col name for a120
SQL> select name, status from v$datafile;
NAME STATUS
------------------------------------------------------------------------------------------------------------------------ -------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_system_doqhbjop_.dbf SYSTEM
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_sysaux_doqhbnkx_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_undotbs1_doqhbq7b_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_undotbs2_doqhbzyd_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_users_doqhc0tz_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_test_ffsnh1x9_.dbf ONLINE
6 rows selected.
[root@odanode1 ~]# oakcli create -h
Usage:
oakcli create {database | dbstorage | snapshotdb| dbhome | db_config_params } [<options>]
Where:
database - Creates the Database
snapshotdb - Creates the Snapshot Database
dbhome - Creates the Database Home
dbstorage - Creates Storage for a given Database
db_config_params - Creates the Database configuration parameter file
[root@odanode1 ~]# oakcli create snapshotdb -h
Usage:
oakcli create snapshotdb -db <db_name> -from <db_name>
where:
db_name - Name of the database to be created.
from - Source database for creating the snapshot database.
Here we are creating a snapshot database newdb from source database odatest
[root@odanode1 ~]# oakcli create snapshotdb -db newdb -from odatest
INFO: 2018-04-22 14:51:11: Please check the logfile '/opt/oracle/oak/log/odanode1/tools/12.1.2.10.0/createdb_newdb_56401.log' for more details
Please enter the 'root' password :
Please re-enter the 'root' password:
Please enter the 'SYS' password for the Database odatest:
Please re-enter the 'SYS' password:
Please select one of the following for Database Deployment [1 .. 2] :
1 => RACONE
2 => RAC
2
The selected value is : RAC
Specify the Database Class (1. odb-01 '1 core, 8 GB memory' 2. Others) [1] :
The selected value is : odb-01 '1 core, 8 GB memory'
..........Completed
...
SUCCESS: All nodes in /opt/oracle/oak/temp_clunodes.txt are pingable and alive.
......
SUCCESS: All nodes in /opt/oracle/oak/temp_clunodes.txt are pingable and alive.
INFO: 2018-04-22 14:54:03: Creating the SNAP Database 'newdb' from the source Database 'odatest'
INFO: 2018-04-22 14:54:11: Do not perform any Structural change to Database 'odatest' till SNAP Database 'newdb' is created
INFO: 2018-04-22 14:54:31: Taking SNAP of the Database 'odatest'
INFO: 2018-04-22 14:54:32: Successfully took the SNAP of database: odatest
INFO: 2018-04-22 14:55:24: Creating controlfile for database: newdb
INFO: 2018-04-22 14:56:05: Successfully created the control file for the database : newdb
INFO: 2018-04-22 14:56:05: Adding log files for the second thread for the database : newdb
INFO: 2018-04-22 14:56:11: Successfully added the log files for second thread
INFO: 2018-04-22 14:56:15: Recovering the database: newdb, snapshot time : '2018-04-22:14:54:32' , until time : '2018-04-22:14:54:49'
INFO: 2018-04-22 14:56:17: Successfully recovered the database
INFO: 2018-04-22 14:56:17: Opening the database with resetlogs
INFO: 2018-04-22 14:56:27: Successfully opened the database after recovery
INFO: 2018-04-22 14:56:31: Setting the temporary tablespace for database : newdb
INFO: 2018-04-22 14:56:33: Successfully set the temporary tablespace for the database : newdb
INFO: 2018-04-22 14:57:35: Successfully changed the Database ID
INFO: 2018-04-22 14:58:51: Adding the Database resource to the clusterware
INFO: 2018-04-22 14:59:48: Successfully started the database
INFO: 2018-04-22 14:59:48: Updating the TNS entries for the database newdb
INFO: 2018-04-22 15:00:24: Successfully set the RMAN SNAPSHOT control file
INFO: 2018-04-22 15:00:34: Disabling the external references in the database 'newdb' inherited from 'odatest'
INFO: 2018-04-22 15:00:35: Successfully disabled the external references
INFO: 2018-04-22 15:00:59: Run the SQL script '/u01/app/oracle/product/12.1.0.2/dbhome_1/enable_external_refs_newdb_oMj0.sql' on the
database 'newdb' to enable these external references
Also need to restart the database after running the SQL script
SUCCESS: 2018-04-22 15:01:57: Successfully created the Database 'newdb' from 'odatest'
We can now see the newdb database created and it using ACFS file system
[root@odanode1 ~]# oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
orcldb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
newdb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
odatest RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
SQL> select name, open_mode, database_role, log_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
NEWDB READ WRITE PRIMARY ARCHIVELOG
NEWDB READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
SQL> set lines 400 pages 200
SQL> col name for a100
SQL> select name, status from v$datafile;
NAME STATUS
---------------------------------------------------------------------------------------------------- -------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_system_doqhbjop_.dbf SYSTEM
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_sysaux_doqhbnkx_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_undotbs1_doqhbq7b_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_undotbs2_doqhbzyd_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_users_doqhc0tz_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_test_ffsnh1x9_.dbf ONLINE
6 rows selected.
[root@odanode1 ~]# cd /u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile
[root@odanode1 datafile]# ls -ltr
total 3676440
-rw-r----- 1 oracle asmadmin 23076864 Apr 21 06:00 o1_mf_temp_doqhbr2n_.tmp
-rw-r----- 1 oracle asmadmin 387981312 Apr 22 14:54 o1_mf_undotbs2_doqhbzyd_.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 22 14:54 o1_mf_users_doqhc0tz_.dbf
-rw-r----- 1 oracle asmadmin 1073750016 Apr 22 14:54 o1_mf_test_ffsnh1x9_.dbf
-rw-rw---- 1 oracle asmadmin 23076864 Apr 22 15:01 o1_mf_temp_ffspq06s_.tmp
-rw-r----- 1 oracle asmadmin 734011392 Apr 22 15:21 o1_mf_system_doqhbjop_.dbf
-rw-r----- 1 oracle asmadmin 319823872 Apr 22 15:21 o1_mf_undotbs1_doqhbq7b_.dbf
-rw-r----- 1 oracle asmadmin 1216356352 Apr 22 15:21 o1_mf_sysaux_doqhbnkx_.dbf
Conclusion
In this article we have demonstrated how to create snapshot database using oakcli. Using oakcli automates the entire snapshot database creating process. Snapshot Database is created by taking a snapshot of the Oracle ASM Cluster File System (ACFS) where the source data files reside.
- ODA Snapshot Database is created using oakcli command line utility
- ODA automates the entire snapshot database creation process
- Snapshot Database is created by taking a snapshot of the Oracle ASM Cluster File System (ACFS) where the source data files reside
- The Source Database can be:
- Non-RAC
- RAC
- RAC One Node
- Snapshot Database requires less time and Storage
- No downtime for source database for creating snapshot database
- You can create an Oracle RAC database from an Oracle RAC One Node database
- Create snapshot database feature is not available in ODA X6 - S / M / L
- Create snapshot database feature is planned for 12.2 ODA X6 - S / M / L RDBMS
Prerequisites
The Source Database must meet the following requirement to create Snapshot Database
- It shouldn't be a Standby database
- It shouldn't be a Container database
- It shouldn't be in READ-ONLY, RESTRICTED or ONLINE BACKUP Mode
- It must be in ARCHIVE LOG Mode
- All datafiles should be ONLINE
- It shouldn't use centralized wallet with TDE
- It should be 11.2.0.4 and above
Important Note
- Both the Compute Nodes in ODA cluster must have system clock synchronized. The create snapshot database command may fail if the clocks are not synchronized.
- You can't create a RAC snapshot database from Non-RAC source database and vice versa
- You can create Non-RAC snapshot Database from a Non-RAC Snapshot database
Courtesy Oracle
Steps to Create Snapshot Database Using oakcli utility
- List existing databases
[root@odanode1 ~]# oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
orcldb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
odatest RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
- Make source database is ARCHIVE LOG Mode
SQL> select name, open_mode, database_role, log_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
ODATEST READ WRITE PRIMARY ARCHIVELOG
ODATEST READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 53
Current log sequence 54
- List the database files
SQL> set lines 400 pages 200
SQL> col name for a120
SQL> select name, status from v$datafile;
NAME STATUS
------------------------------------------------------------------------------------------------------------------------ -------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_system_doqhbjop_.dbf SYSTEM
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_sysaux_doqhbnkx_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_undotbs1_doqhbq7b_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_undotbs2_doqhbzyd_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_users_doqhc0tz_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/odatest/ODATEST/datafile/o1_mf_test_ffsnh1x9_.dbf ONLINE
6 rows selected.
- Syntax for oakcli create command
[root@odanode1 ~]# oakcli create -h
Usage:
oakcli create {database | dbstorage | snapshotdb| dbhome | db_config_params } [<options>]
Where:
database - Creates the Database
snapshotdb - Creates the Snapshot Database
dbhome - Creates the Database Home
dbstorage - Creates Storage for a given Database
db_config_params - Creates the Database configuration parameter file
[root@odanode1 ~]# oakcli create snapshotdb -h
Usage:
oakcli create snapshotdb -db <db_name> -from <db_name>
where:
db_name - Name of the database to be created.
from - Source database for creating the snapshot database.
- Execute the following command to create snapshot database
Here we are creating a snapshot database newdb from source database odatest
[root@odanode1 ~]# oakcli create snapshotdb -db newdb -from odatest
INFO: 2018-04-22 14:51:11: Please check the logfile '/opt/oracle/oak/log/odanode1/tools/12.1.2.10.0/createdb_newdb_56401.log' for more details
Please enter the 'root' password :
Please re-enter the 'root' password:
Please enter the 'SYS' password for the Database odatest:
Please re-enter the 'SYS' password:
Please select one of the following for Database Deployment [1 .. 2] :
1 => RACONE
2 => RAC
2
The selected value is : RAC
Specify the Database Class (1. odb-01 '1 core, 8 GB memory' 2. Others) [1] :
The selected value is : odb-01 '1 core, 8 GB memory'
..........Completed
...
SUCCESS: All nodes in /opt/oracle/oak/temp_clunodes.txt are pingable and alive.
......
SUCCESS: All nodes in /opt/oracle/oak/temp_clunodes.txt are pingable and alive.
INFO: 2018-04-22 14:54:03: Creating the SNAP Database 'newdb' from the source Database 'odatest'
INFO: 2018-04-22 14:54:11: Do not perform any Structural change to Database 'odatest' till SNAP Database 'newdb' is created
INFO: 2018-04-22 14:54:31: Taking SNAP of the Database 'odatest'
INFO: 2018-04-22 14:54:32: Successfully took the SNAP of database: odatest
INFO: 2018-04-22 14:55:24: Creating controlfile for database: newdb
INFO: 2018-04-22 14:56:05: Successfully created the control file for the database : newdb
INFO: 2018-04-22 14:56:05: Adding log files for the second thread for the database : newdb
INFO: 2018-04-22 14:56:11: Successfully added the log files for second thread
INFO: 2018-04-22 14:56:15: Recovering the database: newdb, snapshot time : '2018-04-22:14:54:32' , until time : '2018-04-22:14:54:49'
INFO: 2018-04-22 14:56:17: Successfully recovered the database
INFO: 2018-04-22 14:56:17: Opening the database with resetlogs
INFO: 2018-04-22 14:56:27: Successfully opened the database after recovery
INFO: 2018-04-22 14:56:31: Setting the temporary tablespace for database : newdb
INFO: 2018-04-22 14:56:33: Successfully set the temporary tablespace for the database : newdb
INFO: 2018-04-22 14:57:35: Successfully changed the Database ID
INFO: 2018-04-22 14:58:51: Adding the Database resource to the clusterware
INFO: 2018-04-22 14:59:48: Successfully started the database
INFO: 2018-04-22 14:59:48: Updating the TNS entries for the database newdb
INFO: 2018-04-22 15:00:24: Successfully set the RMAN SNAPSHOT control file
INFO: 2018-04-22 15:00:34: Disabling the external references in the database 'newdb' inherited from 'odatest'
INFO: 2018-04-22 15:00:35: Successfully disabled the external references
INFO: 2018-04-22 15:00:59: Run the SQL script '/u01/app/oracle/product/12.1.0.2/dbhome_1/enable_external_refs_newdb_oMj0.sql' on the
database 'newdb' to enable these external references
Also need to restart the database after running the SQL script
SUCCESS: 2018-04-22 15:01:57: Successfully created the Database 'newdb' from 'odatest'
- List the databases
We can now see the newdb database created and it using ACFS file system
[root@odanode1 ~]# oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
orcldb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
newdb RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
odatest RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.170117(24732082,24828633)
- Verify the newly create snapshot database
SQL> select name, open_mode, database_role, log_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
NEWDB READ WRITE PRIMARY ARCHIVELOG
NEWDB READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
SQL> set lines 400 pages 200
SQL> col name for a100
SQL> select name, status from v$datafile;
NAME STATUS
---------------------------------------------------------------------------------------------------- -------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_system_doqhbjop_.dbf SYSTEM
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_sysaux_doqhbnkx_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_undotbs1_doqhbq7b_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_undotbs2_doqhbzyd_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_users_doqhc0tz_.dbf ONLINE
/u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile/o1_mf_test_ffsnh1x9_.dbf ONLINE
6 rows selected.
[root@odanode1 ~]# cd /u02/app/oracle/oradata/datastore/.ACFS/snaps/newdb/NEWDB/datafile
[root@odanode1 datafile]# ls -ltr
total 3676440
-rw-r----- 1 oracle asmadmin 23076864 Apr 21 06:00 o1_mf_temp_doqhbr2n_.tmp
-rw-r----- 1 oracle asmadmin 387981312 Apr 22 14:54 o1_mf_undotbs2_doqhbzyd_.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 22 14:54 o1_mf_users_doqhc0tz_.dbf
-rw-r----- 1 oracle asmadmin 1073750016 Apr 22 14:54 o1_mf_test_ffsnh1x9_.dbf
-rw-rw---- 1 oracle asmadmin 23076864 Apr 22 15:01 o1_mf_temp_ffspq06s_.tmp
-rw-r----- 1 oracle asmadmin 734011392 Apr 22 15:21 o1_mf_system_doqhbjop_.dbf
-rw-r----- 1 oracle asmadmin 319823872 Apr 22 15:21 o1_mf_undotbs1_doqhbq7b_.dbf
-rw-r----- 1 oracle asmadmin 1216356352 Apr 22 15:21 o1_mf_sysaux_doqhbnkx_.dbf
Conclusion
In this article we have demonstrated how to create snapshot database using oakcli. Using oakcli automates the entire snapshot database creating process. Snapshot Database is created by taking a snapshot of the Oracle ASM Cluster File System (ACFS) where the source data files reside.
