Thursday, April 4, 2019

How to activate a standby database so that we end up with 2 primary databases

When planning a migration an important step is to test that all applications can access the new hosts and ideally do some performance tests on the new hardware.

We can clone the DB with RMAN and replicate the redo logs with dataguard.

Then using the following commands we can break the replication and activate the standby without turning the existing primary DB into a standby: If it's a RAC, shutdown all the nodes except one.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>  ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL>  SHUTDOWN IMMEDIATE;
SQL>  STARTUP;

Wednesday, April 3, 2019

How to find an exadata rack serial# from command line

Connect to one of your compute node and type the following as root:
[root@mycomputernode1 ~]# ipmitool sunoem cli "show /SP system_identifier"
Connected. Use ^D to exit.
-> show /SP system_identifier

 /SP
    Properties:
        system_identifier = Exadata Database Machine X6-2 AK19191919

To find the rack size go to /opt/oracle.SupportTools/onecommand/databasemachine.xml | grep MACHINETYPES
-> Session closed

Thursday, March 21, 2019

How to rebuild a table online without down time

I used this method to rebuild a table into a hash partitioned table. It will take an exclusive lock at the very end when it does the table flip. So this works great for tables with not too much transactions at a time or table with transaction burst (just need to do it in that table's downtime) With this method, it seems if some column have default values, they are not copied over at table creation with CTAS. Make sure to add that.
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

create table myschema.mytable_tmp
--These are not standard values. This particular table has huge increase in write activity for short periods of time. Hence the change to a hash partitioned table and the storage parameters.
initrans 40
pctfree 50
partition by hash (id) partitions 32
as
select *
from myschema.mytable
where 1=2;

alter table myschema.table modify
(
insert_date default sysdate
);


EXEC DBMS_REDEFINITION.start_redef_table('myschema', 'mytable', 'mytable_tmp');

-- Copy table dependents
-- Ignore errors because the create table statement above creates null constraints which makes the dependencies' copy fail.
-- Verification after copying shows constraints and index in place
SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'myschema',
    orig_table        => 'mytable',
    int_table         => 'mytable_tmp',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
    copy_triggers     => TRUE,  -- Default
    copy_constraints  => TRUE,  -- Default
    copy_privileges   => TRUE,  -- Default
    ignore_errors     => TRUE, -- Default
    num_errors        => l_num_errors); 
  DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

select *
from dba_constraints
where owner='myschema'
and table_name='mytable';

select *
from dba_constraints
where owner='myschema'
and table_name='mytable_tmp';

select *
from dba_indexes
where owner='myschema'
and table_name='mytable';

select *
from dba_indexes
where owner='myschema'
and table_name='mytable_tmp';

select *
from dba_tables
where owner='myschema'
and table_name='mytable';

select *
from dba_tab_partitions
where table_owner='myschema'
and table_name='mytable';

select *
from dba_ind_partitions
where index_owner='myschema';

EXEC DBMS_REDEFINITION.sync_interim_table('myschema', 'mytable', 'mytable_tmp'); 

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('myschema', 'mytable', 'mytable_tmp');

--We create a local non-prefixed index on the status_id column to help with ITL contention when we get bursts activity.

drop index myschema.ix_mytable_si;
create index myschema.ix_mytable_si on myschema.mytable (status_id) local initrans 50 pctfree 40 parallel 8;
alter index myschema.ix_mytable_si noparallel;

Monday, May 21, 2018

How to take a manual AWR snapshot

It can be useful to capture precisely a period of activity.
On a new database if there are 30 minutes snapshots taken, a 5 minute load test might be skewed by 25 minutes of inactivity.

To run a proper test, do the following:

EXEC dbms_workload_repository.create_snapshot;
Run test here;
EXEC dbms_workload_repository.create_snapshot;
Now run an AWR report with begin time using the first create_snapshot and end time using the second snapshot.

Tuesday, August 16, 2016

PL/SQL to generate passwords

set serveroutput on
DECLARE
   TYPE tCharList IS VARRAY(26) OF CHAR(1);
   TYPE tNumberList IS VARRAY(10) OF CHAR(1);
   vCharLower tCharList;
   vCharUpper tCharList;
   vCharNumber tNumberList;
   vRandomNumber INTEGER;
   vRandomList INTEGER;
   vPassword VARCHAR2(16);
   --vHowMany INTEGER;
