Migrating a new PDB to the existing 19c DataGuard on ExaCC.

We’ve already discussed  how to migrate databases from a standalone 12.2 database to a pluggable database (PDB) in a 19c container in the Oracle cloud. But what if the target container database (CDB)  is already part of a Data Guard configuration and has several PDB in it? I will try to go through the main steps on how to do that without breaking the replication. 

From the high level the process boiled down to several main steps. We plugged in the new database with the clause “standbys=none”. Then we did all our patching, upgrade and all necessary configuration changes. After that I could instantiate the data on the disaster recovery (DR) site in our standby PDB. The final steps made the new PDB on standby site to use the copied data files and enabled the replication.

Here is the high level flowchart for the process:

We started from adding the new PDB on the primary side. You might notice the clause “standbys=none” in the command.

testdb011> CREATE pluggable DATABASE testdg02 AS clone USING '/home/oracle/working/bcadmgo/pdbcdb2.xml' standbys=NONE;
 
Pluggable DATABASE created.
 
testdb011> SELECT name,open_mode,recovery_status FROM v$pdbs;
 
NAME							     OPEN_MODE	RECOVERY
------------------------------------------------------------ ---------- --------
PDB$SEED						     READ ONLY	ENABLED
TESTPDB1						     READ ONLY	ENABLED
TESTDG01						     READ ONLY	ENABLED
TESTDG02						     MOUNTED	DISABLED
 
testdb011> ALTER pluggable DATABASE TESTDG02 OPEN upgrade;
 
Pluggable DATABASE altered.
 
testdb011> SHOW pdbs
 
	       CON_ID CON_NAME			     OPEN MODE	RESTRICTED
--------------------- ------------------------------ ---------- ----------
		 2.00 PDB$SEED			     READ ONLY	NO
		 3.00 TESTPDB1			     READ WRITE NO
		 4.00 TESTDG01			     READ WRITE NO
		 5.00 TESTDG02			     MIGRATE	YES
testdb011>

You can see in the output the database plugged in and in “MIGRATE” state on the primary side and the recovery state is “DISABLED” . What would we see on the DR site?

testdb011> SELECT name,open_mode,recovery_status FROM v$pdbs;
 
NAME							     OPEN_MODE	RECOVERY
------------------------------------------------------------ ---------- --------
PDB$SEED						     READ ONLY	ENABLED
TESTPDB1						     READ ONLY	ENABLED
TESTDG01						     READ ONLY	ENABLED
TESTDG02						     MOUNTED	DISABLED
 
testdb011>

The state is “MOUNTED” and recovery is “DISABLED”. It was expected but what should it show for data files since nothing was really replicated. The alert log showed us that the PDB was plugged into the standby CDB with nonexistent datafile using name pattern UNNAMED*.

Recovery created pluggable database TESTDG02
TESTDG02(5):File #26 added to control file as 'UNNAMED00026'. Originally created as:
TESTDG02(5):'+DATAC1/TESTDB01_MONCTON/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/system.555.1042217171'
TESTDG02(5):because the pluggable database was created with nostandby
TESTDG02(5):or the tablespace belonging to the pluggable database is
TESTDG02(5):offline.
TESTDG02(5):File #27 added to control file as 'UNNAMED00027'. Originally created as:
TESTDG02(5):'+DATAC1/TESTDB01_MONCTON/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/sysaux.556.1042217171'
TESTDG02(5):because the pluggable database was created with nostandby
TESTDG02(5):or the tablespace belonging to the pluggable database is
TESTDG02(5):offline.
TESTDG02(5):File #28 added to control file as 'UNNAMED00028'. Originally created as:
TESTDG02(5):'+DATAC1/TESTDB01_MONCTON/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/undotbs1.557.1042217171'
TESTDG02(5):because the pluggable database was created with nostandby
TESTDG02(5):or the tablespace belonging to the pluggable database is
...

After all patching and upgrade activities on the primary side which you could read in the previous post we were able to open the database. The next step was to make an actual copy of the PDB on the DR site. We used RMAN restore “from service” option. It copied all PDB data files from the source over the network.

Before running the script I recommend to make sure no archived logs are going to be deleted on primary and standby. You can just run the command on both sides to change the RMAN archivelog deletion policy. And monitor the  FRA free size after running it.

RMAN> configure archivelog deletion policy to none;

And if you were using Transparent Data Encryption (TDE) you should have copied your updated wallet over to be able to read the encrypted data files. In the Oracle cloud it will be certainly the case.

The RMAN script is to copy the data was quite simple:

run{
set newname for pluggable database TESTDG02 to new;
restore pluggable database TESTDG02 from service testdb01_moncton;
}

After running the script we got the copy of all data files for our standby PDB. After doing that we should stop our replication. We used the Data Guard Broker and our command looked like that:

DGMGRL> show configuration
 
Configuration - dgconf_testdb01
 
  Protection Mode: MaxPerformance
  Members:
  testdb01_moncton - Primary database
    testdb01_halifax - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 105 seconds ago)
 
DGMGRL> edit database testdb01_halifax set state='apply-off';
Succeeded.
DGMGRL>

Having the replication stopped we switched our standby PDB to use the copied files.

RMAN> switch pluggable database TESTDG02 to copy;
 
datafile 26 switched to datafile copy "+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/system.519.1042285693"
datafile 27 switched to datafile copy "+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/sysaux.520.1042285701"
datafile 28 switched to datafile copy "+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/undotbs1.521.1042285709"
datafile 29 switched to datafile copy "+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/undo_2.522.1042285713"
datafile 30 switched to datafile copy "+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/users.523.1042285719"
 
RMAN>

And enabled the replication for the PDB:

testdb011> ALTER SESSION SET container=TESTDG02;
 
SESSION altered.
 
testdb011> ALTER pluggable DATABASE enable recovery;
 
Pluggable DATABASE altered.
 
testdb011>

That would not be enough if you had database version 18 and higher. You should change status for the datafile to online. We had version 19.7 and ran a script to online the files.

testdb011> ALTER DATABASE datafile '+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/system.519.1042285693' online;
ALTER DATABASE datafile '+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/sysaux.520.1042285701' online;
ALTER DATABASE datafile '+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/undotbs1.521.1042285709' online;
ALTER DATABASE datafile '+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/undo_2.522.1042285713' online;
ALTER DATABASE datafile '+DATAC1/TESTDB01_HALIFAX/A748656F0AC4F483E0530AFE10AC16AA/DATAFILE/users.523.1042285719' online;
DATABASE altered.

Everything was ready and the last task was to resume the replication in the Data Guard Broker:

DGMGRL> edit database testdb01_halifax set state='apply-on';
Succeeded.
DGMGRL>
 
 
DGMGRL> disable configuration
Disabled.
DGMGRL> enable configuration
Enabled.
DGMGRL>

We could see that the replication was enabled for the PDB:

testdb011> SELECT name,open_mode,recovery_status FROM v$pdbs;
 
NAME							     OPEN_MODE	RECOVERY
------------------------------------------------------------ ---------- --------
PDB$SEED						     MOUNTED	ENABLED
TESTPDB1						     MOUNTED	ENABLED
TESTDG01						     MOUNTED	ENABLED
TESTDG02						     MOUNTED	ENABLED

The rest of the action such as switching the database to read-only mode (if you have Active Data Guard) or other configuration steps are up to your requirements.

As you could see the procedure is not complicated but still have quite a few steps to execute. Some steps could be and should be automated but it would be subject for another topic. Happy migrations and stay tuned.

Oracle OCI Database service storage allocation.

Today I would like to discuss the block storage allocation in a VM based Oracle DBCS system. Several times in different conversations it was mentioned that the block storage will be allocated with triple redundancy on the ASM level. Let’s check it out.
If we try to allocate the minimum size volume 256GB for an Oracle VM based DBCS it shows the total storage as 712GB.
Screen Shot 2020-01-06 at 10.08.39 AM.png
But why does it show the 712GB?
Screen Shot 2020-01-06 at 10.11.22 AM.png
And, if we increase the initial storage allocation to 1024GB the total allocation will grow to 1480Gb.
Screen Shot 2020-01-06 at 10.13.28 AM.png
It is not the triple allocation of storage. But where are the additional 456GB?
Let’s have a look at the actual allocation in the ASM on one of the DBCS VM.
I’ve created a DBCS VM with 256GB ASM based storage for data and here are block storage volumes presented to the system:

[grid@gleborcl ~]$ lsblk
NAME                        MAJ:MIN    RM  SIZE RO TYPE MOUNTPOINT
sda                           8:0       0   58G  0 disk
|-sda1                        8:1       0  486M  0 part /boot/efi
|-sda2                        8:2       0  1.4G  0 part /boot
`-sda3                        8:3       0 52.2G  0 part
|-VolGroupSys4-LogVolRoot 249:0       0   35G  0 lvm  /
`-VolGroupSys4-LogVolSwap 249:1       0   16G  0 lvm  [SWAP]
sdb                           8:16      0   64G  0 disk
sdc                           8:32      0   64G  0 disk
sdd                           8:48      0   64G  0 disk
sde                           8:64      0   64G  0 disk
sdf                           8:80      0   64G  0 disk
sdg                           8:96      0   64G  0 disk
sdh                           8:112     0   64G  0 disk
sdi                           8:128     0   64G  0 disk
sdj                           8:144     0  200G  0 disk /u01
asm!commonstore-330         248:168961  0    5G  0 disk /opt/oracle/dcs/commonstore

We have eight 64GB disks attached to the system as volumes and 200GB as a volume for Oracle binaries. It gives us exactly 712GB in total. And here we can see how the eight volumes are used.

SQL> SELECT name,path,total_mb FROM v$asm_disk ORDER BY 1;
 
NAME			       PATH				TOTAL_MB
------------------------------ ------------------------------ ----------
DATA_0000		       /dev/DATADISK3			   65536
DATA_0001		       /dev/DATADISK2			   65536
DATA_0002		       /dev/DATADISK1			   65536
DATA_0003		       /dev/DATADISK4			   65536
RECODISK1		       /dev/RECODISK1			   65536
RECODISK2		       /dev/RECODISK2			   65536
RECODISK3		       /dev/RECODISK3			   65536
RECODISK4		       /dev/RECODISK4			   65536
 
8 ROWS selected.
 
SQL> SELECT name,TYPE,total_mb FROM v$asm_diskgroup;
 
NAME			       TYPE	TOTAL_MB
------------------------------ ------ ----------
DATA			       EXTERN	  262144
RECO			       EXTERN	  262144
 
SQL>

The data and reco disk groups are created with external redundancy and have four 64GB disks which give us 256GB usable space for each group. And, by the way, if you are wondering whether Oracle uses ASMLib or AFD here is how Oracle provides disks names and permissions.

[grid@gleborcl ~]$ cat /etc/udev/rules.d/70-names.rules
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360e2e6804e814b04bf647bbd60c92978", SYMLINK+="DATADISK1",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="3600984cc51b945ae9142bb8a6890c444", SYMLINK+="DATADISK2",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360e7ee9971f5452caaff44c6f0b0ea2f", SYMLINK+="DATADISK3",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="36007a37863594be48f687a92b73b6ba8", SYMLINK+="DATADISK4",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360446c5789094176991e3773b7503877", SYMLINK+="RECODISK1",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360a67d4641874d9ca8a2a22f8719ca56", SYMLINK+="RECODISK2",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360a4a4df343041e3b45b88ebe39d67cd", SYMLINK+="RECODISK3",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="36019ec16293445d19fe8a3734792ec19", SYMLINK+="RECODISK4",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
KERNEL=="sd*", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="36093853c644b4e619a2f7ead2b8f38ee", SYMLINK+="localdisk",  OWNER="grid",  GROUP="asmadmin",  MODE="0660"
[grid@gleborcl ~]$

It is provided by the UDEV rules. The ASMlib and AFD are not used in the Oracle cloud.
What if we scale storage up to 1024GB?
Screen Shot 2020-01-06 at 12.19.16 PM.png

Oracle attaches new 256GB volumes to the system

[grid@gleborcl ~]$ lsblk
NAME                        MAJ:MIN    RM  SIZE RO TYPE MOUNTPOINT
sda                           8:0       0   58G  0 disk
|-sda1                        8:1       0  486M  0 part /boot/efi
|-sda2                        8:2       0  1.4G  0 part /boot
`-sda3                        8:3       0 52.2G  0 part
|-VolGroupSys4-LogVolRoot 249:0       0   35G  0 lvm  /
`-VolGroupSys4-LogVolSwap 249:1       0   16G  0 lvm  [SWAP]
sdb                           8:16      0   64G  0 disk
sdc                           8:32      0   64G  0 disk
sdd                           8:48      0   64G  0 disk
sde                           8:64      0   64G  0 disk
sdf                           8:80      0   64G  0 disk
sdg                           8:96      0   64G  0 disk
sdh                           8:112     0   64G  0 disk
sdi                           8:128     0   64G  0 disk
sdj                           8:144     0  200G  0 disk /u01
sdk                           8:160     0  256G  0 disk
sdl                           8:176     0  256G  0 disk
sdm                           8:192     0  256G  0 disk
sdn                           8:208     0  256G  0 disk
asm!commonstore-330         248:168961  0    5G  0 disk /opt/oracle/dcs/commonstore
[grid@gleborcl ~]$
SQL> SELECT name,TYPE,total_mb FROM v$asm_diskgroup;
 
NAME			       TYPE	TOTAL_MB
------------------------------ ------ ----------
DATA			       EXTERN	 1048576
RECO			       EXTERN	  262144
 
SQL>

And after rebalancing operation, the old four 64GB disks for the data disk group are deleted and detached from the system.

[grid@gleborcl ~]$ lsblk
NAME                        MAJ:MIN    RM  SIZE RO TYPE MOUNTPOINT
sda                           8:0       0   58G  0 disk
|-sda1                        8:1       0  486M  0 part /boot/efi
|-sda2                        8:2       0  1.4G  0 part /boot
`-sda3                        8:3       0 52.2G  0 part
|-VolGroupSys4-LogVolRoot 249:0       0   35G  0 lvm  /
`-VolGroupSys4-LogVolSwap 249:1       0   16G  0 lvm  [SWAP]
sdf                           8:80      0   64G  0 disk
sdg                           8:96      0   64G  0 disk
sdh                           8:112     0   64G  0 disk
sdi                           8:128     0   64G  0 disk
sdj                           8:144     0  200G  0 disk /u01
sdk                           8:160     0  256G  0 disk
sdl                           8:176     0  256G  0 disk
sdm                           8:192     0  256G  0 disk
sdn                           8:208     0  256G  0 disk
asm!commonstore-330         248:168961  0    5G  0 disk /opt/oracle/dcs/commonstore
[grid@gleborcl ~]$

Now we have four 64GB disks for the reco disk group, four 256GB disks for data and the same 200GB for binaries totaling 1480GB. The binaries and the reco disks allocations are the same for data from 256 to 1024GB. For a 2048GB data disk group, it is different with 408GB for the reco disk group (4x102GB). With the bigger data storage allocation, the size of the reco disk group is increasing.

So, Oracle is using external redundancy ASM disk groups relying on the storage layer as a safety net. Planning the storage allocation is a bit tricky and we need to verify how much block storage is going to be allocated for each size of the data disk group. I tried to find it in the documentation but was not able to locate it. Probably the easiest way is to go the OCI console and push the button to scale storage for any DBCS system or the button to create a new DBCS. It will show how much storage in total is going to be allocated. I hope it might help to properly plan and estimate the cost of the resources on OCI.

Linux LVM for Oracle Database on OCI

Oracle Database as a service (DBCS) on Oracle Cloud Infrastructure (OCI) had been traditionally built based on Oracle Grid Infrastructure with ASM as the main storage layer for the database, however Oracle recently has started to offer a Linux LVM as the storage alternative. Which option is the better alternative? Let’s review some of the differences between the two options.

When provisioning a new DBCS VM on the OCI you are given two choices, Oracle Grid Infrastructure and Linux LVM, Linux LV is positioned by Oracle as a better option for quick deployment.

Screen Shot 2019-11-17 at 9.01.43 AM.png

How much faster is the deployment of a VM with LVM in compared to the GI ASM option? I compared both options in the Toronto region. The creation of the LVM based database system took from 14:07 GMT to 14:25 GMT or just about 18 minutes. For the ASM based DBCS, the deployment took from 14:35 GMT to 15:56 GMT taking 1 hour 21 minutes. The ASM option was about 4.5 times slower.
What are the other differences? First, the LVM based DB system is only a single node option.RAC is not an option on LVM – only single node. Second, there are differences with the available database versions. The GI ASM option offers the full range from 11gr2 to 19c but the LVM based option can use only 18c and 19c database versions.

Screen Shot 2019-11-17 at 10.09.39 AM.png

Third, the initial storage size available on GI ASM version is from 256GB up to 40 TB where as for the LVM option the initial size is from 256 Gb to 8 TB. Scaling is different as well. The max storage scaling for the LVM option depends on the initial storage size chosen during creation. For example, for the initial 256 Gb, we can scale up only up to 2560 Gb. The full matrix on the scaling options for LVM based database can be found in the Oracle documentation.
On the LVM based VM, we are getting not one but two different volume groups for our database. One of them is 252 Gb RECO_GRP designed for redo logs and has been built based on two 128 Gb physical volumes and the second one is the DATA_GRP with another two 128 Gb volumes.
Screen Shot 2019-11-17 at 10.29.32 AM

On the ASM version, we have eight 64 Gb disks for two external redundancy ASM disk groups. It is relatively the same volume size and the same redundancy level. It looks like Oracle uses hardware raids vs ASM or LVM based protection.
Screen Shot 2019-11-17 at 11.11.12 AM.png

Screen Shot 2019-11-17 at 11.13.20 AM.png

Screen Shot 2019-11-17 at 11.17.22 AM.png

What about performance? I tried a simple load test using Dominic Giles’ Swingbench tool and compared similar runs on the LVM and on the ASM based DB system created in the same region using the same VM shape and storage size. I used a small VM.Standard2.1 shape for my VM and 256 Gb initial storage allocation.The options for the “oewizard” generator were “-async_off -scale 5 -hashpart -create -cl -v”.
Here are results for LVM based deployment.
The SOE schema creation time:

Screen Shot 2019-11-17 at 11.20.56 AM.png

For the test itself I used the “charbench” with parameters “-c ../configs/SOE_Server_Side_V2.xml -v users,tpm,tps,vresp -intermin 0 -intermax 0 -min 0 -max 0 -uc 128 -di SQ,WQ,WA -rt 00:10:00”

LVMforDBCS_12.png

Here is the test result summary for the LVM based instance:

LVMforDBCS_13.png

And here are results for the ASM GI installation.
The SOE schema generation:
Screen Shot 2019-11-17 at 12.29.03 PM.png

We can see that it took 58 min on ASM vs 34 min on LVM with 24,544 rows generated per second on ASM vs 43,491 on LVM. I cannot say for sure without more elaborate troubleshooting why it was so slow, but I could see that CPU usage was significantly higher on the ASM based VM than on the LVM and it seemed that not all the load was from the database. Some other tools (like Oswatcher) contributed to the load. Probably it could show different results with bigger shapes where it would be able to use more CPU.

And here is the test result summary for the ASM based instance:
LVMforDBCS_14.png

LVMforDBCS_15.png

The tests showed relatively the same performance ratio between LVM and ASM based instances as during the data generation. The LVM was about two times faster than the ASM based instance. When I looked at the AWR for the ASM based instance it seemed that the CPU was the main bottleneck in the performance. As I said earlier it is quite possible that for larger VMs with more CPU the difference would not be as big.

Overall, the LVM based option for DBCS can be a great option if you want to fire a new single-node Oracle DBCS instance and you can work within the scaling and DB version limitations. In terms of performance, the LVM had much better performance results as compared to the ASM option for a small 1 OCPU shape VM. In my opinion, LVM is as a good tool for developers and testers or even for production machines considering the superior performance results with a small machine.