CBD and PDB (Logging into CDB and PDBs)

What is CDB?
CDB is short for container database, container database in 12c is similar to similar to normal 11g DB having all the files like controlfile, redolog file,temp files, undo and datafiles. It contains the meta data of those objects which may be owned by portable DB with in that CDB and are visible to all PDBS depending about the right and privileges.
What are PDBs?
PDB is short for portable databases, and it contains all those information related to itself, there is no controlfile, redolog files specifically for the pdbs but it have its own datafiles and tempfiles containg data about the PDB, from 12.2 onwards it has its undo file.

Checking the BG process for a CDB:

The background process for CDB can be checked at os level:

$ps -ef|grep cdb_name

if we grep the CBD name, command result no BG processs, it means the DB services are not yet started.

Starting up the DB 

$ sqlplus / as sysdba

sql> startup;

Once the DBs are up, lets check the services:

Checking the Name and its status whether it CBD or Conventional DB:

Viewing the PDB Name:

Status of PDBS:
By default when you retart the CDB, the default status of PDB will be mount. i.e

we have to issue below command to convert PDB from mount to read write:

alter pluggable database pdb1 open;

Now checking the status, now its ready to use and operations can be performed on it:

PDB can be connected after creating service (already discussed my earlier blog), show as:

Block Corruption Recovery / Corrupted Block Recovery

Recovering Corrupted Blocks

Blocks corruption is very rare in Oracle DBMS, but when it occurs it is very dangerous and can result in DB crash or lost of blocks or datafile, but if you have active complete backup and archive logs, it can be easily recovered/repaired.

In this blog, i'll test by corrupting one of my TEST db datafile block and than will try to repair that block online without impacting the DB.

Let's have a fun:


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

• We can check, if there is any corrupted block currently in DB:

SQL> select * from v$database_block_corruption;

no rows selected

SQL>

• Now i'll corrupt one of datafiles block. i.e

we will check the datafile location by:

SQL> select file_name,file_id from dba_data_files;

FILE_NAME                                     FILE_ID
-------------------                             ----------

/u01/app/oracle/oradata/DB11G/users01.dbf         4
       

/u01/app/oracle/oradata/DB11G/undotbs01.dbf 3


/u01/app/oracle/oradata/DB11G/sysaux01.dbf 2
       
Now we will corrupt the datafile by adding below line

• I'll run below line at OS level to corrupt an example datafile

dd ibs=8192 seek=100 count=110 if=/dev/zero of=example01.dbf conv=notrunc

i.e

[oracle@linuxtest ~]$ cd /u01/app/oracle/oradata/DB11G/
[oracle@linuxtest DB11G]$
[oracle@linuxtest DB11G]$ pwd
/u01/app/oracle/oradata/DB11G
[oracle@linuxtest DB11G]$ ls -ltr
total 2893212
-rw-r----- 1 oracle oinstall 943726592 Apr  4 02:54 system01.dbf~
-rw-r----- 1 oracle oinstall  52429312 Apr 10 14:38 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Apr 10 14:38 redo01.log
-rw-r----- 1 oracle oinstall  20979712 Apr 10 14:39 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Apr 10 14:49 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr 10 14:52 example01.dbf
-rw-r----- 1 oracle oinstall  62922752 Apr 10 15:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 943726592 Apr 10 15:06 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Apr 10 15:06 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr 10 15:06 redo03.log
-rw-r----- 1 oracle oinstall   9748480 Apr 10 15:06 control01.ctl

[oracle@linuxtest DB11G]$ dd ibs=8192 seek=100 count=110 if=/dev/zero of=example01.dbf conv=notrunc  110+0 records in
1760+0 records out
901120 bytes (901 kB) copied, 0.00209488 seconds, 430 MB/s

• Let's check the V$database_block_corruption

SQL> select * from v$database_block_corruption;

no rows selected

SQL>

•  As there are no row in validated yet, But we have error reported in alert log file. i.e

Corrupt block relative dba: 0x0140006c (file 5, block 108)
Completely zero block found during validation
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Hex dump of (file 5, block 109) in trace file /u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_11146.trc

• It can be validated as below:

RMAN> backup validate database;

Starting backup at 10-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11G/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11G/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11G/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11G/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11G/undotbs01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12442        115209          998654
  File Name: /u01/app/oracle/oradata/DB11G/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              58764
  Index      0              12304
  Other      0              31690

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              18066        64042           998653
  File Name: /u01/app/oracle/oradata/DB11G/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              12179
  Index      0              8677
  Other      0              25078

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              385          7680            998654
  File Name: /u01/app/oracle/oradata/DB11G/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              7295

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              25210        25600           888027
  File Name: /u01/app/oracle/oradata/DB11G/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              93
  Index      0              39
  Other      0              258

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1738         12803           775084
  File Name: /u01/app/oracle/oradata/DB11G/example01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4410
  Index      0              1262
  Other      111            5390

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_11146.trc for details
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              594
Finished backup at 10-APR-18

RMAN>

• Now Checking the V$database_block_corruption view

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        116          1                  0 FRACTURED
         5          7        109                  0 ALL ZERO
         5          6          1                  0 FRACTURED

SQL>

Now it is showing the file number and block number which is corrupted, it can be repaired if we have a valid backup and archivelogs are also available:

• It can be as simple as this, by issuing single command:

RMAN> blockrecover corruption list;

Starting recover at 10-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11G/backupset/2018_04_04/o1_mf_nnndf_TAG20180404T223416_fdbb5rl0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11G/backupset/2018_04_04/o1_mf_nnndf_TAG20180404T223416_fdbb5rl0_.bkp tag=TAG20180404T223416
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 10-APR-18

RMAN>

