Technology

SSH connection from on-premise Linux or Unix host to Oracle cloud

Posted on by debasis maity

Problem

1.First I copied the private key generated by puttygen (The public key was provided during DB creation in oracle cloud).After that I tried to connect cloud database using below command:-

cd /home/oracle

chown oracle:oinstall course_priv.ppk

chmod 0600 course_priv.ppk

[oracle@gnssrv01 ~]$ ssh -i course_priv.ppk opc@144.21.83.108
Enter passphrase for key ‘course_priv.ppk’:
Enter passphrase for key ‘course_priv.ppk’:
Enter passphrase for key ‘course_priv.ppk’:
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

It was asking for passphrase which I selected NULL during creation of private key using puttygen. I was not able to login .

Solution:-

1.Let me create the private key in openssh format from import/export option of puttygen

2.Now I need to import the private key generated earlier using puttygen.

3.Now this will show your public key.

4.Please export the key using “Export OpenSSH key” option

Save this key as my_priv_key and transfer to on-premise host.

5.Now you will be able to login 

[root@gnssrv01 oracle]# chown oracle:oinstall my_priv_key
[root@gnssrv01 oracle]# chmod 0600 my_priv_key
[root@gnssrv01 oracle]# su – oracle
[oracle@gnssrv01 ~]$ ssh -v -i my_priv_key oracle@144.21.83.108   (The -v option is for debug.You can omit it)

TDE encryption setup for migrating on-premise backup to Oracle cloud using RMAN

Posted on by debasis maity
This note describe how we can configure TDE encryption for RMAN backup in on-premise database to migrate to Oracle cloud.

1.First I need to update sqlnet.ora with proper location where my wallet will reside to enable TDE encryption.

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
 ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
 (METHOD=FILE)
 (METHOD_DATA=
 (DIRECTORY=/var/opt/oracle/hdg/tde_wallet)
 )
 )

SQLNET.ENCRYPTION_SERVER = requested

SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256, AES256)

SQLNET.ENCRYPTION_CLIENT = requested

SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256, AES256)

2.Now I need to create the directory and restart the listener.

mkdir -p /var/opt/oracle/hdg/tde_wallet

3.Connect to the root container and create the keystore. Please restart database if you find nothing in v$encryption_wallet.

SQL> administer key management create keystore '/var/opt/oracle/hdg/tde_wallet' identified by manager123;

keystore altered.

SQL> administer key management set keystore open identified by manager123;

keystore altered.

SQL> administer key management set key identified by manager123 with backup using 'TDE';

keystore altered.
If you want to enable autologin,you can do below
SQL> administer KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM keystore '/u01/app/oracle/tde_wallet' identified by manager123;

keystore altered.
For special case,where you deleted the old keystore , you need to do following step
QL> administer key management create keystore '/u01/app/oracle/local/wallet_orcl' identified by manager123;

keystore altered.

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

no rows selected

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=TRUE SCOPE=MEMORY;

System altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY manager123;

keystore altered.

4.If you have PDB,you need to connect to PDB and check whether auto login is enabled from PDB

SQL>alter session set container=PDB1;

SQL> select * from v$encryption_wallet;

FILE
OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED
4

You can see OPEN_NO_MASTER_KEY which will create problem during backup after setting encryption.
SQL> alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf');
 alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf')
 *
 ERROR at line 1:
 ORA-28374: typed master key not found in wallet
Now please follow below step to resolve .This step is valid for 12.2:-
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY manager123 with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

FILE 
OPEN AUTOLOGIN SINGLE NO
 4

5.Convert non-encrypted tablespace to encrypted online in 12.2 to support TDE encryption.

SQL> alter tablespace system ENCRYPTION online using 'AES256' encrypt FILE_NAME_CONVERT = ('system01.dbf', 'system01_encrypt.dbf');

Tablespace altered.

Backup on-premise database and restore to Oracle cloud database

Posted on by debasis maity

My on-premise database hostname:-gnssrv01

Database name:-orcl12c

Cloud database hostname:-myclone

A.Backup of on-premise database to cloud container

Please create the empty container for holding backup of on-premise database using following link

https://clouddba.co/oracle-dbaas-1z0-160-part-24-backup-in-single-instance-oracle-cloud/

1.First I need to install backup module for cloud in on-premise database

