Friday, June 24, 2016

Oracle: Resize Datafiles

with query as (
    select /*+ NO_MERGE MATERIALIZE */
        file_id,
        tablespace_name,
        max(block_id + blocks) highblock
    from
        dba_extents
    group by
        file_id, tablespace_name
)
select
    'alter database datafile '|| q.file_id || ' resize ' || ceil ((q.highblock * t.block_size + t.block_size)/1024)  || 'K;' cmd
from
    query q,
    dba_tablespaces t
where
    q.tablespace_name = t.tablespace_name;

Oracle: Query Long Run Session


  select
     opname, to_char(start_time, 'yyyy-mm-dd-hh24:mi:ss'),  target,  sofar, totalwork, units, elapsed_seconds, message
  from v$session_longops
  order by start_time desc;


select
   substr(a.spid,1,9) pid,
   substr(b.sid,1,5) sid,
   substr(b.serial#,1,5) ser#,
   substr(b.machine,1,6) box,
   substr(b.username,1,10) username,
   -- b.server,
   substr(b.osuser,1,8) os_user,
   substr(b.program,1,30) program
from
   v$session b,
   v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;


 SELECT DISTINCT *
FROM
  (SELECT to_date(t.START_DATE,'DD.MM.YYYY HH24:MI:SS') Started_Date ,
    s.sid,
    s.username,
    t.status tr_status,
    s.status sess_status,
    NVL(s.program,s.module) Program,
    sq.sql_text
  FROM v$transaction t,
    v$session s,
    v$sql sq
  WHERE t.addr      = s.taddr
  AND s.sql_address = sq.address(+)
  AND t.start_date  < (sysdate-4/24)
  ORDER BY 1
  ); 

Wednesday, June 22, 2016

xvfb-run: extension "RANDR" missing on display

Problem:


Xlib:  extension "RANDR" missing on display

Solution:

/usr/bin/xvfb-run -a "-extension RANDR" ./build.sh

Tuesday, June 21, 2016

Oracle: Duplicate RAC 1 Node Database to RAC 1 Node from Active Database

Prepare Environment:

Grid Listener:


$ cd /usr2/app/grid/11.2.0.4/network/admin

$ cat listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = QA)
      (ORACLE_HOME = /usr2/app/oracle/product/11.2.0.3)
      (SID_NAME = QA)
    )
  )

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


Oracle init File:


$ cat initQA.ora
*.db_name='QA'

Copy password file from source database.


Add auxiliary database into source database tnsname.ora



Start auxiliary database at nomount mode



Duplicate with RMAN

On destination node,
$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"

$ rman target sys/xxxx@csis2:1521/prod auxiliary sys/xxxx@tcsqa

set echo on;
run
{
 allocate channel tgt1 device type disk ;
 allocate channel tgt2 device type disk ;
 allocate auxiliary channel aux1 device type disk ;
 set newname for datafile 147 to '+DATA';
 set newname for datafile 148 to '+DATA';
 duplicate target database to QA from active database
  spfile
   parameter_value_convert 'prod', 'qa', 'PROD', 'QA'
   set control_files='+DATA','+ARC'
   set db_file_name_convert='+DATA/PROD', '+DATA/QA'
   set log_file_name_convert='+DATA','+DATA'
   set cluster_database='false'
   set sga_max_size='3G'
   set sga_target='2G'
   set db_recovery_file_dest='/dbfs/flash_recovery_area/QA'
  logfile
    group 1 ('+DATA') size 500m reuse,
    group 2 ('+DATA') size 500m reuse,
    group 3 ('+DATA') size 500m reuse;
}

Oracle: Duplicate RAC 1 Node Database to RAC 1 Node from Backup

Prepare Environment:

Grid Listener:


$ cd /usr2/app/grid/11.2.0.4/network/admin

$ cat listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = QA)
      (ORACLE_HOME = /usr2/app/oracle/product/11.2.0.3)
      (SID_NAME = QA)
    )
  )

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


Oracle init File:


$ cat initQA.ora
*.audit_file_dest='/usr2/app/oracle/admin/QA/adump'
*.compatible='11.2.0'
*.control_files='+DATA/QA/CONTROLFILE/control01.ctl','+DATA/QA/CONTROLFILE/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert =("+DATA/PROD","+DATA/QA")
*.log_file_name_convert =("+DATA/PROD","+DATA/QA","+ARC","+ARC",)
*.db_name='QA'
*.db_unique_name='QA'
*.diagnostic_dest='/usr2/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=QAX)'
*.event=''
*.job_queue_processes=10
*.log_buffer=46579712# log buffer update
*.nls_date_format='DD-MON-YYYY'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=2500
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=3052M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=1000
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=15744K
*.sec_case_sensitive_logon=FALSE
*.sessions=2048
*.sga_max_size=3840M# internally adjusted
*.sga_target=3G
*.skip_unusable_indexes=TRUE
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/usr1/CSISII/LOGS','/tmp'


