HOWTO: Enable Archivelog Mode in a RAC database

Hi folks. Its been a long time since I posted so thought i’d better post something 🙂

This is a pretty trivial thing but I always forget the syntax hence thought i’d post it for the benefit others and myself so here goes…

1. Change the cluster database parameter so the database can be mounted in exclusive mode which is required to enable archive logging

alter system set cluster_database=false scope=spfile;

2. Shutdown the database using srvctl

srvctl stop database -d ORA

3. Startup one of the instances upto the mount state

sqlplus / as sysdba

startup mount

4. Enable archivelog mode

alter database archivelog;

5. Change the cluster_database parameter back to true in the spfile

alter system set cluster_database=true scope=spfile;

6. Shutdown the instance

shutdown immediate

7. Startup the database using srvctl

srvctl start database -d ORA

8. Once the database is back up you can verify the change by connecting to one of the database instances

sqlplus / as sysdba

archive log list

For example:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archlogs/ORA/
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

Simple as that.

Notes:

You don’t need to set log_archive_dest1 as it defaults to the flash recovery area (ie USE_DB_RECOVERY_FILE_DEST) although you’ll need to make sure it is large enough for your needs.

I usually make sure that log_archive_format includes the db name, ie ORA_%t_%s_%r.arc, but again you don’t have to.

Modifying AWR Automatic Snapshot Settings

Sorry i’ve not posted for a while, my only excuse is that we have moved house so had no internet for two weeks. Anyway, back to business with another quick one which is again based on the fairly hefty subject of the Automatic Workload Repository, also known as AWR.

There are a number of AWR views (all starting DBA_HIST_) which I will go into in other posts but the one we are interested in to display the AWR settings is DBA_HIST_WR_CONTROL. This view only has 4 columns which are:

SQL> desc dba_hist_wr_control
 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 DBID                            NOT NULL NUMBER
 SNAP_INTERVAL                   NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION                       NOT NULL INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                                  VARCHAR2(10)

If we select from it we can see the defaults

SQL> set linesize 100
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3566081556 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

So, the defaults are automatic snapshots are taken every 1 hour and snapshots are kept for 7 days.

So, what if we want to change these? Oracle have supplied a package DBMS_WORKLOAD_REPOSITORY which is used to take manual snapshots, create baselines and the important one for us, change snapshot settings. So, for example, if we wanted to change the settings so we took snapshots every 10 minutes and kept these snapshots for 10 days we would issue the following sql:

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

All the values are in minutes, so for the retention 14400 is 10 days. So, if we now look at dba_hist_wr_control we can see our change has been reflected

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3566081556 +00000 00:10:00.0    +00010 00:00:00.0    DEFAULT

It is always worth reviewing the size of your SYSAUX tablespace before you make any changes. Oracle have provided a handy script for this, which is called utlsyxsz.sql (held in $ORACLE_HOME/rdbms/admin) which will estimate the required size for the SYSAUX tablespace based on the retention period, snapshot interval you are going to use and a few other variables like number of active sessions and number of tables.

Giving Access to run AWR/ADDM reports to a non priv user

Another quick one as its one I just found and, as it turns out, is easy peasy…

SQL>  create user awradm identified by whatever  2  default tablespace users 3  temporary tablespace temp;

User created.

SQL> grant connect, resource, advisor to awradm;

Grant succeeded.

SQL> grant select any dictionary to awradm;

Grant succeeded.

SQL> grant advisor to repview;

Grant succeeded.

SQL> grant execute on dbms_workload_repository to repview;

Grant succeeded.

…..And thats it…the awradm user can now run awrrpt.sql and addmrpt.sql

HOWTO: Manually Convert Single Instance to RAC on Unix

Now, I know what you are going to say and yes I know there is a new utility in 10g called rconfig that will do all this for you but just in case you want to do it the old fashioned way I thought I’d include the steps to do it. So, here we go…

1. Convert the oracle home on each Node

(Note 211177.1 on Metalink)

a. Login as the Oracle software owner and make sure any databases running out of this oracle home are down

