Oracle Restart hands on

Starting from Oracle Database 11g, a new product (or functionality) called Oracle Restart comes with the part of the Grid Infrastructure installation. It seems Oracle decided to use crsctl, crs_stat, and srvctl like RAC commands for also managing the processes of the single instance databases. This standardization seems handy to me as i have already get used to manage RAC databases day by day.

After upgraded one of the development databases in our data center from 10.2.0.4 to 11.2.0.2 as well as the ASM instance, i decided to spend some of my time to play with this new functionality.

As on the RAC installations status of the services can be investigated with the crs_stat -t command. I think it is understandable that there is not vip, ons, gsd services here as this is not a RAC database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6 

[oracle@rhel6]:/oracle > crs_stat
NAME=ora.DG_DB_ASM.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.asm
TYPE=ora.asm.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on rhel6

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on rhel6 

The processes of the CRS (it is "HAS" for single instance) is again controlled by crsctl as it is in the RAC installations. You can use check, start, stop options to manage the processes as usual. A small note; CRS processes in the RAC installation is not installed for the single instance installations. For the single instance installations, there is the HAS processes stands for "High Availability Services" and covers the cssd and diskmon processes.

[oracle@rhel6]:/oracle > crsctl check has
CRS-4638: Oracle High Availability Services is online
[oracle@rhel6]:/oracle > crsctl check css
CRS-4529: Cluster Synchronization Services is online
[oracle@rhel6]:/oracle > crsctl check resource ora.cssd

[oracle@rhel6]:/oracle > crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel6'
CRS-2673: Attempting to stop 'ora.cssd' on 'rhel6'
CRS-2677: Stop of 'ora.cssd' on 'rhel6' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rhel6'
CRS-2677: Stop of 'ora.diskmon' on 'rhel6' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel6' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@rhel6]:/oracle > crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

After my upgrade process Oracle Restart could not be able to manage the upgraded database. By using srvctl i added the database resource to the repository so that i can manage the database services by using srvctl command line tool. One of the nicest option is, by using the "-a" option and supplying dependent diskgroups of the database makes Oracle Restart to start the ASM and mount the related diskgroups before starting up the database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl add database -h          

Adds a database configuration to be managed by Oracle Restart.

Usage: srvctl add database -d  -o  
  [-m ] 
  [-p ] 
  [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] 
  [-s ] 
  [-t ] 
  [-n ] 
  [-y {AUTOMATIC | MANUAL}] 
  [-a ""]
-d       Unique name for the database
-o          ORACLE_HOME path
-m               Domain for database. Must be set if database has DB_DOMAIN set.
-p               Server parameter file path
-r                 Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s        Startup options for the database. Examples of startup options are open, mount, or nomount.
-t         Stop options for the database. Examples of shutdown options are normal, transactional, immediate, or abort.
-n         Database name (DB_NAME), if different from the unique name given by the -d option
-y             Management policy for the database (AUTOMATIC or MANUAL)
-a ""      Comma separated list of disk groups
-h                       Print usage

[oracle@rhel6]:/oracle > srvctl add database -d ORCLT -o /oracle/orahome1
[oracle@rhel6]:/oracle >
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl start database -d ORCLT
[oracle@rhel6]:/oracle > ps -ef | grep smon
oracle  9109530        1   0 15:46:08      -  0:00 ora_smon_ORCLT
oracle 11075806        1   0 15:43:50      -  0:00 asm_smon_+ASM
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle >                                                                                                     
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl status database -d ORCLT
Database is running.

[oracle@rhel6]:/oracle >            
                    
