How to Grant the SYS user sufficient privileges on Windows.



Several months ago I setup an Oracle instance on Windows for testing. Then we moved from one state to another. Finally the time arrived to startup that Windows server again. Enough time had passed that I could not remember setting any particular password on any of the SYSDBA accounts. The default passwords were not working. None of my favorite passwords were working. If I could connect / as sysdba, I could change the passwords to anything I like. When I tried that, it said ORA-01031: insufficient privileges.

What to do now?

I checked the Windows services to make sure they were running: yep.

I bounced the Windows services: no improvement.

I ran the orapwd.exe command to create a password file in the $ORACLE_HOME/dbs directory. It prompts you for the password to use for the SYSDBA account. That didn't help.

I mean, if you OWN the box, you should be able to connect / as sysdba, because that would use local authentication, which runs the service, which has authority to run the service, which should work. Right?

I opened the Database Configuration Assistant to see what it had to offer, but it wants to know the username and password for a SYSDBA account, so that was another dead end. I checked later and there was nothing that would have been helpful there anyway. Fine. If we're going to fail, let's fail as fast as possible.

I tried to "connect internal", but the error message that it gave me indicates that syntax is long out of date.

It reminds me of a quote – "America can be trusted to do the right thing, after exhausting all the alternatives."

Don't misunderstand that to be anti-American. Consider another quote, "Democracy is the worst form of government, except for all the others."

So I'm just thankful to even have such problems. Anyway,...

Finally, under the Start Menu for my Oracle program group, under Configuration and Management Tools, I found the Administration Assistant for Windows. It offers a screen such as the one shown below.

This computer is named SANDBOX. I login as Blue Cutie, which is "an administrator", although not "THE administrator". Somehow this Administrator account on this server is gone. I tried to add it back, and it said it was already there. You can't win an argument like that, can you? That is a puzzle for another day.

Nevertheless, after I right-mouse clicked on the OS Database Administrators – Computer line, and added Blue Cutie (which is the account I login with) as an administrator, I was able to go back to SQL*Plus, connect / as sysdba, and set the password to whatever I want. "Alter user sys identified by whatever;"

Best wishes to always be able to regain access to your SYSDBA account.

~ the hard-working DBA.

How to collect Oracle system statistics.

We need to collect system statistics for our Oracle production databases.

As I have studied this, I see there are actually 3 categories of these statistics – dictionary statistics, fixed-object statistics, and system (hardware) statistics.

The first two are recommended for all applications, while the E-business Suite specifically does not want the system (hardware) statistics.

I don’t know what specific procedure or commands that our previous DBAs used to collect these statistics.   When I read their workplans in our change-tracking application, all they bothered to say was “1) Save old statistics, and 2) collect new ones.”   Sorry, that is just not specific enough for me.   Nor I have never noticed that we already have an official script to collect these statistics.  

So I wrote the following script, which makes sense to me.  

It produces the output shown below, which is about as safe and transparent as would seem worthwhile.

At least, this is what works for me, here, now.    No promises it will do anything for you.  In fact this is probably WRONG for you in at least 1 way, probably more!  Perhaps it will give you some ideas.   If so, you are welcome to them.

Nevertheless, “You can’t beat something with nothing.”, and this is a more specific and more recoverable than whatever secret procedure we used in the past.

Let me know if I can talk about that more.

And best wishes to always have accurate statistics.

~the hard-working DBA

> update_system_stats.ksh -i ORACAT20

Thu Jul 21 12:56:54 MDT 2011: Starting.

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 21 12:56:56 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> Connected.

SQL> SQL>            exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_DICT_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>         exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_FIXED_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>         --

SQL>         alter table ENC_DICT_STATS_20110721125654 move tablespace tools;

Table altered.

SQL>         alter table ENC_FIXED_STATS_20110721125654 move tablespace tools;

Table altered.

SQL>         --

SQL>         alter index ENC_DICT_STATS_20110721125654 rebuild tablespace tools;

Index altered.

SQL>         alter index ENC_FIXED_STATS_20110721125654 rebuild tablespace tools;

Index altered.

SQL>         --

SQL>         exec dbms_stats.export_dictionary_stats (               statown=>'SYSTEM',              stattab=>'ENC_DICT_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>         exec dbms_stats.export_fixed_objects_stats (            statown=>'SYSTEM',              stattab=>'ENC_FIXED_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>         --

SQL>         exec dbms_stats.gather_dictionary_stats ();

PL/SQL procedure successfully completed.

SQL>         --

SQL>         exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL>

SQL> exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_SYS_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>                 alter table ENC_SYS_STATS_20110721125654 move tablespace tools;

Table altered.

SQL>                 alter index ENC_SYS_STATS_20110721125654 rebuild tablespace tools;

Index altered.

SQL>                 exec dbms_stats.export_system_stats (                   statown=>'SYSTEM',                      stattab=>'ENC_SYS_STATS_20110721125654');

PL/SQL procedure successfully completed.

SQL>                 exec dbms_stats.gather_system_stats (                   gathering_mode=>'INTERVAL',                     interval=>60);

PL/SQL procedure successfully completed.

SQL>

SQL> set echo off

The following Stats tables are available to restore from, if necessary.

TABLE_NAME

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

ENC_SYS_STATS_20110721125654

ENC_FIXED_STATS_20110721125654

ENC_DICT_STATS_20110721125654

3 rows selected.

Here are the system statistics in effect now.

SNAME         PNAME           PVAL1 PVAL2

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

SYSSTATS_INFO STATUS                AUTOGATHERING

SYSSTATS_INFO DSTART                07-21-2011 13:08

SYSSTATS_INFO DSTOP                 07-21-2011 14:08

SYSSTATS_INFO FLAGS               0

SYSSTATS_MAIN CPUSPEEDNW 312.295633

SYSSTATS_MAIN IOSEEKTIM          10

SYSSTATS_MAIN IOTFRSPEED       4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

SYSSTATS_TEMP SBLKRDS      35062065

SYSSTATS_TEMP SBLKRDTIM    16253810

SYSSTATS_TEMP MBLKRDS      13777560

SYSSTATS_TEMP MBLKRDTIM    12537540

SYSSTATS_TEMP CPUCYCLES    50114736

SYSSTATS_TEMP CPUTIM      162174037

SYSSTATS_TEMP JOB                 1

SYSSTATS_TEMP CACHE_JOB           2

SYSSTATS_TEMP MBRTOTAL    218243782

22 rows selected.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Thu Jul 21 13:08:16 MDT 2011: Done.

> cat update_system_stats.ksh

#!/bin/ksh

#======== ========= ========= ========= ========= ========= ========= ==========

# Purpose: See Usage explanation.

#

# See Oracle Support Node ID: 368252.1

# That explains, it is "mandatory" to gather statistics for the SYS schema.

# With 10g, the gather_dictionary_stats procedure will do that.

#

# Statistics on the fixed objects (V$ views) are collected using the

# gather_fixed_objects procedure.  That is better than using the

# $APPL_TOP/admin/adstats.sql script, because that script requires the

# database to be in restricted mode, while defeats the whole purpose, because

# you want to collect statistics on the v$views while the database is

# running a typical workload.

#

# Finally it says, "The use of dbms_stats.gather_system_stats to collect

# system stats is not recommended with Apps E-Business Suite 11i or R12."

#

# This script should be run on all production databases about once per quarter

# while the database has a representative workload, say midweek, during

# midmorning or midafternoon.

#

# History

# 20110721      SWC     Wrote it.

#======== ========= ========= ========= ========= ========= ========= ==========

usage ()

{

echo    "

        Usage: update_system_stats.ksh -i ORACLE_SID [-m]

        This script will run the following procedures:

                dbms_stats.gather_dictionary_stats,

                dbms_stats.gather_fixed_stats,

                and IFF the instance is not running E-Business Suite, it will also run

                dbms_status.gather_system_stats.

        The previous statistics will be exported to tables with a date-time suffix,

        so if you decide you need to restore the old statistics, you can retrieve

        them from there.

        The script will use the ORACLE_SID to identify a file containing the

        other variables that need to be set for this script. That file is

        expected to be found in ~/scripts/env/$ORACLE_SID.env

        -m      an optional argument to e-mail the results, which might be

                useful if you want to schedule this as a cronjob.  So far

                I anticipate this will be run as needed in the foreground.

        The required variables in the env file are:

        ORACLE_SID

                the name of the Oracle instance to monitor.

        ORACLE_HOME

                where to find the Oracle software for this SID

        PATH

                where to find utilities used by this script.

        MAIL_READER

                who to notify in case the threshold is exceeded.

        NUM_DAYS_TO_SAVE_LOG_FILES

                will delete old logs older than the specified number of days.

        Routine output will be saved in a subdirectory called logs.

        The log file will be named update_system_stats.\$SID.\$DATE.log

        Best wishes."

exit 1

}

#======== ========= ========= ========= ========= ========= ========= ==========

export MAIL_IT=0

# Parse the command line parameters.

if [ $# -eq 0 ]; then

        usage

fi

while getopts m:i:h option

do

        case $option in

                i) SID=$OPTARG

                        ;;

                i) MAIL_IT=1

                        ;;

                h) usage

                        ;;

                \?) usage

                        ;;

        esac

done

#======== ========= ========= ========= ========= ========= ========= ==========

HOME=~

ENV_FILE=$HOME/scripts/env/$SID.env

if [ ! -f $ENV_FILE ]; then

        echo "Error: ENV file not found."

        echo "Expected to find $ENV_FILE."

        exit 1

fi

. $ENV_FILE

if [ "$ORACLE_SID." = "." ]; then

        echo "Error: ORACLE_SID is not set."

        exit 2

fi

if [ "$ORACLE_HOME." = "." ]; then

        echo "Error: ORACLE_HOME is not set."

        exit 3

fi

if [ ! -d $ORACLE_HOME ]; then

        echo "Error: ORACLE_HOME is not found."

        echo "Expected to find directory $ORACLE_HOME"

        exit 4

fi

if [ "$NUM_DAYS_TO_SAVE_LOG_FILES." = "." ]; then

        echo "Error: NUM_DAYS_TO_SAVE_LOG_FILES is not set."

        exit 8

fi

#======== ========= ========= ========= ========= ========= ========= ==========

echo "`date`: Starting."

DIR=`dirname $0`

cd $DIR

DIR=`pwd`

BASE=`basename $0 .ksh`

if [ ! -d $DIR/logs ]; then

        mkdir $DIR/logs

        # If the logs subdirectory doesn't exist, then create it

else

        # remove old logs

        find ./logs -name '*.log' -mtime +$NUM_DAYS_TO_SAVE_LOG_FILES -exec rm {} \;

        find ./logs -name '*.sql' -mtime +$NUM_DAYS_TO_SAVE_LOG_FILES -exec rm {} \;

fi

DATE=`date +%Y%m%d%H%M%S`

SQL_FILE=$DIR/logs/$BASE.$DATE.sql

LOG_FILE=$DIR/logs/$BASE.$DATE.log

sqlplus -s /nolog <<-EOF >$LOG_FILE

        connect system/$SYSTEM_PASSWORD@$ORACLE_SID

EOF

if [ `grep -c ORA- $LOG_FILE` -gt 0 ]; then

        echo "Error: Unable to connect to $ORACLE_SID as system."

        exit 1

fi

DICTIONARY_STAT_TABLE="ENC_DICT_STATS_$DATE"

SYSTEM_STAT_TABLE="ENC_SYS_STATS_$DATE"

FIXED_STAT_TABLE="ENC_FIXED_STATS_$DATE"

#======== ========= ========= ========= ========= ========= ========= ==========

# Collect dictionary and fixed stats for all instance types.

echo   "set echo on

        exec dbms_stats.create_stat_table ('SYSTEM', '$DICTIONARY_STAT_TABLE');

        exec dbms_stats.create_stat_table ('SYSTEM', '$FIXED_STAT_TABLE');

        --

        alter table $DICTIONARY_STAT_TABLE move tablespace tools;

        alter table $FIXED_STAT_TABLE move tablespace tools;

        --

        alter index $DICTIONARY_STAT_TABLE rebuild tablespace tools;

        alter index $FIXED_STAT_TABLE rebuild tablespace tools;

        --

        exec dbms_stats.export_dictionary_stats (\

                statown=>'SYSTEM', \

                stattab=>'$DICTIONARY_STAT_TABLE');

        exec dbms_stats.export_fixed_objects_stats (\

                statown=>'SYSTEM', \

                stattab=>'$FIXED_STAT_TABLE');

        --

        exec dbms_stats.gather_dictionary_stats ();

        --

        exec dbms_stats.gather_fixed_objects_stats;

        " > $SQL_FILE

#======== ========= ========= ========= ========= ========= ========= ==========

# If this instance is NOT E-Business suite, then collect system stats too.

sqlplus /nolog <<-EOF > $LOG_FILE

        connect system/$SYSTEM_PASSWORD@$ORACLE_SID

        set echo on

        select 'EU FOUND'

        from dual

        where exists (select 1 from dba_users where username = 'EU');

EOF

if [ `grep -c 'EU FOUND' $LOG_FILE` -eq 0 ]; then

        echo   "exec dbms_stats.create_stat_table ('SYSTEM', '$SYSTEM_STAT_TABLE');

                alter table $SYSTEM_STAT_TABLE move tablespace tools;

                alter index $SYSTEM_STAT_TABLE rebuild tablespace tools;

                exec dbms_stats.export_system_stats (\

                        statown=>'SYSTEM', \

                        stattab=>'$SYSTEM_STAT_TABLE');

                exec dbms_stats.gather_system_stats (\

                        gathering_mode=>'INTERVAL', \

                        interval=>60);

                " >> $SQL_FILE

fi

#======== ========= ========= ========= ========= ========= ========= ==========

# Let the user know where the old statistics are saved.

echo   "set echo off

        prompt "The following Stats tables are available to restore from, if necessary."

        select  table_name from dba_tables

        where table_name like 'ENC_%_STATS_%'

        ;

        prompt "Here are the system statistics in effect now."

        column sname format a13

        column pname format a10

        column pval2 format a19

        select * from sys.aux_stats$

        ;" >> $SQL_FILE

#======== ========= ========= ========= ========= ========= ========= ==========

# Finally run the SQL script.

sqlplus /nolog <<-EOF | tee $LOG_FILE

        connect system/$SYSTEM_PASSWORD@$ORACLE_SID

        @$SQL_FILE

EOF

if [ "$MAIL_IT" -eq 1 ]; then

        for MAIL_READER in $MAIL_READERS; do

                # Mail the results to the MAIL_READER

                HOST=`hostname`

                SUBJ="Info: $HOST: $ORACLE_SID: $BASE: Completed."

                FROMMAIL="`whoami`@$HOST"

                mailx -r $FROMMAIL -s "$SUBJ" $MAIL_READER < $LOG_FILE

        done

fi

#======== ========= ========= ========= ========= ========= ========= ==========

echo "`date`: Done."

I'm relieved to report that I have passed the DBA development test 70-433.  Here are some of the preparations I made.   If these ideas would be helpful to you, you are welcome to them, and I will wish you well.

Microsoft’s website for this exam has a long list of training materials.

For me, Step 1 was to read the book that Microsoft calls the Self-Paced Training Kit.   That book is definitely mandatory. I read that at least twice.   And just as important, I did ALL the practices too.

But when I took the assessment test on the disk included with that book, it was like I didn't know anything. There are 200 sample questions. I would try, say 10 questions, and get 1 right.  The book simply did not cover anywhere near the amount of material referenced by those questions.

