OCI Database Refresh & from ExaCS to DBCS , patch the refreshed DBCS via OCI CLI
Published: 2022-05-27 13:24:30

Lets' jump straight into the point here ,
here is a traditional On Prem situation 

- My Production is in Exadata , My Dev/Test is in non Exadata 
			Traditional Patching methods would help test the GI/DB Patching 
	
Now here is the same situation in OCI 

- My Production is in ExaCS , My Dev/Test is in DBCS ( VM DB Systems ) 
- Is there a solution to actually test your database  patches in DBCS ( VM DB Systems ) 

It is defenitely  possible but there is a very deep answer to this , Before we go running into the solution , there are a few things to be aware of in ExaCS and DBCS DBCS ( VM DB Systems )  

## 1) Encryption - OCI has always on Encryption 

If you had assumed restoring RMAN backups from ExaCS to DBCS without Encryption , you are wrong OCI Databases always need have Encryption on.


If you had assumed restoring RMAN backups from ExaCS to DBCS without Encryption , you are wrong OCI Databases always need to have Encryption on If you are new to TDE wallets , I suggest this Oracle Documentation Reading 
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/configuring-transparent-data-encryption.html#GUID-753C4808-CC51-4DA1-A5C3-980417FDAB14

The Key Stores in ExaCS and DBCS are in completely different locations  In ExaCS the gv$encryption_wallet; does not show you any WRL 

```
exacs> select * from gv$encryption_wallet;
   INST_ID|WRL_TYPE            |WRL_PARAMETER                           |STATUS         |WALLET_TYPE         |WALLET_OR|FULLY_BAC|    CON_ID
----------|--------------------|----------------------------------------|---------------|--------------------|---------|---------|----------
         1|FILE                |                                        |OPEN           |AUTOLOGIN           |SINGLE   |NO       |         3
         2|FILE                |                                        |OPEN           |AUTOLOGIN           |SINGLE   |NO       |         3

```
It is actually seen in $ORACLE_HOME/network/admin/sqlnet.ora


```
.....
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/<<db_name>>/db_wallet)))
.....
$ ls -l /var/opt/oracle/dbaas_acfs/orcl/tde_wallet
total 84
-rw------- 1 oracle oinstall  1234 XXX 19  202X cwallet.sso 
-rw------- 1 oracle oinstall  1234 XXX 19  202X ewallet.p12 

```

In DBCS ( VM DB Systems ) it is found in a different location and is also seen in $ORACLE_HOME/network/admin/sqlnet.ora

```

dbcs> select * from gv$encryption_wallet;
WRL_TYPE            |WRL_PARAMETER                                               |STATUS         |WALLET_TYPE         |WALLET_OR|FULLY_BAC|    CON_ID
--------------------|------------------------------------------------------------|---------------|--------------------|---------|---------|----------
FILE                |/opt/oracle/dcs/commonstore/wallets/tde/<<db_unique_name>>/ |OPEN           |AUTOLOGIN           |SINGLE   |NO       |         1

```
Note that in both DBCS and ExaCS there will be completely different TDE wallets , so I had to use the same keys as in ExaCS in DBCS so I ca recover my Backups ,

We have only solved 1/3rd of our problem , restoring ExaCS to DBCS is just not enough we need to make it OCI recognisable


## 2) Making a Database OCI recognisable.

In case you are restoring a database from ExaCS to DBCS without having it recognized by OCI Console then there is no real use of having an OCI Database.

Lets say my ExaCS database has the following name 

```

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      exacs

```

NOTE : I used the following script for my Backups in ExaCS to a File Storage in OCI , If you are new to OCI File Storage , I highly recommend this reading 
https://docs.oracle.com/en-us/iaas/Content/File/Concepts/filestorageoverview.htm

```
################# rman_orcl_L0.sh
#!/bin/bash
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_x
NOW=$(date +"%Y_%m_%d_%H%M%S")
$ORACLE_HOME/bin/oraenv <<< orcl1
export ORACLE_SID=orcl1
$ORACLE_HOME/bin/rman target / <<EOF
SPOOL LOG TO '/backup_path/file_store/orcl_L0_$NOW.log';
run{
  ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
        BACKUP AS COMPRESSED BACKUPSET FORMAT '/backup_path/file_store/orcl-L0-%U';
                INCREMENTAL LEVEL 0 DATABASE
                INCLUDE CURRENT CONTROLFILE
                PLUS ARCHIVELOG;
        BACKUP CURRENT CONTROLFILE FORMAT '/backup_path/file_store/orcl-cfile_%U.bkp';
        BACKUP SPFILE FORMAT '/backup_path/file_store/orcl-spfile_%U.bkp';
        BACKUP ARCHIVELOG ALL FORMAT '/backup_path/file_store/orcl-L0-arch_%U.bkp';
}
SPOOL LOG OFF;
exit;
EOF

```


Now lets get back to our DBCS ( VM Db System ) where all the action is going to be 

```

I had created the DB System with the following 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      dbcsxy
db_name                              string      dbcs
db_unique_name                       string      dbcsxy_abc1xy
```


Now to restore the ExaCS to DBCS , you must have copied over the TDE wallet files from ExaCS key store to DBCS key store 
there is no other way you will be able to restore the backups , this is definitely needed 

Now how do we restore the database as a service, delete the DBCS files in ASM do not drop database from the cluster **---- this is the trick **

