The Fei's Tech Notes: 2014

Tuesday, December 23, 2014

LDOM: Rescue Virtual Machine

# ldm add-vdsdev /apps/solaris/sol-10-u11-ga-sparc-dvd.iso iso_sol_10_11@primary-vds0

# ldm add-vdisk vdisk_iso iso_sol_10_11@primary-vds0 vmsolaris02

# ldm list-domain

NAME STATE FLAGS CONS VCPU MEMORY UTIL NORM UPTIME

primary active -n-cv- UART 8 8G 0.5% 0.5% 61d 22h 52m

vmsolaris01 active -n---- 5000 64 64G 0.0% 0.0% 60d 23h 8m

vmsolaris02 active -n---- 5001 48 32G 0.2% 0.2% 54d 23h 23m

# ldm list-variable auto-boot? vmsolaris02

auto-boot?=true

# ldm set-var auto-boot\?=false vmsolaris02

# telnet <hostname> 5001

Trying 17.1.20.164...

Connected to sun04.

Escape character is '^]'.

Connecting to console "vmsolaris02" in group "vmsolaris02" ....

Press ~? for control options ..

vmsolaris02 console login:

VNTSD commands

~# --Send break

~^B --Send alternate break

~. --Exit from this console

~w --Force write access

~n --Console next

~p --Console previous

~? --Help

Press ~# to stop ldom

Password: Debugging requested; hardware watchdog suspended.

c)ontinue, s)ync, r)eset? r

Resetting...

NOTICE: Entering OpenBoot.

NOTICE: Fetching Guest MD from HV.

NOTICE: Starting additional cpus.

NOTICE: Initializing LDC services.

NOTICE: Probing PCI devices.

NOTICE: Finished PCI probing.

SPARC T4-2, No Keyboard

OpenBoot 4.36.1, 32.0000 GB memory available, Serial #83577585.

Ethernet address 0:14:4f:fb:4a:f1, Host ID: 84fb4af1.

{0} ok devalias

vdisk_iso /virtual-devices@100/channel-devices@200/disk@1

disk0 /virtual-devices@100/channel-devices@200/disk@0

vnet2 /virtual-devices@100/channel-devices@200/network@0

net /virtual-devices@100/channel-devices@200/network@0

disk /virtual-devices@100/channel-devices@200/disk@0

virtual-console /virtual-devices/console@1

name aliases

{0} ok boot -s /virtual-devices@100/channel-devices@200/disk@1

Boot device: /virtual-devices@100/channel-devices@200/disk@0:a File and args: -s /virtual-devices@100/channel-devices@200/disk@1

