Oracle Tips and Techniques

The srvctl modify instance command is used to modify an instance configuration in the  OCR (Oracle cluster registry).
Used to modify the configuration for a database instance from its current node to another node or
changes the dependency between and ASM instance and a database instance.

Syntax:

srvctl modify instance -d db_unique_name -i inst_name {-n node_name | -s asm_instance_name | -r}

-d database name (unique name)
-i database instance name.
-n Node name.
-s asm_instance_name: name of the ASM instance (dependency to database instance).
-r : Remove ASM instance dependency from database instance.

 

 
Examples

An example of this command to relocate a database instance is:

srvctl modify instance -d crm -i crm1 -n my_new_node

The following example of this command establishes a dependency between an ASM instance and a database instance:

srvctl modify instance -d crm -i crm1 -s asm1

Filed under: Oracle Concept by admin
1 Comment »

The srvctl modify database command is used to modify a database configurations in the  OCR (Oracle cluster registry).
The OCR is the repository used by the CRS (cluster readu services) processes to run a database in RAC environment.

 

Syntax:
srvctl modify database -d db_unique_name [-n db_name] [-o oracle_home] [-m domain_name]
  [-p spfile] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s start_options] [-y {AUTOMATIC | MANUAL}]
 
-d database name (unique name)
-n db_name: database name if different from unique name
-o oracle_home:Oracle home
-m domain_name: Domain name of the database
-p spfile: server parameter file
-r role [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY]: Role of the database
-s start_options:Startup options for the database.
-y Management policy for the database, either automatic or manual.
-h

Example:

srvctl modify database -d mndb -r logical_standby

This command makes the database mndb a LOGICAL_STANDBY database.

srvctl modify database -d mndb -r physical_standby

This command makes the database mndb a PHYSICAL_STANDBY database.

Filed under: CRS by admin
2 Comments »

The srvctl add commands can be used to add a database, instance, service, nodeapps and asm configurations in the  OCR (Oracle cluster registry). The OCR is the repository used by the CRS (cluster readu services) processes to run a database in RAC environment.

So the following operation can be performed

srvctl add database
srvctl add instance
srvctl add service
srvctl add nodeapps
srvctl add asm

SRVCTL ADD DATABASE

This command should be used to add a database configuration to the clustered database configuration.

Sintax:

srvctl add database -d db_name -o oracle_home

-d is database name
-o is oracle home

Example:

srvctl add database -d mndb -o /u01/app/oracle/product/11.1.0/db_1

This example adds a database named mndb and specifies that oracle home is in
/u01/app/oracle/product/11.1.0/db_1

SRVCTL ADD INSTANCE

This command should be used to add an instance configuration to the clustered database configuration.

Syntax:

srvctl add instance -d db_name -i instance_name -n node_name

-d is database name
-i name of the instance
-n name of the node where the instance should normally run

Example:

srvctl add instance -d mndb -i mndb1 -n node01

The above example will add an instance called mndb1 to the node node01. The instance will be based on the database called mndb.

SRVCTL ADD SERVICE
This command should be used to add service configuration to the clustered database configuration.

Syntax:

srvctl add service -d db_unique_name -s service_name -r preferred_list [-a available_list] [-P TAF_policy]

-d db_unique_name (the unique database name)
-s service_name (a name to the service that you are adding)
-r preferred list, a list of preferred instance where the service should run
-a available list on instances. The list will be used when no preffered instance is available
-P TAF (transparent application failover) polic (e.g NONE, BASIC, or PRECONNECT).

Example:

srvctl add  service -d mndb -s mndb_sales -r mndb1 -a mndb2
srvctl add  service -d mndb -s mndb_sales -r mndb1 -a mndb2 -P Preconnect

SRVCTL ADD NODEAPPS
This command should be used to add nodeapps configuration to the clustered database configuration.

Syntax:

srvctl add nodeapps -n node_name -o oracle_home -A addr_str

-n node name
-o oracle home location
-A address string

VIP (virtual ip) address (name|ip/netmask[/if1[|if2|...]]).

Example:

srvctl add nodeapps -n node01 -o /u01/app/oracle/product/11.1.0/db_1 -A 132.12.23.40/255.255.255.0

SRVCTL ADD ASM
This command should be used to add asm configuration to the clustered database configuration.
Adds a record for an ASM instance to the specified node.

Syntax:

srvctl add asm -n node_name -i asm_instance_name -o oracle_home

