Shrink a datafile

March 11, 2014March 11, 2014 maxmaiaLeave a comment

The smallest size a datafile can be resized:

set lines 190 pages 200 column resize_command format a90


column value new_val block_size

select value from v$parameter where name = 'db_block_size';
select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&&block_size)/1024/1024 )||'M;' resize_command

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+) order by file_name;
RESIZE_COMMAND

--------------------------------------------------------------------------------

alter database datafile 'B:\ORADATA\PSAPF0P.DBF' resize 800M;

alter database datafile 'B:\ORADATA\PSAPUNDO.DBF' resize 53M;

alter database datafile 'B:\ORADATA\SYSAUX01.DBF' resize 574M;

alter database datafile 'B:\ORADATA\USERS01.DBF' resize 602M;

alter database datafile 'C:\ORACLE\ORADATA\TST\PSAPSR3.DBF' resize 1M;

alter database datafile 'C:\ORACLE\ORADATA\TST\SYSTEM01.DBF' resize 369M;

6 rows selected.

Oracle books

March 10, 2014March 10, 2014 maxmaiaLeave a comment

Couple of Oracle interesting books.

Find ENTERPRISE MANAGER DATABASE 12c used port

March 7, 2014March 7, 2014 maxmaiaLeave a comment

How to find port used by Enterprise Manager, eg. https://localhost:5500/em

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT() ----------------------------- 0

Elapsed: 00:00:07.25
SQL> select dbms_xdb_config.gethttpsport() from dual;
DBMS_XDB_CONFIG.GETHTTPSPORT() ------------------------------ 5500

Linux – current CPU frequency

March 7, 2014April 14, 2014 maxmaiaLeave a comment

# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 58
Model name: Intel(R) Core(TM) i5-3320M CPU @ 2.60GHz
Stepping: 9
CPU MHz: 1868.546
CPU max MHz: 3300.0000
CPU min MHz: 1200.0000
BogoMIPS: 5188.19
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 3072K
NUMA node0 CPU(s): 0-3

or:
# cat /sys/devices/system/cpu/cpu0/cpufreq/cpuinfo_cur_freq
1378609

[max@localhost ~]$ sar -m CPU 1 1
Linux 3.13.6-200.fc20.x86_64 (localhost.localdomain) 04/14/2014 _x86_64_ (4 CPU)

01:43:31 PM CPU MHz
01:43:32 PM all 2299.54
Average: all 2299.54

Find CPU fan speed:

[max@localhost ~]$ sar -m FAN 1 1
Linux 3.13.6-200.fc20.x86_64 (localhost.localdomain) 04/14/2014 _x86_64_ (4 CPU)

01:43:45 PM FAN rpm drpm DEVICE
01:43:46 PM 1 3768.00 3768.00 thinkpad-isa-0000
Average: 1 3768.00 3768.00 thinkpad-isa-0000

Find CPU temperature:

[max@localhost ~]$ sar -m TEMP 1 1
Linux 3.13.6-200.fc20.x86_64 (localhost.localdomain) 04/14/2014 _x86_64_ (4 CPU)

01:45:53 PM TEMP degC %temp DEVICE
01:45:54 PM 1 44.00 0.00 acpitz-virtual-0
01:45:54 PM 2 44.00 50.57 coretemp-isa-0000
01:45:54 PM 3 39.00 44.83 coretemp-isa-0000
01:45:54 PM 4 43.00 49.43 coretemp-isa-0000
01:45:54 PM 5 44.00 0.00 pkg-temp-0-virtual-

Average: TEMP degC %temp DEVICE
Average: 1 44.00 0.00 acpitz-virtual-0
Average: 2 44.00 50.57 coretemp-isa-0000
Average: 3 39.00 44.83 coretemp-isa-0000
Average: 4 43.00 49.43 coretemp-isa-0000
Average: 5 44.00 0.00 pkg-temp-0-virtual-

Check SAP version from SQL*Plus

March 7, 2014March 7, 2014 maxmaiaLeave a comment

