Feroz DBA'S Blog

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....

  Tuples: my_tup = [(1,2),(2,3),(4,5),(6,7),(8,9)] for tup in my_tup:     print(tup) Output: (1, 2) (2, 3) (4, 5) (6, 7) (8, 9) Tuple unpacking methods: Method-1: using (a,b) my_tup = [(1,2),(2,3),(4,5),(6,7),(8,9)] for a,b in my_tup: print(a) print(b) Output: 1 2 2 3 4 5 6 7 8 9 Dictionaries: my_dict = {'Apple':2, 'Grapes':1, 'Orange':1 } for dict in my_dict: print(dict) Output: Apple Grapes Orange Note: By default, it prints only the Keys but not the values. To print both the keys and pairs we have to use items() like my_dict.items() my_dict.values() my_dict = {'Apple':2, 'Grapes':1, 'Orange':1 } for dict in my_dict.items(): print(dict) Output: ('Apple', 2) ('Grapes', 1) ('Orange', 1) Again Here you could use tuple unpacking technique. my_dict = {'Apple':2, 'Grapes':1, 'Orange':1 } for key,value in my_dict.items(): print(key) print(value) Output: Apple 2 Grapes...

 You can verify by using these blow options: 1. grep the ocssd logfile contents by searching a key word "master node" Go to the location of cssd logfile and run the below command: grep -i "master node" ocssd.log | tail -1 Output:  [CSSD]CLSS-3001: local node number 1, master node number 1 2. grep crsd logfile contents by searching a key word "MASTER" Go to the location of crsd logfile and run the below command: grep MASTER crsd.log | tail -1 3. By querying the database view  "V$GES_RESOURCE"  4. by locating the OCR backup, because the node that store OCR backups is the master node. ocrconfig -showbackup

Questions related to: Basic RAC Questions RAC installation Questions RAC Upgrade/Patching Questions RAC Data Guard Configuration Questions RAC troubleshooting Questions RAC Basics: 1. What are the Clusterware processes in RAC?  Ans: ocssd: Cluster synchronization services crsd: Cluster ready services evmd: Event manager daemon oprocd: Process monitor daemon 2. What are background process in RAC? LMS LMD LMON LCK DIAG 3. What are clusterware components? Voting disk OCR 4. How to backup OCR? RAC Troubleshooting: 1. How to troubleshoot node reboot/eviction?

  ---- Check backup locations SELECT      database_name,      backup_finish_date,      CASE msdb..backupset.type          WHEN 'D' THEN 'Database'          WHEN 'L' THEN 'Log'      END AS backup_type,      physical_device_name,   device_type  FROM msdb.dbo.backupmediafamily  INNER JOIN msdb.dbo.backupset      ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  ORDER BY database_name,backup_finish_date; use msdb select * from backupset select logical_name,physical_name,backup_size from backupfile  EXEC msdb.dbo.sp_delete_database_backuphistory  @database_name = N'AdventureWorks2012' GO select name,recovery_model_desc from sys.databases order by name SELECT database_name AS [Database],  COUNT(backup_set_id) AS Orphans FROM   backupset WHERE  database_name NOT IN (SELECT n...

  Backup full database (CDB + all PDBs): It is similar to non-CDB database. Connect to root container and run the below commands. rman target / backup database;  ---- this will backed up CDB and all PDBs backup archivelog all; backup database plus archivelog; [oracle@oracle12c rman_backup]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jun 11 17:24:01 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. connected to target database: PRDCDB (DBID=3622167870) RMAN> backup database; Starting backup at 11-JUN-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=73 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=00020 name=/u01/app/oracle/oradata/dupdb/undotbs01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/dupdb/system01.dbf inp...

  SQL> SET LINES 132 PAGES 100 SQL> COL con_name FORM A15 HEAD "Container|Name" COL tablespace_name FORM A15 COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg." COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg." -- COMPUTE SUM OF fsm apm ON REPORT BREAK ON REPORT ON con_id ON con_name ON tablespace_name -- WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm FROM cdb_free_space cf1 ,v$containers c1 WHERE cf1.con_id = c1.con_id GROUP BY c1.con_id, cf1.tablespace_name), y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm FROM cdb_data_files cd ,v$containers c2 WHERE cd.con_id = c2.con_id GROUP BY c2.con_id ,cd.tablespace_name) SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm FROM x, y, v$containers v WHERE x.con_id = y.con_id AND x.tablespace_name = y.tablespace_name AND v.con_id = y.con_id UNION SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024 FROM v$contain...

  Note:  Generally, Container database won't let open all PDBs in READ-WRITE mode after each database restarts and to make all the PDBs to open in READ-WRITE mode we need to create a trigger to achieve this. Method-1: Using SAVE STATE option SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1593835520 bytes Fixed Size                  8793256 bytes Variable Size             956302168 bytes Database Buffers          620756992 bytes Redo Buffers                7983104 bytes Database mounted. Database opened. SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------   ...

