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;
Friday, June 24, 2016
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.shTuesday, June 21, 2016
Oracle: Duplicate RAC 1 Node Database to RAC 1 Node from Active Database
Prepare Environment:
$ 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
$ cat initQA.ora
*.db_name='QA'
Copy password file from source database.
$ 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;
}
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:
$ 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
$ 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'
RMAN> list backupset;
$ 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;
}
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'
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.
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
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
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
Subscribe to:
Posts (Atom)