b. cd $ORACLE_HOME/rdbms/lib

c. make -f ins_rdbms.mk rac_on

If this step did not fail with fatal errors then proceed to step 4.

d. make -f ins_rdbms.mk ioracle

2. Each instance require its own redo thread. So add a new logfile thread for each additional instance.

alter database add logfile thread 2   group 4 ('/oradata1/ORA2/ORA2_t2g4_m1.rdo',           '/oradata1/ORA2/ORA2_t2g4_m2.rdo') size 50m,   group 5 ('/oradata1/ORA2/ORA2_t2g5_m1.rdo',           '/oradata1/ORA2/ORA2_t2g5_m2.rdo') size 50m,   group 6 ('/oradata1/ORA2/ORA2_t2g6_m1.rdo',           '/oradata1/ORA2/ORA2_t2g6_m2.rdo') size 50m / ALTER DATABASE ENABLE PUBLIC THREAD 2 /

3. Each instance requires its own undo tablespace. So add an undo tablespace for each additional instance

create undo tablespace undo02 datafile '/oradata1/ORA2/ORA2_undo02_01.dbf' size 4001m /

4. Create the cluster views needed for RAC

SQL> @?/rdbms/admin/catclust

5. If you are using an spfile, create an init.ora from it.

SQL> create pfile='/tmp/initORA.ora' from spfile
/

6. Edit the init.ora to include the cluster parameters

*.cluster_database_instances=2
*.cluster_database=TRUE
ORA1.instance_name='ORA1'
ORA2.instance_name='ORA2'
ORA1.instance_number=1
ORA2.instance_number=2
ORA1.thread=1
ORA2.thread=2
ORA1.undo_tablespace='UNDO01'
ORA2.undo_tablespace='UNDO02'
ORA1.local_listener='LISTENER_ORA1'
ORA2.local_listener='LISTENER_ORA2'
ORA1.remote_listener='LISTENER_ORA2'
ORA2.remote_listener='LISTENER_ORA1'

7. Shutdown and startup using the edited init.ora

SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup pfile='/tmp/initORA.ora'

8. If the db starts up ok using this init.ora, create your spfile in a shared location

SQL> create spfile='/sharedlocation/spfileORA.ora' from pfile='/tmp/initORA.ora';

9. On each node create a link in $ORACLE_HOME/dbs to the shared spfile, ie

cd $ORACLE_HOME/dbs
ln -s /sharedlocation/spfileORA.ora spfileORA1.ora

10. Add the database and instances to the cluster registry

srvctl add database -d ORA -o $ORACLE_HOME
srvctl add instance -d ORA -i ORA1 -n oraserv1
srvctl add instance -d ORA -i ORA2 -n oraserv2

11. Start the db through server control

srvctl start database -d ORA

NB If you still have one instance up from step 7 you will get an error but this is nothing to worry about, as the node that is down should still start.

12. Create services as needed.

This can be done through the dbca under Service management or manually as follows:

srvctl add service -d ORA -s ORA_TAF -r ORA1, ORA2

Your single instance database is now RAC enabled. I have only recently done this so I know these steps work. 🙂

Creating a DTP enabled RAC Service

OK, this is going to be a quickie but thought I’d include it as its something I only use rarely hence by the time I’m doing it again i’ve forgotten it and have to go ferreting through the documentation to find the syntax. So, anyway here goes….

Firstly, lets create a RAC service:

srvctl add service -d ORA -s ORA_XA1 -r ORA1 -a ORA2

Now to enable if for Distributed Transaction Processing and XA:

execute dbms_service.modify_service (service_name => 'ORA_XA1' , dtp => true);

There is also a good article on the oracle website concerning RAC and XA as follows:

link

Sequences in Oracle 10g RAC

Yay my first post and I’m going to start with something that caught me out a while back. I apologize in advance for plagorising various web pages on the same subject, but i will try to use most of my own content on this one.

Ok lets create a sequence on my two node RAC database with a cache of 50:

09:48:17 SQL> create sequence seq_rac_test cache 50;

Sequence created.

Elapsed: 00:00:00.03

