Monday, February 28, 2011

ORA-32004, ORA-32004 and ORA-07286 all in one

I was trying enable archive log for my database when I made a typo and entered the wrong path. When I tried to start database, I got ORA-32004, ORA-32004 and ORA-07286 all in one error message.

Googled around and found something similar at:
http://savaskulah.wordpress.com/2010/09/05/log_archive_dest_2-destination-string-cannot-be-translated/

Here is how I got the error message in the first place. The highlighted path did not exit, and that is what caused all the bother.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5268046048 bytes
Database Buffers         4999610368 bytes
Redo Buffers               18743296 bytes
Database mounted.

SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
TESTGM    NOARCHIVELOG

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletestgm.ora


SQL> alter system set log_archive_start=TRUE
  2  scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1='location=/gm_oraarc/testgm' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
IBM AIX RISC System/6000 Error: 2: No such file or directory
SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
IBM AIX RISC System/6000 Error: 2: No such file or directory
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Here is what I did to fix the issue:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 28 13:54:56 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> create pfile from spfile;

File created.

SQL> quit
Disconnected


I then edited the inittestgm.ora file under $ORACLE_HOME/dbs , and corrected the archive log destination path of *.log_archive_dest_1='location=/gm_oraarch/testgm'

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 28 14:27:11 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup nomount pfile='$ORACLE_HOME/dbs/inittestgm.ora';
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5268046048 bytes
Database Buffers         4999610368 bytes
Redo Buffers               18743296 bytes
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


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

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5268046048 bytes
Database Buffers         4999610368 bytes
Redo Buffers               18743296 bytes
Database mounted.
Database opened.
SQL> SELECT value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgm.ora

SQL>



Monday, February 21, 2011

How to find the Network Interface Card(Network Adapter) information on AIX7.1

isvp17> lsattr -El ent0
alt_addr      0x000000000000   Alternate Ethernet address                True
flow_ctrl     no               Request Transmit and Receive Flow Control True
jumbo_frames  no               Request Transmit and Receive Jumbo Frames True
large_receive yes              Enable receive TCP segment aggregation    True
large_send    yes              Enable hardware Transmit TCP segmentation True
media_speed   Auto_Negotiation Requested media speed                     True
multicore     yes              Enable Multi-Core Scaling                 True
rx_cksum      yes              Enable hardware Receive checksum          True
rx_cksum_errd yes              Discard RX packets with checksum errors   True
rx_clsc       1G               Enable Receive interrupt coalescing       True
rx_clsc_usec  95               Receive interrupt coalescing window       True
rx_coalesce   16               Receive packet coalescing                 True
rx_q1_num     8192             Number of Receive queue 1 WQEs            True
rx_q2_num     4096             Number of Receive queue 2 WQEs            True
rx_q3_num     2048             Number of Receive queue 3 WQEs            True
tx_cksum      yes              Enable hardware Transmit checksum         True
tx_isb        yes              Use Transmit Interface Specific Buffers   True
tx_q_num      512              Number of Transmit WQEs                   True
tx_que_sz     8192             Software transmit queue size              True
use_alt_addr  no               Enable alternate Ethernet address         True

isvp17> lsdev |grep -i ethernet
en0        Available          Standard Ethernet Network Interface
en1        Defined            Standard Ethernet Network Interface
ent0       Available          Logical Host Ethernet Port (lp-hea)
ent1       Available          Virtual I/O Ethernet Adapter (l-lan)
et0        Defined            IEEE 802.3 Ethernet Network Interface
et1        Defined            IEEE 802.3 Ethernet Network Interface
lhea0      Available          Logical Host Ethernet Adapter (l-hea)
isvp17>

How to find the HBA information on an AIX7.1 server

Run the following command on the host systems. The highlighted area tells us that we have Virtual Fibre Channel Adapter, which mean we are vios client and the adapter is a virtual that was provided by the vios server.


isvp17> lscfg -vpl fcs0
  fcs0             U8233.E8B.065D51P-V1-C36-T1  Virtual Fibre Channel Client Adapter

        Network Address.............C05076037CF00000
        ROS Level and ID............
        Device Specific.(Z0)........
        Device Specific.(Z1)........
        Device Specific.(Z2)........
        Device Specific.(Z3)........
        Device Specific.(Z4)........
        Device Specific.(Z5)........
        Device Specific.(Z6)........
        Device Specific.(Z7)........
        Device Specific.(Z8)........C05076037CF00000
        Device Specific.(Z9)........
        Hardware Location Code......U8233.E8B.065D51P-V1-C36-T1


  PLATFORM SPECIFIC

  Name:  vfc-client
    Node:  vfc-client@30000024
    Device Type:  fcp
    Physical Location: U8233.E8B.065D51P-V1-C36-T1

