Mir Sayeed Hassan Blog- Oracle DBA

Verify Primary & Standby Database are sync & applied logs ON PRIMARY: col MEMBER FORMAT A100 set linesize 200 sql> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#; ON STANDBY: select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; Or SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY; Or SELECT * FROM V$DATAGUARD_STATS WHERE NAME='apply lag'; Or SELECT DEST_ID "ID", STATUS "DB_status",DESTINATION "Archive_dest", ERROR "Error&qu

Simple Performance Test in our Test ENV. Test in our env: SQL> conn mir/(Password); Connected SQL> select table_name from user_tables; TABLE_NAME ------------------ TEST SQL> desc test;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  ENO                                                NUMBER(10)  NAME                                               VARCHAR2(20)  SAL                                                NUMBER(20) SQL> select * from test; no rows selected SQL> insert into test values(1,'a',1000); 1 row created. SQL>  insert into test values(2,'b',2000); 1 row created. SQL>  insert into test values(3,'c',3000); 1 row created. SQL>  insert into test values(4,'d',4000); 1 row created. SQL> explain plan for select * from test where sal=1000; Explained SQL>  select * from table(dbms_xplan.display(

RMAN Backup Compressions The compression levels are BASIC, HIGH,  MEDIUM and  LOW To use this option, we can run the following RMAN commands RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; followed by .. RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;   -Default by Oracle or RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’; or RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’; or RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’; Tests carried out our Etick - Test Database & Verified . However, it should be noted that use of LOW, MEDIUM and HIGH,  The backup set size and backup duration are shown below. Compression Level ‘Basic’ backupset size: 327 M time: 56 sec Compression Level ‘HIGH’ backupset size: 266 M time: 3 mins Compression Level ‘Medium’ backupset size: 346 M time:  35 sec Compression Level ‘Low’ backupset size: 418 M time: 15 sec To summaries’ we can conclude : LOW – corresponds to LZO (11gR2) – smalles

Tested in our Test ENV Example: Rename the datafile (Here i have test by creating the new tablespace with datafile / We can use it for existing datafile to Rename/Move) SQL> create tablespace newrename datafile '/u01/app/oracle/oradata/prim/newrename.dbf' size 10m; Tablespace created. SQL> select name from V$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prim/system01.dbf /u01/app/oracle/oradata/prim/sysaux01.dbf /u01/app/oracle/oradata/prim/undotbs01.dbf /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/example01.dbf /u01/app/oracle/oradata/prim/PAP_DATA.dbf /u01/app/renametest/renametest.dbf /u01/app/oracle/oradata/prim/newrename.dbf Make the Tablespace Offline: SQL> alter tablespace newrename offline; Tablespace altered. Rename the File by OS Level (Safer side copy the datafile then rename by using the mv command) $ cp newrename.dbf newrename.db

Register the archivelog Manually for single & multiple/bulk  archivelog in Oracle DB Fallow the below simple steps to perform the operation: Register the archive log manually in a database: rman>alter database register logfile '/u01/app/oracle/FRA/arch_1_101.arc'; Bulk register the archive lo g rman> catalog start with '/var/arch';

Perform the expdp (fulldb/schema/table) by using the ASM Storage Step 1 - Create the Directory in ASM location (Example - +DATA/mir_exp) [grid@asmdb ~]$ asmcmd ASMCMD> ls CRS/ DATA/ FRA/ ASMCMD> cd DATA ASMCMD> mkdir mir_exp ASMCMD> cd DATA ASMCMD> ls ASM/ ASMDB1/ bck/ mir_exp/ ASMCMD> exit Step 2: Create the directory & give the require privileges SQL> create directory mir_exp as '+DATA/mir_exp'; Directory created. SQL> grant read,write on directory mir_exp to system; Grant succeeded. SQL> grant exp_full_database to system; Grant succeeded. Step 3: Execute the expdp [oracle@asmdb ~]$ expdp system/ (Password) directory=mir_exp dumpfile=mir.dmp logfile=mir.log schemas=mir Export: Release 11.2.0.4.0 - Production on Wed Jan 25 12:23:33 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productio

Oracle 11g Physical standby data Guard Failover steps In case of worst situation with data guard primary database, or not available for production than we can activated standby database as a primary production database. standby Database: Verify database name its open mode and its role from following SQL command: SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- RTS MOUNTED PHYSICAL STANDBY Following sort of SQL command will help to bring up standby as primary: SQL> alter database recover managed standby database finish; Database altered. SQL> alter database activate standby database; Database altered. Managed recovery process has been stopped between primary and standby database and standby becomes primary database. Bounce your database and verify database name its open mode and its role: SQL> shutdown immediate; ORA-01109

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All

Configure the Multiple Listener Port in Oracle DB & Tested in our Envirnoment Configure Port 1521 – Default port Configure Port 1522   - Port Added by request from client Vi listener.ora LISTENER1 =    (DESCRIPTION_LIST =      (DESCRIPTION =        (ADDRESS_LIST =          (ADDRESS =            (PROTOCOL = TCP)            (HOST = *****)            (PORT = 1522)          )        )      )    ) SID_LIST_LISTENER1 =    (SID_LIST =      (SID_DESC =        (GLOBAL_DBNAME = prim)        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)        (SID_NAME = prim)      )   ) LISTENER2 =    (DESCRIPTION_LIST =      (DESCRIPTION =        (ADDRESS_LIST =          (ADDRESS =            (PROTOCOL = TCP)            (HOST = *****)            (PORT = 1521)          )        )      )    ) SID_LIST_LISTENER2 =    (SID_LIST =      (SID_DESC =        (GLOBAL_DBNAME = prim)        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)        (SID_NAME

Open Standby Database in Read-write Mode When Primary is Lost Completely There may be scenario where Primary database is lost and we are only left with the standby database. In this scenario's we have to open the standby database in read-write mode. Below are the steps to convert standby database to Primary database. 1.)  Open standby database in mount state :  SQL> select name,open_mode from v$database; NAME       OPEN_MODE ------     ------------- stby      READ ONLY SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.   Total System Global Area      263639040  bytes Fixed Size                             1373964      bytes Variable Size                         230689012  bytes Database Buffers                  25165824     bytes Redo Buffers                        6410240       bytes Database mounted. SQL> select open_mode ,protection_mode , database_role fro

The below document is testing in our Env for the Corep Database If we want to change SYS password on the primary database of a dataguard environment, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like: Command:   Alter user sys identified by *******; Error log : ------------------------------------------------------------------ Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191 ------------------------------------------------------------------ This is because "If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated." So your password file is updated in primary side but not in stan

ADDING NEW SAN DISK TO EXISTING ASM DISK_GROUP First Check the current attached disks number     # cat /proc/scsi/scsi | egrep -i 'Host:' | wc -l  -----   #fdisk -l 2>/dev/null | egrep '^Disk' | egrep -v 'dm-' | wc -l Check current multipath setup using multipath or powermt command     # multipath -l     mpath2 (36006016015501c0018c07c18e0d8dc11)     [size=68 GB][features="1 queue_if_no_path"][hwhandler="1 emc"]     \_ round-robin 0 [active]     \_ 0:0:0:2 sdc 8:32  [active]     \_ round-robin 0 [enabled]     \_ 1:0:0:2 sdi 8:128 [active]     mpath1 (36006016015501c0084227c0ee0d8dc11)     [size=68 GB][features="1 queue_if_no_path"][hwhandler="1 emc"]     \_ round-robin 0 [active]     \_ 1:0:0:1 sdh 8:112 [active]     \_ round-robin 0 [enabled]     \_ 0:0:0:1 sdb 8:16  [active]     mpath0 (36006016015501c00b6de3705e0d8dc11)     [size=68 GB][features="1 queue_if_no_path"][hwhandler=&q

RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2 Testing Various RAC Failure scenario like: 1 - Node Failure 2 - DB Instance Failure 3 - ASM Instance Failure 4 - Listener failure 5 - Public Network Failure 6 - Private Network Failure 7 - OCR & Voting Disk Failure 8 - ASM Disk Failure ============== 1 - Node Failure ============== It can be planned, unplanned or all the nodes it could be any scenario Let us start the workload & shutdown the node The expected result will be: Resources will go offline Instance recovery will be performed Node vip & scan vip fail to surviving node Scan listener will fail over surviving node Client connection are moved to surviving instance Now shutdown Node 1 in OS Level intentionally or due to some reason the node 1 is shutdown #Shutdown –h now Check the node 2 status [oracle@test-rac2 ~]$ crs_stat -t Name           Type           Target    State     Host ----------------------------