Tablespace_Details

Select A.Tablespace_Name, B.Total/1024/1024 "Total_MB",
       (B.Total-a.Total_Free)/1024/1024 "MB_Used",
       A.Total_Free/1024/1024 "MB_Free",
       (A.Total_Free/B.Total) * 100 "Pct_Free",
       ((B.Total-A.Total_Free)/B.Total) * 100 "Pct_Used"
  From (Select Tablespace_Name, Sum(Bytes) Total_Free
          From Sys.Dba_Free_Space
         Group By Tablespace_Name     ) A
     , (Select Tablespace_Name, Sum(Bytes) Total
          From Sys.Dba_Data_Files
         Group By Tablespace_Name     ) B
Where A.Tablespace_Name LIKE upper('%&Tablespace%')
  And A.Tablespace_Name = B.Tablespace_Name
 --AND ((B.Total-A.Total_Free)/B.Total) * 100 > 96
ORDER BY 1;

ORACLE 12c

12c
Links: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_unplug_plug/pdb_unplug_plug.html
           https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89250
          http://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1.php
Installation is same as 11g but needs more storage space.
There are two important parts
CDB (Container Database)
PDB (Pluggable Database)






1 CDB holds multiple PDBs
CDB is created when we create a Database and a PDB is also created with it.
We can create CDB and PDB with DBCA , if we select the option “ container database”  it will become a container database otherwise it will be a normal database.

Data Files:
Container Database (CDB): SYSTEM,SYSAUX,UDNO,TEMP,USERS
Pluggable Database(PDB): SYSTEM,SYSAUX,TEMP,USERS    (it uses the UNDO tablespace of CDB)
Select * from cdb_data_files;
Select * from dba_data_files;    There are equal number of cdb as dba (just replace dba with cdb)      


We can add new tablespace to each of the PDB as and when required by first connection to that PDB and executing the normal create tablespace sql.
Views
The introduction of the multitenant option brings with it an extra layer of data dictionary views, allowing reporting across the root container and the pluggable databases (PDBs). Ignoring editions for the moment, prior releases had the following hierarchy.
DBA_ : All objects in the database.
|
--ALL_ : Objects accessible by the current user, including those owned by the current user.
  |
  --USER_ : Objects owned by the current user.
With Oracle 12c, an extra layer is added to the hierarchy.
CDB_ : All objects in the root container and all PDBs.
|
--DBA_ : All objects in the root container or PDB, depending on the current settings.
  |
  --ALL_ : Objects accessible by the current user, including those owned by the current user.
    |
    --USER_ : Objects owned by the current user.

LOG Files:
               Both CDB and PDB uses the same logfiles.

Users and Roles:
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
           Likewise, there are two types of roles.
Common Role : The role is present in all containers (root and all PDBs).
Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Create Common Users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;

-- Create the common user using the default CONTAINER setting.
CREATE USER c##test_user2 IDENTIFIED BY password1;
GRANT CREATE SESSION TO c##test_user2;

Create Local Users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

-- Connect to a privileged user in the PDB.
CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.
CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;

    
Users and Schemas in a CDB and PDB
 



Connection: SQL PLUS
Connecting to CDB:  sqlplus sys/oracle@//standby:1521/cdb1
Connecting to PDB: sqlplus sys/oracle@//standby:1521/pdb1
Altering Session: alter session set container=pdb2;
   alter session set container=pdb$seed;
   alter session set container=cdb$root;

There is no SID for PDB  cannot connect to PDB directly from SQL Developer

To connect PDB from SQL Developer we have to configure tnsname.ora as follows

TNSNAMES.ORA
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB2)
    )
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )



IMPORTANT DATA DICTIONARY VIEWS
--To view information about the containers in a CDB:
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

--To view information about PDBs:
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

--To view the open status of each PDB:
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

--Showing the Tables Owned by Specific Schemas in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND
t.OWNER IN('C##TEST_USER1','TEST') AND
p.PDB_ID = t.CON_ID --and t.TABLE_NAME='TEST'
ORDER BY p.PDB_ID;
 

--Showing the Users in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;