krtld: Unused kernel arguments: `/virtual-devices@100/channel-devices@200/disk@1'.

SunOS Release 5.10 Version Generic_147147-26 64-bit

Booting to milestone "milestone/single-user:default".

Hostname: vmsolaris02

Requesting System Maintenance Mode

SINGLE USER MODE

Root password for system maintenance (control-d to bypass):

single-user privilege assigned to /dev/console.

Entering System Maintenance Mode

Dec 23 13:53:47 su: 'su root' succeeded for root on /dev/console

Oracle Corporation SunOS 5.10 Generic Patch January 2005

To reset user role

# rolemod -K type=normal root

UX: rolemod: root is currently logged in, some changes may not take effect until next login.

To reset password

# passwd

# ldm set-var auto-boot\?=true vmsolaris02

Thursday, December 18, 2014

Oracle 11g R2 and T4-1 Performance Tunning

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

In alert log, below entries were found:
Deprecated system parameters with specified values:
log_archive_start

Fix: alter system reset log_archive_start scope=spfile sid='*' ;

======
Performance Tunning with SPFILE

alter system set processes=20000 scope=spfile;
alter system set open_cursors=8000 scope=spfile;

alter system set pga_aggregate_target=13G scope=spfile;
alter system set sga_target=11G scope=spfile;
alter system set sga_max_size=11G scope=spfile;
alter system set memory_target=24G scope=spfile;
alter system set memory_max_target=24G scope=spfile;

alter system set recyclebin=OFF scope=spfile;
alter system set filesystemio_options=setall scope=spfile;

begin
for c in (select sid, serial# from v$session) loop
dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 200);
end loop;
end;
/

Wednesday, December 17, 2014

Oracle 11g R2: export table

ORA-20446: The owner of the job is not registered
ORA-06512: at “SYSMAN.MGMT_JOBS”, line 168
ORA-06512: at “SYSMAN.MGMT_JOBS”, line 86
ORA-06512: at line 1

Cause:

This is an Oracle bug in 11g, there is a fix available, you can use following workaround:

Fix
$ sqlplus /nolog
SQL> connect sysman/<password>
SQL> execute MGMT_USER.MAKE_EM_USER('USERID');

Wednesday, December 10, 2014

Oracle Linux 7: Network Bonding

Ref# http://www.unixmen.com/linux-basics-create-network-bonding-centos-6-5/

Create the bond
# nmcli con add type bond con-name bond0 ifname bond0 mode balance-rr
Add each interface to the bond:
# nmcli con add type bond-slave ifname em3 master bond0
# nmcli con add type bond-slave ifname em4 master bond0

Restart the NetworkManager service
# service NetworkManager restart

Types of Network Bonding

According the to the official documentation, here is the types of network bonding modes.

mode=0 (balance-rr)

Round-robin policy: It the default mode. It transmits packets in sequential order from the first available slave through the last. This mode provides load balancing and fault tolerance.

mode=1 (active-backup)

Active-backup policy: In this mode, only one slave in the bond is active. The other one will become active, only when the active slave fails. The bond’s MAC address is externally visible on only one port (network adapter) to avoid confusing the switch. This mode provides fault tolerance.

mode=2 (balance-xor)

XOR policy: Transmit based on [(source MAC address XOR'd with destination MAC address) modulo slave count]. This selects the same slave for each destination MAC address. This mode provides load balancing and fault tolerance.

mode=3 (broadcast)

Broadcast policy: transmits everything on all slave interfaces. This mode provides fault tolerance.

mode=4 (802.3ad)

IEEE 802.3ad Dynamic link aggregation. Creates aggregation groups that share the same speed and duplex settings. Utilizes all slaves in the active aggregator according to the 802.3ad specification.

Prerequisites:

- Ethtool support in the base drivers for retrieving the speed and duplex of each slave.
- A switch that supports IEEE 802.3ad Dynamic link aggregation. Most switches will require some type of configuration to enable 802.3ad mode.

mode=5 (balance-tlb)

Adaptive transmit load balancing: channel bonding that does not require any special switch support. The outgoing traffic is distributed according to the current load (computed relative to the speed) on each slave. Incoming traffic is received by the current slave. If the receiving slave fails, another slave takes over the MAC address of the failed receiving slave.

Prerequisite:

- Ethtool support in the base drivers for retrieving the speed of each slave.

mode=6 (balance-alb)

Adaptive load balancing: includes balance-tlb plus receive load balancing (rlb) for IPV4 traffic, and does not require any special switch support. The receive load balancing is achieved by ARP negotiation. The bonding driver intercepts the ARP Replies sent by the local system on their way out and overwrites the source hardware address with the unique hardware address of one of the slaves in the bond such that different peers use different hardware addresses for the server.

Friday, November 28, 2014

Oracle Performance Tuning

SQL_ID and CPU Usage
SQL> select * from (
select sql_id, inst_id,
sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU"
from gv$active_session_history cash
where sample_time > sys date -5/(60*24)
group by sql_id, instead
order by 3 desc
) where rownum <11;

Query top 10 SQL_ID's in the last 10 minutes

select * from
(select NVL(SQL_ID,'NULL') AS SQL_ID,
SUM(1) AS "DBTime IN SECONDES" FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sys date - 10/(24*60)
GROUP BY SQL_ID
ORDER BY 2 DESC)
WHERE ROWNUM < 11;

Wait Events Across Nodes
select * from (
select sql_id, inst_id,
sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU",

event , count(distinct(session_id||session_serial#)) as "SESSION COUNT"

from gv$active_session_history cash
where sample_time > sys date -5/(60*24)
group by event,sql_id, instead
order by 3 desc
) where rownum <11;

IO Waits by object from ASH

SELECT TW.*, DO.object_name from (
select current_obj#,
ROUND(SUM(CASE WHEN TIME_WAITED >= 1000000 THEN 1
ELSE 1000000 / TIME_WAITED END)) AS "Estimated IO Waits",
SUM(1) AS "ESTIMATED DBTIME"
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 5/(24*60) and TIME_WAITED > 0
and WAIT_CLASS = 'User I/O'
GROUP BY CURRENT_OBJ#
ORDER BY 2 DESC) TW, DBA_OBJECTS DO
WHERE DO.object_id=TW.current_obj#
and ROWNUM < 11;

Thursday, November 27, 2014

Oracle: Preliminary Connection

Login as oracle
$ sqlplus -prelim "/as sysdba"

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit

$ sqlplus / as sysdba
SQL> startup

Tuesday, November 25, 2014

Oracle VM Manager/VM Server: Migrate virtual machine between in-compatible/in different pool

Shutdown target virutla machine
Create same size virtual disk on target server
Copy the existing virtual disk to replace the file generated in last step
Create network for the target virtual machine
Create new virtual machine with same configuration as original virutal machine
Start up target virtual machine

To Create another Oracle VM Manager to replace existing one:

Copy the UUID of your original Oracle VM Manager from the file /u01/app/oracle/ovm-manager-3/.config.

After mounting the Oracle VM Manager install dvd on the file system of your virtual machine, run the installer as follows: ./runinstaller.sh --uuid <original_manager_uuid>. Select your preferred installation type, as you would with a regular install.

Stop your original Oracle VM Manager.
From the new Oracle VM Manager virtual machine, rediscover your Oracle VM Server and storage, and refresh your storage repository to make sure that that all your VM resources reappear.
After rediscovering the minimal setup, proceed with the installation and configuration of your full Oracle VM environment.

Tuesday, November 18, 2014

Oracle: Check Table Analyzed Time

SELECT t.owner,

       t.table_name AS "Table Name",

       t.num_rows AS "Rows",

       t.avg_row_len AS "Avg Row Len",

       Trunc((t.blocks * p.value)/1024) AS SizeKB,

       to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed"

FROM   dba_tables t,

       v$parameter p

WHERE t.owner like '%PROD'

AND   p.name = 'db_block_size'

ORDER by t.table_name,t.owner,t.last_analyzed desc,t.table_name

/

Friday, November 14, 2014

Tinycore Resources

Install SSH on Tinycore Linux

$ sudo tce-load -iw openssh.tcz
$ sudo cp /usr/local/etc/ssh/sshd_config.example /usr/local/etc/ssh/sshd_config
$ sudo /usr/local/etc/init.d/openssh start
Persist sshd
Append below lines to /opt/.filetool.lst

http://gr8idea.info/os/tutorials/tiny-core/resolution.html
/usr/local/etc/ssh
/etc/passwd
/etc/shadow

Protect user tc with password

Automatically start up sshd
Append below line to /opt/bootlocal.sh
/usr/local/etc/init.d/openssh start

Backup new configurations
$sudo filetool.sh -b

Change Display Resolution

To change the screen resolution, in the menu (right mouse click), go to Tools -> Xvesa Setup. You will see a new window where you can change the screen resolution. Set this as desired, and press "Enter." You will see a new window where you can select the mouse type. Select the appropriate type, and press "Enter." For this to take effect, you need to turn the computer off and restart. To save settings, you must Backup when shutting down Tiny Core.

$ sudo filetool.sh -b

Alternate Methods

Another way to change the screen resolution is to edit the Grub menu. Open the file "menu.lst" in Grub, and add "xsetup" to the end of the line which begins with "kernel /boot/bzImage." After starting the computer, you will be able to select the screen resolution and mouse type. To save these settings, you must Backup when shutting down Tiny Core. Once this is set up, you can remove "xsetup" from Grub.
If using the live CD, when you get to the boot prompt, type "tinycore xsetup" and press enter.

Xorg

If Xorg is installed, Xvesa Setup won't run. To overcome this, remove Xorg from the tce directory, start the computer without it, run Xvesa Setup, then put Xorg back. Restart the computer for it to be loaded.

Friday, November 7, 2014

Oracle DB Optimization

Some info on the Optimizer, Optimizer Mode and the Cost Based Approach: http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#PFGRF001

http://www.dba-oracle.com/art_otn_cbo.htm

SET LINESIZE 300

SELECT t.owner,

       t.table_name AS "Table Name",

       t.num_rows AS "Rows",

       t.avg_row_len AS "Avg Row Len",

       Trunc((t.blocks * p.value)/1024) AS "Size KB",

       to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed"

FROM   dba_tables t,

       v$parameter p

WHERE t.owner like '%PROD'

AND   p.name = 'db_block_size'

ORDER by t.owner,t.last_analyzed desc,t.table_name

/

Tuesday, October 21, 2014

Create Raw Disk

# dd if=/dev/zero of=/dev/rdsk/c0t5000CCA0562BD16Cd0

on Linux
# watch -n5 kill -USR1 $(pgrep ^dd)

On MacBook

# watch -n5 kill -INFO $(pgrep ^dd)

Monday, October 20, 2014

Solaris 11: Hide dot files

cd /export/user1
sudo find . -name '\.*' -exec chmod S+cH {} \;
cd ..
sudo chmod S-cH user1

Friday, October 10, 2014

Oracle VM Manager: Enabling Remote Log Ins

http://docs.oracle.com/cd/E27300_01/E27308/html/vmiug-manager-tcps.html

To enable TCPS connections from a remote Oracle VM Manager:

Enter the following commands on the Oracle VM Manager host to create the keystore:

# cd /u01/app/oracle/ovm-manager-3/bin
# ./secureOvmmTcpGenKeyStore.sh

You are prompted to enter the following information:

Generate OVMM TCP over SSH key store by following steps:
Enter keystore password: password
Re-enter new password: password
What is your first and last name?
  [Unknown]:  name     
What is the name of your organizational unit?
  [Unknown]:  unit       
What is the name of your organization?
  [Unknown]:  organization
What is the name of your City or Locality?
  [Unknown]:  City
What is the name of your State or Province?
  [Unknown]:  State
What is the two-letter country code for this unit?
  [Unknown]:  country_code
Is CN=name, OU=unit, O=organization, L=City, ST=State, C=country_code correct?
  [no]:  yes

Enter key password for <ovmm> 
        (RETURN if same as keystore password): password
Re-enter new password: password

Use the keystore to enable the TCPS service using the secureOvmmTcp.sh script, which is in the same directory as the keystore script above. On the Oracle VM Manager host, enter:

# ./secureOvmmTcp.sh

You are prompted to enter the following information:

Enabling OVMM TCP over SSH service

Please enter the OVM manager user name: username            The local Oracle VM Manager
                                                                  username to use
Please enter the OVM manager user password: password        The local Oracle VM Manager 
                                                                  password to use
Please enter the password for TCPS key store : password     The keystore password created 
                                                                   in the previous script
The job of enabling OVMM TCPS service is committed, please restart OVMM to take effect.

The username and password entered here are the local Oracle VM Manager authentication credentials to use when logging in from the remote Oracle VM Manager instance.

Restart the local Oracle VM Manager instance:

# /sbin/service ovmm stop
# /sbin/service ovmm start

Thursday, October 9, 2014

ZFS NFS Share

`New Sharing`

zfs set sharenfs=rw=host1:host2:host3,ro=host4 pool1

unless you really do need to be exporting the filesystems with root permissions to all those hosts, in which case it's safest to be explicit:

zfs set sharenfs=rw=host1:host2:host3,root=host1:host2:host3,ro=host4 pool1

`Modify Existing NFS sharing on ZFS`

# zfs get share goweek/ishare
NAME         PROPERTY  VALUE  SOURCE
goweek/ishare  share     name=ishare,path=/ishare,prot=smb,csc=auto  local
goweek/ishare  share     name=goweek_ishare,path=/ishare,prot=nfs,sec=sys,root=effectivea.goweekend.ca:effectiveb.goweekend.ca:mtata3.goweekend.ca:blue2.goweekend.ca:biserver.goweekend.ca:blue.goweekend.ca:purple.goweekend.ca,prot=smb  local

# zfs set share=name=goweek,path=/ishare,prot=nfs,anon=65534,sec=sys,root=effectivea.goweekend.ca:effectiveb.goweekend.ca:mtata3.goweekend.ca:blue2.goweekend.ca:vmtata01.goweekend.ca:blue.goweekend.ca:purple.goweekend.ca:wsdev.goweekend.ca,prot=smb goweek/ishare
name=goweek_ishare,path=/ishare,prot=nfs,anon=65534,sec=sys,root=effectivea.goweekend.ca:effectiveb.goweekend.ca:mtata3.goweekend.ca:blue2.goweekend.ca:vmtata01.goweekend.ca:blue.goweekend.ca:purple.goweekend.ca:wsdev.goweekend.ca,prot=smb

Monday, October 6, 2014

Solaris 11: changing the hostname

1.Check the current environment properties:

root@sun1:~# svccfg -s system/identity:node listprop config
config                 application        
config/enable_mapping boolean     true
config/nodename       astring     sun1
config/loopback       astring     sun1

2. Change to new name

root@sun1:~# svccfg -s system/identity:node setprop config/nodename="sun01"
root@sun1:~# svccfg -s system/identity:node setprop config/loopback="sun01"

3. Refresh the properties:

root@sun1:~# svccfg -s system/identity:node refresh

4. Restart the service:

root@sun1:~# svcadm restart system/identity:node

5. Verify changes:

root@sun1:~# svccfg -s system/identity:node listprop config
config                 application        
config/enable_mapping boolean     true
config/nodename       astring     sun01
config/loopback       astring     sun01

6. Reboot to refresh all cached old host name.

Friday, October 3, 2014

Sparc M4000 - Solaris 11.1 Installation and configuration

http://www.oracle.com/technetwork/systems/patches/firmware/release-history-jsp-138416.html

http://docs.oracle.com/cd/E26502_01/html/E28983/gkkvs.html

AVAILABLE DISK SELECTIONS:
       0. c1t1d31 <SUN-UniversalXport-0735 cyl 8 alt 2 hd 64 sec 64>
          /pci@1,700000/SUNW,qlc@0/fp@0,0/ssd@w203400a0b874746e,1f
       1. c2t0d31 <SUN-UniversalXport-0735 cyl 8 alt 2 hd 64 sec 64>
          /pci@3,700000/SUNW,qlc@0/fp@0,0/ssd@w202400a0b874746e,1f
       2. c3t5000C500179375C3d0 <SUN146G cyl 14087 alt 2 hd 24 sec 848> Solaris
          /scsi_vhci/disk@g5000c500179375c3
       3. c3t5000C5001793792Fd0 <SUN146G cyl 14087 alt 2 hd 24 sec 848>
          /scsi_vhci/disk@g5000c5001793792f
Specify disk (enter its number): ^C

bash-3.2# devfsadm
bash-3.2# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
       0. c1t1d31 <SUN-UniversalXport-0735 cyl 8 alt 2 hd 64 sec 64>
          /pci@1,700000/SUNW,qlc@0/fp@0,0/ssd@w203400a0b874746e,1f
       1. c2t0d31 <SUN-UniversalXport-0735 cyl 8 alt 2 hd 64 sec 64>
          /pci@3,700000/SUNW,qlc@0/fp@0,0/ssd@w202400a0b874746e,1f
       2. c3t5000C500179375C3d0 <SUN146G cyl 14087 alt 2 hd 24 sec 848> Solaris
          /scsi_vhci/disk@g5000c500179375c3
       3. c3t5000C5001793792Fd0 <SUN146G cyl 14087 alt 2 hd 24 sec 848>
          /scsi_vhci/disk@g5000c5001793792f
Specify disk (enter its number):

To get {ok} prompt:
XSCF> sendbreak -y -d 0
Send break signal to DomainID 0?[y|n] :y

XSCF> showdomainmode -d 0
Host-ID             :856e6b3e
Diagnostic Level    :min
Secure Mode         :on
Autoboot            :on
CPU Mode            :auto

# init 0

ok: boot cdrom

sparcsrv1 console login: sparcsrv1 console login: sparcsrv1 console login: exit from console.
XSCF> version -c xcp
XSCF#0 (Active )
XCP0 (Current): 1091
XCP1 (Reserve): 1091

XSCF> getflashimage ftp://137.15.140.70/FFXCP1117.tar.gz
0MB received
1MB received
2MB received
...
36MB received
37MB received
38MB received
39MB received
40MB received
41MB received
42MB received
43MB received
Download successful: 44711 Kbytes in 48 secs (918.104 Kbytes/sec)
Checking file...
MD5: e8bf7c11500d71e5a50491bcb67f3357
XSCF> getflashimage -l
Existing versions:
        Version                Size Date
        FFXCP1117.tar.gz   45784699 Wed Oct 01 15:34:51 UTC 2014
XSCF> version -c xcp -v
XSCF#0 (Active )
XCP0 (Current): 1091
OpenBoot PROM : 02.13.0000
XSCF          : 01.09.0001
XCP1 (Reserve): 1091
OpenBoot PROM : 02.13.0000
XSCF          : 01.09.0001
OpenBoot PROM BACKUP
#0: 02.13.0000
#1: --.--.----
XSCF> flashupdate -c check -m xcp -s 1117
XCP update is possible with domains up
XSCF> flashupdate -c update -m xcp -s 1117
The XSCF will be reset. Continue? [y|n] :y
Checking the XCP image file, please wait a minute
XCP update is started (XCP version=1117:last version=1091)
OpenBoot PROM update is started (OpenBoot PROM version=02320000)
OpenBoot PROM update has been completed (OpenBoot PROM version=02320000)
XSCF update is started (XSCFU=0,bank=1,XCP version=1117:last version=1091)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=00:version=01110007:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=00:version=01110007:last version=01090001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=01:version=01110007:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=01:version=01110007:last version=01090001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=02:version=01100001:last version=01080001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=02:version=01100001:last version=01080001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=03:version=01110007:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=03:version=01110007:last version=01090001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=04:version=01110007:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=04:version=01110007:last version=01090001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=05:version=01110004:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=05:version=01110004:last version=01090001)
XSCF download is started (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=07:version=01090001:last version=01090001)
XSCF download has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091, Firmware Element ID=07:version=01090001:last version=01090001)
XSCF update has been completed (XSCFU=0,bank=1,XCP version=1117:last version=1091)
XSCF is rebooting to update the reserve bank
XSCF> Connection to csis2rsc closed by remote host.
XSCF> version -c xcp -v
XSCF#0 (Active )
XCP0 (Reserve): 1091
OpenBoot PROM : 02.13.0000
XSCF          : 01.09.0001
XCP1 (Current): 1117
OpenBoot PROM : 02.32.0000
XSCF          : 01.11.0007
OpenBoot PROM BACKUP
#0: 02.13.0000
#1: 02.32.0000

XSCF> version -c cmu -v
DomainID 0: 02.32.0000
DomainID 1: 02.32.0000
XSB#00-0: 02.13.0000(Current)     00.00.0000(Reserve)
XSB#00-1: 02.13.0000(Current)     00.00.0000(Reserve)
XSB#00-2: 02.13.0000(Current)     00.00.0000(Reserve)
XSB#00-3: 02.13.0000(Current)     00.00.0000(Reserve)

restart Domain
# shutdown -y -g0 -i6

XSCF> console -d 0

XSCF> version -c cmu -v
DomainID 0: 02.32.0000
DomainID 1: 02.32.0000
XSB#00-0: 02.13.0000(Reserve)     02.32.0000(Current)
XSB#00-1: 02.13.0000(Current)     00.00.0000(Reserve)
XSB#00-2: 02.13.0000(Current)     00.00.0000(Reserve)
XSB#00-3: 02.13.0000(Current)     00.00.0000(Reserve)

# pkg update --accept

# stmsboot -e

# rolemod -K type=normal root

# newfs /dev/rdsk/c0t600A0B800074746E00000602542BAB88d0s6

Login as oracle10
$ ./runInstaller -silent -noconfig -responseFile /apps/db_install.rsp

Patch OPatch
p6880880_112000_SOLARIS64.zip

Apply p16056266_112030_SOLARIS64.zip
Unzip it to /var/tmp/patch
$ cd /var/tmp/patch
$ opatch apply

N-Apply: process() done.
Composite patch 16056266 successfully applied.
Locker::release()
OPatch Session completed with warnings.
Log file location: /usr2/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-10-01_16-12-43PM_1.log
Cleaning up the directory : "/usr2/app/oracle/product/11.2.0.3/.patch_storage/patch_unzip"...

OPatch completed with warnings.

# zfs set volsize=20G rpool/swap

Enable rlogin
# inetadm -e rlogin

Grant su to user

# rolemod

# usermod -K type=role root
# usermod -R +root -A +solaris.system.maintenance darrenm
# rolemod -K roleauth=user root
# passwd -N root

If you have made root as role and you want to go back to a traditional direct login capability for root you can do so by simply running:

# rolemod -K type=normal root

Thursday, September 25, 2014

Run scripts @ user logon - Windows

Group Policy Object Editor\User Configuration\Administrative Templates\System\Logon\Run these programs at user logon

Choose Enable, and click button Show, click Add and type in below entry:
cmd /C c:\scripts\atsignon.cmd

Thursday, September 11, 2014

Solaris 11: Create Local IPS

https://blogs.oracle.com/Solaris11Life/entry/solaris_11_and_ips_potential

https://blogs.oracle.com/OTNGarage/entry/how_to_upgrade_an_oracle

pkg set-publisher -G '*' -M '*' -g file:///solaris11.1Repo/repo solaris

pkg install pkg://solaris/x11/library/mesa
pkg install solaris/x11/diagnostic/x11-info-clients

pkg search mesa
pkg search x11-info-clients

Update local repository:
# pkgrecv -s http://pkg.oracle.com/solaris/release/ -d /solaris11.2Repo '*'

to connect to Oracle package repository, you may need set your proxy

# export http_proxy=proxy.goweekend.ca:8008

After you have updated your repository, run the following command to catalog any new packages found in the repository and update all search indexes.

# pkgrepo -s /export/repoSolaris11 refresh

Tuesday, August 12, 2014

Oracle: Change datafile names

Follow below procedure to change data file names, you may need make changes to fit your case.

Procedure:

    Create respect scripts to change data file names at system and database level
    Shutdown Database
    Run script to change data file names at system level
    Run script to change data file names at database level
    Start up database

Practice:

1. Using below statement to generate script to change datafile names in database

SQL> select 'alter database rename file ''' || file_name || '''' || ' to ' || '''' || SUBSTR(file_name, 1,19) || 'pkg' || SUBSTR(file_name, 24) || ''';' from dba_data_files where file_name like '%gw%' order by file_name;

and export the result into renDBfiles.sql

2. Using below statement to generate shell script to rename data files at system level

SQL> select 'mv ' || file_name || ' ' || SUBSTR(file_name, 1,19) || 'pkg' || SUBSTR(file_name, 24) from dba_data_files where file_name like '%gw%' order by file_name;

and export the result into renSysfiles.sh

3. Shutdown Database
SQL> shutdown immediate;

4. Rename data files at system level
$ ./renSysfiles.sh

5. Rename the data files at mount stage (database is not open)
SQL> startup mount;

6. Rename date files at database level
SQL> @renDBfiles.sql

7. restart database
SQL> SHUTDOWN IMMEDIATE
SQL> startup

XServer/Xterm Trouble shooting

keep getting the message: "Server is already active for display 0"

You get an error message like:

Fatal server error:
Server is already active for display 0
 If this server is no longer running, remove /tmp/.X0-lock
 and start again.

The number denotes the display number (in this case 0). This number needs to be unique on the system, so you cannot run two servers on one system with identical display numbers. This message indicates that there is already a server with this number running on the system. You can verify this by running

 ps aux | grep `cat /tmp/.X0-lock`

If you see an output like:

root  2283  0.5  5.1 27796 6536 ?    S  Apr21  59:03 [X]

it indicates that there is indeed an Xserver running under this PID. To start a second server on the same system you have to give it a different dislay number. If you start your servers using startx you can do

startx -- :1

to start a server with display number 1. If you are sure there is no other server running on your system and above ps command indicates that no server with this PID is running, you should remove the file /tmp/.X0-lock by doing (as 'root'):

rm -rf /tmp/.X0-lock

I keep getting the message: "Cannot establish any listening sockets..."

You get an error message like:

_XSERVTransSocketINETCreateListener: ...SocketCreateListener() failed
_XSERVTransMakeAllCOTSServerListeners: server already running
Fatal server error:
Cannot establish any listening sockets - Make sure an X server isn't already running

This problem is very similar to the previous one. You will get this message possibly because the lock file was removed somehow or some other program which doesn't create a lock file is already listening on this port. You can check this by doing a netstat -ln. Xservers usually listen at tcp port 6000+, therefore if you have started your Xserver with the command line option :1 it will be listening on port 6001.
Please check the article above for further information.

Wednesday, August 6, 2014

Sparc Firmware Upgrade

Firmware Downloads
http://www.oracle.com/technetwork/systems/patches/firmware/release-history-jsp-138416.html

Display Firmware Version
-> show /HOST sysfw_version

/HOST
    Properties:
        sysfw_version = Sun System Firmware 8.5.0.a 2014/04/10 12:47

The following procedure describes how to retrieve the new firmware from MyOracle
Support and complete the installation.

PROCEDURE
---------

Assuming all of the prerequisites have been met, the firmware update procedure
can now be executed.

1. To access the latest or desired release of Sun System Firmware from MyOracle
   Support, search on Patches for your hardware platform. Based on your entitlement,
   you will find a list of available releases.

2. Choose the version you are interested in. Older releases of Sun System Firmware
   have been listed using this title: (example)

      Patch 148822-05: FIRMWARE: SPARC T4-1 - Sun System Firmware 8.2.2.c

   Newer releases will use this title format: (example)

      Patch 16588794: FIRMWARE: SPARC T5-2 SUN SYSTEM FIRMWARE 9.0.0.H

3. Click on the release and on that MOS page, click on the "Download" button
    to download a zipped file.

4. Unzip the file and find the .pkg file. On older releases, it will be at the
    top level. Newer releases will include a "Sun System Firwmware" subdirectory
    under a top-level "Firmware" subdirectory. You will find the .pkg file under
    the "Sun System Firmware" subdirectory.

5. The *.pkg file is the image that will be transferred to the Service Processor
    for installation/upgrade purposes. This is accomplished via the included
    "sysfwdownload" utility found under the "Sun System Firmware" subdirectory.

6. From a Solaris terminal window on the system to be upgraded, type the following:

        # ./sysfwdownload -u [image].pkg
        Answer yes/no to the following question:

        WARNING: Host will be powered down for automatic firmware update when download is completed.
        Do you want to continue(yes/no)?

7. The sysfwdownload tool will copy the [image].pkg to the dual bank storage on
    the Service Processor. Then it will automatically shutdown the Solaris host.
    The service processor will automatically load the firmware image onto the
    Service Processor and Host. The system will then automatically reset the
    Service Processor, and when Oracle ILOM reboots, the new Sun System Firmware
    image will be running. The host will re-boot after the installation is completed.

8. Log back into the Solaris host, now run ./sysfwdownload -g and verify the Sun
    System Firmware version is the one you loaded.

        # ./sysfwdownload -g

Sparc Firmware Upgrade

Display Firmware Version
-> show /HOST sysfw_version

/HOST
    Properties:
        sysfw_version = Sun System Firmware 8.5.0.a 2014/04/10 12:47

The following procedure describes how to retrieve the new firmware from MyOracle
Support and complete the installation.

PROCEDURE
---------

Assuming all of the prerequisites have been met, the firmware update procedure
can now be executed.

1. To access the latest or desired release of Sun System Firmware from MyOracle
   Support, search on Patches for your hardware platform. Based on your entitlement,
   you will find a list of available releases.

2. Choose the version you are interested in. Older releases of Sun System Firmware
   have been listed using this title: (example)

      Patch 148822-05: FIRMWARE: SPARC T4-1 - Sun System Firmware 8.2.2.c

   Newer releases will use this title format: (example)

      Patch 16588794: FIRMWARE: SPARC T5-2 SUN SYSTEM FIRMWARE 9.0.0.H

3. Click on the release and on that MOS page, click on the "Download" button
    to download a zipped file.

4. Unzip the file and find the .pkg file. On older releases, it will be at the
    top level. Newer releases will include a "Sun System Firwmware" subdirectory
    under a top-level "Firmware" subdirectory. You will find the .pkg file under
    the "Sun System Firmware" subdirectory.

5. The *.pkg file is the image that will be transferred to the Service Processor
    for installation/upgrade purposes. This is accomplished via the included
    "sysfwdownload" utility found under the "Sun System Firmware" subdirectory.

6. From a Solaris terminal window on the system to be upgraded, type the following:

        # ./sysfwdownload -u [image].pkg
        Answer yes/no to the following question:

        WARNING: Host will be powered down for automatic firmware update when download is completed.
        Do you want to continue(yes/no)?

7. The sysfwdownload tool will copy the [image].pkg to the dual bank storage on
    the Service Processor. Then it will automatically shutdown the Solaris host.
    The service processor will automatically load the firmware image onto the
    Service Processor and Host. The system will then automatically reset the
    Service Processor, and when Oracle ILOM reboots, the new Sun System Firmware
    image will be running. The host will re-boot after the installation is completed.

8. Log back into the Solaris host, now run ./sysfwdownload -g and verify the Sun
    System Firmware version is the one you loaded.

        # ./sysfwdownload -g

Tuesday, August 5, 2014

Windows 2003 Terminal User License Expired

Windows 2003 Terminal User License Expired

1. Stop Terminal Server Licensing in Component Services
2. Remove files in C:\WINDOWS\system32\LServer except Bkup
3. Start Terminal Server Licensing in Component Services

Thursday, July 31, 2014

Solaris 11 Repository

configure proxy
# export https_proxy=http://proxy.goweekend.ca:8888

Update local repository
# pkg set-publisher -G '*' -M '*' -g file:///root/repo/repo solaris
# pkgrecv -s http://pkg.oracle.com/solaris/release/ -d /root/repo/repo '*'

# pkg info kerberos-5

-> if is not installed, you will need to recreate your local repo with Solaris11.1 repo image, then you will be able to install the pkg
* full repository can be downloaded from document "Where to download Oracle Solaris ISO images and Update Releases ( Doc ID 1277964.1 )"
* then follow instructions http://docs.oracle.com/cd/E26502_01/html/E28985/copyrepo1.html#scrolltoc

-> if is installed, in order to update it, you will need to recreate your local repository with the full repo of Solaris 11.1 + SRU 20.5
* SRU iso image can be downloaded from document "Oracle Solaris 11.1 Support Repository Updates (SRU) Index ( Doc ID 1501435.1 )"

Wednesday, July 30, 2014

Solaris 11: Recover pam.conf

Get OK Prompt
1. ssh to ILOM console
2. From the ILOM prompt , type the below.

-> set /HOST send_break_action=break
-> start /SP/console to get to the ok prompt.

Manual system reset from the ILOM prompt.

-> set /HOST/bootmode script="setenv auto-boot? false"
-> reset /SYS

{0} ok probe-scsi-all
This command may hang the system if a Stop-A or halt command
has been executed. Please type reset-all to reset the system
before executing this command.
Do you wish to continue? (y/n) y
/pci@400/pci@2/pci@0/pci@e/scsi@0

FCode Version 1.00.63, MPT Version 2.00, Firmware Version 9.05.00.00

Target 9
Unit 0   Disk   HITACHI H109060SESUN600G A690    1172123568 Blocks, 600 GB
SASDeviceName 5000cca0562b9db8 SASAddress 5000cca0562b9db9 PhyNum 0
Target a
Unit 0   Removable Read Only device   TEAC     DV-W28SS-W       1.0A
SATA device PhyNum 7
Target b
Unit 0   Disk   HITACHI H109060SESUN600G A690    1172123568 Blocks, 600 GB
SASDeviceName 5000cca0562bd16c SASAddress 5000cca0562bd16d PhyNum 1

/pci@400/pci@1/pci@0/pci@b/pci@0/usb@0,2/hub@2/hub@3/storage@2
Unit 0   Removable Read Only device    AMI     Virtual CDROM   1.00

{0} ok devalias

screen                   /pci@400/pci@1/pci@0/pci@0/pci@0/display@0
mouse                    /pci@400/pci@1/pci@0/pci@b/pci@0/usb@0,2/hub@2/device@4/mouse@1
net3                     /pci@500/pci@1/pci@0/pci@5/network@0,1
net2                     /pci@500/pci@1/pci@0/pci@5/network@0
dvd                      /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p7
disk5                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p5
disk4                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p4
disk3                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p3
disk2                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p2
disk1                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p1
disk0                    /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p0
disk                     /pci@400/pci@2/pci@0/pci@e/scsi@0/disk@p0
scsi0                    /pci@400/pci@2/pci@0/pci@e/scsi@0
scsi                     /pci@400/pci@2/pci@0/pci@e/scsi@0
rcdrom                   /pci@400/pci@1/pci@0/pci@b/pci@0/usb@0,2/hub@2/hub@3/storage@2/disk@0
rkeyboard                /pci@400/pci@1/pci@0/pci@b/pci@0/usb@0,2/hub@2/device@4/keyboard@0
rscreen                  /pci@400/pci@1/pci@0/pci@0/pci@0/display@0:r1280x1024x60
net1                     /pci@400/pci@1/pci@0/pci@4/network@0,1
net0                     /pci@400/pci@1/pci@0/pci@4/network@0
net                      /pci@400/pci@1/pci@0/pci@4/network@0
virtual-console          /virtual-devices@100/console@1

{0} ok boot dvd -s

login with username/password   : root/solaris

Mostly the pool name for root file system is (rpool)

# zpool import |grep -i pool:
pool:rpool

# zpool import -f rpool   (to import root pool)
# mkdir /a
# zfs set mountpoint=/a rpool/ROOT/solaris
# zfs mount rpool/ROOT/solaris

Recover pam.conf

set back mount points to original and export the pool

# umount /a
# zfs set mountpoint=/ rpool/ROOT/solaris
# zpool export rpool
# halt

Go back to ILOM
-> set /HOST/bootmode script="setenv auto-boot? true"
-> reset /SYS

Monday, July 28, 2014

Sparc: Assign Static IP Address to NET MGT Port

set /SP hostname=sun-rsc

set /SP system_identifier=sun-t42

Assign a Static IP Address to the NET MGT Port

-> set /SP/network pendingipdiscovery=static
-> set /SP/network pendingipaddress=17.15.20.63
-> set /SP/network pendingipgateway=17.15.20.1
-> set /SP/network pendingipnetmask=255.255.255.0
-> show /SP/network
-> set /SP/network commitpending=true
-> show /SP/network

-> set /SP/clock timezone=America/Toronto

Tuesday, July 22, 2014

Solaris 11: Configure network interface

Setup DNS

Check DNS and make sure domain & search are present

    # cat /etc/resolv.conf
    domain dcs.bbk.ac.uk
    search dcs.bbk.ac.uk
    nameserver      193.61.1.1
    nameserver      193.61.2.2

Update service if required:

    # sudo svccfg
    svc:> select dns/client
    svc:/network/dns/client> setprop config/domain = astring: "dcs.bbk.ac.uk"
    svc:/network/dns/client> setprop config/search = astring: "dcs.bbk.ac.uk"
    svc:/network/dns/client> setprop config/nameserver = net_address: (193.61.1.1 193.61.2.2)
    svc:/network/dns/client> select dns/client:default
    svc:/network/dns/client> refresh
    svc:/network/dns/client> validate
    svc:/network/dns/client> exit

# netadm list

# netadm enable -p ncp DefaultFixed

# dladm show-phys
LINK              MEDIA                STATE      SPEED DUPLEX    DEVICE
net1              Ethernet             unknown    0      unknown   igb1
net3              Ethernet             unknown    0      unknown   igb3
net0              Ethernet             up         100    full      igb0
net2              Ethernet             unknown    0      unknown   igb2
net4              Ethernet             up         10     full      usbecm2

# ipadm create-ip net1
# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --
net0       ip       ok       yes    --
net1       ip       down     no     --
net4       ip       ok       yes    --
# ipadm create-addr -T static -a 13.5.210.16/24 net1/sun     13.5.210.16/24
net4/v4           static   ok           169.254.182.77/24
lo0/v6            static   ok           ::1/128
net0/v6           addrconf ok           fe80::210:e0ff:fe57:ebe4/10

# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --
net0       ip       ok       yes    --
net1       ip       ok       yes    --
net4       ip       ok       yes    --
# ipadm show-addr
ADDROBJ           TYPE     STATE        ADDR
lo0/v4            static   ok           127.0.0.1/8
net0/v4           static   ok           13.5.210.6/24
net1/sun         static   ok           13.5.210.16/24
net4/v4           static   ok           169.254.182.77/24
lo0/v6            static   ok           ::1/128
net0/v6           addrconf ok           fe80::210:e0ff:fe57:ebe4/10

# nscfg export svc:/network/dns/client:default

Tuesday, July 15, 2014

Confluence Development

http://stackoverflow.com/questions/14585365/how-do-i-create-text-file-for-confluence-and-then-import-it-as-a-confluence-wiki

import sys
import xmlrpc.client
import os
import re

# Connects to confluence server with username and password
site_URL = "YOUR_URL"
server = xmlrpc.client.ServerProxy(site_URL + "/rpc/xmlrpc")

username = "YOUR_USERNAME"
pwd = "YOUR_PASSWORD" 
token = server.confluence2.login(username, pwd)

# The space you want to add a page to
spacekey = "YOUR_SPACENAME"

# Retrives text from a file
f = open('FileName.txt', 'r')
content = f.read()
f.close()

# Creates a new page to insert in the new space from text file content
newpage = {"title":"NEW_PAGENAME", "space":spacekey, "content":content}
server.confluence2.storePage(token, newpage)

server.confluence2.logout(token)

Securing Oracle Database

http://docs.oracle.com/cd/B28359_01/network.111/b28531/guidelines.htm#CHDGIEJE

http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm

Monday, July 14, 2014

Oracle SQLPLUS change output format

set feedback off
set trimspool on
set pagesize 10000
set linesize 1000
set heading off

Friday, July 11, 2014

Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=550009615142183&id=837570.1&_afrWindowMode=0&_adf.ctrl-state=igytrxho8_97

Determine PFILE or SPFILE is used to start up database.

SQL> show parameter spfile;
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Wednesday, July 9, 2014

Solaris: Set Up Automatic Data Collection

Become the root role.
Run the svcadm enable system/sar:default command.This version of the sadc command writes a special record that marks the time when the counters are reset to zero (boot time).
Edit the /var/spool/cron/crontabs/sys crontab file.

Note - Do not edit a crontab file directly. Instead, use the crontab -e command to make changes to an existing crontab file.
```
# crontab -e sys
```

Uncomment the following lines:

0 * * * 0-6 /usr/lib/sa/sa1
20,40 8-17 * * 1-5 /usr/lib/sa/sa1
5 18 * * 1-5 /usr/lib/sa/sa2 -s 8:00 -e 18:01 -i 1200 -A

Enable Audit in Oracle Database

select * from DBA_STMT_AUDIT_OPTS;

select * from DBA_OBJ_AUDIT_OPTS;

select * from DBA_AUDIT_TRAIL;

AUDIT ALL BY USER BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY USER BY ACCESS;

SELECT * FROM audit_actions;

NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE;

NOAUDIT ALL;

TRUNCATE TABLE AUD$;

Oracle: Find Oracle User Session ID with OS process id

SQL> select * from v$process;

SQL> SELECT a.sid, b.spid FROM v$session a, v$process b
WHERE a.paddr = b.addr
and b.spid = 10099;

Solaris: Calculate Memory Consumed by project

# ps -e -o pid,user,project,rss,vsz,zone,comm >/tmp/3-9309294501_ps.out

# ipcs -mAZ >/tmp/3-9309294501_ipcs.out

# echo "::walk proc p| ::print -t proc_t p_cred |::print struct cred cr_projid|::grep ".==0t101" |::eval '<p=K'" |mdb -k > /tmp/3-9309294501_mdb_ora_procs.out

# echo "::walk proc p| ::print -t proc_t p_cred |::print struct cred cr_projid|::grep ".==0t101" |::eval '<p=K' |::pmap" | mdb -k

# echo "::cat /tmp/3-9309294501_mdb_ora_procs.out|::pmap; ::cat /tmp/3-9309294501_mdb_ora_procs.out |::ps" | mdb -k >/tmp/3-9309294501_pmap.out

# cat 3-9309294501_pmap.out |egrep -v '(^S|^R|^Z)'| awk '{print $3}' | grep -v SIZE | sed -e 's/k//g' | awk '$1 < 1500000 {x=x+$1} END {print x/1024/1024}'

# prstat -J -c 1 1
# prstat -a -s rss -c 1 1

Thursday, July 3, 2014

Oracle Fix Missing Datafile

To fix the missing user tablespace,
1. Create a new tablespace
2. Drop the old tablespace
3. Change the new tablespace name to old tablespace name
4. Change affected user default tablespace to new one.

Example:
1. Create a new tablespace
create tablespace USERS datafile '/data6/GOWEEK/USERSPACE.dbf' size 200M autoextend on next 500k maxsize 500m;

2. Drop the old tablespace
DROP TABLESPACE USERSPACE INCLUDING CONTENTS AND DATAFILES;

3. Change new table space name
ALTER TABLESPACE USERS RENAME TO USERSPACE;

4. Use below script to change user default table space, you may need to change the statement

SQL> select 'ALTER USER '||username||' DEFAULT TABLESPACE USERSPACE;' FROM DBA_USERS WHERE DEFAULT_TABLESPACE like '%_$%' and username not in('SYS','SYSTEM');

Reference:

select * from dba_data_files;

select * from dba_users;

select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

Oracle: Change datafile names

1. Using below statement to generate script to change datafile names in database

SQL> select 'alter database rename file ''' || file_name || '''' || ' to ' || '''' || SUBSTR(file_name, 1,19) || 'pfr' || SUBSTR(file_name, 24) || ''';' from dba_data_files where file_name like '%KEYWORD%' order by file_name;

and export the result into renDBfiles.sql

2. Using below statement to generate shell script to rename data files at system level

SQL> select 'mv ' || file_name || ' ' || SUBSTR(file_name, 1,19) || 'pfr' || SUBSTR(file_name, 24) from dba_data_files where file_name like '%KEYWORD%' order by file_name;

and export the result into renSysfiles.sh

3. Shutdown Database
SQL> shutdown immediate;

4. Rename data files at system level
$ ./renSysfiles.sh

5. Rename the data files at mount stage (database is not open)
SQL> startup mount;

6. Rename date files at database level
SQL> @renDBfiles.sql

7. Startup database
SQL> startup

Friday, June 27, 2014

Backing Up Control Files

Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two options:

    Back up the control file to a binary file (duplicate of existing control file) using the following statement:

    ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

    Produce SQL statements that can later be used to re-create your control file:

    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This command writes a SQL script to a trace file where it can be captured and edited to reproduce the control file. View the alert log to determine the name and location of the trace file.

Monday, June 23, 2014

Soalrs/Oracle: Out of memory to start up Oracle DB instance

SQL> startup
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument

# cat /etc/project

Create new project for user oracle
# projadd -U oracle -K "project.max-shm-memory=(priv,16GB,deny)" user.oracle

or Modify existing prjoect
# projmod -s -K "project.max-shm-memory=(priv,16GB,deny)" user.oracle
user.oracle

Activate user.oracle
# newtask -v -p user.oracle

# prctl -i project user.oracle

Restart the database instance
$ prctl -i project user.oracle
project: 100: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-contracts
        privileged      10.0K       -   deny                                 -
        system          2.15G     max   deny                                 -
project.max-locked-memory
        usage           1.52GB
        system          16.0EB    max   deny                                 -
project.max-port-ids
        privileged      8.19K       -   deny                                 -
        system          65.5K     max   deny                                 -
project.max-shm-memory
        privileged      16.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
project.max-shm-ids
        privileged        128       -   deny                                 -
        system          16.8M     max   deny                                 -
project.max-msg-ids
        privileged        128       -   deny                                 -
        system          16.8M     max   deny                                 -
project.max-sem-ids
        privileged        128       -   deny                                 -
        system          16.8M     max   deny                                 -
project.max-crypto-memory
        usage               0B
        privileged      15.7GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
project.max-tasks
        usage               1
        system          2.15G     max   deny                                 -
project.max-processes
        usage              20
        system          2.15G     max   deny                                 -
project.max-lwps
        usage              59
        system          2.15G     max   deny                                 -
project.cpu-cap
        usage               0
        system          4.29G     inf   deny                                 -
project.cpu-shares
        usage               1
        privileged          1       -   none                                 -
        system          65.5K     max   none                                 -
zone.max-lofi
        usage               0
        system          18.4E     max   deny                                 -
zone.max-swap
        usage           27.5GB
        system          16.0EB    max   deny                                 -
zone.max-locked-memory
        usage           15.7GB
        system          16.0EB    max   deny                                 -
zone.max-shm-memory
        system          16.0EB    max   deny                                 -
zone.max-shm-ids
        system          16.8M     max   deny                                 -
zone.max-sem-ids
        system          16.8M     max   deny                                 -
zone.max-msg-ids
        system          16.8M     max   deny                                 -
zone.max-processes
        usage             394
        system          2.15G     max   deny                                 -
zone.max-lwps
        usage           5.21K
        system          2.15G     max   deny                                 -
zone.cpu-cap
        usage               0
        system          4.29G     inf   deny                                 -
zone.cpu-shares
        usage               1
        privileged          1       -   none                                 -
        system          65.5K     max   none                                 -