MULTITENANT service register in 19c

 MULTITENANT:

BEGIN

  DBMS_SERVICE.create_service(

    service_name => 'my_new_service',

    network_name => 'my_new_service'

  );

END;

/

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

SELECT name,

       network_name

FROM   v$active_services

ORDER BY 1;

BEGIN

    DBMS_SERVICE.START_SERVICE (SERVICE_NAME   => 'herospdb.localdomain');

END;

/

BEGIN

  DBMS_SERVICE.modify_service(

    service_name => 'my_new_service',

    goal         => DBMS_SERVICE.goal_throughput

  );

END;

/

sqlplus ram/ram@demo:1521/herospdb.localdomain

services for pluggable database in 19c

 command for pluggable database services.

COLUMN NAME FORMAT A12;

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

show pdbs;

alter session set container=PDB01;

check the services for pluggable database:

COLUMN NAME FORMAT A15;

COLUMN NETWORK_NAME FORMAT A15;

SELECT NAME,NETWORK_NAME FROM DBA_SERVICES;

other network name

lsnrctl status|grep -i pdb01 -A 1

srvctl status service -d CDBAMN   //check the service for container database.

lsnrctl status|grep -i SOA -A 1   // soa is service name

crsctl stat res ora.cdbamn.soa.svc -t

crsctl stat res ora.cdbamn.db -t

FRa space:

column SPACE_LIMIT_Gb format 999;

column SPACE_USED_Gb format 999;

select name, SPACE_LIMIT/1024/1024/1024 SPACE_LIMIT_Gb, SPACE_USED/1024/1024/1024 SPACE_USED_Gb, SPACE_RECLAIMABLE from v$recovery_file_dest;

12cr2 installation steps on redhat 7 in vmware

network --hostonly adapter --public ip

internet --privnet

nat

set it automatically

________________________________________

cat /etc/oracle-release

uname -a

yum search oracle

yum -y install oracle-database-server-12cR2-preinstall.x86_64

check package information

 yum info oracle-database-server-12cR2-preinstall.x86_64

check kernel parameters

cat /etc/sysctl.conf

b. Check the security Limits:

cat /etc/security/limits.d/oracle-database-server-12cR2-preinstall.conf

check groups

 cat /etc/group

id oracle

usermod -g oinstall -G dba,oper,backupdba,racdba oracle

or

# /usr/sbin/groupadd -g 54321 oinstall

# /usr/sbin/groupadd -g 54322 dba

# /usr/sbin/groupadd -g 54323 oper

# /usr/sbin/groupadd -g 54324 backupdba

# /usr/sbin/groupadd -g 54325 dgdba

# /usr/sbin/groupadd -g 54326 kmdba

# /usr/sbin/groupadd -g 54327 asmdba

# /usr/sbin/groupadd -g 54328 asmoper

# /usr/sbin/groupadd -g 54329 asmadmin

# /usr/sbin/groupadd -g 54330 racdba

# /usr/sbin/useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,asmoper,asmadmin oracle

# passwd oracle

# cat /etc/oraInst.loc

inventory_loc=/u01/app/oraInventory

inst_group=oinstall

mkdir -p /u01/app/oraInventory

mkdir -p /u01/app/oracle/product/12.2.0.1/db_1

chown -R oracle:oinstall /u01

chmod -R 775 /u01

Set secure linux by editing the “/etc/selinux/config” file, making sure the SELINUX flag is set as follows

# cat /etc/selinux/config

SELINUX=permissive

Once the change is complete, restart the server or run the following command.

# setenforce Permissive

If we need to disable firewall in linux do the following:

# systemctl stop firewalld

# systemctl disable firewalld

Login as oracle user set the following entry in .bash_profile

# su – oracle

# cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export LC_ALL=C

if [ $USER = “oracle” ]; then