set lines 190 pages 200 col COMPONENT for a25 col COMP_TYPE for a15 select distinct 'select * from '||owner||'.svers;' from dba_tables where table_name='USR02'; select distinct 'select * from '||owner||'.cvers;' from dba_tables where table_name='USR02';


select * from SAPSR3.svers;
VERSION

----------

731         -- is Netweaver 7.31
select * from SAPSR3.cvers;

COMPONENT RELEASE EXTRELEASE COMP_TYPE ------------- --------------- ------------- ----------- PI_BASIS 731 0000000000 X SAP_ABA 731 0000000000 S SAP_BASIS 731 0000000000 S SAP_BW 731 0000000000 W ST-PI 2008_1_710 0000000004 X

SSH passwordless between servers

March 7, 2014 maxmaiaLeave a comment

1. on both servers execute:
ssh-keygen -t rsa ssh-keygen -t dsa cd /home/oracle/.ssh cp authorized_keys authorized_keys.`date -Ins` cat id_rsa.pub >> authorized_keys; cat id_dsa.pub >> authorized_keys chmod 600 authorized_keys
2. then copy and combine both authorized_keys on both servers to be the same

Fast parallel ZIP compression with PIGZ

March 7, 2014July 28, 2014 maxmaiaLeave a comment

Compress an entire directory using all CPU threads, monitoring the progress:

tar -c DIR1 | pv --size `du -csh DIR1 | grep total | cut -f1` | pigz -9 > DIR1.gz

Uncompress:
tar -zxvf DIR1.gz

Table growth monitoring

March 6, 2014March 6, 2014 maxmaiaLeave a comment

SQL to display change size history of a segment:

column owner format a16 column object_name format a36 column start_day format a11 column block_increase format 9999999999

select obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD hh24') start_day, sum(a.db_block_changes_delta) block_changes, sum(a.SPACE_USED_DELTA) SPACE_USED_DELTA, sum(a.SPACE_ALLOCATED_DELTA) SPACE_ALLOCATED from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj where sn.snap_id = a.snap_id and obj.object_id = a.obj# and obj.owner ='&owner' and obj.object_name ='&seg_name' and end_interval_time between to_timestamp('01-JAN-1999','DD-MON-RRRR') and to_timestamp('01-JAN-2999','DD-MON-RRRR') group by obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD hh24') order by start_day desc ;

--------------------------------------------------------------- |START_DAY |BLOCK_CHANGES|SPACE_USED_DELTA|SPACE_ALLOCATED| --------------------------------------------------------------- |2014-MAR-05 04| 0 | 0 | 0 | |2014-MAR-05 03| 87,520 | 5,141,804-| 0 | |2014-MAR-05 02| 351,184 | 18,512,873-| 0 | |2014-MAR-05 01| 545,920 | 49,948,868 | 67,108,864 | |2014-MAR-05 00| 606,368 | 68,297,877 | 0 | |2014-MAR-04 22| 616,480 | 19,457,197-| 0 | |2014-MAR-03 22| 96,354,576 | 956,926,956 | 1,080,033,280 | |2014-MAR-03 21| 163,731,904 | 1,735,144,707 | 1,990,197,248 | |2014-MAR-03 20| 209,874,400 | 2,363,545,288 | 2,668,625,920 | |2014-MAR-03 19| 68,288,784 | 791,862,298 | 978,321,408 | |2014-MAR-03 16| 3,536 | 176,949 | 0 | |2014-MAR-03 15| 6,784 | 257,722 | 0 | |2014-MAR-03 14| 9,744 | 211,441-| 0 | |2014-MAR-03 12| 4,320 | 279,758 | 0 | |2014-MAR-03 00| 76,592 | 2,376,594 | 0 | |2014-MAR-02 23| 176,256 | 5,067,188 | 0 | |2014-MAR-02 22| 249,056 | 8,890,419 | 0 | |2014-MAR-02 21| 172,784 | 5,782,929 | 0 | |2014-MAR-02 20| 150,832 | 5,998,138 | 0 |

Conteaza destinatia, dar si calatoria!

Category: Oracle