You need to download opc.cert and opc_install.jar mentioned in above metalink note (Doc ID 2360941.1)
[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle-data-storage6-1 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'xxx' -walletDir /home/oracle/my_wallet -libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8"?><container name="oracle-data-storage6-1"><object><name>file_chunk/804514966/ORCL12C/backuppiece/2018-04-23/18t11tsh_1_1/KTVs8w9V0wwD/0000000001</name><hash>5752a8f0131e0f3d0c728f15a7ea71d6</hash><bytes>75235328</bytes><content_type>Application/Octet-Stream</content_type><last_modified>2018-04-23T03:09:28.687920</last_modified></object></container>
 Error: The specified OPC host name should not include container or object in the URL path.

In that case we will try not to provide the storage container name and try.Oracle will create a default container.

[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'Start123456$' -walletDir /home/oracle/my_wallet 
-libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8" standalone="yes"?><account name="Storage-62b84aec8de8478ba17a6e1f84c3475c"><container><name>oracle-data-storage6-1</name><count>1</count><bytes>20640</bytes><accountId><id>31714</id></accountId><deleteTimestamp>0.0</deleteTimestamp><containerId><id>7665965</id></containerId></container></account>
Oracle Database Cloud Backup Module credentials are valid.
Debug: Certificate Success: file = /home/oracle/opc.cert
Debug: Certificate Success:
 Subject : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
 Validity : Fri Nov 10 00:00:00 UTC 2006 - Mon Nov 10 00:00:00 UTC 2031
 Issuer : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
Oracle Database Cloud Backup Module wallet created in directory /home/oracle/my_wallet.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.
Debug: Temp zip file = /tmp/opc_linux646549103754721599993.zip
Debug: Downloaded 27314069 bytes in 14 seconds.
Debug: Transfer rate was 1951004 bytes/second.
Download complete.
Debug: Delete RC = true
Please note your default container is oracle-data-storage6-1.

2.Please encrypt all tablespace (Including PDB tablespaces)  using below note.

http://clouddba.co/tde-encryption-setup-for-migrating-on-premise-backup-to-oracle-cloud-using-rman/

3. Taking backup from on-premises to oracle cloud now.Please note that backup configuration file was created as a part of step 1.

[oracle@gnssrv01 dbs]$ cat $ORACLE_HOME/dbs/opcorcl12c.ora
OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun
OPC_WALLET=’LOCATION=file:/home/oracle/bkp_wallet CREDENTIAL_ALIAS=alias_opc’

RMAN script to execute:-
run{
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure default device type to sbt;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database ;
backup archivelog all;
backup current controlfile;
}

[oracle@gnssrv01 dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Apr 23 08:25:49 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (DBID=804514966)

RMAN> set encryption on;

executing command: SET encryption
using target database control file instead of recovery catalog

RMAN> run{
2> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS=’SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure default device type to sbt;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database plus archivelog;
backup current controlfile;
}3> 4> 5> 6> 7> 8> 9>

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

Starting backup at 23-APR-18
current log archived
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=276 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=250 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=41 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=270 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: SID=30 device type=SBT_TAPE
channel ORA_SBT_TAPE_5: Oracle Database Backup Service Library VER=12.2.0.1
channel ORA_SBT_TAPE_1: starting compressed archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=15 STAMP=974188502
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_2: starting compressed archived log backup set
channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=17 STAMP=974190408
channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: starting compressed archived log backup set
channel ORA_SBT_TAPE_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=16 STAMP=974188571
channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18
piece handle=1at11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:00:25
channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18
piece handle=19t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=18t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:05:05
Finished backup at 23-APR-18

Starting backup at 23-APR-18
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_1: starting compressed full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01_encrypt.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_2: starting compressed full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01_encrypt.dbf
channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: starting compressed full datafile backup set
channel ORA_SBT_TAPE_3: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl12c/orclpdb/sysaux01_encrypt.dbf
channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: starting compressed full datafile backup set
channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1ft11u65_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:05:15
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18
piece handle=1et11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:09:40
channel ORA_SBT_TAPE_4: starting compressed full datafile backup set
channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl12c/undotbs01.dbf_encrypt.dbf
channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1gt11ug0_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:04:26
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1it11uoa_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:01:25
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl12c/orclpdb/users01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18
piece handle=1ht11uo9_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:01:26
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1jt11uqv_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:00:25
channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18
piece handle=1ct11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:12:13
channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18
piece handle=1dt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:14:33
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=1bt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:38:04
Finished backup at 23-APR-18

Starting backup at 23-APR-18
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_1: starting compressed archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=18 STAMP=974193071
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=1kt120dg_1_1 tag=TAG20180423T091112 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
Finished backup at 23-APR-18

Starting backup at 23-APR-18
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_1: starting compressed full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=1lt120ej_1_1 tag=TAG20180423T091147 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 23-APR-18

Starting Control File and SPFILE Autobackup at 23-APR-18
piece handle=c-804514966-20180423-00 comment=API Version 2.0,MMS Version 12.2.0.1
Finished Control File and SPFILE Autobackup at 23-APR-18

4.Please note control file backup taken now which will be required to restore in cloud database.

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
45 Full 512.00K SBT_TAPE 00:00:08 23-APR-18
BP Key: 45 Status: AVAILABLE Compressed: YES Tag: TAG20180423T091147
Handle: 1lt120ej_1_1 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle
Control File Included: Ckp SCN: 2008170 Ckp time: 23-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
46 Full 18.00M SBT_TAPE 00:00:09 23-APR-18
BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20180423T091214
Handle: c-804514966-20180423-00 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle
Control File Included: Ckp SCN: 2008184 Ckp time: 23-APR-18

5.Please note list of archive logs in backup.These archive logs needs to be restored later .

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL12C
=====================================================================

Key Thrd Seq S Low Time
——- —- ——- – ———
15 1 16 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_16_fftjzrkr_.arc

16 1 17 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_17_fftk238x_.arc

17 1 18 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_18_fftlvjqy_.arc

18 1 19 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_19_fftogq8q_.arc

19 1 20 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_ffxojbfp_.arc

20 1 21 A 24-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_21_ffxpg2fn_.arc

B.Restoration of on-premise database backup to cloud Machine

1.Please copy the TDE wallet generated in on-premise in previous step to oracle cloud TDE wallet (/u01/app/oracle/admin/orcl12c/tde_wallet).This database will be cloned from existing on-premise backup stored in cloud container.

[oracle@myclone admin]$ cat sqlnet.ora
SQLNET.ENCRYPTION_SERVER = required

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)

SQLNET.CRYPTO_CHECKSUM_SERVER = required

ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/tde_wallet)))

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = FALSE