Now, let us log onto the vios server itself, and see what kind on HBA adapter it is using to connect to the storage system. This tells us that the adapter that we are using a dual port 8Gb adapter.
telnet (vios server name)

IBM Virtual I/O Server

login: padmin
padmin's Password:
Last unsuccessful login: Mon Jan 24 09:13:56 MST 2011 on /dev/pts/0 from sig-9-65-56-25.mts.ibm.com
Last login: Sun Feb 20 14:34:26 MST 2011 on /dev/pts/0 from 9.57.85.97

$ oem_setup_env
# lscfg -vpl fcs0
  fcs0             U78A0.001.DNWK129-P1-C1-T1  8Gb PCI Express Dual Port FC Adapter (df1000f114108a03)

        Part Number.................10N9824
        Serial Number...............1B046059F8
        Manufacturer................001B
        EC Level....................D77040
        Customer Card ID Number.....577D
        FRU Number..................10N9824
        Device Specific.(ZM)........3
        Network Address.............10000000C9AA5388
        ROS Level and ID............02781174
        Device Specific.(Z0)........31004549
        Device Specific.(Z1)........00000000
        Device Specific.(Z2)........00000000
        Device Specific.(Z3)........09030909
        Device Specific.(Z4)........FF781116
        Device Specific.(Z5)........02781174
        Device Specific.(Z6)........07731174
        Device Specific.(Z7)........0B7C1174
        Device Specific.(Z8)........20000000C9AA5388
        Device Specific.(Z9)........US1.11X4
        Device Specific.(ZA)........U2D1.11X4
        Device Specific.(ZB)........U3K1.11X4
        Device Specific.(ZC)........000000EF
        Hardware Location Code......U78A0.001.DNWK129-P1-C1-T1


  PLATFORM SPECIFIC

  Name:  fibre-channel
    Model:  10N9824
    Node:  fibre-channel@0
    Device Type:  fcp
    Physical Location: U78A0.001.DNWK129-P1-C1-T1

Update: I also found a great tool called "hbainfo" at http://www.tablespace.net/hbainfo/ I tested it on 6.1 as well as 7.1, and it works great on both. The o/p below is from 6.1 though.



# uname -a
AIX sonasisvp1_ora1 1 6 00F61CE14C00
# ./hbainfo
Total Adapters:                 2
This Adapter Index:             0
Adapter Name:                   com.ibm-df1000f114108a0-1
Manufacturer:                   IBM
SerialNumber:                   1C00908221
Model:                          df1000f114108a0
Model Description:              FC Adapter
HBA WWN:                        20000000C99D9E4C
Node Symbolic Name:
Hardware Version:
Driver Version:                 6.1.4.6
Option ROM Version:             02781174
Firmware Version:               111304
Vendor Specific ID:             0
Number Of Ports:                1
Driver Name:                    /usr/lib/drivers/pci/efcdd
Port Index:                     0
Node WWN:                       20000000C99D9E4C
Port WWN:                       10000000C99D9E4C
Port Fc Id:                     330496
Port Type:                      Fabric
Port State:                     Operational
Port Symbolic Name:
OS Device Name:                 fcs0
Port Supported Speed:           Unknown - transceiver incable of reporting
Port Speed:                     Unknown - transceiver incable of reporting
Port Max Frame Size:            2112
Fabric Name:                    10000005336C6CF9
Number of Discovered Ports:     2
Seconds Since Last Reset:       0
Tx Frames:                      159
Tx Words:                       9
Rx Frames:                      313
Rx Words:                       20
LIP Count:                      0
NOS Count:                      0
Error Frames:                   0
Dumped Frames:                  0
Link Failure Count:             0
Loss of Sync Count:             5
Loss of Signal Count:           0
Primitive Seq Protocol Err Cnt: 0
Invalid Tx Word Count:          16
Invalid CRC Count:              0
#



Thursday, February 17, 2011

cp : 0653-447 Requested a write of 4096 bytes, but wrote only 3584.