Cloning from PDB1 to PDB4  SQL> -- cloning PDB SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. SQL> alter pluggable database pdb1 open read only; Pluggable database altered. SQL> create pluggable database pdb4 from pdb1 file_name_convert=('/u01/app/oracle/oradata/prdcdb/pdb1/','/u01/app/oracle/oradata/prdcdb/pdb4/'); Pluggable database created. SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> alter pluggable database pdb4 open; Pluggable database altered. SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO   ...

Plug pdb5 and unplug pdb4 in the same container database (CDB) SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 PDB1                           READ WRITE NO          4 PDB2                           READ WRITE NO          5 PDB4                           READ WRITE NO          6 PDB3                      ...

Shutdown a container database (CDB) sqlplus / as sysdba shutdown immediate Startup a container database (CDB) sqlplus / as sysdba startup Shutdown a Pluggable database (PDB) SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. Check the status of pluggable database (PDB) SQL> select name, open_mode from v$pdbs where name='PDB1'; NAME       OPEN_MODE ---------- ---------- PDB1       MOUNTED SQL> select name,open_mode from v$containers where name='PDB1'; NAME       OPEN_MODE ---------- ---------- PDB1       MOUNTED Startup pluggable database (PDB) SQL> alter pluggable database pdb1 open; Pluggable database altered. Stop all pluggable databases (PDBs) SQL> alter pluggable database all close immediate; Pluggable database altered. Startup all pluggable databases (PDBs) SQL> alter pluggable database all open; Pluggable database altered.

 How to run expdp in Pluggable database (PDB): Step-1: PDB services should be registered in the listener. [oracle@oracle12c ~]$ lsnrctl status |grep pdb1 Service "pdb1" has 1 instance(s). Step-2: Add PDB entries in TNS file. PDB1 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.localdomain)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = pdb1)     )   ) Step-3: Create a data pump directory under the PDB. SQL> alter session set container=pdb1; Session altered. $ mkdir -p /u01/pdb1_expdir SQL> create directory pdb1_expdir as '/u01/pdb1_expdir'; Directory created. SQL> grant read,write on DIRECTORY pdb1_expdir to HR; Grant succeeded. Step-4: Backup the PDB. expdp HR/HR@pdb1 directory=pdb1_expdir dumpfile=pdb1_hr.dmp logfile=exp_pdb1_hr.log schemas=HR Export logfile: [oracle@oracle12c ~]$ expdp HR/HR@pdb1 directory=pdb1_expdir dumpfile=pdb1_hr....

 Non-CDB database name: dupdb CDB database name: prdcdb Step-1: Shutdown non-cdb database (dupdb) SQL> show parameter uniq NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_unique_name                       string      DUPDB SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. Step-2: Startup non-cdb database in read-only mode SQL> startup mount ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size                  8628936 bytes Variable Size             373294392 bytes Database Buffers          687865856 bytes Redo Buffers        ...