Uncategorized
Oracle Database Appliance : Create Snapshot Database Using OAKCLI
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.