As user oracle I was trying to copy the Oracle binaries which I had downloaded and were under /home/root on an AIX7.1 machine.

$ cp -p /home/root/aix.ppc64_11gR2_database_1of2.zip .
cp : 0653-447 Requested a write of 4096 bytes, but wrote only 3584.
$ ulimit -a
time(seconds)        unlimited
file(blocks)         2097151
data(kbytes)         131072
stack(kbytes)        32768
memory(kbytes)       32768
coredump(blocks)     2097151
nofiles(descriptors) 2000
threads(per process) unlimited
processes(per user)  unlimited

$ ulimit -f unlimited
ksh: ulimit: 0403-045 The specified value is outside the user's allowable range.
Edited the /etc/security/limits file and added fsize = -197151 for oracle user as shown below.
isvp18> vi /etc/security/limits

pconsole:
stack_hard = 131072
data = 1280000
data_hard = 1280000

oracle:
fsize = -197151


isvp18> su - oracle
$ ulimit -f unlimited
$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         131072
stack(kbytes)        32768
memory(kbytes)       32768
coredump(blocks)     2097151
nofiles(descriptors) 2000
threads(per process) unlimited
processes(per user)  unlimited

$ cp /home/root/aix.ppc64_11gR2_database_1of2.zip .
$ cp /home/root/aix.ppc64_11gR2_database_2of2.zip .
$ cksum /home/root/aix.ppc64_11gR2_database_1of2.zip
1915658395 1564425851 /home/root/aix.ppc64_11gR2_database_1of2.zip
$ cksum aix.ppc64_11gR2_database_2of2.zip
1152318705 1007010341 aix.ppc64_11gR2_database_2of2.zip

Copy went through fine at the end :-)

Wednesday, February 16, 2011

mount: 0506-324 Cannot mount /dev/lv03 on /oraarch: The media is not formatted or the format is not correct.

I was trying to mount one of my file systems on AIX7.1 while I got this error message

isvp18> mount /oraarch
Replaying log for /dev/lv03.
mount: 0506-324 Cannot mount /dev/lv03 on /oraarch: The media is not formatted or the format is not correct.
0506-342 The superblock on /dev/lv03 is dirty.  Run a full fsck to fix.

So I ran fsck on the file system as suffested, and answered Yes to the question that fsck asked.
isvp18> fsck /oraarch



** Checking /dev/rlv03 (/oraar)
** Phase 0 - Check Log
log redo processing for /dev/rlv03
** Phase 1 - Check Blocks and Sizes
** Phase 2 - Check Pathnames
** Phase 3 - Check Connectivity
** Phase 4 - Check Reference Counts
** Phase 5 - Check Inode Map
** Phase 6 - Check Block Map
Bad Block Map; SALVAGE? yes
** Phase 6b - Salvage Block Map
Superblock is marked dirty; FIX? yes
9 files 1528152 blocks 191934120 free
***** Filesystem was modified *****


Tried to mount the file system again after the fsck completed, and it worked fine.
isvp18> mount /oraarch
isvp18> df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4       10240.00   3502.43   66%    52007     7% /
/dev/hd2       16896.00  14625.86   14%    44235     2% /usr
/dev/hd9var      512.00    215.45   58%     5765    11% /var
/dev/hd3        3584.00   3332.43    8%      483     1% /tmp
/dev/hd1       15360.00   7167.52   54%     3716     1% /home
/dev/hd11admin    256.00    255.61    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt     512.00    327.88   36%     7015     9% /opt
/dev/livedump    256.00    255.64    1%        4     1% /var/adm/ras/livedump
/dev/lv02      94464.00  93567.66    1%       21     1% /oralog
/dev/fslv00    94464.00  92401.49    3%       22     1% /oradata
/dev/bkuplv00   9440.00   7707.68   19%       28     1% /bak/test/home/test
vanhalen:/vanhalen/tools    512.00    486.43    5%      541     1% /testlab/tools
/dev/lv03      94464.00  93717.83    1%       18     1% /oraarch

Accessing Oracle database volumes that were backedup using FlashCopy feature of the Storwize V7000

Backing up an Oracle database using FlashCopy is all very simple. The Storwize GUI is really cool, and with
a click of a few buttons we are up and going.

The tricky part is once the backup has completed. The new volumes now have the LVM data structures, which means now they will belong to the original volume group as the source volumes.