Now we can select a couple of values from it from RAC node 1:

09:48:33 SQL> select seq_rac_test.nextval
 09:50:50   2  from dual;

NEXTVAL
 ----------
 1

Elapsed: 00:00:00.01
 09:50:53 SQL> /

NEXTVAL
 ----------
 2

Elapsed: 00:00:00.00
 09:50:54 SQL> /

NEXTVAL
 ----------
 3

Elapsed: 00:00:00.00
 09:50:55 SQL> /

NEXTVAL
 ----------
 4

Elapsed: 00:00:00.00

Now lets try selecting a few from node 2:

09:51:54 SQL> select seq_rac_test.nextval
 09:51:57   2  from dual;

NEXTVAL
 ----------
 51

Elapsed: 00:00:00.27
 09:51:59 SQL> /

NEXTVAL
 ----------
 52

Elapsed: 00:00:00.00
 09:52:01 SQL> /

NEXTVAL
 ----------
 53

Elapsed: 00:00:00.00
 09:52:02 SQL> /

NEXTVAL
 ----------
 54

Elapsed: 00:00:00.00

So, each instance caches a chunk of numbers to allocate for the sequence. So, i hear you cry, what happens when instance 1 runs out of its 50 and has to grab another chunk? Well, lets try it:

………

09:55:50 SQL> /

NEXTVAL
 ----------
 48

Elapsed: 00:00:00.01
 09:55:50 SQL> /

NEXTVAL
 ----------
 49

Elapsed: 00:00:00.00
 09:55:51 SQL> /

NEXTVAL
 ----------
 50

Elapsed: 00:00:00.00
 09:55:51 SQL> /

NEXTVAL
 ----------
 101

Elapsed: 00:00:00.46

Surprise surprise it grabs the next 50 after the chunk instance.

OK so lets try it with the order clause.

09:59:54 SQL> create sequence seq_rac_test cache 50 order;

Sequence created.

Elapsed: 00:00:00.01

Lets do the same as we did with the last test and select a few from instance 1:

10:00:43 SQL> select seq_rac_test.nextval from dual;

NEXTVAL
 ----------
 1

Elapsed: 00:00:00.45
 10:01:44 SQL> /

NEXTVAL
 ----------
 2

Elapsed: 00:00:00.00
 10:01:45 SQL> /

NEXTVAL
 ----------
 3

Elapsed: 00:00:00.00
 10:01:46 SQL> /

NEXTVAL
 ----------
 4

Elapsed: 00:00:00.00

And now from instance 2:

09:52:02 SQL> select seq_rac_test.nextval from dual;

NEXTVAL
 ----------
 5

Elapsed: 00:00:00.02
 10:02:18 SQL> /

NEXTVAL
 ----------
 6

Elapsed: 00:00:00.01
 10:02:20 SQL> /

NEXTVAL
 ----------
 7

Elapsed: 00:00:00.01
 10:02:20 SQL> /

NEXTVAL
 ----------
 8

Elapsed: 00:00:00.00

So with the order clause we can see that oracle will guarantee that the sequence numbers will be delivered in order. Obviously this clause doesn’t really make much difference with a single instance db but with a multi node RAC database it makes a HUGE difference.

Reading up on this RAC uses a global lock to make sure the sequence numbers are delivered in order. Then when the sequence numbers are all allocated the sequence needs to be updated which then causes a read over the interconnect for the block containing the specific sequence object.

Anyway, hope this little test is of use to people other than me and I apologize to other notes on the same subject for blatantly robbing bits and pieces to add to this note.

Welcome and Disclaimer

Welcome to my new blog. I have a few blogs but unfortunately none of them were accessible from work so this is a bit of a test/trial. The idea is that I am going to use this for oracle stuff that I should remember but don’t, so hopefully it will be useful to others as well as myself.

The usual disclaimer applies:

The opinions expressed herein are my own personal opinions and no one else’s. So, if you like what you read tell your friends and if you don’t keep your gob shut 🙂

Anyway, hope you like the stuff I write and find it useful and feel free to comment either way…