SQLNET.EXPIRE_TIME = 10

SSL_VERSION = 1.2

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/db_wallet)))

Please restart the database and check wallet status

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/u01/app/oracle/admin/orcl12c/tde_wallet/
OPEN AUTOLOGIN SINGLE NO
1

2.Please  copy opc wallet from on-premise database which we created using step 1 procedure (earlier part of this note) of this note.You can also create a new OPC file as well using step 1 procedure.

3.Please make a note or modify the opc configuration file.Please note I have explicitly mentioned OPC_CONTAINER if it is not able to identify from previous process.

[oracle@myclone dbs]$ cat opcorcl12c.ora
OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun
OPC_WALLET=’LOCATION=file:/home/oracle/my_wallet CREDENTIAL_ALIAS=alias_opc’ OPC_CONTAINER=’oracle-data-storage6-1′

4.Please restore the controlfile now.Please use the control file tag we identified during backup.

[oracle@myclone orcl12c]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 06:48:01 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size 8796624 bytes
Variable Size 704644656 bytes
Database Buffers 1979711488 bytes
Redo Buffers 75087872 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@myclone orcl12c]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 06:49:43 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (not mounted)

RMAN>set decryption identified by ‘manager123’; <—(If you do not use autologin TDE wallet,you need to execute this command for password based TDE wallet)

RMAN> set DBID=804514966;<---(This DBID is on-premise database DBID)
run{

executing command: SET DBID
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore controlfile from 'c-804514966-20180423-00';
}

RMAN> 2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=237 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=12.2.0.1

Starting restore at 24-APR-18

channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:03
output file name=/u02/app/oracle/oradata/orcl12c/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/orcl12c/control02.ctl
Finished restore at 24-APR-18
released channel: t1

RMAN>alter database mount;

5.Now we will restore the database.

run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore database;
}

RMAN> 2> 3> 4>

allocated channel: t1
channel t1: SID=22 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=12.2.0.1

Starting restore at 24-APR-18
Starting implicit crosscheck backup at 24-APR-18
Finished implicit crosscheck backup at 24-APR-18

Starting implicit crosscheck copy at 24-APR-18
Finished implicit crosscheck copy at 24-APR-18

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_8_ffxnqmvs_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_7_ffxlzxkz_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_22/o1_mf_1_6_ffrz86dj_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_1_ffpn8cd7_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_3_ffpn9jpr_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_2_ffpn969s_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_4_ffpnbm66_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_5_ffpnc7g3_.arc

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf
channel t1: reading from backup piece 1ft11u65_1_1
channel t1: piece handle=1ft11u65_1_1 tag=TAG20180423T083306
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

…….

channel t1: restore complete, elapsed time: 00:00:15
Finished restore at 24-APR-18
released channel: t1

6.Please restore the archive logs  from backup

[oracle@myclone orcl12c]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 07:32:18 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (DBID=804514966, not open)

RMAN> run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore archivelog from sequence 16 until sequence 21;
}

7.Recover the database now

[oracle@myclone orcl12c]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 07:33:55 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2007164 generated at 04/23/2018 08:33:07 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffxq
gvp1_.arc
ORA-00280: change 2007164 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2008148 generated at 04/23/2018 09:11:11 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_.
arc
ORA-00280: change 2008148 for thread 1 is in sequence #20
ORA-00278: log file
‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffx
qgvp1_.arc’ no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_
.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

7. Please open the database in resetlogs

SQL> alter database open resetlogs;

Database altered.

 

Oracle DBaaS 1z0-160 part 31 :- Oracle cloud database security

Posted on by debasis maity

Data security

In Oracle Database Cloud Service databases, data security is provided for data in transit and data at  rest.

Security of data in transit is achieved through network encryption. Data at rest security is achieved through encryption of data stored in

database data files and backups.Data in Oracle Database files, including backups, is secured by the use of encryption implemented through a key management framework. Security of data across the network is provided by native Oracle Net encryption and integrity capabilities.

Security of data at rest

Oracle Database Cloud Service uses Oracle Transparent Data Encryption to encrypt data in the database data files and in backups. Encrypted data is also protected in temporary tablespaces, undo segments, redo logs and during internal database operations such as JOIN and SORT.