Let see how all this happens. In my case, I had my Oracle file on the physical disks hdisk1, hdisk2 , and hdisk2. The physical volumes each belonged to volume groups test_copy1, test_copy2, and test_copy3 respectively. I have created 3 file systems /oradata , /oralog , and /oraarch on these volume groups, to be used by Oracle.

After the FlashCopy Backup/Clone on the volumes complete, we see that the new volumes belong to the volume groups that they are clones of.
svp18> lspv
hdisk0          00f65d52a5abd459                    rootvg          active
hdisk2          00f65d5108ffefb6                    test_copy2
hdisk3          00f65d51bfba4e2e                    test_copy3
hdisk4          00f65d51c465f8eb                    metro
hdisk5          00f65d52ce125f70                    swap            active
hdisk1          00f65d5108ff9043                    test_copy1
hdisk6          00f65d51c465f8eb                    metro
hdisk7          00f65d5108ff9043                    test_copy1
hdisk8          00f65d5108ffefb6                    test_copy2
hdisk9          00f65d51bfba4e2e                    test_copy3
isvp18>

The recreatevg command overcomes the problem of duplicated LVM data structures and identifiers caused by a disk duplication process such as FlashCopy. It is used to recreate an AIX Volume Group (VG) on a set of target volumes that are copied from a set of source volumes belonging to a specific VG. The command will allocate new physical volume identifiers (PVIDs) for the member disks and a new Volume Group identifier (VGID) to the Volume Group. The command also provides options to rename the logical volumes with a
prefix you specify, and options to rename labels to specify different mount points for file systems.

isvp18> varyoffvg test_cp1
isvp18> varyoffvg test_cp2
isvp18> varyoffvg test_cp3
isvp18> lspv
hdisk0          00f65d52a5abd459                    rootvg          active
hdisk2          00f65d5108ffefb6                    test_cp2
hdisk3          00f65d51bfba4e2e                    test_cp3
hdisk4          00f65d51c465f8eb                    metro
hdisk5          00f65d52ce125f70                    swap            active
hdisk1          00f65d5108ff9043                    test_cp1
hdisk6          00f65d51c465f8eb                    metro
hdisk7          00f65d5108ff9043                    test_cp1
hdisk8          00f65d5108ffefb6                    test_cp2
hdisk9          00f65d51bfba4e2e                    test_cp3
isvp18> chdev -l hdisk7 -a pv=clear
hdisk7 changed
isvp18> chdev -l hdisk8 -a pv=clear
hdisk8 changed
isvp18> chdev -l hdisk9 -a pv=clear
hdisk9 changed
isvp18> lspv
hdisk0          00f65d52a5abd459                    rootvg          active
hdisk2          00f65d5108ffefb6                    test_cp2
hdisk3          00f65d51bfba4e2e                    test_cp3
hdisk4          00f65d51c465f8eb                    metro
hdisk5          00f65d52ce125f70                    swap            active
hdisk1          00f65d5108ff9043                    test_cp1
hdisk6          00f65d51c465f8eb                    metro
hdisk7          none                                None
hdisk8          none                                None
hdisk9          none                                None
isvp18>
isvp18> recreatevg -y fc_test_cp1 -L /backup -Y bkup hdisk7
fc_test_cp1
isvp18>
isvp18> recreatevg -y fc_test_cp2 -L /backup -Y bkup hdisk8
fc_test_cp2
isvp18> recreatevg -y fc_test_cp3 -L /backup -Y bkup hdisk9
isvp18> lspv
hdisk0          00f65d52a5abd459                    rootvg          active
hdisk2          00f65d5108ffefb6                    test_cp2        active
hdisk3          00f65d51bfba4e2e                    test_cp3        active
hdisk4          00f65d51c465f8eb                    metro
hdisk5          00f65d52ce125f70                    swap            active
hdisk1          00f65d5108ff9043                    test_cp1        active
hdisk6          00f65d5226de2253                    fc_metro        active
hdisk7          00f65d5226bdf18a                    fc_test_cp1     active
hdisk8          00f65d5226c876f6                    fc_test_cp2     active
hdisk9          00f65d5226ca8c57                    fc_test_cp3     active
In /etc/filesystems:
/backup/oralog:
        dev             = /dev/bkuplv02
        vfs             = jfs
        log             = /dev/bkuploglv02
        mount           = true
        check           = false
        options         = rw
        account         = false

/backup/oraarch:
        dev             = /dev/bkuplv03
        vfs             = jfs
        log             = /dev/bkuploglv03
        mount           = true
        check           = false
        options         = rw
        account         = false

/backup/oradata:
        dev             = /dev/bkuplv01
        vfs             = jfs
        log             = /dev/bkuploglv00
        mount           = true
        check           = false
        options         = rw
        account         = false

isvp18> mount /backup/oradata
isvp18> mount /backup/oralog
isvp18> mount /backup/oraarch
isvp18>
isvp18> ln -s /backup/oradata /oradata
isvp18> ln -s /backup/oralog /oralog
isvp18> ln -s /backup/oraarch /oraarch
isvp18> ls -l /oradata
lrwxrwxrwx    1 root     system           15 Feb 14 23:25 /oradata -> /backup/oradata
isvp18> su - sqlplus
3004-500 User "sqlplus" does not exist.
isvp18> su - oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 14 23:26:52 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5268046048 bytes
Database Buffers         4999610368 bytes
Redo Buffers               18743296 bytes
Database mounted.
Database opened.
SQL>
The database is now accessible for test, backup, or other development purposes. It is important to note that while the clone is accessing the remote copy replicas, it will not be updated with the latest updates happening to the Metro or Global mirror target volumes.

Thursday, February 10, 2011

Moving the control, data and redo log file of an Oracle 11g R2 database to a new location

I'm looking into moving the location the control file of my Oracle database into a different location. The new location will be a file system that has been mounted on an IBM Storwize V7000 storage system.

Found some great information at http://psoug.org/reference/control_file.html

Here is what I did on my system.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:01:47 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /home/test/test1/control01.ctl
                                                 , /home/test/test1/control02.c
                                                 tl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL>

On the host system, copy the files from their current location(/home/test/test1) to the new location(/oraarch/test1)


$ ls
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf
control02.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf
$ pwd
/home/test/test1
$

Next, update the init<instance_name>.ora file under $ORACLE_HOME/dbs with the new location
$ cat inittest1.ora | grep control
*.control_files='/oraarch/test1/control01.ctl','/oraarch/test1/control02.ctl'

Now, create a new spfile with the updated information.
SQL> create spfile from pfile;

File created.

SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oraarch/test1/control01.ctl,
                                                 /oraarch/test1/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

There we go, we have the location of the control files updated.


==================================================================

Next we will move the data files to the new location.
isvp17> su - oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:59:00 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/test/test1/system01.dbf
/home/test/test1/sysaux01.dbf
/home/test/test1/undotbs01.dbf
/home/test/test1/users01.dbf

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

Copy the data files to the new location.

$ cp /home/test/test1/system01.dbf /oradata/test1
$ cp /home/test/test1/sysaux01.dbf /oradata/test1
$ cp /home/test/test1/undotbs01.dbf /oradata/test1
$ cp /home/test/test1/users01.dbf /oradata/test1


Start the database in startup mount mode
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 13:04:41 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5301600480 bytes
Database Buffers         4966055936 bytes
Redo Buffers               18743296 bytes
Database mounted.
SQL>
SQL> alter database rename file '/home/test/test1/system01.dbf' to '/oradata/test1/system01.dbf';

Database altered.

SQL> alter database rename file '/home/test/test1/sysaux01.dbf' to '/oradata/test1/sysaux01.dbf';

Database altered.
SQL> alter database rename file '/home/test/test1/undotbs01.dbf' to '/oradata/test1/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/home/test/test1/users01.dbf' to '/oradata/test1/users01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/test1/system01.dbf
/oradata/test1/sysaux01.dbf
/oradata/test1/undotbs01.dbf
/oradata/test1/users01.dbf

SQL>

==================================================================================


Lastly we will change the location of the redo logs of the database

Found lots of good information on it at http://www.ordba.net/Tutorials/Redolog.htm

Here is how I moved my redlo logs to the new location.

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



$ cp redo0*.log /oralog/test1
$ ls /oralog/test1
redo01.log  redo02.log  redo03.log
$


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5301600480 bytes
Database Buffers         4966055936 bytes
Redo Buffers               18743296 bytes
Database mounted.
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/test/test1/redo03.log
/home/test/test1/redo02.log
/home/test/test1/redo01.log