[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   OFFLINE   ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6

The second handy feature is "enable" and "disable" of the srvctl which configures the related objects restart options on host restart or restart of the process on failure.

[oracle@rhel6]:/oracle > srvctl enable -h 

The SRVCTL enable command enables the named object so that it can run under 
  Oracle Restart for automatic startup, failover, or restart.

Usage: srvctl enable database -d 
Usage: srvctl enable service -d  -s ""
Usage: srvctl enable asm
Usage: srvctl enable listener [-l ]
Usage: srvctl enable diskgroup -g 
Usage: srvctl enable ons [-v]
Usage: srvctl enable eons [-v]

Shutting down everything nicely with Oracle Restart.

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT
[oracle@rhel6]:/oracle > srvctl stop diskgroup -g DG_DB_ASM
[oracle@rhel6]:/oracle > srvctl stop asm
[oracle@rhel6]:/oracle > srvctl stop listener

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type OFFLINE   OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6  

resources:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/restart001.htm
$ srvctl -h

Random Data Generation and DBMS_RANDOM

Generating random data itself is already a subject of its own. But here i can only write about the needs of random data in a database environment and how a database developer or administrator can generate the random data to fulfill his/her requirements. I personally used random data generation in three main purposes up to now.

DATAMASKING

Almost in every company i have worked for, datamasking is a must-have procedure for test data generation, especially after restoring production data to a prelive or day-1 database. In common sense just truncating the sensitive data or updating the columns to NULL maybe enough for security purposes. But in some individual cases application logic needs to continue which depends on the data availability. In such cases there are two main roadmaps, hashing the real data and un-identify it or some random data may be generated for representing the real sensitive data. The generated data may also ensure the formatting of the known structures such as credit card information, postal code or telephone number of the customers.

PASSWORD GENERATION

After applying the new password policy to our database, forgotten passwords by the end users are not recovered that easily because of the password complexity functions (by applying password verify function) and the password_reuse_max parameter of the profile. This means that, the portal used by the level-1 support is not enough as the procedure lies behind the button is simply changes the password to a default value which is not changed from the beginning of the procedure at all, which causes ORA-28007: the password cannot be reused exception.

TEST DATA GENERATION

In order to test any functionality or any new features by myself, generating data for newly created tables is a necessity. Formatted varchar2 and number columns are very handy to test some functionalities and how some functionalities behave in large amount of structured table data.

Dictionary views can be used for this purpose but If it is not enough for the individual test conditions then generation a random data within the desired format is crucial.

DBMS_RANDOM PACKAGE

For all the mentioned purposes, oracle database has a built-in package which is DBMS_RANDOM to generate random data. There are several functions in this package which can be used to obtain same results with some manipulation of the output values. Main functions to produce random number would be;

DBMS_RANDOM.VALUE() whichs output value is NUMBER datatype, this means you can produce up to 32 digit numbers.

select dbms_random.value() as result from dual;

RESULT
------
0.33363563290178533954768590716355821427

DBMS_RANDOM.VALUE(arg1 number, arg2 number) returns a random value between the supplied limits. The following sql should return a random number between 1 and 20.

select dbms_random.value(1,20) as result from dual;

RESULT
------
14.25291834803147861791906299688905822678

DBMS_RANDOM.STRING() function can also be used in order to generate a random character or a string. Function has two parameters one for the type of characters to be used and the second parameter for the length of the string. The following function definition is copied here from and Oracle 11gR2 databases DBMS_RANDOM package spec. It explains itself very well and nothing needs to be added.

FUNCTION string (opt char, len NUMBER)

/* 
"opt" specifies that the returned string may contain:

'u','U': upper case alpha characters only
'l','L': lower case alpha characters only
'a','A': alpha characters only (mixed case)
'x','X': any alpha-numeric characters 'p','P': any printable characters
*/
RETURN VARCHAR2;  
--string of  characters

So, if you need to produce some random string with 8 characters long and all characters are alpha numeric and lower case then it is easy by using the DBMS_RANDOM.STRING() function:

select dbms_random.string('l',8) as result from dual;

RESULT
------
yhxctomv

DBMS_RANDOM BY EXAMPLES

All the explanations of the examples can be found in the document that i shared from here. You can also find the link at the bottom of this post to the same document.

--------------------------
--masking the card numbers
--Ex.1
--------------------------
select 
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0) || '-' ||
lpad(round(dbms_random.value*power(10,4)),4,0)  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
2877-6639-0728-5456
6026-6002-2218-9038
7679-8441-0899-2826
8294-6783-6110-7988
1836-0407-9206-3333
*/

--------------------------
--masking the card numbers
--Ex.2
--------------------------
select 
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000')) || '-' ||
ltrim(to_char(dbms_random.value(1,9999),'0000'))  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
1558-9846-7194-5325
5109-3233-0641-9209
3081-5946-9840-6615
4400-9638-6333-9113
2928-9883-1771-0465
*/

--------------------------
--masking the card numbers
--Ex.3
--------------------------
select 
ltrim(replace(to_char(round(dbms_random.value*power(10,16)),'0000,0000,0000,0000'),',','-')) as card_number
from dual
connect by level <=5;
/*
CARD_NUMBER
-----------
0157-8125-6418-6025
3829-9039-1357-9048
2876-1086-5371-8152
2775-1748-2591-2523
2058-2404-1101-5320
*/