--Showing the Data Files for Each PDB in a CDB
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
-Showing the Temp Files in a CDB
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;

--Showing the Services Associated with PDBs
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
  ORDER BY PDB;

--To determine the current container ID:
SHOW CON_ID

--To determine the current container Name:
SHOW CON_NAME
                                             




                                                                      PLUG AND UNPLUG PDB
Connect to CDB1 with sys as sysdba
UNPLUG PDB
Use SQL*Plus to close the PDBs before they can be unplugged.
alter pluggable database pdb1 close immediate;
Unplug the closed PDB and then specify the path and name of the XML file.
alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/pdb1.xml';
Drop the closed PDB and keep the data files.
drop pluggable database pdb1 keep datafiles;
Verify the status of the unplugged PDB
select pdb_name, status from cdb_pdbs where pdb_name in ('PDB1');

COMPATIBILITY CHECK
sqlplus / as sysdba
[if cdb2 is not started up, start it up now.]
set serveroutput on
DECLARE
   compatible BOOLEAN := FALSE;
BEGIN   
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
   else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
   end if;
END;

PLUG IN PDB: NOCOPY METHOD
Connect to CDB2 with sys as sysdba
create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml' 
NOCOPY 
TEMPFILE REUSE;
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_NOCOPY';
select open_mode from v$pdbs where name='PDB_PLUG_NOCOPY';
List the data files of the plugged PDB.
select name from v$datafile where con_id=3;
alter pluggable database pdb_plug_nocopy open;
exit

PLUG IN PDB: COPY METHOD
Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy
sqlplus / as sysdba
Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.
create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml' 
COPY 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';
select open_mode from v$pdbs where name='PDB_PLUG_COPY';
List the data files of the plugged PDB.
select name from v$datafile where con_id=4;
exit

PLUG IN PDB: AS CLONE MOVE METHOD
Create and define a destination for the new data files, use the data files of the unplugged PDB to plug the PDB into another CDB, and then move the data files to another location.
mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move
sqlplus / as sysdba
Plug the PDB into the CDB and move the data files to a new location.
create pluggable database pdb_plug_move using '/u01/app/oracle/oradata/pdb2.xml' 
MOVE 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');
An error message is returned because of the non-uniqueness of the GUID. This is a good example of using the AS CLONE clause. 
create pluggable database pdb_plug_move 
AS CLONE using '/u01/app/oracle/oradata/pdb2.xml' 
MOVE 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE';
select open_mode from v$pdbs where name='PDB_PLUG_MOVE';
List the data files of the plugged PDB.
select name from v$datafile where con_id=5;

Physical Data Guard Step By Step

Server Detials  

Primary Database : DB11G

IP: 192.168.222.133

Server Name : oraclepri

……………………………………………

Standby Database : STD

IP: 192.168.222.135

Server Name: oraclesec

stop iptables service in both the machine with following command

service iptables stop

Do a ping test from both server to check if they can resolve each others ip address

Required Parameters

DB_Name                           : Must be same on Primary and all Standby database

DB_Unique_Name            : Must be different on Primary and all Standby database

Log_Archive_Config          : This parameter includes db_unique_name

                                                which are the part of dataguard  

                                                Configuration

Log_Archive_dest               :Define local and remote archive  
log file location

Log_archive_dest_state_n : Define state of archiving
(enable or  differ)

Remote_login_passwordfile   : Must be in EXCLUSIVE mode

FAL_SERVER                            : Used for archivelog gap

                                                       resolution(required only in

                                                        Physical standby database server)

DB_FILE_NAME_CONVERT: Required when directory
structure different for datafile
in standby server

Log_file_name_convert           : Required when directory
structure different for log file
in standby server

Standby_file_management   : Set it to auto to create new files in standby automatically

On Oraclepri DB11G database check archive log mode

select log_mode from v$database;

If it is not in archive log mode then change it with following steps

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

select log_mode from v$database;

Make sure Database is in force_logging mode

Select force_logging from v$database;

If it is not in forcelogging mode then change it to forcelogging

Alter database force logging;

Verify DBName and DBUniqueName of Primary database

show parameter db_name

show parameter db_unique_name