SQL>
SQL> alter database rename file '/home/test/test1/redo01.log' to '/oralog/test1/redo01.log';

Database altered.

SQL> alter database rename file '/home/test/test1/redo02.log' to '/oralog/test1/redo02.log';

Database altered.

SQL> alter database rename file '/home/test/test1/redo03.log' to '/oralog/test1/redo03.log';

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oralog/test1/redo03.log
/oralog/test1/redo02.log
/oralog/test1/redo01.log

SQL>


Wednesday, February 9, 2011

fshop_make: 0506-252 A file system with nbpi = 4096 cannot exceed 134217728 512-byte blocks

I was trying to create a 100 GB file system my IBM Storwize V7000 volume, and was getting this error message from smitty while creating the file system
Error message: fshop_make: 0506-252 A file system with nbpi = 4096 cannot exceed 134217728 512-byte blocks





Increasing the nbpi as shown below allowed me to create the file system.


smitty made the following entry in /etc/filesystems for my file system:

/oradata:
        dev             = /dev/lv01
        vfs             = jfs
        log             = /dev/loglv00
        mount           = true
        options         = rw
        account         = false


snitty also create the /oradata mount point
isvp17> ls -l /oradata
total 0

Mounting /oradata:
isvp17> mount /oradata
isvp17> df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on

/dev/lv01      94464.00  93717.84    1%       17     1% /oradata

Adding swap space in AIX7.1 for running Oracle 11gR2

The swap requirement for running Oracle 11g R2 on AIX7.1 is as follows:
For 4 GB to 8 GB Memory the swap requirement is 2 times the size of RAM, for 8 GB to 32 GB the requirement is 1.5 times the size of RAM, and if the Memory is more than 32 GB then the swap space required is 32 GB.

isvp17> lsps -a
Page Space      Physical Volume   Volume Group Size %Used Active  Auto  Type Chksum
hd6             hdisk0            rootvg         512MB     3   yes   yes    lv     0
Ok, we are not doing good in that front. We'll have to find a way to increase the size of swap from 512MB to 
36GB.


So let us see how this can be done on AIX 7.1 .

isvp18> lspv
hdisk0          00f65d52a5abd459                    rootvg          active
hdisk1          00f65d52c3d3cfa6                    oradata         active
hdisk2          none                                None
hdisk3          none                                None
hdisk4          00f65d51c465f8eb                    metro
hdisk5          none                                None
isvp18> mkvg -y swap hdisk5


isvp17> lspv
hdisk0          00f65d51a5aa3cf1                    rootvg          active
hdisk1          00f65d51bfba4e2e                    test1           active
hdisk2          none                                None
hdisk3          none                                None
hdisk4          00f65d51c465f8eb                    metro           active
hdisk5          00f65d51cdf2c48c                    swap            active
isvp17> smitty mkps
+--------------------------------------------------------------------------+
|                            VOLUME GROUP name                             |
|                                                                          |
| Move cursor to desired item and press Enter.                             |
|                                                                          |
|   rootvg                                                                 |
|   test1                                                                  |
|   metro                                                                  |
|   swap                                                                   |
|                                                                          |
| F1=Help                 F2=Refresh              F3=Cancel                |
| F8=Image                F10=Exit                Enter=Do                 |
| /=Find                  n=Find Next                                      |
+--------------------------------------------------------------------------+



Add Another Paging Space

Type or select values in entry fields.
Press Enter AFTER making all desired changes.

[Entry Fields]
Volume group name                                   swap
SIZE of paging space (in logical partitions)       [564]                   #
PHYSICAL VOLUME name                                                       +
Start using this paging space NOW?                  yes                    +
Use this paging space each time the system is       yes                    +
RESTARTED?
Checksum Size                                      []                      +#








F1=Help             F2=Refresh          F3=Cancel           F4=List
F5=Reset            F6=Command          F7=Edit             F8=Image
F9=Shell            F10=Exit            Enter=Do





COMMAND STATUS

Command: OK            stdout: yes           stderr: no

Before command completion, additional instructions may appear below.

paging00



isvp17> lsps -a
Page Space      Physical Volume   Volume Group Size %Used Active  Auto  Type Chksum
paging00        hdisk5            swap         36096MB     1   yes   yes    lv     0
hd6             hdisk0            rootvg         512MB     3   yes   yes    lv     0
isvp17>

