Amazon books

Friday, December 19, 2014

Using TAF To collect information about your RAC Nodes

This command is used to generate all information about your RAC Nodes using TAF Utility:

/u01/app/11.2.0.4/grid/tfa/bin/tfactl diagcollect  -from "Dec/18/2014 13:00:00" -to   "Dec/18/2014 15:00:00"


If you do not have TAF yet, install it in all your Oracle Environments. It is like a join of diagnostic tools simplified with one command.

Best Regards,
Paulo Portugal

Gathering ExaWatcher information easily

If you had a problem / crash in your DB Machine and need to investigate it you can simply us this command below to gather information from time where the crash happened:

[root@ex01dbadm07 oracle.ExaWatcher]# ./GetExaWatcherResults.sh --from 12/18/2014_13:00:00 --to 12/18/2014_15:00:00


This command will collect all ExaWatcher information generated during the time you specified.

Best Regards,
Paulo Portugal 

Wednesday, December 10, 2014

New way to check your patches applied in Oracle Database 12c

Simple run this query below:

SYS@BWOEM12C_1>select xmltransform(DBMS_QOPATCH.GET_OPATCH_LIST, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LIST,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Patch Details:

Patch     16619249:   applied on 2014-06-30T17:43:15-03


SYS@BWOEM12C_1>


Wednesday, November 5, 2014

GoldenGate Error ERROR OGG-01028

If you get this error:

Oracle GoldenGate Capture for Oracle, ext_cont.prm:  Formatting error on: table name SALES_SOUB_ADMIN.CW_OPT_INS, rowid AAAR5fAAGAAJ/3PAB8, X
ID 20.12.64, position (Seqno 64, RBA 3474748). Error converting timestamp with timezone from Oracle to ASCII format for column DT_LAST_UPDATE.


Include this parameter in your .prm file:

TRANLOGOPTIONS INCLUDEREGIONID


Check MOS Note: 1929158.1

Best Regards,
Paulo Portugal

Tuesday, October 28, 2014

Change your DG Protection Mode with one single parameter

Instead of setting 3 parameters to increase your detection and protection mode when using a DG environment, in 11g we can set just one parameter:

db_ultra_safe  = data_only, or data_and_index or off (Protect just table data, protect also index or none) .

This parameter changes these other 3 :  db_block_checking, db_lost_write_protect and db_block_checksum


Best Regards,
Paulo Portugal

Tuesday, October 14, 2014

Check if your database is using ASYNC_IO or not

TO check if your database is using async_io, just execute this query below;

SYS@ABR1 AS SYSDBA> select count(*) filetype_name,asynch_io from v$iostat_file group by filetype_name,asynch_io;

FILETYPE_NAME ASYNCH_IO
------------- ---------
   1 ASYNC_OFF
   1 ASYNC_OFF
   1 ASYNC_OFF
  15 ASYNC_ON
   1 ASYNC_OFF
   1 ASYNC_OFF
   1 ASYNC_OFF
   1 ASYNC_OFF
   1 ASYNC_OFF
   1 ASYNC_OFF
 185 ASYNC_ON
   1 ASYNC_OFF

12 rows selected.

SYS@ABR1 AS SYSDBA>


As you can see, I have 185 datafiles using ASYNC IO, if you cannot find anyone using ASYNC_ON, than you are not using ASYNC_IO, check your disk_asynch_io initialisation parameter and make sure it is set to TRUE.  Also check with your OS admin if your OS is ok to use ASYNC IO.

Best Regards,
Paulo Portugal


Thursday, October 2, 2014

GGSCI (host) 3> dblogin useridalias ggadm ERROR: Unable to open credential store. Error code 28761..

Add credentias to new version of GG:




GGSCI (host) 4> ADD CREDENTIALSTORE

Credential store created in ./dircrd/.

GGSCI (host) 5> ALTER CREDENTIALSTORE ADD USER ggadm, password xxxxx alias ggadm

Credential store in ./dircrd/ altered.

GGSCI (host) 6> dblogin useralias ggadm



This wors fine for me.

Best Regards,
Paulo Portugal

Friday, September 12, 2014

Change VMs on ODA using OAKCLI

1-Change  memory of a VM on ODA

oakcli configure vm VMBUILDER -maxmemory 24000 -memory 24000

2-Change CPU of a VM on ODA

oakcli configure vm VMBUILDER -vcpu 32 -maxvcpu 32

3-Configure a vm template

oakcli configure vmtemplate OEM12CTMPL -memory 8192M -maxmemory 8192M
oakcli configure vmtemplate OEM12CTMPL -network "['type=netfront, bridge=net1']"




4-Create CPU Pools


   
    oakcli create cpupool empool -numcpu 4 -node 0
    oakcli create cpupool empool -numcpu 4 -node 1

5-Use HA on a VM

oakcli configure vm OEM12CNODE1 -failover true -prefnode 0
    oakcli configure vm OEM12CNODE2 -failover true -prefnode 1




Best Regards,
Paulo Portugal


Wednesday, September 3, 2014

root.sh Failed on 11g RAC Installation. "USM driver install actions failed" "ACFS-9361: Removing device 'acfsctl' failed with error code '5888'"

If you got this error below while installing a new RAC 11g,

Now product-specific root actions will be performed.
Using configuration parameter file: /opt/oracle/products/11.2/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
Installing Trace File Analyzer
USM driver install actions failed
/opt/oracle/products/11.2/grid/perl/bin/perl -I/opt/oracle/products/11.2/grid/perl/lib -I/opt/oracle/products/11.2/grid/crs/install /opt/oracle/products/11.2/grid/crs/install/rootcrs.pl execution failed


This is because your servers have some files from an old installation. Remove them. On all nodes, run these command below logged in as root:

/usr/sbin/rmsecattr -c /usr/lib/methods/defacfsctl
/usr/sbin/rmsecattr -c /usr/lib/methods/udefacfsctl
/usr/sbin/rmsecattr -c /usr/lib/methods/cfgacfsctl
/usr/sbin/rmsecattr -c /usr/lib/methods/ucfgacfsctl
/usr/sbin/rmsecattr -c /usr/lib/methods/defadvmctl
/usr/sbin/rmsecattr -c /usr/lib/methods/udefadvmctl
/usr/sbin/rmsecattr -c /usr/lib/methods/cfgadvmctl
/usr/sbin/rmsecattr -c /usr/lib/methods/ucfgadvmctl
/usr/sbin/rmsecattr -c /usr/lib/methods/cfgadvmvol
/usr/sbin/rmsecattr -c /usr/lib/methods/ucfgadvmvol
/usr/sbin/rmauth -h oracle
rmrole oracle_devmgmt
setkst
rm /usr/lib/drivers/oracle*
rm /usr/lib/methods/*advm* /usr/lib/methods/*acfs*
rm -rf /sbin/helpers/acfs
rm /usr/sbin/acfsutil* /usr/sbin/advmutil*
rm /sbin/acfsutil* /sbin/advmutil*

After that, run root.sh again


Best Regards,
Paulo Portugal



Tuesday, September 2, 2014

Command to nanually deploy agent on EM Grid 12c

This is an example to deploy an agent from the target host using command line:

/db/products/agent12c/ADATMP_2014-09-02_23-40-20-PM/agentDeploy.sh -ignorePrereqs ORACLE_HOSTNAME=xxx0307.b2w AGENT_BASE_DIR=/db/products/agent12c OMS_HOST=xxx105.b2w EM_UPLOAD_PORT=4900 AGENT_INSTANCE_HOME=/db/products/agent12c/agent_inst b_doDiscovery=false b_startAgent=false b_forceInstCheck=true AGENT_PORT=3872


Best Regards,
Paulo Portugal

New Data Pump (IMPDP) In Memory Option on 12c



No you can specify a TRANSFORM option when using Data Pump to use IM clause on all objects being imported.

impdp system dumptile=dir:file.dmp TRANSFORM=INMEMORY:Y


This is a default option for data pump in 12c. So, remember if you are not using in memory, take it off.

Best Regards,
Paulo Portugal

Friday, August 22, 2014

New Cluster ASM 12.1.0.2 Commands

ASMCMD> help
asmcmd
        Starts asmcmd or executes the command

Synopsis
        asmcmd [-V] [--nocp] [-v {errors | warnings | normal | info | debug} ] [--privilege {sysasm | sysdba} ] [-p] [--inst ] [--discover][]

Description
        The environment variables ORACLE_HOME and ORACLE_SID determine the
        instance to which the program connects, and ASMCMD establishes a
        bequeath connection to it, in the same manner as a SQLPLUS / AS
        SYSASM.  The user must be a member of the OSASM group.

        If Flex ASM is enabled, the ASMCMD connects to any one of the ASM
        instances running in the cluster. The connection to ASM instance does
        not depend on the environment variables ORACLE_HOME and ORACLE_SID.
        The ASMCMD alert log shows to which instance ASMCMD is connected to.
        If the user wants to connect to a specific ASM instance, --inst option
        should be used to specify the instance name.

        Specifying the -V option prints the asmcmd version number and
        exits immediately.

        Specifying the --nocp option disables connection pooling feature for
        ASMCMD.
       
        Specifying the -v option prints extra information that can help
        advanced users diagnose problems.

        Specify the --privilege option to choose the type of connection. There are
        only two possibilities: connecting as SYSASM or as SYSDBA.

        The default value if this option is unspecified is SYSASM.

        Specifying the -p option allows the current directory to be displayed
        in the command prompt, like so:

        ASMCMD [+DATA/ORCL/CONTROLFILE] >

        Specifying the --discover option uses discovery string obtained from
        Listener, does not use ORACLE_SID. 
       
        [command] specifies one of the following commands, along with its
        parameters.

        Type "help [command]" to get help on a specific ASMCMD command.

        commands:
        --------

        md_backup, md_restore

        amdu_extract

        lsattr, setattr

        audcleanaudittrail, audclearproperty, audcleartimestamp
        audcreatejob, auddropjob, audloaduniauditfiles, audsetdebug
        audsetjobinterval, audsetjobstatus, audsetproperty, audsettimestamp
        audsettraillocation, audshowtimestamp, lsaudcleanevents
        lsaudcleanupjobs, lsaudconfigparams

        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
        mkdir, pwd, rm, rmalias, showclustermode, showclusterstate
        showpatches, showversion

        mapau, mapextent

        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
        offline, online, rebal, remap, umount

        pwcopy, pwcreate, pwdelete, pwget, pwmove, pwset

        afd_configure, afd_deconfigure, afd_dsget, afd_dsset
        afd_filter, afd_label, afd_lsdsk, afd_scan, afd_state
        afd_unlabel, dsget, dsset, lsop, mkcc, rmcc, shutdown
        spbackup, spcopy, spget, spmove, spset, startup

        chtmpl, lstmpl, mktmpl, rmtmpl

        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr, rpusr

        volcreate, voldelete, voldisable, volenable, volinfo
        volresize, volset, volstat

ASMCMD> lsaudconfigparams
Type   Parameter Name             Parameter Value         
       AUDIT FILE MAX SIZE        10000                   
       AUDIT FILE MAX SIZE        10000                   
       AUDIT FILE MAX AGE         5                       
       AUDIT FILE MAX AGE         5                       
       AUDIT_TRAIL_WRITE_MODE     QUEUED WRITE MODE       
       AUDIT_TRAIL_WRITE_MODE                             
ASMCMD> showclustermode
ASM cluster : Flex mode disabled
ASMCMD> showclusterstate
Normal
ASMCMD> showversion
ASM version         : 12.1.0.2.0
ASMCMD> spbackup
usage: spbackup
help:  help spbackup
ASMCMD>
ASMCMD> help spbackup
spbackup
        Backs up an Oracle ASM SPFILE to a back up file.

Synopsis
        spbackup

Description
        The options for the spbackup command are described below:
        source        - specifies the source file name
        destination     - specifies the destination file name
        spbackup should be used when you want to make single or multiple
        backups of an SPFILE in the same or different disk group without
        creating an SPFILE in target disk group.
        Note the following about the use of spbackup:
            spbackup can back up an Oracle ASM SPFILE from a disk group to a
            disk group or to an operating system file.
            spbackup can back up an Oracle ASM SPFILE from an operating
            system file to a disk group or to an operating system file.
            spbackup can back up an Oracle ASM SPFILE when the SPFILE is being
            used by an open Oracle ASM instance.
            spbackup can make multiple backups of an Oracle ASM SPFILE in the
            same disk group.
        spbackup does not affect the GPnP profile.  The back up file that is
        created is not a special file type and is not identified as an SPFILE.
        This back up file cannot be copied with spcopy.  To copy this back up
        file, use the ASMCMD cp command.
        To make a copy of a back up file in a disk group that is identified as
        a SPFILE file:
          1. Use the ASMCMD cp command to copy the back up file from the disk
             group to an operating system file.
          2. Use the ASMCMD spcopy command to copy the operating system file
             to a disk group.

Examples
        The following are examples of the spbackup command.
        The first example backs up the SPFILE in the DATA disk group.
        The second example backs up the SPFILE from the DATA disk group to the
        FRA disk group.
        ASMCMD>spbackup +DATA/asm/asmparameterfile/registry.253.721810181
                        +DATA/spfileBackASM.bak
        ASMCMD>spbackup +DATA/asm/asmparameterfile/registry.253.721810181
                        +FRA/spfileBackASM.bak

See Also
       cp spcopy

ASMCMD> help chtmpl
chtmpl
        Changes the attributes of a template.

Synopsis
        chtmpl -G { [--striping {coarse|fine}] [--redundancy {high|mirror|unprotected} ] [--primary {hot|cold}] [--secondary {hot|cold}]}

Wednesday, July 30, 2014

Tracing EM Grid 12c for Performance Issues on a specific target/page

This is what I have done:


1-Login to EM
./emcli login -username=sysman

2-List sessions
./emcli  list_active_sessions -details

3-Enable trace
./emcli trace -enable=true -user=F2C_PAULO_PORTUGAL

4-Access the slow page on Grid 12c

5-Disable Trace
./emcli trace -enable=false -user=F2C_PAULO_PORTUGAL

6-Generate the report from raw file

./emctl genreport oms -file_name f2c_paulo_portugal_1406760005606.trace



Best Regards,
Paulo Portugal

Tuesday, July 29, 2014

Using LOGDUMP GoldenGate Example

This is what I do to find the line where the replicat golden gate process abended:


Logdump 277 >open ./dirdat/pa000376
Current LogTrail is /ggshop/glete/gghome/dirdat/pa000376
Logdump 278 >ghdr on
Logdump 279 >detail data
Logdump 280 >usertoken detail
Logdump 281 >ggstokens detail
Logdump 282 >ghdr on
Logdump 283 >detail on
Logdump 284 >detail data
Logdump 285 >usertoken detail
Logdump 286 >ggstoken detail
Logdump 287 >pos 19930247
Reading forward from RBA 19930247
Logdump 288 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 46 (x002e) IO Time : 2014/07/25 16:09:52.963.776
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 38756 AuditPos : 144659472
Continued : N (x00) RecCount : 1 (x01)

2014/07/25 16:09:52.963.776 FieldComp Len 46 RBA 19930247
Name: SALES_SHOP_ADMIN.FN_PROPERTY_CONFIG
After Image: Partition 4 G s
0000 0016 0000 0012 5553 455f 494e 5355 5241 4e43 | ........USE_INSURANC
455f 4c49 5354 0001 0008 0000 0004 7472 7565 0004 | E_LIST........true..
0004 ffff 0000 | ......
Column 0 (x0000), Len 22 (x0016)
0000 0012 5553 455f 494e 5355 5241 4e43 455f 4c49 | ....USE_INSURANCE_LI
5354 | ST
Column 1 (x0001), Len 8 (x0008)
0000 0004 7472 7565 | ....true
Column 4 (x0004), Len 4 (x0004)
ffff 0000 | ....

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4178 5a45 4141 7441 414a 6149 4d41 4145 0001 | AAAxZEAAtAAJaIMAAE..
TokenID x4c 'L' LOGCSN Info x00 Length 13
3832 3834 3735 3335 3036 3637 38 | 8284753506678
TokenID x36 '6' TRANID Info x00 Length 13
3134 342e 3234 2e33 3738 3536 35 | 144.24.378565

Logdump 289 >exit



Best Regards,
Paulo Portugal

Monday, July 7, 2014

CELL-02625: Interface eth2 refers to device name "eth2".

CellCLI> create cell stocell1 interconnect1=eth2

CELL-02625: Interface eth2 refers to device name "eth2".
Device name must be same as Interface name.


To fix that, change the interface name in your network file /etc/sysconfig/network-scripts/ifcfg-eth2


This field:
NAME="System eth2" must be changed to NAME="eth2"


Best Regards,
Paulo Portugal

CELL-01514: Connect Error. Verify that Management Server is listening at the specified HTTP port: 8888.

CellCLI> list cell detail

CELL-01514: Connect Error. Verify that Management Server is listening at the specified HTTP port: 8888.
cellsrvStatus:     stopped
msStatus:           running
rsStatus:           running


Edit your /etc/hosts file and comment out this line below:

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6



Best Regards,
Paulo Portugal

Friday, June 27, 2014

ERROR Applying patch Database 12c Prerequisite check "CheckActiveFilesAndExecutables" failed.

The file that the installation is complaining is :

Following executables are active :
/dbgc/products/12.1.0/rdbms/lib/libclntsh.so.12.1
Log file location: /dbgc/products/12.1.0/rdbms/cfgtoollogs/opatch/opatch2014-06-27_15-55-45PM_1.log


1-Check who is using this file

fuser /dbgc/products/12.1.0/rdbms/lib/libclntsh.so.12.1

2-Kill all PIDs using this file

3-Restart patching :

opatchauto apply /opt/oracle/binarios/18139660 -oh /dbgc/products/12.1.0/rdbms/ -ocmrf /opt/oracle/12.1.0/grid/OPatch/ocm/bin/ocm.rsp




Best Regards,
Paulo Portugal

Wednesday, June 25, 2014

Quickly create and mount ACFS DB 12c - Manually

1-Create volume
volcreate -G DG_GRID -s 20G ACFS_VOL1

2-Check volume created

ASMCMD> volinfo --all
Diskgroup Name: DG_GRID

Volume Name: ACFS_VOL1
Volume Device: /dev/asm/acfs_vol1-131
State: ENABLED
Size (MB): 20480
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

ASMCMD>


3-Create ACFS

[grid@bwdhdbpr106 ~]$ /sbin/mkfs -t acfs /dev/asm/acfs_vol1-131
mkfs.acfs: version                   = 12.1.0.1.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/acfs_vol1-131
mkfs.acfs: volume size               = 21474836480
mkfs.acfs: Format complete.
[grid@bwdhdbpr106 ~]$


4-Mount ACFS (login as root)

/bin/mount -t acfs /dev/asm/acfs_vol1-131 /ACFS


Best Regards,
Paulo Portugal

Check the Health of your GI and DB 12c Installation with a single command

Use this simple command to check the health of your installation (GI and DB 12c):

[grid@xxxxxx bwdhdbpr106]$ cluvfy comp health

Verifying Health Check
Total memory check passed
Available memory check passed
Swap space check failed
Check failed on nodes:
bwdhdbpr106
Free disk space check passed for "bwdhdbpr106:/tmp"
Check for multiple users with UID value 500 passed
User existence check passed for "grid"
Group existence check passed for "oinstall"
Membership check for user "grid" in group "oinstall" [as Primary] passed
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "make"
Package existence check passed for "binutils"
Package existence check passed for "gcc(x86_64)"
Package existence check passed for "libaio(x86_64)"
Package existence check passed for "glibc(x86_64)"
Package existence check passed for "compat-libstdc++-33(x86_64)"
Package existence check passed for "glibc-devel(x86_64)"
Package existence check passed for "gcc-c++(x86_64)"
Package existence check passed for "libaio-devel(x86_64)"
Package existence check passed for "libgcc(x86_64)"
Package existence check passed for "libstdc++(x86_64)"
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check passed for "sysstat"
Package existence check passed for "ksh"
Check for multiple users with UID value 0 passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed

Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...
CTSS resource check passed


Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed

Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP


Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes

NTP common Time Server Check started...
Check of common NTP Time Server passed

Clock time offset check from NTP Time Server started...
Clock time offset check passed

Clock synchronization check using Network Time Protocol(NTP) passed


Oracle Cluster Time Synchronization Services check passed

Checking OHASD integrity...

OHASD integrity check passed

Checking CRS integrity...

Clusterware version consistency passed.

CRS integrity check passed

Checking Single Client Access Name (SCAN)...

Checking TCP connectivity to SCAN Listeners...
TCP connectivity to SCAN Listeners exists on all cluster nodes

Checking name resolution setup for "oemgt-scan.b2w"...

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


ERROR:
PRVG-1101 : SCAN name "oemgt-scan.b2w" failed to resolve

ERROR:
PRVF-4657 : Name resolution setup check for "oemgt-scan.b2w" (IP address: 10.13.0.227) failed

ERROR:
PRVF-4664 : Found inconsistent name resolution entries for SCAN name "oemgt-scan.b2w"

Checking SCAN IP addresses...
Check of SCAN IP addresses passed

Verification of SCAN VIP and Listener setup failed

Checking node application existence...

Checking existence of VIP node application (required)
VIP node application check passed

Checking existence of NETWORK node application (required)
NETWORK node application check passed

Checking existence of ONS node application (optional)
ONS node application check passed

The disk free space for file system path "/opt/oracle/12.1.0/grid" is sufficient on all nodes

Verification of Health Check was unsuccessful on all the specified nodes.
[grid@xxxxx bwdhdbpr106]$



Best Regards,
Paulo Portugal

Check if your ASM is Flex or not

To check if your ASM instance is using FLEX configuration use this command below:


[grid@bwdhdbpr106 bwdhdbpr106]$ asmcmd
ASMCMD> showclustermode
ASM cluster : Flex mode disabled
ASMCMD> showclusterstate
Normal
ASMCMD>



Best Regards,
Paulo Portugal

Installing GI 12c . root.sh show "/proc/net/ipv6_route: No such file or directory"

If you got this message while running post-installation root.sh script GI 12c:

/proc/net/ipv6_route: No such file or directory

You can safe ignore it as per note: "Error "/proc/net/ipv6_route: no such file or directory" while Running root.sh or Srvctl Modify Nodeapps Commands (Doc ID 1616776.1)"


Best Regards,
Paulo Portugal

Sunday, June 15, 2014

Install new ORACLE HOME on Exadata - Manually



1-Create user, groups and OH directory on all cells

dcli -k -g dbnodes -l root  --Configure user equivalence for root if not already done
dcli -g dbnodes -l root /usr/sbin/useradd -u 503 -g oinstall -G dba,asmdba,asmadmin,racoper orabob --Add user
dcli -g dbnodes -l root id orabob --Check user
dcli -g dbnodes -l root mkdir -p /u01/app/oracle/product/11.2.0.4/dbbob  --Create OH directory
dcli -g dbnodes -l root chown -R orabob:oinstall /u01/app/oracle/product/11.2.0.4/dbbob --Change OH permission
dcli -k -g dbnodes -l orabob --Configure user equivalence for new user


2-Change /etc/security/limits.conf on all cels (Use the same values as your other user created by OneCommand)

oratkc    soft     core unlimited
oratkc    hard     core unlimited
orabob    soft     nproc 131072
orabob    hard     nproc 131072
orabob    soft     nofile 65536
orabob    hard     nofile 65536
orabob    soft     memlock 396748650
orabob    hard     memlock 396748650

3-Login with new user and run runInstaller to install OH Binaries

4-Upgrade OPatch in OH

dcli -l orabob -g /home/orabob/dbnodes -f p6880880_112000_Linux-x86-64.zip -d /tmp
dcli -l orabob -g /home/orabob/dbnodes unzip -oq -d /u01/app/oracle/product/11.2.0.4/dbbob /tmp/p6880880_112000_Linux-x86-64.zip

5-Apply latest BP on OH

5.1-Run OPatch Conflict

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/oracle.SupportTools/onecommand/Software/patches/18371656/18371656/

5.2-Check with there is enough system space:

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/oracle.SupportTools/onecommand/Software/patches/18371656/18371656/

5.3-Apply the BP patch on OH (GI was already applied on past). Login with ROOT user and make sure opatch is in PATH.

opatch auto /opt/oracle.SupportTools/onecommand/Software/patches/18371656/18371656/ -oh /u01/app/oracle/product/11.2.0.4/dbbob

OBS: if you still don't have, create ocm file using command "$ORACLE_HOME/OPatch/ocm/bin/emocmrsp"




Best Regards,
Paulo Portugal

Wednesday, May 21, 2014

Trigger to OPEN all PDBs after starting CDB - Database 12c

If you want to automatize your PDBs to open soon as CDB opens, create a trigger like below:

sqlplus / as sysdba

create or replace trigger OPEN_PDBS after startup on database 
begin 
   execute immediate 'alter pluggable database all open'; 
end OPEN_PDBS;
/

--Testing

SQL> shut immediate
SQL> startup
SQL> select name, open_mode from v$pdbs;



Best Regards,
Paulo Portugal

Create a Pluggable Database 12c - Two Steps

1-First create the directory of datafiles (if using FS of course)

mkdir -p /u01/oradata/SID

2-Now, create the database connected in CDB$ROOT

sqlplus / as sysdba


create pluggable database yourPDB admin user your_PDB_admin identified by mnager123roles = (DBA)FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed','/u01/oradata/SID');


3-Check new services:


sqlplus / as sysdba
SQL> select name, con_id from v$active_services; 



Simple like that.
Best Regards,







Paulo Portugal



Friday, May 16, 2014

OAKCLI SHOW REPO - Doesn't show any repository after restarting ODA

To fix that, restart your ODA deamon whith this command below:

oakcli restart oak


After that you will see your repositories using command "oakcli show repo"


Best Regards,
Paulo Portugal

Thursday, May 15, 2014

Some VERY Useful 12c Commands

--Check current container
SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

--Switch to a different container
ALTER SESSION SET CONTAINER = salespdb;
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER SESSION SET CONTAINER = PDB$SEED;


--Execute one command in many PDBs at the same time
DECLARE
  c1 INTEGER;
  rowcount INTEGER;
  taskList VARCHAR2(32767) :=
    'DECLARE
      PRAGMA AUTONOMOUS TRANSACTION;
     BEGIN
       -- Create the hr.identact table.
       EXECUTE IMMEDIATE
         ''CREATE TABLE hr.identact
             (actionno NUMBER(4) NOT NULL,
              action VARCHAR2 (10))'';
       EXECUTE IMMEDIATE
         ''INSERT INTO identact VALUES(1, 'ACTION1')'';
       -- A commit is required if the tasks include DML.
       COMMIT;
       EXCEPTION
         WHEN OTHERS THEN
           -- If there are errors, then drop the table.
           BEGIN
             EXECUTE IMMEDIATE ''DROP TABLE identact'';
           EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
        END;';
  TYPE containerListType IS TABLE OF VARCHAR2(128) INDEX BY PLS_INTEGER;
  containerList  containerListType;
BEGIN
  containerList(1) := 'PDB1';
  containerList(2) := 'PDB2';
  c1 := DBMS_SQL.OPENCURSOR;
  FOR conIndex IN containerList.first..containerList.last LOOP
    DBMS_OUTPUT.PUT_LINE('Creating in container: ' || containerList(conIndex));
    DBMS_SQL.PARSE(c => c1 ,
                   statement => taskList,
                   language_flag => DBMS_SQL.NATIVE,
                   edition= > NULL,
                   apply_crossedition_trigger => NULL,
                   fire_apply_trigger => NULL,
                   schema => 'HR',
                   container => containerList(conIndex));
     rowcount := DBMS_SQL.EXECUTE(c=>c1);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c=>c1);
END;
/


--Change open mode of PDBs databases
ALTER PLUGGABLE DATABASE salespdb, hrpdb  OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb CLOSE IMMEDIATE;

--Open a PDB in read write mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN

--Open a PDB in restricted mode
STARTUP PLUGGABLE DATABASE hrpdb RESTRICT

--Open a PDB in restricted and read only mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT

--Open a PDB in read only mode
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY

--If the PDB is already open, use FORCE to close and open in read write mode
STARTUP PLUGGABLE DATABASE hrpdb FORCE

--Alter system parameters in all containers
ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;

--Execute DML/DDL in a current container
CREATE USER testpdb IDENTIFIED BY password
   DEFAULT TABLESPACE pdb1_tbs
   QUOTA UNLIMITED ON pdb1_tbs
   CONTAINER = CURRENT;
 
--Create a COMMON user in a CDB (user in all PDBS). Common user MUST start with c##

CREATE USER c##testcdb IDENTIFIED BY password
   DEFAULT TABLESPACE cdb_tbs
   QUOTA UNLIMITED ON cdb_tbs
   CONTAINER = ALL;


--Run catblock.sql script in all containers at once
perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql

--Run catblock.sql script in specif CDBs
perl catcon.pl -u SYS -U SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -c 'HRPDB SALESPDB' -b catblock_output catblock.sql

--Run catblock script in a CDB except from PDBs hrpdb and salespdb
perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -C 'HRPDB SALESPDB' -b catblock_output catblock.sql

--Close a PDB
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

--Change open mode of a PDB to read only
ALTER PLUGGABLE DATABASE OPEN READ ONLY;


--Bring a datafile of a PDB online
ALTER PLUGGABLE DATABASE DATAFILE '/u03/oracle/pdb1_01.dbf' ONLINE;

--Change  the default tablespace of a PDB
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;

--Change global database name for a PDB
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO salespdb.example.com;

--Create a service for a PDB using SRVCTL
srvctl add service -db mycdb -service salesrep -pdb salespdb

--Create a service for a PDB using DBMS_SERVICE

BEGIN
  DBMS_SERVICE.CREATE_SERVICE(
    service_name => 'salesrep',
    network_name => 'salesrep.example.com');
END;
/

--General queries
SELECT CDB FROM V$DATABASE;

COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

COLUMN PDB_NAME FORMAT A15
  SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;


--Show tables owned by specif schemas in Multiples PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A30

SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
 FROM DBA_PDBS p, CDB_TABLES t
 WHERE p.PDB_ID > 2 AND
       t.OWNER IN('HR','OE') AND
       p.PDB_ID = t.CON_ID
 ORDER BY p.PDB_ID;

--Show users in multiple PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30

SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
 FROM DBA_PDBS p, CDB_USERS u
 WHERE p.PDB_ID > 2 AND
       p.PDB_ID = u.CON_ID
 ORDER BY p.PDB_ID;

--Shoe datafiles for each PDBs
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
 FROM DBA_PDBS p, CDB_DATA_FILES d
 WHERE p.PDB_ID = d.CON_ID
 ORDER BY p.PDB_ID;



--Shoe tempfiles in CDB
COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45

SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
 FROM CDB_TEMP_FILES
 ORDER BY CON_ID;

--Show services associated with a PDBs
COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999

SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
 WHERE PDB IS NOT NULL AND
       CON_ID > 2
 ORDER BY PDB;

--Show current container ID and name
SHOW CON_ID
SHOW CON_NAME
SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;
SELECT CON_DBID_TO_ID(2226957846) FROM DUAL;


--History of PDBs
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15

SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
 FROM CDB_PDB_HISTORY
 WHERE CON_ID > 2
 ORDER BY CON_ID;

--Check open mode of all PDBs

SELECT NAME,OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;

Friday, May 9, 2014

Some OAKCLI Commands - ODA X4 Installed on VM with Weblogic

I just finish an ODA X4 installation using Oracle VM and deployed a WLS template. Everything was fine. You must not forget to download 2.9 Configurator for WLS. The 2.7 doesn't work.

Some commands that may help admin this env:

--COmandos OAKCLI Abramus

1-Modify some resources on a VM:

cd /opt/oracle/oak/bin

./oakcli stop vm WLS_1_mydomain_mycluster_1

./oakcli configure vm WLS_1_mydomain_mycluster_1 -vcpu 3 -memory 4196

./oakcli start vm WLS_1_mydomain_mycluster_1


2-Completely delete a VM

cd /opt/oracle/oak/bin

./oakcli stop vm WLS_mydomain_ManagedServer_8

./oakcli delete vm WLS_mydomain_ManagedServer_8

3-Undeploy and WLS environment

./cleanup.sh

4-Accessing your Admin Console of WLS env:


https://admin-server-ip:listen-port/console
https://10.0.1.186:7001/console

5-Show your VMs created:

oakcli show vm
oakcli show vmtemplates

6-Create new CPU pools

oakcli create cpupool wlsCpuPool -numcpu 8 -node 0
oakcli create cpupool wlsCpuPool -numcpu 8 -node 1


7-Create new databases on ODA X4:
oakcli create database -db sales1 -version 11.2.0.4
oakcli create database -db sales2 -oh home2 -params salesdbtemplate

8-Create new databases configuration files

oakcli create db_config_params -conf filename
oakcli show db_config_params

9-Upgrading database in ODA

oakcli show databases
oakcli update -patch 2.6.0.0.0 --database (from node 0)
oakcli show databases




Best Regards,
Paulo Portugal


Thursday, May 1, 2014

After a New APEX Installation got "Connection was reset" attempting to access http://192.168.20.28:8080 - Admin Page

I just installed APEX on two new environments. This is an EBS database and they have the same version of database 11.2.0.3 and applications 12.1.3.  No error in APEX installation LOG.

On first installation, everything went fine without any major problem. On second installation, using the same procedure, I finished the installation but I couldn't access the APEX webpage. Neither /apex nor /apex_admin.


After trying to find the error, I saw that APEX needs DISPATCHERS and Oracle Support recommended to set it on my database.

So, the solution was:

1-Set Dispatchers on your database because it is a pre-req to work XDB.

2-Look at you listener status and see the XDB port there:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=f2chml.f2c.pvt)(PORT=1565)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=f2chml.f2c.pvt)(PORT=8080))(Presentation=HTTP)(Session=RAW))


Best Regards,
Paulo Portugal

Wednesday, April 30, 2014

How to check Embebbed PL/SQL Gateway using on APEX

Just run the script below:

[oradsv@f2chml admin]$ sqlplus / as sysdba
@
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 30 12:28:08 2014

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

epgstat.sql
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
+--------------------------------------+
| XDB protocol ports:                  |
|  XDB is listening for the protocol   |
|  when the protocol port is non-zero. |
+--------------------------------------+

HTTP Port FTP Port
--------- --------
     8080        0

1 row selected.

+---------------------------+
| DAD virtual-path mappings |
+---------------------------+

Virtual Path                     DAD Name
-------------------------------- --------------------------------
/apex/*                          APEX

1 row selected.

+----------------+
| DAD attributes |
+----------------+

DAD Name     DAD Param                DAD Value
------------ ------------------------ ----------------------------------------
APEX         database-username        ANONYMOUS
             default-page             apex
             document-table-name      wwv_flow_file_objects$
             request-validation-funct wwv_flow_epg_include_modules.authorize
             ion

             document-procedure       wwv_flow_file_mgr.process_download
             nls-language             american_america.al32utf8
             document-path            docs

7 rows selected.

+---------------------------------------------------+
| DAD authorization:                                |
|  To use static authentication of a user in a DAD, |
|  the DAD must be authorized for the user.         |
+---------------------------------------------------+

no rows selected

+----------------------------+
| DAD authentication schemes |
+----------------------------+

DAD Name             User Name                        Auth Scheme
-------------------- -------------------------------- ------------------
APEX                 ANONYMOUS                        Anonymous

1 row selected.

+--------------------------------------------------------+
| ANONYMOUS user status:                                 |
|  To use static or anonymous authentication in any DAD, |
|  the ANONYMOUS account must be unlocked.               |
+--------------------------------------------------------+

Database User   Status
--------------- --------------------
ANONYMOUS       OPEN

1 row selected.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+

Allow repository anonymous access?
----------------------------------
false

1 row selected.

SQL>

Monday, April 28, 2014

APEX Installation Problem - ORA-04031 Compiling WWV_FLOW_GEN_API2

I just installed 4.2.5 APEX Installation and the component stays invalid.

I check wich object was invalid and got this package body below. I tried to compile and got SHARED POOL memory error.

SQL> alter package apex_040200.WWV_FLOW_GEN_API2 compile body;

alter package apex_040200.WWV_FLOW_GEN_API2 compile body
*
ERROR at line 1:
ORA-04031: unable to allocate 91248 bytes of shared memory ("shared pool","unknown object","PLMCD^72ae492e","BAMIMA: Bam Buffer")


#####################################

I them flush shared and buufer cache pool and compiled again. This works for me:

SQL> alter system flush buffer_cache ;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter package apex_040200.WWV_FLOW_GEN_API2 compile body;

Package body altered.

SQL>

######################################


Best Regards,
Paulo Portugal

Wednesday, March 26, 2014

Calculate Storage ROI with this basic tool

This is a basic tool that could help in calculating the saving in your storage for a 11g Database.


http://www.oracle.com/us/media/calculator/db11g/index.html

Best Regards,
Paulo Portugal

Thursday, March 20, 2014

Oracle SHUTDOWN HANGING

If you are trying to NOT shutdown abort your database, this is a good workaround:

1-Shutdwn abort once
SQL> SHUT ABORT

2-Startup in RESTRICTED MODE
SQL> startup RESTRICT

3-Shutdown IMMEDIATE now
SQL>SHUT IMMEDIATE

4-Finally, startup normally
SQL> startup


Best Regards,
Paulo Portugal

Tuesday, March 18, 2014

2014-03-18 20:15:23 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, ext_sou1.prm: OCI Error getting OCI_ATTR_NAME for UDT SYS.XMLTYPE (status = 24328-ORA-24328: illegal attribute value), SQL.

If your EXTRACT process is abending with error like below:

2014-03-18 20:15:23  ERROR   OGG-00665  Oracle GoldenGate Capture for Oracle, ext_sou1.prm:  OCI Error getting OCI_ATTR_NAME for UDT SYS.XMLTYPE (status = 24328-ORA-24328: illegal attribute value), SQL.


Check if that is a new table created with XMLYPE column data type.

select distinct data_type from dba_tab_columns ;
select object_name,object_type, created from dba_objects where object_name in (select table_name from dba_tab_columns where data_type= 'XMLTYPE');


Add a new line in your EXTRACT parameter excluding this table from replication:

TABLEEXCLUDE owner.yournewtable;



This work for me.

Best Regards,
Paulo Portugal

Wednesday, March 12, 2014

Script Backup database using RMAN in Oracle 8i

This is my last example:

run {
     allocate channel c1 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c1 kbytes = 20000000;
     allocate channel c2 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c2 kbytes = 20000000;
     allocate channel c3 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c3 kbytes = 20000000;
     allocate channel c4 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c4 kbytes = 20000000;
     allocate channel c5 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c5 kbytes = 20000000;
     allocate channel c6 type disk format '/migra_UOL/APPRD/%d_APPRD_BKP_12_MAR_2014_%u_%s_%p';
     set limit channel c6 kbytes = 20000000;
     backup current controlfile format '/migra_UOL/APPRD/ctl_for_APPRD_antes.ctl';
     backup database include current controlfile tag = 'Backup_APPRD_12_Mar_2014';
     backup current controlfile format '/migra_UOL/APPRD/ctl_for_APPRD_depois.ctl';
     allocate channel ch1 type disk format '/migra_UOL/APPRD/%d_ARCH_%u_%s_%p';
     backup archivelog all;
}

PS: I used kbytes to set a maximum file size so I could move these files while backup is still running without going out of space.


Best Regards,
Paulo Portugal

Oracle 8i 8.1.7.4 Clone showing errors sqlplus Could not load program sqlplus: Symbol resolution failed for sqlplus because

After cloning Oracle 8i binaries I was getting these errors below when trying to start a sqlplus session:


sqlplus
Could not load program sqlplus:
Symbol resolution failed for sqlplus because:
        Symbol pw_post (number 274) is not exported from dependent
          module /unix.
        Symbol pw_wait (number 275) is not exported from dependent
          module /unix.
        Symbol pw_config (number 276) is not exported from dependent
          module /unix.
        Symbol aix_ora_pw_version3_required (number 277) is not exported from dependent
          module /unix.
Examine .loader section symbols with the 'dump -Tv' command.


Even after checking LIBPATH, LD_LIBRARY_PATH, OS Patches, Relinking binaries without any error the same problem persists.

I them found the patch in MOS 2896876. Ifter downloading it, run the ROOTPRE.sh as ROOT and relink all again.

This fixed my problem.

Best Regards,
Paulo Portugal