Step 2.  I happened to have a copy of the SQL Server 2008 Internals book by Delaney et.al, which covers a lot of these same topics. That helped fill-in a number of gaps. Sometimes that book went WAY further into the internals than anyone needs to know for this test.   When that happened it was easy-enough to turn the page until it related to me again.   After that I could get 3 out of 10 questions right.

Step 3.   I read The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design, edited by Horniger.  I’m willing to say that was helpful. If you are going to take this test, I think it is definitely worth reading. I have to admit it really irritated me that some chapters seemed to have a misspelled word or grammatical error on every page. If we have all the time in the world, I have to think a team of us can write a better book than they did. But it was helpful. After that I could get 5 out of 10 right.

Step 4.  I read DeBetta's free Introduction to SQL Server 2008.  You can’t read the same thing over and over again.   Every additional author gives you a new way of thinking about things.   He has provided a great service to our community by providing a readable, good quality explanation full of good ideas.   It may be free, but it’s a keeper on my bookshelf.    

Step 5.   After all that, it was still not enough.   So basically I decided I had to write, or at least assemble, my own book. I mean, I would read a question, and say to myself, "Wow, I just do NOT know enough about that yet."  So I would do a Google Search, and find some article or blog about it, and paste that into a Word document for the things I needed to know more about myself. I assembled over 180 pages of such notes.

Step 6.   After that, the only thing left I could do was build my test-taking muscles. The book(s) will tell you what you CAN do. But the practice questions will help you see the LIMITs.

For example, I knew you can insert into view (with restrictions), and you can use the OUTPUT clause to return changed data back to the screen, or insert it to a table, or into a table variable, but OUTPUT cannot insert into a view. I can only remember such factoids if a practice question gives me a reason to.

Or, do you know that SET SHOWPLAN ON will show you the ACTUAL execution plan, while SET SHOWPLAN_XML ON will show you the ESTIMATED execution plan. I cringe to think about what my brain has to do to remember such details!

Because, practice does not make perfect. PERFECT PRACTICE makes perfect. If I didn't know the answer to a practice question, there is no point in guessing.  Nor is there any point to simply learning the answer to 1 question.  The best way to meet the challenge is to go study that on the spot.

Hopefully some of those ideas are helpful to you.
Best wishes.
~the hard-working DBA

How to measure the throughput of RMAN.

One day I had to (was granted the privilege to!) rehost a production database onto a new machine.

The Oracle 11g Backup and Recovery User’s Guide explains how to DUPLICATE a database.     The duplicate command is convenient because we can put all the instructions in a single command file.  That would be fine if wanted to copy a production database into development.

This database is over 850GB.   I’d like to rehost this database with less than 1 hour of down time.   If I do everything all at once in a single command file, it will take too long to restore the datafiles onto the new target, much less recover them.

Oracle 10g has a Backup and Recovery Advanced User’s Guide.  (If 11g has an advanced guide, I sure don’t see it.)   Chapter 7 has a section called “Restore and Recovery of the Database on New Host”, which explains a different procedure using the restore-database and recover-database command.   This should be the ideal procedure.

See, I can “restore” the database onto the new host any day of the week.   Then I can “recover” the latest transaction logs, in batches, until my maintenance window starts.

When the maintenance window starts, the following steps can be completed in less than an hour.

-          Stop the listener

-          Make sure the users are disconnected.

-          Alter system switch logfile N times, and let the archiver saves the online logs.

-          Shutdown the old instance

-          Apply the latest archived logs to the new instance

-          Alter system open resetlogs.

So.  I can test this right?  Fine.   So I setup the test, and start doing the “restore database” step.   I’m using 8 channels.  It seems to move along.   Every once in a while one of the channels finishes with one of the pieces.   Is there a way to measure how fast it is going?

The following query will serve this purpose:

set linesize 126

column Pct_Complete format 99.99

column client_info format a25

column sid format 999

column MB_PER_S format 999.99

select s.client_info,

l.sid,

l.serial#,

l.sofar,

l.totalwork,

round (l.sofar / l.totalwork*100,2) "Pct_Complete",