There we go, we now have more than enough swap to run Oracle 11gR2 on AIX7.1

Thursday, February 3, 2011

Creating an Oracle database on AIX 7.1 using dbca

Add $ORACLE_HOME/bin to your PATH.  Then, as user oracle, run dbca.















isvp17> ps -ef | grep test1
  oracle  6226048        1   0 15:33:10      -  0:00 ora_ckpt_test1
  oracle  6750276        1   0 15:33:10      -  0:00 ora_dbw1_test1
  oracle  6815788        1   0 15:33:10      -  0:00 ora_lgwr_test1
  oracle  6881376        1   0 15:33:10      -  0:00 ora_dbw0_test1
  oracle  7077968        1   0 15:33:09      -  0:00 ora_mman_test1
  oracle  7274686        1   0 15:33:16      -  0:00 ora_qmnc_test1
  oracle  7405818        1   0 15:33:09      -  0:00 ora_dia0_test1
  oracle  7536806        1   0 15:33:09      -  0:00 ora_psp0_test1
  oracle  7733266        1   0 15:33:09      -  0:00 ora_dbrm_test1
  oracle  7929936        1   0 15:33:26      -  0:00 ora_q000_test1
  oracle  7995638        1   0 15:33:10      -  0:00 ora_reco_test1
  oracle  8060940        1   0 15:33:10      -  0:00 ora_smon_test1
  oracle  8126588        1   0 16:28:21      -  0:00 ora_w000_test1
  oracle  8257780        1   0 15:33:09      -  0:00 ora_diag_test1
  oracle  8388706        1   0 15:33:09      -  0:00 ora_pmon_test1
  oracle  8519756        1   0 15:33:09      -  0:00 ora_gen0_test1
  oracle  8585246        1   0 15:33:10      -  0:00 ora_dbw2_test1
  oracle  8716448        1   0 15:33:09      -  0:00 ora_vktm_test1
  oracle  8781918        1   0 15:33:10      -  0:00 ora_mmnl_test1
  oracle  9109568        1   0 15:33:10      -  0:00 ora_mmon_test1
  oracle  9240606        1   0 15:33:10      -  0:00 ora_d000_test1
  oracle  9830446        1   0 15:33:10      -  0:00 ora_s000_test1
  oracle 54853772        1   0 15:38:18      -  0:00 ora_smco_test1
  oracle 56033458        1   0 15:33:26      -  0:00 ora_q001_test1
  oracle 56098994        1   0 15:33:18      -  0:00 ora_cjq0_test1
isvp17>
$ export ORACLE_SID=test1
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 3 16:35:55 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

$ cd /home/test
$ ls
lost+found  test1
$ cd test1
$ ls
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf
control02.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf
$

Tuesday, February 1, 2011

Oracle 11g R2 installation fails with 'Error in invoking target' message on AIX 7.1

I was installing Oracle database 11g R2 on a Power system running AIX 7.1 using runInstaller when I got the following error message during the linking of the binaries.

The exact error message was:
"Error in invoking target 'agent nmb nmo nmhs' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2011-02-01_02-56-05PM.log' for details"



The log file had the following message:
isvp17> ls
installActions2011-02-01_02-56-05PM.log
oraInstall2011-02-01_02-56-05PM.err
oraInstall2011-02-01_02-56-05PM.out

isvp17> grep Segmentation *.log
ld: 0706-010 The binder was killed by a signal: Segmentation fault
        Check for binder messages or use local problem reporting procedures.

It looks like the AIX bug  "IZ89165" , which was originated from AIX 6.1 bug "IZ88711".
https://www-304.ibm.com/support/docview.wss?uid=isg1IZ89165
https://www-304.ibm.com/support/docview.wss?uid=isg1IZ88711

Though the bug had been identified with AIX6.1 TL06. It looks like the same issue can be seen AIX7.1 too.


Fix/Workaround:
isvp17> cd /u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/
isvp17> grep blazy *.*
env_emagent.mk:LIB_JVM_LINK = -L$(JRE_LIB_DIR)/classic -L$(JRE_LIB_DIR) -blazy -ljava -ljvm

Backup the original file:
isvp17> cp env_emagent.mk env_emagent.mk.orig

isvp17> vi env_emagent.mk 
<delete -blazy> from the above line. 

Next, go back and press the Retry button twice(don't ask me why :-) in the GUI to continue with the installation.