The Fei's Tech Notes: June 2016

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:

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