aio.MB_PER_S,

aio.LONG_WAIT_PCT

from v$session_longops l,

v$session s,

(select sid,

serial,

100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",

sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"

from v$backup_async_io

group by sid, serial) aio

where aio.sid = s.sid

and aio.serial = s.serial#

and l.opname like 'RMAN%'

and l.opname not like '%aggregate%'

and l.totalwork != 0

and l.sofar <> l.totalwork

and s.sid = l.sid

and s.serial# = l.serial#

order by 1;

This will produce a result-listing such as the following (spaces removed to fit).

CLIENT_INFO             SID SERIAL#   SOFAR TOTALWORK   Pct MB_PER_S LONG_WAIT

                                                                                        Complete                                     PCT

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

rman channel=ORA_DISK_1 336       7 3080004   6875904 44.79    16.76        79

rman channel=ORA_DISK_2 335       5   12293  12533760   .10    37.22        82

rman channel=ORA_DISK_3 334       5 5672665   6666112 85.10    37.47        82

rman channel=ORA_DISK_4 333       5 4183281   8384894 49.89    35.32        81

rman channel=ORA_DISK_5 339      36 2383878  12521472 19.04    14.11        78

rman channel=ORA_DISK_6 343      22   44293  10289152   .43    10.88        78

rman channel=ORA_DISK_7 341      19 6881776   7932672 86.75    35.76        80

rman channel=ORA_DISK_8 340      40 1476057   8941568 16.51    26.97        86

8 rows selected. 

Well.  This is ghastly.

The fastest channel of any of them is pushing the blazing-fast speed of 37.47 MB per second.  

I had to talk with my storage adminstrators about that. 

This machine is supposed to have a 1GB Ethernet connection.   That ought to be able to push over 110MB per second, and I’m barely getting one-third of that.   They eventually found that one of their interfaces was generating a lot of errors.  No wonder each channel is spending 80% of the time in a long-wait state

I ran the query multiple times, and it seemed like the average was 35MB/s.   I had to move 850GB of data.

850GB * 1024MB/GB / 35MB/s  * 1hour/3600s = 7.1 hours.

The actual time turned out to be 6.9 hours – close enough.

That showed the throughput is not cumulative.   Just because 8 channels push 35MB/s each does not mean they push (8*35=) 280MB/s.     Then I could have finished in about 1 hour.   Trust me, I would have noticed the difference

Hopefully you can get better throughput than that.   If not, talk to your coworkers, and see if they can find any blockage in the plumbing.

Best wishes.

~the hard-working DBA.

Every to-do list needs a schedule

Every to-do list needs a schedule.

One thing I have learned:  A to-do list without a schedule  is simply a wish-list, not a to-do list.   You will WISH you could do those things, and they will never get done till you find time for them.

On my last engagement, the company had a vision.  It identified strategies to implement the vision.  And it had goals for how to execute the strategies.   All very nice.

Our department had a list of list of projects necessary to accomplish the goals.  We must have had 200 projects on that list.  Most projects would take weeks.  Some would take months.  How could we get all the work done?    

The project managers and department leaders would get together every Monday morning at 9:00am.  We reviewed our progress from last week, and planned for who should work on what next. 

The beautiful thing was that we didn’t just say WHAT to do.  We ALLOCATED TIME for the staff to work on it.  

If we didn’t actually ALLOCATE TIME to work on something, then clearly it must not have been a priority.

It wasn’t always  perfect.   “No plan of battle survives the first shot.”  Nevertheless, it did make sure that projects with priority always continued to get sufficient resources to keep moving forward.  And it went a long way to make sure that management didn’t have unrealistic expectations for what the staff could do. 

When the staff knew what the priorities were, AND were given time to work on them, they always stepped to deliver, on time, with good quality.

The same has been true in my own life.  I can list dozens of things I’d like to do.  The only tasks that actually get done are the ones that I specifically allocate time for. 

You can ask yourself, what have you specifically allocated time for today?

Best wishes to always find time for the things that are actually important to you.
~ the hard-working DBA