[Off topic] How to calculate your lease payment and negotiate lease deal

First visit all the dealerships and test drive the vehicles you like. Just take basic quotes from the dealers and do not buy at that point.

Once you have driven all the cars narrow down based on what you like. After you know what car you liked the most start research on the car.

Hit to edmunds forums and go to the vehicle specific forums and see what others are paying. Also request money factor and residual value of the model you would like to buy and one of the moderator will post the values for you.

Now visit specific car related forums. In my case i am planning to get a 2016 Honda civic touring.

So i went to civicx forums to see what are the possible issues people are facing and see if there are any TSB's to fix the issue. Make sure you check these issues before you finalize your purchase.

In addition to that see what prices people are paying around your area as they have state specific forums in civicx for example.

Once you get an idea just go to truecar and get the price for your model. Just use this as reference.

Also in edmunds you can check the invoice price of the vehicle.

The sticker price for civic touring $27335 ( Including destination fee)

Invoice price is around $24600 + $835 destination fee

Keeping these in mind i want to target price less than invoice including destination for $24500

From here i started requesting quotes online from all dealerships around me in 30 mile radius.

After negotiations over the phone and emails i am left with prices ranging from 24100 to 24900

I send this 24100 quote i got from the dealer who is quite far from my place to nearest dealer to see if he can beat it and i said i will be coming in today for purchase.

Instantly i got a call saying to bring the printed quote and they will beat it by $50 bucks.

So i headed to the dealership and when i told them this is for a lease they said for lease they make calculations differently and some crap. Dont hesitate to walkout. They will call you back in most of the cases. They will try to comeup with some numbers and force you but be firm and ask them the money factor (multiply this value with 2400 to get interest rate) and residual value( this is the value finance company decides what the value of car at the end of 3 yr lease)

So For honda civic touring i was told residual is 57% and MF is 0.00070(only if credit score above 760)


Do the math dont panic and let dealer match the price. This is how you calculate

Residual value is always calculated on MSRP or sticker price

Since dealer said 57%

27335*(57/100) = 15580.95

So your car is worth 15580.95 at the end of the lease. If you want you can buy your car if you want for that price at the ened of lease.

Money factor i was told is 0.00070

multiply money factor by 2400 to get interest rate. This is just for your reference,

In this case it is 0.00070*2400=1.68%



For lease you pay the depreciation of the car and the interest

MSRP=27335
Negotiated price= 24050
Residual Value= 15580.95
Lease acquisition fee =$595

So Total price you negotiated= 24050+595=$24645


Lease acquisition fee is mandatory when you are leasing


So first calculate this if its for 36 month lease

Depreciation = (24645-15581)/36 = $251.77

Interest = (24645+15581)*0.00070 = $28.15

Add these two

$251.77 + $28.15 = $279.92 + tax

These are the monthly payments you need to pay for 36 months

Our tax rate is 8% so it is $22.39


So total including tax per month is $302.31 for 36 months including tax.

Dont panic and be clear and dont overpay.

In addition to this when you drive of you need to pay dealer, title and documentation fee. In California it is less than $400

Happy Leasing. Leave comments if you have any questions.

Basic procedures to give db level grants to users

Some times we need to grant a particular db user to kill sessions and for that we need to  grant alter system access which is not a best practice. So to over come we create a simple procedure to do the required actions and grant execute permissions to the user on that procedure.

Here are few examples..

Killing session procedure