Make DB_unique_name to be part of dataguard

alter system set log_archive_config=’DG_CONFIG=(DB11G,STD)’;

Set archivelog destinations

ALTER SYSTEM set log_archive_dest_2=’service=STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STD’;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set remote login password to exclusive

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SHOW PARAMETER REMOTE_LOGIN

Set FAL_SERVER and file name convert parameter in case the directory structure is different in primary and standby database

alter system set fal_server=STD;

alter system set db_file_name_convert=’STD’,’DB11G’ SCOPE=SPFILE;

alter system set log_file_name_convert=’STD’,’DB11G’ SCOPE=SPFILE;

alter system standby_file_management=AUTO

Lets configure required services in primary server

netmgr

Service Naming should be for db11g and std in both the servers and listener configuration on primary is as above and for secondary as below image

The difference is Global Database Name is same for both primary and secondary but SID is different.

Start the listener in both primary and secondary after configuring the netmgr

Let’s take the backup of the database by

rman target /

backup database plus archivelog;

After the backup is completed you can find the backup files in /u01/app/oracle/flash_recovery_area/DB11G folder

Now lets create standby control file and pfile

alter database create standby controlfile as ‘/tmp/stdcontrol.ctl’

create pfile=’/tmp/initSTD.ora’ from spfile;

Change initSTD.ora file parameter as we will shift it to standby server

Below images are of pfile of primary and secondary server

on primary

On Secondary

Now copy the control file to standby server

scp /tmp/stdcontrol.ctl oracle@192.168.222.135:/u01/app/oracle/oradata/control01.ctl

Now lets go to standby server and copy the control01.ctl to /u01/app/oracle/flash_recovery_area/STD/control02.ctl

cp /u01/app/oracle/oradata/control01.ctl /u01/app/oracle/flash_recovery_area/STD/control02.ctl

Now copy the pfile to standby server

scp /tmp/initSTD.ora oracle@192.168.222.135:/u01/app/oracle/product/11.2.0/dbhome/dbs/

Lets copy the backup folder to standby server

scp -r /u01/app/oracle/flash_recovery_area/DB11G oracle@192.168.222.135:/u01/app/oracle/ flash_recovery_area/

Create a password file in primary site and copy it to the secondary site and change the name to match the seocndary database name copying

go inside the $ORACLE_HOME/dbs/

orapwd file=orapwDB11G password=oracle force=y        (password should be same as sys password)

Now copy the file to standby side

scp orapwDB11G oracle@192.168.222.135:/u01/app/oracle/product/11.2.0/dbhome/dbs/orapwSTD

On standby server update the /etc/oratab file with below information

vi /etc/oratab

STD:/u01/app/oracle/product/11.2.0/dbhome:N

update the .bash_profile with standby database name

Now run

source ~/.bash_profile

Now login as sysdba

sqlplus / as sysdba

create spfile from pfile=’/u01/app/oracle/product/11.2.0/dbhome/dbs/initSTD.ora’;

Now restore the backups

rman target /

startup mount;

restore database;

Now create standby logfile in both standby and primary server

first on standby server

alter database add standby logfile ( ‘/u02/app/oracle/oradata/STD/standby_redo01.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/STD/standby_redo02.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/STD/standby_redo03.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/STD/standby_redo04.log’) size 50M;

On Primary Server

alter database add standby logfile ( ‘/u02/app/oracle/oradata/DB11g/standby_redo01.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/DB11g/standby_redo02.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/DB11g/standby_redo03.log’) size 50M;

alter database add standby logfile ( ‘/u02/app/oracle/oradata/DB11g/standby_redo04.log’) size 50M;

Now the moment come when we start the standby database

alter database recover managed standby database disconnect from session;

Now check the alert log file to see the progress

Go to primary database and do a log switch by

alter system switch logfile;

Then check the alert log file in standby server to see the progress

the default mode for dataguard is MAX_PERFORMANCE

In this mode after a log switch occurs the archive log file is transfered to secondary file to apply

Open the Standby database in read only mode

alter database recover managed standby database cancel;

alter database open read only;

To put the database in standby mode again do the following

alter database recover managed standby database disconnect from session;