Generate the following file list from ASM in DBCS

```
SQL> set heading off linesize 999 pagesize 0 feedback off trimspool on 
SQL> spool /tmp/files.lst
SQL> select 'asmcmd rm '||name from v$datafile
union all
select 'asmcmd rm '||name from v$tempfile union all
select 'asmcmd rm '||member from v$logfile; 
SQL> spool off
SQL> create pfile='/tmp/<standby DB_UNIQUE_NAME>.pfile' from spfile; #Backup spfile $chmod 777 /tmp/files.lst
```
I use the following steps to restore the ExaCS Backups into DBCS (VM DB System )

```
Database Restore Step 1) Delete all the ASM Files as in list /tmp/files.lst , do not drop the database from the Cluster.


Database Restore Step 2)  Use the DBCS spfile and only change whats minimum , in my case it's just the db_name 
In case you are any custom parameters please feel free to change it ,  My intention here is to ensure I stick to DBCS parameters as much as possible as my database should be DBCS compatible and should not carry over all the parameters from EXACS

In the DBCS it is important to set the following 

alter system set db_name=exacs scope=spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      dbcsxy
db_name                              string      dbcs			----> to be changed to exacs
db_unique_name                       string      dbcsxy_abc1xy



Database Restore Step 3) Restore Controlfile and database 

RMAN> RESTORE CONTROLFILE FROM "/backup_path/file_store/orcl-cfile_XXXXXXX.bkp";


---------- Run the Restore Script 

RMAN>

run
{
  ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl_L0_%U';
  ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/backup_path/file_store/orcl-L0-arch_%U.bkp';
  BACKUP ARCHIVELOG ALL FORMAT '/backup_path/file_store/orcl-L0-arch_%U.bkp';
  set newname for database to new;
  restore database;
  switch datafile all;
  recover database;
}


---------- Open database with reset logs
SQL> alter database opn resetlogs;


```

Now my database is open but how do I check my database is available as a service 


3) Check one of the OCI service features  - for.e.g. take an OCI Backup 
--------------------------------------------------------------------------

I will go back to my tried and tested ocidtab utility to quickly take an OCI backup of my Oracle database 

A quick reference to my tab utility in case you are new to it 
https://blog.pythian.com/the-ocidtab-a-solution-to-make-oci-cli-scripting-easier/

```
$ oci db backup create --database-id $DB_OCID --display-name 27May2022_Backup --profile DEV-PROFILE
{
  "data": {
    "availability-domain": "EtvR:my-region-1-AD-1",
    "compartment-id": "ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "database-edition": "ENTERPRISE_EDITION",
    "database-id": "ocid1.database.oc1.my-region-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "database-size-in-gbs": null,
    "display-name": "27May2022_Backup",
    "id": "ocid1.dbbackup.oc1.my-region-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "kms-key-id": null,
    "kms-key-version-id": null,
    "lifecycle-details": null,
    "lifecycle-state": "CREATING",    <<<<<<<------ THIS MEANS MY BACKUP IS RUNNING AND MY DATABASE IS RECOGNISED AS A DBCS SERVICE 
    "shape": "VM.Standard2.1",
    "time-ended": null,
    "time-started": "2022-05-27T11:59:19.785000+00:00",
    "type": "FULL",
    "vault-id": null,
    "version": "12.2.0.1.220118"
  },
  "etag": "abcdefgh",
  "opc-work-request-id": "ocid1.coreservicesworkrequest.oc1.my-region-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
}
```


You can  see the Database is recognised as an OCI service  by kicking off one of the OCI database utilities. In the similar manner OCI metrics can also be enabled for this database , I will skip the metrics part for another blog post.

**You can  see the Database is recognised as an OCI service  by kicking off one of the OCI database utilities.**
In the similar manner OCI metrics can also be enabled for this database , I will skip the metrics part for another blog post.

## 3) Patching DBCS via OCI Console 

If you have reached this far , you have done a marvelous job

NOTE : There is a breather in ExaCS that the Exadata Components are scheduled by OCI internally and the customer does not have to perform any action apart from monitoring the tools/ExaCS components patching , customers will be getting notifications about the patching periodically.

Dom0 - Priviliged access to ExaCS Components 
DomU - No Prviliged access to Oracle VM Components ,
( i.e. you cannot login to the management console of a DB Node in OCI )

The following links will provide a good refresher for ExaCS patching concepts in general

https://www.youtube.com/watch?v=SGZZzGr9X5E
https://docs.oracle.com/en/cloud/paas/exadata-cloud/csexa/patching.html
https://docs.oracle.com/cd/E35328_01/E35332/html/vmusg-ovm-intro.html#:~:text=Dom0%20is%20an%20abbreviation%20for,Oracle%20VM%20Server%20by%20dom0.

For OCI Patching I have a separate Blog post just to adress this naunces of OCI Patching , I always use OCI CLI for patching , the blog below addresses them, basically we can patch the DBCS refreshed above via OCI CLI 

https://blog.pythian.com/oci-database-cloud-service-patching-of-database-and-grid-infrastructure-using-oci-cli/

We would have achived two things with this 

1. Apps are are safely tested using OCI CLI Patching in Dev environments 
2. OCI Patching by itself is safely tested in Dev environments on the refreshed database using OCI CLI.

# #oci #ocidtab #oracle #patching #database #OracleCloudInfrastructure #Exadata #ExaCS