New in 12c
How to add node to the clusterRun cluster verify to check that host03 can be added as node
[grid@rac3 ~]$ $GRID_HOME/bin/cluvfy stage -pre nodeadd -n rac3 -fixup -fixupnoexec
Run the addnode.sh to add the node
[grid@rac3 ~]$ export IGNORE_PREADDNODE_CHECKS=Y
[grid@rac3 ~]$ $GRID_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac3-vip}"
[root@rac3 ~]# /u01/app/oraInventory/orainstRoot.sh
[root@rac3 ~]# /u01/app/oraInventory/orainstRoot.sh
[root@rac3 ~]# crsctl check cluster -all
How to add a Database home to a 12c RAC Cluster
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1/
[oracle@rac1 ~]$ cd $ORACLE_HOME/oui/bin
[oracle@rac1 bin]$ ./addNode.sh -silent "CLUSTER_NEW_NODES={rac3}"
[root@rac3 ~]# /u01/app/oracle/product/12.1.0.1/dbhome_1/root.sh
From From RAC1 node,add logfile,thread,undo and assign instance number;
SQL> alter database add logfile thread 3 group 5 ('+DATA') size 50M, group 6 ('+DATA') size 50M
SQL> alter database enable public thread 3;
SQL> create undo tablespace undotbs3 datafile '+DATA' size 200M autoextend on;
SQL> alter system set undo_tablespace='undotbs3' scope=spfile sid='orcl3';
SQL> alter system set instance_number=3 scope=spfile sid='orcl3';
SQL> alter system set cluster_database_instances=3 scope=spfile sid='*';
[oracle@rac3 bin]$ srvctl add instance -d orcl -i orcl3 -n rac3
[oracle@rac3 bin]$ srvctl status database -d orcl -v
[oracle@rac3 ~]$ srvctl start instance -d orcl -i orcl3
[oracle@rac1 ~]$ srvctl status database -d RAC -v
How to Connect to CDB
export ORACLE_SID=cdb01
sqlplus / as sysdba
sqlplus sys/oracle@localhost:1521/cdb01 as sysdba
How to Connect to PDB
alter session set container = pdbName;
connect user/pass@PDB1 -- Use tnsnames entries
connect user/pass@//host:port/pdbServiceName
connect localuser/password@//cluster-scan:scanPort/pdbServiceName
sqlplus user/pass@//host:port/pdbServiceName
sqlplus localuser/password@//cluster-scan:scanPort/pdbServiceName
sqlplus user/pass@PDB1 -- Use tnsnames entries
How to backup in Oracle 12c
Backup of complete container( ROOT + ALL PDBS )
$ rman target sys/oracle@cdb1
connected to target database: CDB1 (DBID=828012650)
RMAN> backup database plus archivelog
RMAN> backup database root ; Backup of only root container:
Backup of particular pluggable database:(backup pluggable database ORCL;)
$ rman target sys/oracle@cdb1
connected to target database: CDB1 (DBID=828012650)
RMAN> backup pluggable database ORCL;
RMAN> backup tablespace USERS; Backup of tablespace of pluggable database(by connecting to PDB )
Which are the new process in 12c
ARSn - Recover aborted ASM transactional operations.
FENC - processes the fence request from CSSD to drain outstanding I/O’s and reject new on failure
LGnn - Listener Registration Process
LREG - Listener Registration Process
RMON - Manages the rolling migration procedure
RPOP - Repopulation Daemon for repoulationg data files from snapshot files and backup files
How to Tell If the Database Is a CDB
select name, cdb, con_id from v$database;
Viewing Information About Containers
select name, con_id, db_id from v$containers;
Viewing Information About the PDBs
select pdb_id, pdb_name, status from cdb_pdbs;
Finding the Open Mode of a PDB
select name, open_mode, restricted, open_time from v$pdbs;
Viewing the History of PDBs
select db_name, con_id, pdb_name, operation, cloned_from_pdb_name
from cdb_pdb_property;
Viewing all the tablespaces in a CDB
select tablespace_name, con_id from cdb_tablespaces
What is Oracle Data Redaction
Mask (redact) of data returned from queries issued by applications.
Full redaction : All content of the columns
Partial redaction : Portion of the column data
Random redaction : randomly each time it's dispalyed
No redaction :
Regular expressions : regular expressions to look for patterns of data to redact
How to configure REDACTION
SQL> BEGIN
DBMS_REDACT.add_policy(
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
column_name => 'card_no',
policy_name => 'redact_card_no',
function_type => DBMS_REDACT.full,
expression => '1=1'
);
END;
SQL> SELECT object_owner,object_name,function_type,function_parameters FROM redaction_columns;
What is a Database Vault in Oracle 12c?
Database Vault is very useful to protect your data from users such as DBA who has access to all tables
Database Vault owner : this user is granted the DV_Owner role and manage database role and configurations
Database Vault manager : which is granted DV_ACCTMGR role, and used to manage database user account
SQL> BEGIN
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'SENSITIVE_DATA_REALM',
grantee => 'SYSTEM',
auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
END;
What is the difference between Common User and Local User In 12c Multitenant ?
Local User :
1.Local can be created only in PDBs. it is dedicated for that PDB only. ( Means this user can’t be created in other PDBS).
2.With the appropriate privileges, a local user can access object in a common user’s schema
COMMON USER:
Can only be created in root container.
Used to perform an activity in all the containers including root container of the CDB.
Common username must start with C##.
While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
It is not recommended to create objects under common user
While creating user if tablespace is used, then that tablespace should be present in all the containers i.e root container and PDBS.
For common user , grant privilege with container=ALL, else PDBS will not inherit that privilege.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user C##TESTINGUSER identified by C##TESTINGUSER;
SQL> grant dba to C##TESTINGUSER container=ALL; --if container=ALL not given then within PDB user will not have given access.
How to create istener for PDB ?
LISTENER_DBATEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbatest-host)(PORT = 1573))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1573))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_PDB_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_PDB_LISTENER=SUBNET # line added by Agent
lsnrctl start LISTENER_DBATEST
SQL> alter session set container=PDB1;
SQL> show con_name
SQL> alter system set listener_networks='(( NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbatest-host)(PORT =1573)))))' scope=both;
SQL> alter system register;
Make an entry in tnsnames.ora ans use it to connect through sqlplus
How to create istener for PDB ?
LISTENER_DBATEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbatest-host)(PORT = 1573))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1573))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_PDB_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_PDB_LISTENER=SUBNET # line added by Agent
lsnrctl start LISTENER_DBATEST
SQL> alter session set container=PDB1;
SQL> show con_name
SQL> alter system set listener_networks='(( NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbatest-host)(PORT =1573)))))' scope=both;
SQL> alter system register;
Make an entry in tnsnames.ora ans use it to connect through sqlplus
How to improve table performance for IN-MEMORY In Oracle 12c ?
In-Memory feature enables data to be simultaneously populated in memory in both a row format (in the buffer cache) and a new in-memory column format.
SQL> alter system set inmemory_size=5G scope=spfile;
SQL> alter table dbaclass.test2 inmemory;
SQL> ALTER TABLESPACE USERS DEFAULT INMEMORY;
What is ASM Flex cluster ?
First Convert Oracle ASM to Flex ASM and then change cluster mode to flex.
Asm Flex cluster :
ASM instance run on a seperate physical server
Require designated NIC for communication
Listener is configured
Prerequisites :
OCR, SPFile and password file all stored in a disk group
COMPATIBLE.ASM set to 12.1 or higher
Convert to FlexASM:
[root@india1 bin]# ./crsctl get cluster mode status
Cluster is running in "standard" mode
$ asmcmd
ASMCMD> showclustermode
ASM cluster : Flex mode disabled
./srvctl config asm
Button : Convert to Oracle Flex ASM
...
...
[root@india1 ~]# /u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh
Convert Standard Cluster mode to Felx :
Hub Nodes:
have direct access to the shared storage and access the OCR and Voiting Disk VD directly.
Leaf Nodes:
Communicates with the Hub Node that it is attached to.
No direct access to the shared storage like the Hub Nodes
Prerequisites :
setup GNS and add the GNS VIP to cluster.
Steps:
setup GNS and add the GNS VIP to cluster. ./srvctl add gns -i 172.15.1.108 -d usgns.shcl.com
Convert cluster mode to flex ./crsctl set cluster mode flex
Restart crs
How to use database In archive feature ?
Create tablespace with p_active and p_archived tablespaces using ora_archive_state column.
update table set ora_archive_state=1 where anycolumn = fulfill some condition
Enable row movement so that rows move to p_archived tablespaces ;
Make the p_archived tablespaces read only.
Backup table space and exclude it from whole backup.