Mount the backup to the server used to duplicate database




Add auxiliary database into source database tnsname.ora



Start auxiliary database at nomount mode



Get last thread and sequence number of Source Database Backup


RMAN> list backupset;

Duplicate with RMAN

On destination node,
$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"

$ rman target sys/xxxx@csis2:1521/prod auxiliary sys/xxxx@tcsqa

set echo on
run{
 allocate auxiliary channel C1 device type disk;
 SET UNTIL SEQUENCE 782 thread 1 ;
 duplicate target database to QA;
}

ORA-15124: ASM file name contains an invalid alias name

SQL> startup nomount
ORA-15124: ASM file name '+DATA/QA/CONTROLFILE/control01.ctl,+DATA/QA/CONTROLFILE/control02.ctl' contains an invalid alias name


Solution:
Change Control_files Entry: Single Quote each control file name separately.
Original:
*.control_files='+DATA/CONTROLFILE/control01.ctl,+DATA/CONTROLFILE/control02.ctl'




Correct One:
*.control_files='+DATA/CONTROLFILE/control01.ctl','+DATA/CONTROLFILE/control02.ctl'

Monday, June 20, 2016

Restore control file

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 4009738240 bytes
Fixed Size                  2165512 bytes
Variable Size            2768246008 bytes
Database Buffers         1224736768 bytes
Redo Buffers               14589952 bytes
ORA-00214: control file '/dbfs/data2/ORCL/control02.ctl' version 2077610
inconsistent with file '/dbfs/data1/ORCL/control01.ctl' version 2090226

Solution:
Shutdown the database, then use correct control file to replace corrupted one.

Oracle: Create Directory and Grant Access


create or replace directory oradir as '/path/to/destination';
GRANT READ,WRITE on directory ora_dir to public;
GRANT READ,WRITE on directory ora_dir to user1;

Tuesday, June 14, 2016

Oracle Trouble Shooting Startup Hang

$ truss -Daefo /tmp/startup.txt sqlplus / as sysdba
SQL> startup mount
SQL> alter session set tracefile_identifier='10046';
SQL> Alter session set timed_statistics = true;
SQL> Alter session set statistics_level=all;
SQL> Alter session set max_dump_file_size = unlimited;
SQL> Alter session set events '10046 trace name context forever, level 12';

SQL> alter database open;
-- Wait 5-10 minutes then kill process and shutdown immediate


+++

Please collect the Hanganalyse report from another session to identify the blocking chains

SQL> sqlplus " / as sysdba"
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
-- wait 90 seconds
SQL> oradebug hanganalyze 3
SQL> oradebug tracefile_name
SQL> exit



++ Upload the startup.txt , 10046 trace file and Hang analyze

Thursday, June 2, 2016

Solaris 11 Roll Back Patch

 # pkg info entire
          Name: entire                                         
       Summary: entire incorporation including Support Repository Update (Oracle Solaris 11.2.13.6.0).
   Description: This package constrains system package versions to the same                         
                build.  WARNING: Proper system update and correct package                           
                selection depend on the presence of this incorporation.                             
                Removing this package will result in an unsupported system.  For                    
                more information see:                                                               
                https://support.oracle.com/rs?type=doc&id=1672221.1                                 
      Category: Meta Packages/Incorporations                                                        
         State: Installed                                                                           
     Publisher: solaris                                                                             
       Version: 0.5.11 (Oracle Solaris 11.2.13.6.0)                                                 
 Build Release: 5.11                                                                                
        Branch: 0.175.2.13.0.6.0                                                                    
Packaging Date: August 10, 2015 04:16:29 PM                                                         
          Size: 5.46 kB                                                                             
          FMRI: pkg://solaris/entire@0.5.11,5.11-0.175.2.13.0.6.0:20150810T161629Z    

# beadm list          
BE        Flags Mountpoint Space  Policy Created                      
--        ----- ---------- -----  ------ -------                      
solaris   -     -          21.34M static 2016-05-27 06:46             
solaris-1 NR    /          33.96G static 2016-05-27 07:03  

# beadm activate solaris
# reboot
# pkg info entire
          Name: entire
       Summary: Incorporation to lock all system packages to the same build
   Description: This package constrains system package versions to the same
                build.  WARNING: Proper system update and correct package
                selection depend on the presence of this incorporation.
                Removing this package will result in an unsupported system.
      Category: Meta Packages/Incorporations
         State: Installed
     Publisher: solaris
       Version: 0.5.11
 Build Release: 5.11
        Branch: 0.175.2.0.0.42.0
Packaging Date: June 24, 2014 07:38:32 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/entire@0.5.11,5.11-0.175.2.0.0.42.0:20140624T193832Z