-n node name
-i asm instance name
-o oracle home (e.g. /u01/app/oracle/product/11.1.0/db_1)

Example:

srvctl add asm -n node01 -i asm01 -o /u01/app/oracle/product/11.1.0/db_1

Filed under: CRS by admin
No Comments »

 Simply starting an Oracle database with startup command is not too complex. Actually it is just running a few commands and your database will come up happily and be ready for normal operation.

 For example to start your Oracle database you can just login and execute startup command as follows.

 $sqlplus / as sysdba

 SQL>startup

 

This command will start your database.

 But in the background there are a few stages that are hidden when you use the above command. Understanding these stages will help you to get a better insight view of Oracle startup process.

Oracle startup process consists of three stages

Stage 1: NOMOUNT

Stage 2: MOUNT

Stage 3: OPEN

 

 Stage 1: NOMOUNT

This is the first stage in the startup process.  You can start Oracle database in nomount mode using the command

SQL>startup nomount

When you execute the above command, an Oracle instance is started. When instance starts it will read the initialisation file (commonly known as parameter file) called init.ora file. From this parameter file the instance will know about the size of SGA, PGA, database buffer size and other configurable parameters. The instance will also start the Oracle background process such as (PMON, SMON, LGWR etc). This stage also opens the alert log and the trace files.

 

Stage 2: MOUNT

The next stage after NOMOUNT is called MOUNT. You can manually start an Oracle database in MOUNT stage using the command

SQL>startup mount

Or when database is already in nomount stage then you can change the stage by running the command

SQL>alter database mount;

 

When database goes into mount stage, it will read the control files specified in the parameter file. Remember the parameter file was read in the first stage (nomount). The control files contain the information about the physical structure of the database. So the control file will have the names and locations of all the datafiles and online redo log files. At this stage these datafiles and log files are not opened.

 

Some database administration operations can only be performed when the Oracle database is MOUNT stage. For example Oracle full database recovery can be done only when the database is in mount stage. If you want to rename a datafile you may need to take the database to mount stage unless the tablespace of the datafile is already offline.

 

Stage 3: OPEN

The final stage in the Oracle startup process. When the database is open then only normal database operations can takes place. Which means users and applications can login and start reading/writing data.

Running the command below will start the Oracle database and put into OPEN stage.  

SQL>startup

And if the database is already in MOUNT stage then you can open the database using the command

SQL> alter database open;

 

When database is open it will open the datafiles and redo log files. If any of these files are missing or corrupted then Oracle will not open successfully and will return error.

Filed under: Oracle Concept by admin
No Comments »

Error:ORA-30926: unable to get a stable set of rows in the source tables

Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

Action:

On many websites you will see the action you need to take is
“Remove any non-deterministic where clauses and reissue the dml.”

This is not that clear – at least for me.

So here is an explanation with example:

The error occurs when the target table (where you want to merge) has a primary key/unique key
constraints and the source table (from where data to be merged into target) or source query
contains more than 1 record for the unique key.

In other words if you have multiple records in the source table that matches the unique key value
of one single record in the target table you will get this error.

See this example below:

create table t1(a number,  b number, constraint pk_t1 primary key(a));

create table t2(a number,  b number);

insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);
insert into t1 values (4,1); (more…)

Filed under: Oracle Concept by admin
1 Comment »

People describe database cloning in different ways. Basically Oracle database cloning simply means copying an Oracle database from another database. For example you want to refresh your test oracle database using production data. In that case you can do database cloning by simply getting rid of your test database

and recreating it with production database including data.

You may need/want to clone a database for any number of reasons such as:

• 1. Your database is somehow corrupted
• 2. Your database rollback segment or other tablespaces are corrupt and you can not recover them
• 3. You simply want to refresh your database from another database

Whatever the reasons are, the cloning steps remain mainly same. Here are the steps that you need to execute to clone Oracle database.

Assumptions:

1. You can not OPEN your corrupt database but you can login as sysdba.
2. You have access to a functioning database which you want to copy
3. You have access DBA credentials

Step1: Make a list of your logfiles, datafiles and controlfiles of your corrupt database. You will need this list to delete them physically.

Logon to the corrupt database as:

sqlplus / as sysdba

or

svrmgrl

connect / as sysdba

Run following commands to get the list of your logfiles, datafiles etc:

select name from v$controlfile;

select * from v$logfile;