Oracle Transparent Data Encryption (TDE) includes a keystore (referred to as a wallet in Oracle Database 11g and previous releases) to securely store master encryption keys, and a management framework to securely and efficiently manage the keystore and perform key maintenance operations.

TDE is the underlying mechanism used for default tablespace encryption and encrypted backups. It uses a two-tiered, key-based architecture to transparently encrypt and decrypt data. The master encryption key is stored in the software keystore. For tablespace encryption, this master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace. By default in Oracle Database Cloud Service When a database deployment is created on Oracle Database Cloud Service, a local auto-login software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.

The keystore location is specified in the ENCRYPTION_WALLET_LOCATION parameter in

the $ORACLE_HOME/network/admin/sqlnet.ora file.

The Oracle keystore stores a history of retired TDE master encryption keys, which enables you to change them and still be able to decrypt data that was encrypted under an earlier TDE master encryption key.

By default, backups to Cloud Storage for Enterprise Edition databases are encrypted. Recovery Manager (RMAN) performs transparent encryption using the auto-login software keystore.

Please refer below note for more examples:-

https://clouddba.co/tde-encryption-setup-for-migrating-on-premise-backup-to-oracle-cloud-using-rman/

Tablespace Encryption

By default, all new tablespaces that you create in a Database Cloud Service database are encrypted.

However, not all of the tablespaces created when you create a database deployment are encrypted:

  • In an Oracle Database 11g database, none of the tablespaces created when you create a database deployment are encrypted.
  • In an Oracle Database 12c Release 1 database, none of the tablespaces created when you create a database deployment are encrypted. This includes the tablespaces in the root (CDB$ROOT), the seed (PDB$SEED), and the PDB created when

you create a database deployment.

  • In an Oracle Database 12c Release 2 or later database, only the USERS tablespaces created when you create a database deployment are encrypted. None of the other tablespaces are encrypted. This includes the tablespaces in the root (CDB$ROOT), the seed (PDB$SEED), and the PDB created when you create a database deployment.

User-created tablespaces are encrypted by default.

By default, any new tablespaces created by using the SQL CREATE TABLESPACE command are encrypted with the AES128 encryption algorithm. You do not need to include the USING ‘encrypt_algorithm’ clause to use the default encryption.

You can specify another supported algorithm by including the USING ‘encrypt_algorithm’ clause in the CREATE TABLESPACE command. Supported algorithms for Oracle Database 11g and later are AES256, AES192, AES128, and 3DES168.

Managing Tablespace Encryption

You can manage the software keystore (known as an Oracle wallet in Oracle Database 11g), the master encryption key, and control whether encryption is enabled by default.

Managing the Software Keystore and Master Encryption Key Tablespace encryption uses a two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The master encryption key is stored in an external security module (software keystore). This master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

When the database deployment is created on Database Cloud Service, a local autologin software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.

You can change (rotate) the master encryption key by using the tde rotate masterkey subcommand of the dbaascli utility. When you execute this subcommand you will be prompted for the keystore password. Enter the password specified during the database deployment creation process. For example:

DBAAS>tde rotate masterkey

Controlling Default Tablespace Encryption

The ENCRYPT_NEW_TABLESPACES initialization parameter controls default encryption of new tablespaces. In Database Cloud Service databases, this parameter is set to CLOUD_ONLY by default. See Viewing and Modifying Initialization Parameters for additional information.With Oracle Database 12c Release 2 (12.2) or later releases on Database Cloud Service, you can no longer create a new unencrypted tablespace. An error message is returned if you set ENCRYPT_NEW_TABLESPACES to DDL and issue a CREATE TABLESPACE command without specifying an ENCRYPTION clause.

 

Value

 Description
Always During creation, tablespaces are transparently encrypted with the AES128 algorithm unless a different algorithm is specified in the ENCRYPTION clause.
CLOUD_ONLY Tablespaces created in a Database Cloud Service database are transparently encrypted with the AES128 algorithm unless a different algorithm is specified in the ENCRYPTION clause. For noncloud databases, tablespaces are only encrypted if the ENCRYPTION clause is specified. This is the default value.
DDL During creation, tablespaces are not transparently encrypted by default, and are only encrypted if the ENCRYPTION clause is specified.

 

Security of Data in Transit

To secure connections to your Oracle Database Cloud Service databases, you can use native Oracle Net encryption and integrity capabilities.Encryption of network data provides data privacy so that unauthorized parties are not able to view data as it passes over the network. In addition, integrity algorithms protect against data modification and illegitimate replay.

Oracle Database provides the Advanced Encryption Standard (AES), DES, 3DES, and RC4 symmetric cryptosystems for protecting the confidentiality of Oracle Net traffic. It also provides a keyed, sequenced implementation of the Message Digest 5 (MD5) algorithm or the Secure Hash Algorithm (SHA-1 and SHA-2) to protect against integrity attacks.

By default, database deployments on Database Cloud Service are configured to enable native Oracle Net encryption and integrity. Also, by default, Oracle Net clients are configured to enable native encryption and integrity when they connect to an appropriately configured server. If your Oracle Net client is configured to explicitly reject the use of native encryption and integrity then connection attempts will fail.

Checking your Database Cloud Service environment