• lets check the v$database_block_corruption view again and alert log file as well:

1. Checking View

SQL> select * from v$database_block_corruption;

no rows selected

SQL>

      2.Checking Alert log

alter database recover datafile list clear
Completed: alter database recover datafile list clear
Started Block Media Recovery
Recovery of Online Redo Log: Thread 1 Group 1 Seq 19 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DB11G/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 20 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DB11G/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 21 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DB11G/redo03.log
Completed Block Media Recovery

So we have successfully performed recovery of our datafile without impacting database.

Connecting to The Portable Database (Container DB/ PDBs) in 12c,18c

Connecting to Container Database:

As with introduction of Portable databases, during installation you are given option with simple installation of the version  or you can opt for Container Database, once you opt for container DB option, it will ask for portable database name:

Once installation/creation of database is completed, login to root container is as simple we login to normal database. i.e

[oracle@18ctestdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue Mar 13 00:55:06 2018

Version 18.1.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

SQL>

SQL> sho user

USER is "SYS"

SQL> sho con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

But Logging in to PDBS is not that simple,  as PDBs will be accessible through some create net service:

Let have a look at tnsnames.ora file:

No Service is created against Portable container database, so we should create the service through netca:

Since service is created now. lets check the tnsnames.ora file :

As now we have services configured for PDB let access the PDB through that service:

If you don't have service configured we can access the container by set container clause:

Oracle 18c Installation (binaries and Database) on Linux (not an official release, only for Testing purpose)

World's 1st autonomous Database. The 18c

18c database eliminates the complexity, human error and manual efforts. Still official release to come for DBs on Linux server, they have released version for exadata oracle public cloud (on trial version as well).

I've tried to installed it on linux with some parameter changes on Linux:

Setting environment for Oracle software and Databases (RPMS)

Installing RPMS and setting IPs:

Making host file entry, specifying ip in host file:

 vi /etc/hosts

127.0.0.1       localhost localhost.localdomain

192.168.1.12   18ctestdb    18ctestdb

[root@18cTestdb ~]#

Add the following lines to the "/etc/sysctl.conf" file, or in a file called

"/etc/sysctl.d/98-oracle.conf".

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

than in order to be activate/to take effects now run below command

/sbin/systcl -p

Add the following lines to a file called "/etc/security/limits.d/oracle-database-server-

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

Installing the RPM available at Package directory of ISO image:

cd /media/rhel5/package

rpm -Uvh --force --nodeps gcc-4.4.7-3.el6.x86_64.rpm

 rpm -Uvh --force --nodeps gcc-c++-4.4.7-3.el6.x86_64.rpm

 rpm -Uvh --force --nodeps binutils*

 rpm -Uvh --force --nodeps compat-libcap1*

 rpm -Uvh --force --nodeps compat-libstdc++-33*

 rpm -Uvh --force --nodeps compat-libstdc++-33.i686*

 rpm -Uvh --force --nodeps glibc*

 rpm -Uvh --force --nodeps glibc.i686*

 rpm -Uvh --force --nodeps glibc-devel*

 rpm -Uvh --force --nodeps glibc-devel.i686*

 rpm -Uvh --force --nodeps ksh*

 rpm -Uvh --force --nodeps libaio*

 rpm -Uvh --force --nodeps libaio.i686*

 rpm -Uvh --force --nodeps libaio-devel*

 rpm -Uvh --force --nodeps libaio-devel.i686*

 rpm -Uvh --force --nodeps libX11*

 rpm -Uvh --force --nodeps libX11.i686*

 rpm -Uvh --force --nodeps libXau*

 rpm -Uvh --force --nodeps libXau.i686*

 rpm -Uvh --force --nodeps libXi*

 rpm -Uvh --force --nodeps libXi.i686*

 rpm -Uvh --force --nodeps libXtst*

 rpm -Uvh --force --nodeps libXtst.i686*

 rpm -Uvh --force --nodeps libgcc*

 rpm -Uvh --force --nodeps libgcc.i686*

 rpm -Uvh --force --nodeps libstdc++*

 rpm -Uvh --force --nodeps libstdc++.i686*

 rpm -Uvh --force --nodeps libstdc++-devel*

 rpm -Uvh --force --nodeps libstdc++-devel.i686*

 rpm -Uvh --force --nodeps libxcb*

 rpm -Uvh --force --nodeps libxcb.i686*

 rpm -Uvh --force --nodeps make*

 rpm -Uvh --force --nodeps nfs-utils*

 rpm -Uvh --force --nodeps net-tools*

 rpm -Uvh --force --nodeps smartmontools*

 rpm -Uvh --force --nodeps sysstat*

 rpm -Uvh --force --nodeps unixODBC*

 rpm -Uvh --force --nodeps unixODBC-devel*

Creating the groups:

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 oper

Creating the Oracle User and setting password for it:

useradd -u 54321 -g oinstall -G dba,oper oracle

 passwd oracle

Changing password for user oracle.

New password:

BAD PASSWORD: it is based on a dictionary word

BAD PASSWORD: is too simple

Retype new password:

passwd: all authentication tokens updated successfully.

Setting up directory structure:

mkdir -p /u01/app/oraInventory

mkdir -p /u01/app/oracle/product/18.0.0/db1

chown -R oracle:oinstall /u01/app/oraInventory

chown -R oracle:oinstall /u01/app/oracle/product/18.0.0/db1

Setting environment variable for CDB:

export ORACLE_HOME=/u01/app/oracle/product/18.0.0/db1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.

Now we are set for installation login through Oracle user and install software:

https://18ctestdb:5500/em

Configuring Listener:

We have successfully configured listener and its up and running, now create the database, using DBCA: