Every DBA know’s the situations, where your boss \/ customer ask you to copy fresh data into the test database… since this blog is about oracle databases, we are solving this issue usually either with “datapump” or “rman duplicate” – and there is nothing wrong about it. But now imagine, we are talking about more than 400 test environments. To satisfy all testers, the production is exported every day (although not all test databases are refreshed daily), which takes several hours every day. And then there are a lot of costs for the expensive SAN storage… So i was asked if there is a better, faster and cheaper solution. As you may suppose, after some research I found the 12c new feature of doing snapshot clones… and it’s really nice, so I want to show you how it works.
\n
\nAdvantages<\/p>\n
Disadvantages<\/p>\n
since i do not have a netapp or zfs applicance, I’m using ACFS for my test, but there are also some things to consider:<\/p>\n
OK, lets start… first – as mentioned above – we need a rac. If you don’t know how to install, i recommend to visit this site: rack attack<\/a>. of course you have to replace the variables in the brackets… in my test environment I created a dedicated diskgroup called ACFS and created a volume named oradata. Great! now we have to crate a filesystem on that virtual block device<\/p>\n and last step: mount the volume<\/p>\n that’s it – now the clusterware should detect the new entry in the OCR (the check is running every 30 seconds) and automount the volume… if not, you can do it manually:<\/p>\n next chapter: create the database. Since i want to simulate the complete way, I created two databases: one is the source (=production) called ORCL, the other the target (=test) database named CDB. Both databases are located on ASM disksgroups (not the ACFS volume!). The source is installed the classical way (no multi- or singletenant) while the target is installed as CDB but without any PDB. I just used the user “system”, but any other user with the “create session” and “create pluggable database” privilege is sufficient. based on the size of the database and the speed of your storage, this will take some minutes… please note that you have to open the database first to make some kind of instance recovery, afterwards you can bring the pdb in the correct state to be a seed database. P.S. in some guidelines you will be advised to set the parameter clonedb to true – don’t do, at least if you are using ACFS! it’s only necessary if your storage can’t do snapshots, so oracle is trying to do some magic, don’t know if its working. For further details, just read the manual<\/a> (search for “SNAPSHOT COPY”) and what’s inside?<\/p>\n so oracle “tells” the storage (in this case the ACFS driver) to do a snapshot of these data files (and only these files, if there are other databases on this acfs volume they won’t be inside the snapshot), afterwards these snapshots are linked to the location where the data files of the new clone database should be – thats it… very easy and the dba has not to worry about storage snapshots, it’s done automatically. If you drop the clone the snapshot will also be deleted automatically.<\/p>\n just tell me if you have any questions…<\/p>\n Benjamin<\/p>\n","protected":false},"excerpt":{"rendered":" Every DBA know’s the situations, where your boss \/ customer ask you to copy fresh data into the test database… since this blog is about oracle databases, we are solving this issue usually either with “datapump” or “rman duplicate” – … Continue reading
\nNext we are going to create an ACFS volume… this can be done easily with asmcmd:<\/p>\n\r\nbash> asmcmd volcreate -G <diskgroup> -s 10G <volume_name>\r\n<\/pre>\n
\nthen we have to check, which device name was reserved for this new volume<\/p>\n\r\nbash> asmcmd volinfo -G ACFS oradata\r\nDiskgroup Name: ACFS\r\n\r\n Volume Name: ORADATA\r\n Volume Device: \/dev\/asm\/oradata-30\r\n State: ENABLED\r\n ... \r\n<\/pre>\n
\r\nmkfs.acfs \/dev\/asm\/oradata-30\r\n<\/pre>\n
\r\nsudo \/sbin\/acfsutil registry -a \/dev\/asm\/oradata-30 \/u00\/app\/oracle\/oradata\r\n<\/pre>\n
\r\nsudo mkdir \/u00\/app\/oracle\/oradata\r\nsudo \/bin\/mount -t acfs \/dev\/asm\/oradata-30 \/u00\/app\/oracle\/oradata\r\n<\/pre>\n
\nTo use the PDB-clone-feature, we have to create a database link from the target to the source first:<\/p>\n\r\nSYS@CDB1>CREATE DATABASE LINK orcl CONNECT TO system IDENTIFIED BY <password> USING 'rac01-scan:1521\/orcl';\r\n<\/pre>\n
\nnext we have to prepare the directory and create the master pdb<\/p>\n\r\nbash> mkdir -p \/u00\/app\/oracle\/oradata\/CDB\/prod_seed\r\nsql> CREATE PLUGGABLE DATABASE prod_seed FROM NON$CDB@orcl CREATE_FILE_DEST='\/u00\/app\/oracle\/oradata\/CDB\/prod_seed';\r\n<\/pre>\n
\nwhen finished, you have a seed pdb with the interal structure of a non-cdb… so you have to convert it first.<\/p>\n\r\nSQL> ALTER SESSION SET CONTAINER=prod_seed;\r\nSQL> @$ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql\r\nSQL> ALTER PLUGGABLE DATABASE prod_seed OPEN;\r\nSQL> ALTER PLUGGABLE DATABASE prod_seed OPEN READ ONLY FORCE;\r\nSQL> ALTER PLUGGABLE DATABASE prod_seed SAVE STATE;\r\n<\/pre>\n
\nAnd now: create the clone<\/p>\n\r\nbash> mkdir -p \/u00\/app\/oracle\/oradata\/CDB\/clone1\r\nSQL> CREATE PLUGGABLE DATABASE clone1 FROM prod_seed CREATE_FILE_DEST='\/u00\/app\/oracle\/oradata\/CDB\/clone1' SNAPSHOT COPY;\r\nSQL> ALTER PLUGGABLE DATABASE clone1 OPEN;\r\n<\/pre>\n
\nOK, and now? Finished! … and where is the magic? the last ‘create pluggable database…’ just took a few seconds, regardless the size of the seed database, and it consumes no space at all (at least at the moment)…<\/p>\n\r\nbash> du -sh *\r\n228K clone1\r\n3.6G prod_seed\r\n<\/pre>\n
\r\nls -lhr clone1\/CDB\/72C5EDFE41727F36E0531F01100AB18B\/datafile\/\r\ntotal 20K\r\nlrwxrwxrwx. 1 oracle dba 154 Aug 6 16:17 o1_mf_users_fpjp2wdt_.dbf -> \/u00\/app\/oracle\/oradata\/.ACFS\/snaps\/72C5EDFE41727F36E0531F01100AB18B\/CDB\/prod_seed\/CDB\/72C340E0CEE355E1E0531F01100AFEB9\/datafile\/o1_mf_users_fpjbvo7t_.dbf\r\nlrwxrwxrwx. 1 oracle dba 157 Aug 6 16:17 o1_mf_undotbs2_fpjp2wdg_.dbf -> \/u00\/app\/oracle\/oradata\/.ACFS\/snaps\/72C5EDFE41727F36E0531F01100AB18B\/CDB\/prod_seed\/CDB\/72C340E0CEE355E1E0531F01100AFEB9\/datafile\/o1_mf_undotbs2_fpjbvo7g_.dbf\r\nlrwxrwxrwx. 1 oracle dba 157 Aug 6 16:17 o1_mf_undotbs1_fpjp2wco_.dbf -> \/u00\/app\/oracle\/oradata\/.ACFS\/snaps\/72C5EDFE41727F36E0531F01100AB18B\/CDB\/prod_seed\/CDB\/72C340E0CEE355E1E0531F01100AFEB9\/datafile\/o1_mf_undotbs1_fpjbvo4c_.dbf\r\nlrwxrwxrwx. 1 oracle dba 155 Aug 6 16:17 o1_mf_system_fpjp2w7z_.dbf -> \/u00\/app\/oracle\/oradata\/.ACFS\/snaps\/72C5EDFE41727F36E0531F01100AB18B\/CDB\/prod_seed\/CDB\/72C340E0CEE355E1E0531F01100AFEB9\/datafile\/o1_mf_system_fpjbvnyh_.dbf\r\nlrwxrwxrwx. 1 oracle dba 155 Aug 6 16:17 o1_mf_sysaux_fpjp2wc4_.dbf -> \/u00\/app\/oracle\/oradata\/.ACFS\/snaps\/72C5EDFE41727F36E0531F01100AB18B\/CDB\/prod_seed\/CDB\/72C340E0CEE355E1E0531F01100AFEB9\/datafile\/o1_mf_sysaux_fpjbvo3z_.dbf\r\n<\/pre>\n