The following procedure outlines the basic steps required to confirm that native Oracle Net encryption and integrity are enabled in your Database Cloud Service environment.

  1. In a command shell, connect to the compute node as the oracle user.
  2. Change directories to the location of the sqlnet.ora Oracle Net configuration file. For example:

$ cd $ORACLE_HOME/network/admin

$ ls sqlnet.ora

sqlnet.ora

  1. View the sqlnet.ora file and confirm that it contains the following parameter settings:

SQLNET.ENCRYPTION_SERVER = required

SQLNET.CRYPTO_CHECKSUM_SERVER = required

The required setting enables the encryption or integrity service and disallows the connection if the client side is not enabled for the security service. This is the default setting for database deployments on Database Cloud Service.

Checking your Oracle Net Client Configuration

The following procedure outlines the basic steps required to confirm that native encryption and integrity are enabled in your Oracle Net client configuration.

  1. In a command shell, connect to the Oracle Net client.
  2. Change directories to the location of the Oracle Net configuration files

tnsnames.ora and sqlnet.ora, for example:

$ cd $ORACLE_HOME/network/admin

sqlnet.ora tnsnames.ora

  1. View the sqlnet.ora file and confirm that it does not contain the following

parameter settings:

SQLNET.ENCRYPTION_CLIENT = rejected

SQLNET.CRYPTO_CHECKSUM_CLIENT = rejected

The rejected setting explicitly disables the encryption or integrity service, even if the server requires it. When a client with an encryption or integrity service setting of rejected connects to a server with the required setting, the connection fails with the following error: ORA-12660: Encryption or crypto-checksumming parameters incompatible.

Because native Oracle Net encryption and integrity are enabled in your Database Cloud Service environment by default, any parameter setting other than rejected,or no setting at all, would result in the use of native encryption and integrity.

Verifying the use of Native Encryption and Integrity

You can verify the use of native Oracle Net encryption and integrity by connecting to your Oracle database and examining the network service banner entries associated with each connection. This information is contained in the NETWORK_SERVICE_BANNER column of the V$SESSION_CONNECT_INFO view. The following example shows the SQL command used to display the network service banner entries associated with current connection:

SQL> select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat);

The following example output shows banner information for the available encryption service and the crypto-checksumming (integrity) service, including the algorithms in use:

NETWORK_SERVICE_BANNER

————————————————————————————-

TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 – Production
Encryption service for Linux: Version 12.1.0.2.0 – Production
AES256 Encryption service adapter for Linux: Version 12.1.0.2.0 – Production
Crypto-checksumming service for Linux: Version 12.1.0.2.0 – Production
SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 – Production

Verifying the use of Native Encryption and Integrity

If native Oracle Net encryption and integrity was not in use, the banner entries would still include entries for the available security services; that is, the services linked into the Oracle Database software. However, there would be no entries indicating the specific algorithms in use for the connection. The following output shows an example:

NETWORK_SERVICE_BANNER
————————————————————————————-
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 – Production
Encryption service for Linux: Version 12.1.0.2.0 – Production
Crypto-checksumming service for Linux: Version 12.1.0.2.0 – Production

 

Oracle DBaaS 1z0-160 part 29 :- DBaaS monitor in Oracle cloud

Posted on by debasis maity

How to access DBaaS monitor from console

Please open port 443 (https) using “Access Rules”

Please enable oar_p2_httpssl

Now the port ora_p2_httpssl open now

If you do not want to enable port 443 from console,you need to enable port forwarding.

If you enable port forwarding,you need to open using localhost after enable putty session.

When prompted for a user name and password, enter dbaas_monitor as the user name and the password specified during the database deployment creation process, and then click OK

After logging to DBaaS monitor,You can see below

Using Oracle DBaaS Monitor

Oracle DBaaS Monitor provides monitoring and management of the Oracle database and listener on Oracle Database Cloud Service.

Note:

This section does not apply to database deployments that use Oracle Real Application Clusters. Such deployments do not currently include Oracle DBaaS Monitor.

The following are key things you can do using DBAAS Monitor

  • Administering the Listener
  • Starting and Stopping the Database Instance
  • Viewing and Modifying Initialization Parameters
  • Viewing User Account and Expiring Password Information
  • Viewing Tablespace and Segment Space Usage
  • Changing the TDE Keystore Password
  • Viewing Alert Log Entries and Checking for Errors
  • Viewing Real Time SQL Monitor
  • Administering Pluggable Databases.

Oracle DBaaS Monitor provides monitoring and management of the Oracle database and listener on Oracle Database Cloud Service.

DBaaS Monitor provides quick and easy access to a variety of information about the database instance running on a database deployment:

  • Overall, how much storage is allocated to tablespaces, and how much of that storage is used.
  • For each tablespace, how much storage is allocated and how much of that storage is used, with additional drill-down capabilities to view segments.
  • A real-time graph showing wait events across several selectable categories.
  • The alert log, with log searching capabilities.
  • A list of open user sessions, with drill-down capabilities to view session details such as the last SQL statement, explain plan, waits, contention, and so on.
  • A list of initialization parameters, with the ability to change parameter values, both in memory and in the SPFILE.
  • Indication of whether certain database options are enabled.
  • Monitoring of current and past SQL Developer PDB uploads.
  • A list of the SQL statements that are being monitored in the database, with real time display of details such as the status, duration, degree of parallelism, and so on.