CREATE OR REPLACE procedure kill_db_session
 ( v_sid number, v_serial number )  as
 v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''   || v_sid || ',' || v_serial || ''' IMMEDIATE';
end;
/

Refresh materialized view under a different user

CREATE OR REPLACE PROCEDURE SSS.REFRESH_MV AS

BEGIN

    DBMS_MVIEW.REFRESH('xxx','C');

END REFRESH_MV;

/

Here xxx is tablename and C means complete refresh

Make sure you give grants on tables explicitly for mviews or else they will throw table not found error

Exadata IORM (Incomplete)

alter iormplan dbplan=((name='DDWH', level=1, allocation=10),(name=other, level=1, allocation=90))


alter iormplan active


LIST IORMPLAN detail

alter iormplan objective=auto;

alter iormplan objective=basic;


Example 6-12 Resetting Default Values in an Interdatabase Plan

CellCLI> ALTER IORMPLAN dbPlan="", catPlan=""
CellCLI> ALTER IORMPLAN dbPlan=""
CellCLI> ALTER IORMPLAN catPlan=""


[root@xxx01 ~]# cellcli
CellCLI: Release 12.1.2.3.1 - Production on Mon Jun 13 16:33:04 PDT 2016

Copyright (c) 2007, 2016, Oracle.  All rights reserved.

CellCLI> list iormplan detail;
         name:                   xxx01_IORMPLAN
         catPlan:
         dbPlan:
         objective:              basic
         status:                 active






CellCLI> list iormplan detail;
         name:                   xxx01_IORMPLAN
         catPlan:
         dbPlan:
         objective:              basic
         status:                 active


Manage IO distribution across databases

alter iormplan dbplan=((name='DDWH', level=1, allocation=1),(name=other, level=1, allocation=99))

alter iormplan objective=auto;

alter iormplan active




Reset back to default

alter iormplan objective=basic;


CellCLI> ALTER IORMPLAN dbPlan="", catPlan=""

alter iormplan active

**************************************************************************
To set a plan

change objective from basic to auto

Here allocation is ( Only during contention )
xxxdr will get 35% io
xxdr will get 22% io
xwh will get 22% io

Remaining all db's will be in level 2

alter iormplan objective=auto;

alter iormplan dbplan=((name='XXXDR', level=1, allocation=35),(name='XXDR', level=1, allocation=22),(name='XWH',level=1,allocation=22),(name=other,level=2,allocation=100))

Another scenario from oracle note

suppose we have 3 databases sharing the Exadata storage cells in a hosted environment. Databases 'sales' and 'finance' are more important than 'hr', so we'd like to give them more I/O bandwidth when there is I/O contention. We therefore set the shares in a 4-4-1 ratio. This means that when there is I/O contention, 'sales' and 'finance' will get equal I/O bandwidth. They will each also get 4x the bandwidth of 'hr'. If only one database is active, then it can consume all of the I/O bandwidth.

alter iormplan dbplan = -
  ((name=sales,   share=4), -
   (name=finance, share=4), -
   (name=hr,      share=1), -
   (name=default, share=1));

root account gets locked on exadata

On Exadata after failed login accounts even root account gets locked. Play with values below to increase hits

$  grep -i pam_tally2 /etc/pam.d/sshd
auth       required     pam_tally2.so deny=5 onerr=fail lock_time=600 serialize                        

$ grep -i pam_tally2 /etc/pam.d/login
auth       required     pam_tally2.so deny=5 onerr=fail lock_time=600 serialize

Meaning of options:
1) deny=5 <<<<<< user account will be locked after 5 in-correct wrong passwords
2) lock_time=600   <<<< user account will be locked for 600 seconds i.e. for next 10mins

When Input Wrong Root Password Login Exadata System, We Have To Wait For 10mins ( Doc ID 1541862.1 )

use vi editor to change it to 10 seconds

vi /etc/pam.d/sshd

auth       required     pam_tally2.so deny=5 onerr=fail lock_time=10 serialize



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


To reset ssh for a user and allow logins

pam_tally2 -r -u oracle

Role refresh and scheduler job script in oracle database

The following scheduler job refreshes all the roles in the db with grants for newly created tables under schemas.

It is such a pain to manually grant read or read write access to users in the db when a new table is created in an application schema.

So for this task we created a user name role_refresh. This user has dba role

The roles we create have a naming convention.

Read only roles will have schema_name_ro_role
Read/Write roles will habe schema_name_rw_role

Under this user we created a procedure as well as scheduler job

Procedure

CREATE OR REPLACE PROCEDURE Role_refresh.REFRESH_SCHEMA_PRIVS (p_schema IN VARCHAR2, p_role IN VARCHAR2, p_privilege IN VARCHAR2)
AUTHID CURRENT_USER IS
 v_exec_string VARCHAR2(300);

 CURSOR c_tables IS
  SELECT table_name
  FROM sys.dba_tables
  WHERE owner = p_schema;

 CURSOR c_views IS
  SELECT object_name
  FROM sys.dba_objects
  WHERE owner = p_schema
  AND status='VALID'
  AND object_type='VIEW';

 grant_option EXCEPTION;
 PRAGMA EXCEPTION_INIT(grant_option, -1720);
BEGIN
 IF (p_schema IS NULL OR p_role IS NULL OR p_privilege IS NULL) THEN
  dbms_output.put_line('One of the mandatory parameters are blank.');
  dbms_output.put_line('Usage: refresh_schema_privs(<schema_name>, <role>, [ RO | RW ])');
 ELSE
  FOR r_tables IN c_tables
  LOOP
   BEGIN
    IF UPPER(p_privilege) = 'RO' THEN
     v_exec_string := 'GRANT SELECT ON ' || p_schema || '.' || r_tables.table_name || ' TO ' || p_role;
    ELSIF UPPER(p_privilege) = 'RW' THEN
     v_exec_string := 'GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ' || p_schema || '.' || r_tables.table_name || ' TO ' || p_role;
    ELSE
     dbms_output.put_line('Invalid Input for Parameter privilege.');
    END IF;

   EXECUTE IMMEDIATE v_exec_string;

   EXCEPTION
    WHEN grant_option THEN
    CONTINUE;
   END;
  END LOOP;

  FOR r_views in c_views
  LOOP
   BEGIN
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || p_schema || '.' || r_views.object_name || ' TO ' || p_role;
   EXCEPTION
    WHEN grant_option THEN
    CONTINUE;
   END;
  END LOOP;
 END IF;
END;
/

Scheduler Job

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
      ,start_date      => TO_TIMESTAMP_TZ('2014/03/18 09:32:40.131457 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin
   ROLE_REFRESH.REFRESH_SCHEMA_PRIVS (''TEST_SCHEMA'', ''TEST_SCHEMA_RO_ROLE'', ''RO'');
   ROLE_REFRESH.REFRESH_SCHEMA_PRIVS (''TEST_SCHEMA'', ''TEST_SCHEMA_RW_ROLE'', ''RW'');
end;'
      ,comments        => 'Job to refresh the schema level privileges daily'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS');
END;
/

Metric extensions for user defined metrics OEM 12c

We can create custom alerts in oem 12c

In this example i will show how to create an alerts if there are any user locks in the DB.

On oem 12c home page

go to

Enterprise > Monitoring > Metric extensions

Create a new metric



Target type: Choose what target this alert is for

In my case DB instance

Give a name

Adapter choose sql as i will be using a query for result

Leave the collection schedule as-is



In this page just give the sql query and hit next


Give a column name by clicking add column and select the o/p in my case o/p is a number

select critical threshold and warning

I chose >0 which means i get alerted immediately if there is a lock

I classified it as fault

Hit next

I used default monitoring credentials

tested with a test instance. Test was successful. Hit next





Click finish

Go to metric extensions page again. You will see the metric you just created

select that and click actions and select save as deployable draft


Once that is done. select the metric and click actions again and choose deploy to targets.
Add the target you want here to get alerted for and click Add

Once thats done. You are ready to go. Goto target page. Click Oracle database> Monitoring> All metrics

Here you will see the locks metric that you just created


Select it and you will see the current real time data.

Once you verify it is there. You need to configure your email alerts in incident rules as below.




Once thats done, Create a test table and update it from two different sessions without commit. You should see your alert email as below :)

Changing AWR snapshot retention

To check what the current retention is

 select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
topnsql
from dba_hist_wr_control;


SNAPSHOT_INTERVAL RETENTION_INTERVAL TOPNSQL  
----------------- ------------------ ----------
               60              20160 DEFAULT  



Current retention is snapshot every 60 minutes and retention days=20160 minutes

which means 14 days (24*14*60)


If we want to change retention to 10 days

which means (24*10*60) = 14400

 execute dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 14400);