--------------------------
--masking the card numbers
--Ex.4
--------------------------
select 
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4) || '-' ||
substr(abs(dbms_random.random),1,4)  as card_number
from dual 
connect by level <=5; 
/*
CARD_NUMBER
-----------
8639-7576-1359-3965
1317-1525-2526-1796
1043-5881-1000-7113
2106-3239-8662-3769
1461-7473-5870-6829
*/

--------------------------
--masking the phone number
--Ex.5
--------------------------
select 
  '+' || 
  round(DBMS_RANDOM.VALUE(1,99)) || '-' ||
  round(DBMS_RANDOM.VALUE(10,99)) || '-' ||
  round(DBMS_RANDOM.VALUE(1000000,9999999)) as phone_number
from dual
connect by level <= 5;
/*
PHONE_NUMBER
------------
+8-44-9146987
*/

----------------------
--masking the postcode
--Ex.6
----------------------
select 
  round(dbms_random.value(1000,9999)) || '-' || 
  dbms_random.string('U',2) as postcode 
from dual;
/*
POSTCODE
--------
4997-QP
*/

What if, you have a password verify function which commits the passwords will be at least 8 characters long and must contain alphanumeric characters and this password complexity merged with a profile which has a password_lifetime of two month and password_reuse_max is four. If there is a predefined automatic case which explained detailly in the paper mentioned before which needs random password generation then there is the example which can be used;

-----------------
--random password
--Ex.7
-----------------
select  
  DBMS_RANDOM.STRING('A',1) || 
  round(DBMS_RANDOM.VALUE()*10) || 
  DBMS_RANDOM.STRING('X',6) as password 
from dual;
/*
PASSWORD
--------
w7N3C1YG
*/

TEST DATA GENERATION

I generally use two different methods while generating test data to fill the test tables. One of them is by using the DBMS_RANDOM package and the other is filling the bulk data in the columns with the same output as you can find in the following examples.

In this first part of the following example, the code tries to simulate a username bu using lower case string and with random lengths between five and fifteen. The firstname starts with uppercase by using INICAP() function and the lastname is fully in uppercase by using the UPPER() inline function. The second part is not that clever and it just creates the same data over and over which can substitude a customer name or a username.

----------------------
--test data generation
--Ex.8
----------------------
select 
  initcap(dbms_random.string('L',round(dbms_random.value(5,15)))) || ' ' || 
  upper(dbms_random.string('L',round(dbms_random.value(5,15)))) as name
from dual
connect by level <= 5;
/*
NAME
----
Abzvsidgbcfa AGUGIR
Wvuogptkxwhdwa IPOOXTVBLLCNPV
Yiwcgh SGPFKJYCDISO
Radshiyidcrst ZNKNSEYUZXVWY
Daxeqzugq LKJILZJEYULVI
*/

select
initcap(lpad('x',round(dbms_random.value(5,15)),'x')) || ' ' || 
upper(lpad('y',round(dbms_random.value(5,15)),'y')) as name
from dual
connect by level <= 5;
/*
NAME
----
Xxxxxxxxxxxx YYYYYYYYYYY
Xxxxxxxxxxxxxxx YYYYYYYYYYYYY
Xxxxxxxxxxxxxx YYYYYYYYY
Xxxxxxxx YYYYYYYYYYY
Xxxxxx YYYYYYYY
*/


select 
  initcap(lpad('x',9,'x')) || ' ' ||
  upper(lpad('y',9,'y')) as name
from dual
connect by level <= 5;
/*
NAME
----
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
Xxxxxxxxx YYYYYYYYY
*/

GENERATING MEANINGFUL DATA

Upto now, the generated data was completely dummy. For example while masking the credit card number, it wasn’t important if this was a valid card number or not, or if the names are real names or not. All the data generated were dummy random data which does not make any sense, they are just string or numbers formed by number characters or digits.

But by using the following example random data can be generated which makes sense (or a little sensible than the previous methods).

In this small example, there is a lookup table (which can be extended as far as the individual case needs) and the phone number generator picks up a random country code from the lookup table to generate the phone number. Every random phone number generated will have a valid country code by using the following example. Of course this example can be extended for the area codes as well.

--------------------------
--generating sensible data
--Ex.9
--------------------------
create table t_country_codes 
  (key number(2), 
   code number(2), 
   country varchar2(20));
   
insert into t_country_codes values (1,1,'United States');
insert into t_country_codes values (2,31,'Netherlands');
insert into t_country_codes values (3,44,'United Kingdom');
insert into t_country_codes values (4,49,'Germany');
insert into t_country_codes values (5,90,'Turkey');
commit;

select 
  '+' || code || '-' || 
  round(dbms_random.value(10,99)) || '-' || 
  round(DBMS_RANDOM.VALUE(100000,999999)) as phone_number
from 
  t_country_codes 
where 
  key=(select round(DBMS_RANDOM.VALUE(1,5)) from dual);
/*  
PHONE_NUMBER
------------
+31-54-732777
*/  

This post is taken from the paper i wrote for an internal use and enchanced for my blog post. You can download the paper from the following link if you have google docs access. Generating Random Data in Oracle 11g Database

Howto Recover Archive Gap in Streams Configuration

When i realized that my archive logs are not shipping to the destination database (both the databases are version 10.2.0.4) which is using downstream capture process for streams replication it was too late that i already missed around 20 archived logs. I fixed the problem which was originated from the different service definition in the log_archive_dest_2 system parameter and the TNS alias. But what about the missing archived logs?

--SOURCE DB
SQL> select name, value from v$parameter where name = 'log_archive_dest_2';

NAME               VALUE
----               -----
log_archive_dest_2 SERVICE=ODSD ASYNC NOREGISTER
                   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   
                   DB_UNIQUE_NAME=ODSD

After correcting the entry in the tnsnames.ora file log shipping started from where it paused. I tested it by simply archiving the current redolog.

--SOURCE DB
SQL> alter system archive log current;
System altered.

SH> ls
-rw-r-----    1 oracle   dba      43451392 Aug 05 09:00 1_21759_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug 05 09:57 1_21760_657122256.dbf

--DESTINATION DB
SH> ls
-rw-r-----    1 oracle   dba      15930368 Aug  4 16:38 1_21727_657122256.dbf
-rw-r-----    1 oracle   dba       8708608 Aug  5 09:58 1_21760_657122256.dbf

Now the question how can i recover the gap between the last archivelog and the one created approximately 12 hours ago. First i should define the exact archived logs should be carried from the source to the target. I will check the capture process and see which SCN is it waiting for. As i am using a downstream capture process i should check the capture process in the target database.

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523421102323

I find the SCN number which is waited in the target. I should find which archived log is this scn in the source database?

--SOURCE DB
SQL> SELECT 
  name, dest_id, sequence#, first_change#, next_change#, completion_time 
FROM V$ARCHIVED_LOG where 7523421102323 between first_change# and next_change#

NAME                               DEST_ID SEQUENCE# FIRST_CHANGE# COMPLETION_TIME
----                               ------- -------- ------------- ---------------
/oracle/.../1_21728_657122256.dbf  1       21728    7523421102321 04/08/11 16:38:52
ODSD,                              2       21728    7523421102321 04/08/11 16:38:54
/oracle/.../1_21729_657122256.dbf  1       21729    7523421102323 04/08/11 16:39:10

It seems that after sequence# 21728 archived logs are not shipped to the destination database. What i will do is to copy these archived logs manually to the destination host from the target by using OS commands.

After copying the archived logs i have to register the archived logs for the streams configuration. The beginning of the register command is similar to the one we already know. And a small oppss!...

--DESTINATION DB
SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf';

ORA-16225: Missing LogMiner session name for Streams

SQL> select name, source_database from DBA_LOGMNR_SESSION;

ID NAME               SOURCE_DATABASE
-- ----               ---------------  
2  CAPTURE_TABLE_GRP1 CORED.CEB.LOCAL

SQL> alter database register logical logfile '/oracle/admin/ODSD/archive/1_21759_657122256.dbf' for 'CAPTURE_TABLE_GRP1';

Database altered.

SQL> select logmnr_session_id, name from DBA_LOGMNR_LOG; --or dba_registered_archived_log

LOGMNR_SESSION_ID NAME
----------------- ----
2                 /oracle/admin/ODSD/archive/1_21759_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21760_657122256.dbf
2                 /oracle/admin/ODSD/archive/1_21761_657122256.dbf

Lets check the capture process again. And the second oppss!..

--DESTINATION DB
SQL> select capture_name, state from v$streams_capture;

no rows selected.

SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;

CAPTURE_NAME  STATUS CAPTURED_SCN APPLIED_SCN
------------  ------ ------------ -----------
CAPTURE_TABLE_GRP1 ABORTED 7523421102323 7523421102323

It seems the capture process is aborted while we are registering the archived logs. Maybe we need to restart it.

SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');

SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME       STATE
------------       -----
CAPTURE_TABLE_GRP1 WAITING FOR DICTIONARY REDO: SCN 7523573032011

It seems the problem is solved and the capture process moves on with the next scn and the archived logs.