You can use DBaaS Monitor to view information about the compute node:

  • CPU utilization information in an interactive table format, with automatic refresh intervals
  • OS process information, with filtering and automatic refresh capabilities DBaaS Monitor also provides the following management capabilities:
  • Start up and shut down the database instance.
  • Open and close a pluggable database.
  • Create and drop a pluggable database.
  • Plug in and unplug a pluggable database.
  • Clone a pluggable database.
  • Start and stop the listener.

There are various menus by which you can administer Database.

You can check status of listener and turn off if required

You may also stop/start database using this menu of DBaaS monitor

Please keep in my mind following notes when you shutdown database using DBaaS monitor

You can use DBaaS Monitor to shut down the database instance in IMMEDIATE mode. In this mode, no new connections are allowed. No new transactions are allowed to be started and any uncommitted transactions are rolled back.If you need to shut down the database instance in any other mode (ABORT,NORMAL, or TRANSACTIONAL), use SQL*Plus instead of DBaaS Monitor.

Oracle DBaaS 1z0-160 part 28:- Backup and recovery in RAC in oracle cloud

Posted on by debasis maity

Backup and Recovery from DB Console

You may face problem in taking backup to oracle container for oracle cloud RAC. Please follow below link to troubleshoot.

https://clouddba.co/kbhs-00712-ora-29024-received-from-local-http-service-during-backup-in-rac-oracle-cloud/

Once you have resolved the issue,You can proceed with backup

Please proceed to take backup

Backup log location

You can find backup of RMAN log for oracle cloud in following location:-

[oracle@pocracdemo1 ~]$ cd /home/oracle/bkup/logs/rman/

Please note that actually RMAN commands are being executed from backend
Recovery Manager: Release 12.2.0.1.0 – Production on Fri Apr 13 07:50:38 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1500943058)
RMAN> set command id to “Backup_-8270960425676936486”;
2> list db_unique_name all;
3> list incarnation of database;
4> show all;
5> report schema;
6> set encryption on;
7> backup device type sbt backupset all;
8> backup device type sbt datafilecopy all;
9> delete force noprompt obsolete recovery window of 7 days device type disk;

Now you can see your backup successfully executed

Updating password by using RACCLI utility

1.Connect to the compute node as the opc user.Update the password:

2.$ raccli update backupconfig -params ‘{“cloudStorageUser”:”user-name“,”cloudStoragePwd”:”new-password“}’

where user-name is the user name of the Oracle Cloud user whose credentials are used to access the Storage Cloud Service container and new-password is this user’s new password.

If you have used the update rdk subcommand of the raccli utility to update the cloud tooling to 16.4.5 or later, you must manually update the opc installer for the Oracle Database Cloud Backup Module before you use the update backupconfig subcommand. For instructions, see in Updating the cloud tooling on a deployment hosting Oracle RAC requires manual update of the Oracle Database Cloud Backup Module in Known Issues for Oracle Database Cloud Service.

Customizing with files in RAC needs to be backed up

Customizing Which System Files Are Backed Up

1.Connect as the opc user to the compute node.

2.Edit the contents of the /opt/oracle/dcs/rdbaas/config/oscfg.spec file.

3.The backup feature provided by Oracle Database Cloud Service backs up the files and folders listed in this specification file.

Customizing Which Database Configuration Files Are Backed Up

1.Connect as the opc user to the compute node.

2.Edit the contents of the /opt/oracle/dcs/rdbaas/config/dbcfg.spec file.

3.The backup feature provided by Oracle Database Cloud Service backs up the files and folders listed in this specification file.

Customizing Which Grid Infrastructure Configuration Files Are Backed Up

1.Connect as the opc user to the compute node.

2.Edit the contents of the /opt/oracle/dcs/rdbaas/config/gicfg.spec file.

3.The backup feature provided by Oracle Database Cloud Service backs up the files and folders listed in this specification file.

RACCLI command to update disk recovery window

[opc@pocracdemo1 ~]$ raccli update backupconfig -params '{"diskRecoveryWindow" : 3}'
{
  “jobId” : “22”,
  “requestStatus” : “SUCCESS”
}

[opc@pocracdemo1 ~]$ raccli describe job 22

{
  “requestStatus” : “SUCCESS”,
  “jobStatus” : “RUNNING”,
  “response” : [ ]
}

where days is the number of days for which you want to retain backups.

Changing recovery window in cloud storage

[opc@pocracdemo1 ~]$ raccli update backupconfig -params '{"ossRecoveryWindow" : 15}'
{
  “jobId” : “23”,
  “requestStatus” : “SUCCESS”
}

where days is the number of days for which you want to retain backups.

Please check now that retention policy got changed.

[oracle@pocracdemo1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Apr 13 08:12:55 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1500943058)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;

Customizing time of automatic daily backup

1.Connect as the opc user to the compute node.