select name from v$datafile;

Step2: Shutdown the corrput database using

shutdown abort;

Step3. Physically delete all logfiles, datafiles and controlfiles from the machine.

Step4: Logon to functioning database machine.

Now logon to the database as

sqlplus / as sysdba

or

svrmgrl

connect / as sysdba

whatever is applicable to you according to yuor Oracle database version.

3. Run

alter database backup controlfile to trace;

This command will generate a text formatted trace file that will have the physical structure of the functioning oracle database. You are going to need this file later.

4. Run

show parameter user_dump_dest;

This command will give you the location of your Oracle trace file generated on

Step4.3

5. From another session to your functioning database machine go to the location of your user_dump_dest and make sure there is a trace file and it looks something like this:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “testdb” NORESETLOGS ARCHIVELOG

MAXLOGFILES 64

MAXLOGMEMBERS 5

MAXDATAFILES 64

MAXINSTANCES 32

MAXLOGHISTORY 17871

LOGFILE

GROUP 1 (’/u01/oradata/testdb/oralog1a.rdo’,

‘/u02/oradata/testdb/oralog1b.rdo’) SIZE 25M,

GROUP 2 (’/u01/oradata/testdb/oralog2a.rdo’,

‘/u02/oradata/testdb/oralog2a.rdo’) SIZE 25M

DATAFILE

‘/u01/oradata/testdb/system01.dbf’,

‘/u01/oradata/testdb/mydatabase.dbf’,

‘/u02/oradata/testdb/app_data_01.dbf’,

‘/u02/oradata/testdb/app_data_02.dbf’,

‘/u02/oradata/testdb/app_index_01.dbf’

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

ALTER DATABASE OPEN;

5. Now copy the content of this file in your desktop in a test file called

“create_controlfile.sql”.

Remove the lines “RECOVER DATABASE” and “ALTER SYSTEM ARCHIVE LOG ALL”

and “ALTER DATABASE OPEN;” from create_controlfile.sql;

6. Now shutdown your functioning database and exit, run

shutdown immediate;

exit;

Step5. Once database is shutdown the next step is to copy all files from

your functioning database to the new location (machine of your corrupt database).

If you need to ftp the files then always use “bin”.

Copy all these files (logfiles, datafiles and controlfiles and init.ora) in exactly same locations as they are in the functionning database machine (check create_controlfile.sql for reference). If you need to create directories

then create them. Physical location of each of these file should be exactly same.

Step6. When all files are copied to their correct locations, go to Machine where your database is corrupted.

Then login to oracle as

sqlplus / as sysdba

or

svrmgrl

connect / as sysdba

Step7: Now run the controlfile script:

@create_controlfile.sql;

Your cloned Oracle database should now come up.

Login to your database to see if it is functioning correctly.

But it is very easy and straight forward process. If you have question let me know.

Filed under: Oracle Concept by admin
No Comments »

Quite a few people asked me about why people create blogs and how to create a blog. So I decided to write down a very brief note about it here.

Well, people create blogs for various reasons. One of the most popular reason is that people wants to express themselves and internet is the place where they can write anything they want. Also there are people who like to share their experiences about a particular subject with other people of similar interests. There are business blogs where a business wants to keep its customers informed with news about latest products and services and blogging is a very good way to do that.

In simplest form a blog contains the following:

1. Domain name
2. Hosting space
3. Content management software

Here is the brief description of these main components:

Free or Paid:

However there are many sites where you can create a blog for free at no cost at all. One example is wordpress.com. They will let you create a blog free on their site and they will host your contents too. But there is a catch! You can not have your own domain name. Well, you can give a name to your blog, but that name will be only a part of the real names your blog. For example if you name your blog “myblog” then your real domain name will be myblog.wordpress.com. So my advice is – if you are looking to create a blog of your own for long term then it is better to buy a domain name for yourself. After all it is not that costly. A .com domain name will cost you just around $11 per year.

Get a Domain name:

A domain name is a name or address of  a blog or website. People on internet will visit your blog by typing this domain name on their web browsers. There are thousands of companies from where you can buy a domain name. I bought my blog’s domain name from Godaddy – they offer very affordable prices and their service is very good. Have a look at Domain Sale! $7.49*.com at GoDaddy.com

Content management software:

You don’t need to know html, javascript, xml or anything too technical to start blogging. There are great content management tools available FREE. Yes they are free. Wordpress is such a tool which is very easy to use. Really no technical skill is needed to host a blog using Wordpress.