BEGIN

   --vHowMany := 6;
   vCharLower := tCharList('a', 'b', 'c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');
   vCharUpper := tCharList('A', 'B', 'C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
   vCharNumber := tNumberList('0','1','2','3','4','5','6','7','8','9');
   --FOR n in 1 .. vHowMany LOOP
 
   SELECT dbms_random.value(1,26)
   INTO vRandomNumber
   FROM dual;
           
 

For j in 1 .. 10 LOOP
    vPassword := null;
     vPassword := vCharLower(vRandomNumber);      
   FOR i in 1 .. 15 LOOP
      SELECT dbms_random.value(1,3)
      INTO vRandomList
      FROM dual;
       
      CASE vRandomList
      WHEN 1 THEN
        SELECT dbms_random.value(1,26)
        INTO vRandomNumber
        FROM dual;
           
        vPassword := vPassword || vCharLower(vRandomNumber);
      WHEN 2 THEN
        SELECT dbms_random.value(1,26)
        INTO vRandomNumber
        FROM dual;
           
        vPassword := vPassword || vCharUpper(vRandomNumber);
       
      WHEN 3 THEN
     
        SELECT dbms_random.value(1,10)
        INTO vRandomNumber
        FROM dual;
       
        vPassword := vPassword || vCharNumber(vRandomNumber);
       
      END CASE;
   END LOOP;
   
   dbms_output.put_line(vPassword);
END LOOP;
END;
/

Tuesday, June 7, 2016

Never,ever use LDAP for the RAC install user and sysdba user

Oracle's recommandation is to have a local dba and oinstall user group.
If there are cluster admins and DBAs, then using a cluster user for the cluster installation is best.
Otherwise, it's possible to use one unique user (generally oracle) and one group (generally DBA) for the cluster and database installation.

In any case, always make sure that the groups and users are local users and groups.
check /etc/groups, the groups should be there.

Failing this, it may be possible to install the RAC cluster and the database the first time, but when the time comes to patch the system, and the cluster is restarted, the ASM user may not be able to authenticated and the patch will fail.

I have found myself in this very real situation in pre-prod environment because our SAs did not follow our recommendations and ended up in a 13 hour marathon with a complete re-install of the clusterware.

The good news is, there is no data loss. The ASM disks were still available, and found when the cluster was back online. We had to add the diskgroups back and it was quite a bit of work.

Just use a local user and groups.

How to read an AWR report - How to generate an AWR report with sqlplus?

Generate AWR report with sqlplus:

Connect to sqlplus and run the following command:
sqlplus  @$ORACLE_HOME/rdbms/admin/awrrpt.sql

A few information will be displayed regarding the system.

AWR reports generation works with snapshot ids, so to know that information, we are prompted for a number of days to display them. If there was a problem yesterday, mention 2 days for more snap id:

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
dw02         DW02             29150 03 Jun 2016 00:00      1
                              29151 03 Jun 2016 00:30      1
                              29152 03 Jun 2016 01:00      1
                              29153 03 Jun 2016 01:30      1
                              29154 03 Jun 2016 02:00      1
                              29155 03 Jun 2016 02:30      1
                              29156 03 Jun 2016 03:00      1
                              29157 03 Jun 2016 03:30      1
                              29158 03 Jun 2016 04:00      1
                              29159 03 Jun 2016 04:30      1
                              29160 03 Jun 2016 05:00      1
                              29161 03 Jun 2016 05:30      1
                              29162 03 Jun 2016 06:00      1
                              29163 03 Jun 2016 06:30      1
                              29164 03 Jun 2016 07:00      1
                              29165 03 Jun 2016 07:30      1
                              29166 03 Jun 2016 08:00      1
                              29167 03 Jun 2016 08:30      1
                              29168 03 Jun 2016 09:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 29167
Begin Snapshot Id specified: 29167

Enter value for end_snap: 29168
End   Snapshot Id specified: 29168

Finally, the script prompts for the report name. Do mention a full path, otherwise it will get generated in the current working directory which will be $ORACLE_HOME/rdbms/admin. Additionally, snapids become quickly meaningless when looking if there's a need to search for that report again a few days later. I prefer a format with a full date start time and end time.

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_29167_29168.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/awrrpt_20160603_0830_0900.html

Following this, the report is generated and we can look at it.