2.Enter this raccli command:

     $ raccli update backupconfig -params '{"cronDate" : "time"}'

 where time is the time (using 24-hour, HH:MM format) when daily backups are to occur. For example, "02:45" is 2:45 AM, and "14:45" is 2:45 PM.

Changing backup configuration for RAC cloud storage

1.Connect as the opc user to compute node 1.

2.Use the raccli update backupconfig command to update the configuration.

3.To change the backup destination to Both Cloud Storage and Local Storage, enter the following command. Line breaks have been added for clarity; you must enter the command on a single line.

$ raccli update backupconfig -params '{"diskEnabled" : true, "ossEnabled" : true, 
  "cloudStorageUser" : "username", "cloudStoragePwd" : "password", 
  "cloudStorageContainerUrl" : "container-URL"}'

where:

  • username is the user name of an Oracle Cloud user who has read/write access to the container.
  • password is the password of the user specified in cloudStorageUser.
  • container-URL is the URL of the Oracle Storage Cloud container.
Example:-raccli update backupconfig -params '{"diskEnabled" : true, "ossEnabled" : true, "cloudStorageUser" : "sadhuarun1980@gmail.com", "cloudStoragePwd" : "xxx", "cloudStorageContainerUrl" : "https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/racbackup"}'

Restore latest backup in RAC

[opc@pocracdemo1 ~]$ raccli create recovery -latest

{
  “jobId” : “24”,
  “joburi” : “http://localhost:7070/dcs/24/responses”,
  “requestStatus” : “SUCCESS”
}

[opc@pocracdemo1 ~]$ raccli describe job 24

{
  “requestStatus” : “SUCCESS”,
  “jobStatus” : “RUNNING”,
  “response” : [ {
    “startTime” : “Fri Apr 13 08:25:58 UTC 2018”,
    “status” : “RUNNING”,
    “taskId” : “TaskZJsonRpcExt_5749”,
    “taskName” : “Database Recovery”
  } ]

}

Now once it successfully restore,You can describe the job again

[opc@pocracdemo1 ~]$ raccli describe job 24

{
  “requestStatus” : “SUCCESS”,
  “jobStatus” : “SUCCESS”,
  “response” : [ {
    “startTime” : “Fri Apr 13 08:25:58 UTC 2018”,
    “endTime” : “Fri Apr 13 08:32:44 UTC 2018”,
    “status” : “SUCCESS”,
    “taskId” : “TaskZJsonRpcExt_5749”,
    “taskResult” : “Resource { id: 104049597281494, name: rdbaas_recovery_database, type: RecoveryComponent }”,
    “taskName” : “Database Recovery”
  }, {
    “startTime” : “Fri Apr 13 08:32:44 UTC 2018”,
    “endTime” : “Fri Apr 13 08:32:44 UTC 2018”,
    “status” : “SUCCESS”,
    “taskId” : “TaskZJsonRpcExt_5751”,
    “taskResult” : “Resource { id: 104456071050168, name: null, type: Recovery }”,
    “taskName” : “Persist Recovery Metadata”
  } ]

}

Restoring to specific point in time using RACCLI

[opc@pocracdemo1 ~]$ raccli create recovery -pitr -timestamp '04/13/2018 08:30:30'
{
  “jobId” : “25”,
  “joburi” : “http://localhost:7070/dcs/25/responses”,
  “requestStatus” : “SUCCESS”
}

cd /home/oracle/bkup/logs/rman

RMAN> list db_unique_name all;
2> list incarnation of database;
3> show all;
4> report schema;
5> run { set until time =”to_date(’04/13/2018 08:30:30′,’mm/dd/yyyy hh24:mi:ss’)”; restore database; recover database; }
6> alter database open resetlogs;
7> shutdown immediate;
8>

You can also recover using DB console

 

Oracle DBaaS 1z0-160 part 27 :-Updating cloud tooling using raccli

Posted on by debasis maity

1.Connect as the opc user to the compute node.

2.Execute the raccli update rdk subcommand:

$ raccli update rdk -tag tag-number

where tag-number is the version of tooling you want to update to, without the dots in the version number. For example, to update to 17.2.5 tooling you would enter 1725.

If you are updating the cloud tooling on a database deployment that also uses Oracle Data Guard, you must also execute the update databasepassword command to store the password in the keystore (wallet) if you are updating from release 17.2.1 or earlier.

or

3.You can update cloud tool for RAC using below command.

[opc@pocracdemo1 ~]$ raccli update rdk -tag latest
{
 “jobId” : “41”,
 “requestStatus” : “SUCCESS”
}

Oracle DBaaS 1z0-160 part 26 :- Install RAC from console in oracle database cloud

Posted on by debasis maity

Please select create instance after selecting “Database” option

Please select one of following options(I choose custom)

Please select the option to create RAC

Please provide SSH public key or create new key

SSH key will be generated now

Please provide other relevant information

The instance will be now creating and will take 2-3 hours

After installation you will get following screen

RAC characteristics

Database file storage: for each Oracle RAC database, storage for database data files, the fast recovery area, and the redo logs is created and managed using Oracle Automatic Storage Management (ASM) and Oracle Automatic Storage Management Cluster File System (ACFS) instead of Linux LVM.