Hosting:

Hosting refers to the space where you can put your website contents. Remember a domain name is just a name to identify your blog. You still need some space where you want to put your website contents and other softwares/scripts needed for your blog. Hostgator is such a very well established and respected hosting company where you get everything related to hosting at great prices.

When you are buying hosting space it is important to know what applications you can run/install on that space. For example some cheap hosting providers will not support MySQL database. Some might not support the content management software such as Wordpress, Joomla and others. Read all the functionalities available very carefully, if you have questions better ask them before buying.

One great thing about Hostgator is that they have and support almost all major content management software, database and applications and they are free to use and ready to install, just a few clicks needed to install.

So once you have your domain, hosting and content management software the next step is just installing/setting up these components and then you can start blogging. There are lots of good help pages in Hostgator and Godaddy which should guide you in setting up your blog quite easily.

Once you have setup your blog and started blogging, you can start interacting with visitors to your blog. One way to do this is by allowing your users to comment on your blog. If you are using Wordpress and a standard Wordpress theme then your blog post should have comments forms. However please remember to use some kind of spam protection plugins ( I use math-comment-spam-protection) otherwise you will regularly gets a large number of spam comments.

I hope this brief article has been helpful to you.

Filed under: Others by admin
No Comments »

Before commiting data into database Oracle changes and stores the changed data in redo log files. Every oracle database needs redo log files.  When for some reason some datafile becomes unavailable then these redo log files can be used to rollback changes to the data.

The following command can be used to add a new redo log group to an existing database:

ALTER DATABASE testdb

ADD LOGFILE

GROUP 1 (’/u01/oradata/testdb/redo1a.log’,'/u02/oradata/testdb/redo1b.log’) SIZE 100M;

This command will add a redo log group (Group 1) to the database testdb. The log file group will have two members which are located on two different disks uo1 and uo2.

In Oracle the Group number must be unique. So before creating the group it is vital that you check the existing group number and then increment that number by 1 to get your new group number.

It is possible to add further log members to redo groups.

The command below will add a member to GROUP 1.

ALTER DATABASE testdb

ADD LOGFILE MEMBER

‘/u03/oradata/testdb/redo1c.log’

TO GROUP 1;

Filed under: Oracle DBA Scripts by admin
No Comments »

ORA-00942: Table or View does not exist
Cause of error: Oracle table or view does not exist in the database.
Oracle database privileges are missing or granted incorrectly.

Actions to take: Make sure that the table or view or synonym that you are referencing exists in the database and ensure they have necessary privileges.

For example if you try to access a table or view from another schema and you do not have select privilege to that table or view then you will get this error message. Even if you specify the table or view name using the schema name- you will still get this error.

Lets’ say there is a table called table1 in a schema called schema1. There is another schema called schema2. At the moment you are in Oracle schema2 and want to execute an Oracle select statement on table1 such as

select * from schema1.table;

But if you do not have Oracle select privilege granted to you then you will get ORA-00942.

To fix that login to Oracle in your schema1 schema and then run the following statement to grant select privilege to schema2.

SQL> grant select on schema1.table1 to schema2;

Now you should be able to access table1 data from schema2 as long as you qualify the Oracle table name with the schema name i.e. schema1.table1. This query should return you data now.

select * from schema1.table; (more…)

Filed under: Oracle Errors by admin
1 Comment »

by: Paul Fleming

As an Oracle DBA, you probably have heard a lot about the new security features in Oracle 11G such as database vault, audit vault, strong verifier, enhanced ASO, etc. But with a busy schedule, how do you find the time to study all the topics in depth? Are all of them going to affect your daily DBA operations? What should you know first and foremost? Of course, depending on the particular database you are administering, you will have your unique need. But as a rule of thumb, what tends to affect you the most is almost never those cool new features. The reason is simple: if you don’t use them, they won’t affect you. What tends to affect you is the change in the existing features that might lead to compatibility or interoperability issues. When that script you have used for years suddenly stops working, you will be disappointed. When your boss or above calls to ask what happened, you could feel terrible.

In this article, we discuss the new password case sensitivity feature in 11G. Every Oracle session starts with authentication. If the authentication fails at the beginning of your script, everything that follows will almost certainly be broken. So what was changed, why, and what should you know? (more…)

Filed under: Oracle Concept by admin
No Comments »