Friday, 21 July 2017

Generate missing indexes in 1 environment from another–oracle

I’ve done a heap of tuning in production, created a bunch of indexes and I’m pretty happy with how it looks.  Remember that you only need to create the indexes in the database if they are for tuning – they don’t need to be added to the table specs in JDE.

So, how do I easily generate all of the DDL for these indexes and create them in other locations?

I’’’ generate the create index statements while reconciling

select 'SELECT DBMS_METADATA.GET_DDL(''INDEX'',''' || index_name || ''',''' || OWNER || ''') ||'';'' FROM dual ;'
from all_indexes t1 where t1.owner = 'CRPDTA' and not exists (select 1 from all_indexes t2 where t2.owner = 'TESTDTA' and t1.index_name = t2.index_name) ;

Which will give you a bunch of results like this:


SELECT DBMS_METADATA.GET_DDL('INDEX','F0902_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F41001_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F3413_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03B11_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03012Z1_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03012Z1_ORA0','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F01151_ORA0','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F5646_SRM1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0414_9SRM','CRPDTA') ||';' FROM dual ;

So whack some headers on this to trim the output:

set heading off
set feedback off
set long 99999
set pages 0
set heading off
set lines 1000
set wrap on

And use the run script button in SQL Developer:

image

You’ll get a pile of output like this:


CREATE INDEX "CRPDTA"."F01151_ORA0" ON "CRPDTA"."F01151" ("EAAN8", "EAIDLN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CRPINDEX2"
PARALLEL ;


CREATE INDEX "CRPDTA"."F5646_SRM1" ON "CRPDTA"."F5646" ("ALDOCO", "ALLNID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CRPINDEX2"
PARALLEL ;

You can then change the tablespace and owner information and run in your other environments.

Thursday, 20 July 2017

want to know more about ASM on the ODA?

Here are a couple of handy commands, especially if you are on an ODA

As root, you can see what space is being used by what database:

[root@sodax6-1 datastore]# oakcli show dbstorage

All the DBs with DB TYPE as non-CDB share the same volumes

DB_NAMES           DB_TYPE    Filesystem                                        Size     Used    Available    AutoExtend Size  DiskGroup
-------            -------    ------------                                    ------    -----    ---------   ----------------   --------
JDEPROD, JDETEST   non-CDB    /u01/app/oracle/oradata/datastore                   31G    16.26G      14.74G              3G        REDO
                               /u02/app/oracle/oradata/datastore                 4496G  4346.01G     149.99G            102G        DATA
                               /u01/app/oracle/fast_recovery_area/datastore      1370G   761.84G     608.16G             36G        RECO

Of course, this is what ACFS thinks:

[grid@sodax6-1 ~]$ df -k
Filesystem            1K-blocks       Used  Available Use% Mounted on
/dev/xvda2             57191708   14193400   40093068  27% /
tmpfs                 264586120    1246300  263339820   1% /dev/shm
/dev/xvda1               471012      35731     410961   8% /boot
/dev/xvdb1             96119564   50087440   41149436  55% /u01
/dev/asm/testing-216 1048576000  601013732  447562268  58% /u01/app/sharedrepo/testing
/dev/asm/datastore-344
                        32505856   17050504   15455352  53% /u01/app/oracle/oradata/datastore
/dev/asm/acfsvol-49    52428800     194884   52233916   1% /cloudfs
/dev/asm/datastore-49
                      1436549120  798850156  637698964  56% /u01/app/oracle/fast_recovery_area/datastore
/dev/asm/testing2-216
                      4194304000 1575520568 2618783432  38% /u01/app/sharedrepo/testing2
/dev/asm/datastore-216
                      4714397696 4661989408   52408288  99% /u02/app/oracle/oradata/datastore


Now, you might want to take a look at what ASM thinks about this

[grid@sodax6-1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304  19660800   198252           983040         -392394              0             Y  DATA/
MOUNTED  NORMAL  N         512   4096  4194304   3230720   321792           161536           80128              0             N  RECO/
MOUNTED  HIGH    N         512   4096  4194304    762880   667144           381440           95234              0             N  REDO/


A bit more detial thanks:


[grid@sodax6-1 ~]$ asmcmd volinfo -G DATA -a
Diskgroup Name: DATA

     Volume Name: DATASTORE
      Volume Device: /dev/asm/datastore-216
      State: ENABLED
      Size (MB): 4603904
      Resize Unit (MB): 64
      Redundancy: MIRROR
      Stripe Columns: 8
      Stripe Width (K): 1024
      Usage: ACFS
      Mountpath: /u02/app/oracle/oradata/datastore
 
      Volume Name: TESTING
      Volume Device: /dev/asm/testing-216
      State: ENABLED
      Size (MB): 1024000
      Resize Unit (MB): 64
      Redundancy: MIRROR
      Stripe Columns: 8
      Stripe Width (K): 1024
      Usage: ACFS
      Mountpath: /u01/app/sharedrepo/testing
 
      Volume Name: TESTING2
      Volume Device: /dev/asm/testing2-216
      State: ENABLED
      Size (MB): 4096000
      Resize Unit (MB): 64
      Redundancy: MIRROR
      Stripe Columns: 8
      Stripe Width (K): 1024
      Usage: ACFS
      Mountpath: /u01/app/sharedrepo/testing2

So now, I want to resize, as I’ve made my repo TESTING2 too big and I need some more space in my DATASTORE – so…

[grid@sodax6-1 ~]$ acfsutil size -1T /u01/app/sharedrepo/testing2
acfsutil size: new file system size: 3195455668224 (3047424MB)

and you can see that ACFS actually uses the “Auto-resize increment” to add to the FS when it’s low:

DB_NAMES           DB_TYPE    Filesystem                                        Size     Used    Available    AutoExtend Size  DiskGroup
-------            -------    ------------                                    ------    -----    ---------   ----------------   --------
JDEPROD, JDETEST   non-CDB    /u01/app/oracle/oradata/datastore                   31G    16.26G      14.74G              3G        REDO
                               /u02/app/oracle/oradata/datastore                 4598G  4446.22G     151.78G            102G        DATA
                               /u01/app/oracle/fast_recovery_area/datastore      1370G   761.84G     608.16G             36G        RECO

In my example it’ll add 102GB when low.  So before I resized the /TESTING2 repo, things looked like this:

/dev/asm/datastore-216
                      4714397696 4661989408   52408288  99% /u02/app/oracle/oradata/datastore

After resizing

/dev/asm/datastore-216
                      4821352448 4662201568  159150880  97% /u02/app/oracle/oradata/datastore

So it’s seen that there is some free space (the 1TB I stole) and has given this back to the data area.

Note that I could have done this with oakcli resize repo (but I did not know that at the time).

Wednesday, 19 July 2017

EM express for 12c

This is cool, no more emctl start dbconsole

I went snooping around for emctl and did not find one under 12c

google and found this gem:  http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html#A1 This is probably all you need, but I needed more

When I followed the steps, my browsers got security errors.  Interestingly I only had a port come up with https, not http:

image

Secure Connection Failed

The connection to sodax6-1.oda.aus.osc:5500 was interrupted while the page was loading.

    The page you are trying to view cannot be shown because the authenticity of the received data could not be verified.
     Please contact the website owners to inform them of this problem.


I checked the ports that were open and found that http was not.


SQL> select dbms_xdb.getHttpPort() from dual;

GETHTTPPORT
-----------
       0

SQL> select dbms_xdb_config.getHttpsPort() from dual;


GETHTTPSPORT
------------
        5500


So I ran the below:

exec DBMS_XDB_CONFIG.SETHTTPPORT(5500);

Then was able to login

image

ODA goodness–has this thing started to win me over

If you know about ODA’s, you probably know why I like the X6 about 100000 times more than anything before it – it all comes down to IOPS.  If you want more than 1500 IOPs consistently – then you might want to move on from the X5 if you have a very large database.  The X5 does have some cool stuff to mitigate it (it being the lack of IOPS), but at the end of the day there is limited FLASH to get that slow SAS data closer to the CPU.

But, the X6 is very fast and very nice and very FLASH

One thing I needed to do is quickly test 12c database version, so this can be done with a 1 click [need to be honest here, there is NO graphical interface native on the ODA, you need to start getting very familiar with oakcli commands.  Although this has escalated my confidence, I’ve started writing ksh scripts and automating everything I need on this machine.

Take a look at the above, 1 oakcli command and we are upgrading to 12C, both RAC nodes – everything.

That is cool!  (PS. I know that I can also do this is AWS RDS – and that is a click – so I guess this is just okay)…

There is no progress indicator, just “It will take a few minutes”.

A little “extra for experts”, do not modify the .bash_profile for oracle on the oda_base.  I had it prompting me for what oracle home I wanted and this was breaking a bunch of commands – what a dope I am.

I might make another post in 1 hour when this has broken and I’m picking up the pieces…

Tuesday, 18 July 2017

Database not starting

First thing is to look into the trace dir / logs dir for your database, it’ll look something like:

/u01/app/oracle/diag/rdbms/jdetest/JDETEST1/trace

I find that alert_JDETEST1.log (this is for RAC) is the best place to start, goto the bottom:

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file /u01/app/oracle/diag/rdbms/jdetest/JDETEST1/trace/JDETEST1_ora_46405.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 499289948160 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

Cool, the database have given me all of these good ideas!

But, I cannot start the database – so I cannot run RMAN. 

Chicken or the egg?

So I can see that I’m using all 465G of recovery area.  I need to extend this to be able to start the database properly so that RMAN will work.  So I update the size allocated (as I still have space available on the device) with the commands below

sqlplus / as sysdba

SQL>show parameter db_recovery_file_dest_size

NAME TYPE VALUE

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

db_recovery_file_dest_size big integer 465G

SQL> alter system set db_recovery_file_dest_size=500G ;

I forget, you might need to startup nomount at the SQL command on one of the RAC nodes (the one that you are on)

Then shutdown the database and start it normally (note that I’m only starting a single instance for the time being – not the RAC instance).

then

>rman / target

RMAN> delete noprompt archivelog all completed before 'sysdate - 1/24';

RMAN> quit

Nice, for a non DBA, I have my database back up and running.

Thursday, 6 July 2017

A CNC approach to oracle archive logs getting filled

Truncate them!  obvious…

No, kidding…  We do a lot of work with temp environments and large statements and sometimes that can cause various problems with filling archive logs…  I was getting issues when importing large tables using impdp.  Of course, I do not care about the archive.

Errors like this:

UDI-00257: operation generated ORACLE error 257

login as oracle to the database server.  And delete all archive up to the current time – 1/24 (I did rip this off the net – sorry I missed the credit).

. oraenv

rman

connect target

delete noprompt archivelog all completed before 'sysdate - 1/24';

quit

It’ll plow through the archive and let your statements run again

Wednesday, 5 July 2017

https://edelivery.oracle.com and wget

This is a cool enhancement / feature that I noticed the other day.

When downloading software from edelivery, I see:

image

the wget option at the end.

You can choose this and download a script:

image

You can cut and paste the script to your linux machine, and if the proxy is set up right, you can do the gets.

This has multiple advantages – but primarily if you do not have a graphical interface, you can do all of your downloading.


#!/bin/sh

set -x

#
# Generated onTue Jul 04 14:19:56 PDT 2017# Start of user configurable variables
#
LANG=C
export LANG

# SSO username and password
read -p 'SSO User Name:' SSO_USERNAME
read -sp 'SSO Password:' SSO_PASSWORD


# Path to wget command
WGET=/usr/bin/wget
# Location of cookie file
COOKIE_FILE=/tmp/$$.cookies

# Log directory and file
LOGDIR=.
LOGFILE=$LOGDIR/wgetlog-`date +%m-%d-%y-%H:%M`.log
# Output directory and file
OUTPUT_DIR=.
#
# End of user configurable variable
#

if [ "$SSO_PASSWORD " = " " ]
then
echo "Please edit script and set SSO_PASSWORD"
exit
fi

# Contact osdc site so that we can get SSO Params for logging in
SSO_RESPONSE=`$WGET --user-agent="Mozilla/5.0" --no-check-certificate https://edelivery.oracle.com/osdc/faces/SearchSoftware 2>&1|grep Location`

# Extract request parameters for SSO
SSO_TOKEN=`echo $SSO_RESPONSE| cut -d '=' -f 2|cut -d ' ' -f 1`
SSO_SERVER=`echo $SSO_RESPONSE| cut -d ' ' -f 2|cut -d '/' -f 1,2,3`
SSO_AUTH_URL=/sso/auth
AUTH_DATA="ssousername=$SSO_USERNAME&password=$SSO_PASSWORD&site2pstoretoken=$SSO_TOKEN"

# The following command to authenticate uses HTTPS. This will work only if the wget in the environment
# where this script will be executed was compiled with OpenSSL. Remove the --secure-protocol option
# if wget was not compiled with OpenSSL
# Depending on the preference, the other options are --secure-protocol= auto|SSLv2|SSLv3|TLSv1
$WGET --user-agent="Mozilla/5.0" --secure-protocol=auto --post-data $AUTH_DATA --save-cookies=$COOKIE_FILE --keep-session-cookies $SSO_SERVER$SSO_AUTH_URL -O sso.out >> $LOGFILE 2>&1

rm -f sso.out



  $WGET  --user-agent="Mozilla/5.0" --no-check-certificate --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://edelivery.oracle.com/osdc/download?fileName=V43852-01.zip&token=b0ZNSVUrOU45MFhWb1VZd1Z2NHcrQSE6OiF1c2VybmFtZT1FUEQtU0hBTk5PTi5NT0lSQE1ZUklBRC1JVC5DT00mdXNlcklkPTE4MTI0NTkmY2FsbGVyPVNlYXJjaFNvZnR3YXJlJmNvdW50cnlJZD1BVSZlbWFpbEFkZHJlc3M9c2hhbm5vbi5tb2lyQG15cmlhZC1pdC5jb20mZmlsZUlkPTcwNDE5ODEzJmFydT0xNzM1MjA4OSZhZ3JlZW1lbn10cnVl" -O $OUTPUT_DIR/V43852-01.zip >> $LOGFILE 2>&1


  $WGET  --user-agent="Mozilla/5.0" --no-check-certificate --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://edelivery.oracle.com/osdc/download?fileName=V43853-01.zip&token=ejlqREVLRzV0R0pQeUZKNGlWYU56ZyE6OiF1c2VybmFtZT1FUEQtU0hBTk5PTi5NT0lSQE1ZUklBRC1JVC5DT00mdXNlcklkPTE4MTI0NTkmY2FsbGVyPVNlYXJjaFNvZnR3YXJlJmNvdW50cnlJZD1BVSZlbWFpbEFkZHJlc3M9c2hhbm5vbi5tb2lyQG15cmlhZC1pdC5jb20mZmlsZUlkPTcwNDE5ODEyJmFydT0xNzM1MjA5MCZhZ3JlZW1lbnRJZD0zMzkxNDg4JnNvZnR3YXJlQ2lkcz0mcGxhdGZvcm1DaWRzPTYwJnByb2ZpbGVJbnN0YW5jZUNpZD0tOTk5OSZkb3dubG9hZFNvdXJjZT13Z2V00cnVl" -O $OUTPUT_DIR/V43853-01.zip >> $LOGFILE 2>&1


In this instance I was trying to download a couple of files.

You can also see that I’ve added set –x to my script as I needed to debug some proxy settings, this is a good option, as the script does not have a lot of output if things are going wrong.

Thanks oracle, this is a nice feature!