Included software: Oracle Application Express, Oracle REST Data Services, and Oracle DBaaS Monitor are not currently included.

The following is storage distribution for this installation we carried in previous slide.

Oracle DBaaS 1z0-160 part 25 :- Recover database in oracle database cloud

Posted on by debasis maity

1.Recover database from console

2.Please select among below options

3.Please check the recovery progress now

4.Point in time recovery using PITR

[root@MYTST ORCL]#  dbaascli orec –args -list

[root@MYTST ORCL]#  dbaascli orec –args -pitr TAG20180409T051640

DBAAS CLI version 1.0.0
Executing command orec –args -pitr TAG20180409T051640
–args : -pitr TAG20180409T051640
OREC version: 16.0.0.0
Starting OREC
Logfile is /var/opt/oracle/log/ORCL/orec/orec_2018-04-09_12:39:36.log
Config file is /var/opt/oracle/orec/orec.cfg
DB name: ORCL
OREC:: RUNNING IN NON DATAGUARD ENVIRONMENT
OREC:: Catalog mode:  Disabled
OREC:: Checking prerequirements before recovery process.
OREC:: DB Status : OPEN
OREC:: Changing instance to MOUNT stage.
OREC:: Shutting down the database… Completed.
OREC:: (RMAN) Startup MOUNT… Completed.
OREC:: PITR is within current incarnation 3
INFO : DB instance is up and running after recovery procedure.

OREC:: PITR Completed.

5.Point in time recovery using SCN

# dbaascli orec –args -scn SCN

where SCN is the system change number (SCN) for the end point of the recovery.

The restore and recover process performs these steps:

  • Shuts down the database
  • Extracts and restores configuration files
  • Prepares for recovery
  • Performs the recovery
  • Restarts the database instance after recovery.

6.In data guard configuration,you need to do following

1.Start a root-user command shell:

2.$ sudo -s
#

3.Run the duplicate option of orec.

4.# dbaascli orec –args -duplicate

5.Exit the root-user command shell and disconnect from the compute node:

6.# exit
$ exit

7.Switching backup from the None destination

8.Configuring TDE wallet and backup configuration file

[opc@MyCLONE ~]$ sudo su –

[root@MyCLONE ~]# /var/opt/oracle/dbaascli/dbaascli tde config –ks_login auto

DBAAS CLI version 1.0.0
Executing command tde config –ks_login auto

[root@MyCLONE ~]# cd /var/opt/oracle/ocde/assistants/bkup/

[root@MyCLONE bkup]# ls bkup*.cfg

bkup.cfg

[root@MyCLONE bkup]# mv bkup.cfg bkup_bak.cfg

[root@MyCLONE bkup]# vi bkup.cfg

9.Changing parameter of backup configuration file

# The bkup.cfg file will hold all the config info for BKUP.This configuration for both cloud storage and local disk

bkup_cfg_files=yes
bkup_disk=yes
bkup_disk_recovery_window=7
bkup_oss=yes
bkup_oss_url=https://surcloudworld.au.storage.oraclecloud.com/v1/Storage-surcloudworld/MYCLONE
bkup_oss_user=rsurajit@rediffmail.com
bkup_oss_passwd=Test#123
bkup_oss_recovery_window=30

For the Cloud Storage Only destination, enter:

# The bkup.cfg file will hold all the config info for BKUP

bkup_cfg_files=yes
bkup_disk=no
bkup_oss=yes
bkup_oss_url=https://surcloudworld.au.storage.oraclecloud.com/v1/Storage-surcloudworld/MYCLONE
bkup_oss_user=rsurajit@rediffmail.com
bkup_oss_passwd=Test#123
bkup_oss_recovery_window=oss-days

10.Run the backup assistant with the bkup.cfg file

[root@MyCLONE bkup]# chown root bkup.cfg

[root@MyCLONE bkup]# chmod 0600 bkup.cfg

[root@MyCLONE bkup]# ./bkup -cfg bkup.cfg

using file : /var/opt/oracle/log/ORCL/bkup/temp.sh
Configuring Backup to disk
Common RMAN Config
Instantiating obkup
Instantiating dbcfg.spec
Configuring backup of Config File
Updating Control File Record Keep Time
Enabling block change tracking
Updating RMAN defaults
Adding entry to crontab
INFO: Archivelog management enabled.
Adding entry to crontab
Accessing to your Database ID ..
The DBaaS instance database id is: 1500760727

#### Completed Execution.

11.Details about parameters used for backup configuration

1.disk-days is the number of days for which backups and archived redo logs are maintained on local storage. The interval always ends with the current time and extends back in time for the specified number of days.

2.oss-url is the REST endpoint of the cloud storage container.

3.username is the username for accessing the cloud storage container.

4.password is the password for accessing the cloud storage container.

5.oss-days is the number of days for which backups and archived redo logs are maintained on cloud storage. The interval always ends with the current time and extends back in time for the specified number of days.

 12.Create database from existing backup

Please select “create instance from existing backup”

Now you get confirmation screen and proceed further