if [ $SHELL = “/bin/ksh” ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

Create an environment file for the database to be created with the following variables:

# cat /home/oracle/prod.env

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=prod1.oracle.localdomain

export ORACLE_UNQNAME=PROD1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1

export ORACLE_SID=PROD

export PATH=/usr/sbin:/usr/local/bin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Oracle Installation:

Before starting the installation by “runInstaller” from the unzipped “linuxx64_12201_database.zip”, remember to set the display variable or “xhost +” as oracle user.

# su – oracle

#xhost +

Note:

Oracle as two options during the binary installation, first option is to install binary and create a database during the installation and the the second option is to install only the binaries. Here, we are going with the second option.

Step 1:

# pwd

/home/oracle

# unzip linuxx64_12201_database.zip

cd database

./runInstaller

Once, runInstaller is executed, the below GUI will started for Oracle 12cR2 Installation. In the first empty box provide your E-mail ID if you wish to receive updates from oracle, else uncheck and press “Next”.

id oracle

hostname

cat /etc/hosts

now use dbca to create database.

INMEMORY IN ORACLE 12C

IN Memory

inmemory_size must be set 100m or larger

sga_target=sga+in memory ---singe instance

sga_targer=sga+(inmemory*1)--Rac

pga_target= (max expected parallel servers * 2gb)


how does it work


SGA_TARGET must be large enough to accommodate in memory area.

Contain data in the new in memory columnar format.

inmemory contain two subpools

imcu  allocate 1mb extend in memory area(segements)

smu

object in memory area is composed of one or more

some useful for immemory.

V$inmemory_area current size of pools in the in memory area

v$IM_SEGMENTS  list of segments currently populated in memory column store.

to monitor the progress of population.

V$INMEMORY_AREA ---IS THERE ENOUGH MEMORY

V$IM_SEGMENTS
V$IM_SMU_HEAD




Inmemory column store is not a cache

Any objects with the in memory




oracle -in-memory advisor
===================

Oracle 12c Multitenant Backup and Recovery

Single RMAN backup database command will backup the root container database as well as all the pluggable databases.

[oracle@db12c ~]$ rman  target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 10:05:37 2016

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

connected to target database: DB12C (DBID=1373556589)

RMAN> backup database FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=388 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=00003 name=/u01/app/oracle/oradata/db12c/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/db12c/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db12c/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_2_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_3_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_4_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/db12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db12c/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_5_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-00 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

Backups can also be performed at the pluggable database level.

RMAN> backup pluggable database prodb1 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_11_1.BAK tag=TAG20160906T102038 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-03 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

RMAN> 

RMAN> backup pluggable database prodb2 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_13_1.BAK tag=TAG20160906T102159 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-04 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

We can also use RMAN to connect to an individual pluggable database instead of the container database.

RMAN> list backup of database;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
16      Full    679.84M    DISK        00:00:07     06-SEP-16    
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20160906T102707
        Piece Name: /rman_bak/DB12C_20160906_16_1.BAK
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  8       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
  9       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
  10      Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf

RMAN> 

RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
—- ——- –  – – ———- ——— ——- ——- ———- —
8    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
9    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
10   16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707

Loss of Tempfile at pluggable database level.Temp file is automatically created when the pluggable database is closed and opened.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/temp01.dbf
/u01/app/oracle/oradata/db12c/pdbseed/pdbseed_temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb2/temp012016-08-10_05-31-03-PM.dbf

SQL> alter session set container=prodb1;

Session altered.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf 

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf: No such file or directory

SQL> alter pluggable database prodb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database prodb1 open read write;

Pluggable database altered.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp*
-rw-r—– 1 oracle oinstall 20979712 Sep  6 10:37 /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of Non-System data file at pluggable database level.

[oracle@db12c ~]$ sqlplus sys@prodb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 10:46:17 2016

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/undotbs01.dbf
/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf: No such file or directory

SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10:
'/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf'

SQL> conn / as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO

[oracle@db12c ~]$ rman target sys/oracle@prodb1

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 11:09:14 2016

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

connected to target database: DB12C (DBID=1373556589, not open)

RMAN> select name from v$datafile;

using target database control file instead of recovery catalog

NAME                                                                          
——————————————————————————–

/u01/app/oracle/oradata/db12c/undotbs01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
 

RMAN> restore tablespace sysaux;

Starting restore at 06-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=773 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_16_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_16_1.BAK tag=TAG20160906T102707
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-SEP-16

RMAN> recover tablespace sysaux;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

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

Finished recover at 06-SEP-16

[oracle@db12c ~]$ sqlplus sys/oracle@prodb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 11:12:03 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> recover datafile 10;
Media recovery complete.

SQL> recover datafile 8;
Media recovery complete.
SQL> alter pluggable database prodb1 open read write;

Pluggable database altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of SYSTEM tablespace datafile at pluggable database level.
The entire container database has to be shut down and mounted and pluggable database recovered.

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/system01.dbf

SQL> !ls -l  /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/system01.dbf: No such file or directory

SQL> alter session set container=prodb1;

Session altered.

SQL> shutdown abort
Pluggable Database closed.

To recover the pluggable database we need to connect to the container database, shutdown the container database (this will shutdown all other pluggable databases) , mount the container database and then recover the pluggable database.

[oracle@db12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 13:26:26 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO
        
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3238002688 bytes
Fixed Size                  2929600 bytes
Variable Size            2046823488 bytes
Database Buffers         1174405120 bytes
Redo Buffers               13844480 bytes
Database mounted.

[oracle@db12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:29:19 2016

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

connected to target database: DB12C (DBID=1373556589, not open)

RMAN> restore tablespace prodb1:system;

Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1144 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_21_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_21_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 06-SEP-16

RMAN> recover tablespace prodb1:system;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

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

Finished recover at 06-SEP-16

RMAN> alter database open;

Statement processed

RMAN> recover datafile 10;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

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

Finished recover at 06-SEP-16

RMAN> recover datafile 9;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

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

Finished recover at 06-SEP-16

RMAN> alter pluggable database all open read write;

Statement processed

Loss of SYSTEM data file at the Container database level.
Note:If we lose the container database SYSTEM datafile we cannot connect to any of the pluggable databases as well.
We have to shutdown abort the container database and then mount the container database and perform offline recovery of the SYSTEM tablespace.

SQL> !rm -rf /u01/app/oracle/oradata/db12c/system01.dbf

SQL> select count(*) from dba_objects;
select count(*) from dba_objects
                     *
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/db12c/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter session set container=prodb1;

Session altered.

SQL> alter session set container=prodb2;

Session altered.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         READ WRITE NO
         4 PRODB2                         READ WRITE NO
SQL> alter system flush buffer_cache;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

[oracle@db12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:59:16 2016

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    3238002688 bytes

Fixed Size                     2929600 bytes
Variable Size               2046823488 bytes
Database Buffers            1174405120 bytes
Redo Buffers                  13844480 bytes

RMAN> restore tablespace system;

Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=769 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/db12c/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_20_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_20_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 06-SEP-16

RMAN> recover tablespace system;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

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

Finished recover at 06-SEP-16

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database all open read write;

Statement processed

container and plugabble link

https://www.hhutzler.de/blog/rac-12c-an-pluggable-databases/#Create_Common_and_Local_users

https://muthuappsdba.blogspot.com/2015/09/step-by-step-to-migrate-non-container.html

Create Common Users in 12c container database

When creating a common user the following requirements must all be met.

You must be connected to a common user with the CREATE USER privilege.
The current container must be the root container.
The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
The following example shows how to create common users with and without the CONTAINER clause from the root container.

oracle@Linux03 echo $ORACLE_SID
ORCL

CONN / AS SYSDBA

SQL> show con_name
con_name CDB$ROOT

Lets query some local users by filtering con_id >2. ususally CON_ID greater that 2 is user created PDB's.

SQL> set echo on
SQL> show con_name
con_name CDB$ROOT
SQL> column USERNAME format a40
SQL> column CON_ID format 99
SQL> select username,CON_ID,common from CDB_USERS where CON_ID >2 and common='NO';

USERNAME                                 CON_ID COM
---------------------------------------- ------ ---
PDBORCL1_USR2                                 5 NO
PDBORCL1                                      5 NO
PDBORCL1_USR1                                 5 NO
PDBORCL1_USR2                                 4 NO
PDBORCL2                                      4 NO
IX                                            3 NO
SH                                            3 NO
PDBADMIN                                      3 NO
BI                                            3 NO
OE                                            3 NO
SCOTT                                         3 NO
HR                                            3 NO
PM                                            3 NO

13 rows selected

******************************************************
Now I want to create user C##CDBADMIN1
******************************************************

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected


-- Create the common user using the CONTAINER clause.
CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDBADMIN1 CONTAINER=ALL;

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME  CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1       3 YES
C##CDBADMIN1       5 YES
C##CDBADMIN1       1 YES
C##CDBADMIN1       4 YES

******************************************************
-- Create the common user using the default CONTAINER setting.
******************************************************

SQL> CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle;

User created.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME  CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1       1 YES
C##CDBADMIN1       5 YES
C##CDBADMIN1       3 YES
C##CDBADMIN1       4 YES

SQL> alter session set container=pdborcl1;

Session altered.

***************************************************************
We can grant permission only on current pdb to common user.
****************************************************************

SQL> grant create session TO C##CDBADMIN1 CONTAINER=CURRENT;

Grant succeeded.

**********************************
Lets test connecting to current pdb:
**********************************
SQL> conn C##CDBADMIN1/oracle@pdborcl1
Connected.
SQL> show user
USER is "C##CDBADMIN1"
SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

*************************************
Lets test connecting to other pdb now:
**************************************

SQL> conn C##CDBADMIN1/oracle@pdborcl2
ERROR:
ORA-01045: user C##CDBADMIN1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.


*****************************************************************
YOU can revoke the grants on CDBADMIN11 using REVOKE cmd:
*****************************************************************

SQL> revoke CREATE SESSION from C##CDBADMIN1 CONTAINER=ALL;

Revoke succeeded.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME  CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1       3 YES
C##CDBADMIN1       1 YES
C##CDBADMIN1       5 YES
C##CDBADMIN1       4 YES

*******************************************************
You can drop COMMON USER as below connecting to sys:
*******************************************************

SQL> drop user C##CDBADMIN1 ;

User dropped.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected