Error:ORA-30926: unable to get a stable set of rows in the source tables
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); Continue reading
Clone Oracle database – cloning Tutorial
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:
- You can not OPEN your corrupt database but you can login as sysdba.
- You have access to a functioning database which you want to copy
- 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.
How to create Oracle AWR report for a single instance Oracle database
To create the AWR report for a single instance Oracle database (use awrrpt.sql) as:
1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:
a. format of the report (html/text)
b. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter
c. enter starting snapshot id from the displayed list
d. enter ending snapshot id from the displayed list
e. give a name for the report including file extension (txt/html/htm)
4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.
Here are the steps:
[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18 2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24 2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:45:45 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 999 line 300
SQL>
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
———– ———— ——– ————
2330100236 DEVDB 1 DEVDB
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 2330100236 1 DEVDB DEVDB DEVSERV
Using 2330100236 for database Id
Using 1 for instance number
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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB DEVDB 24129 07 Apr 2011 01:00 1
24130 07 Apr 2011 02:00 1
24131 07 Apr 2011 03:00 1
24132 07 Apr 2011 04:00 1
24133 07 Apr 2011 05:00 1
24134 07 Apr 2011 06:00 1
24135 07 Apr 2011 07:00 1
24136 07 Apr 2011 08:00 1
24137 07 Apr 2011 09:00 1
24138 07 Apr 2011 10:00 1
24139 07 Apr 2011 11:00 1
24140 07 Apr 2011 12:00 1
24141 07 Apr 2011 13:00 1
24142 07 Apr 2011 14:00 1
24143 07 Apr 2011 15:00 1
24144 07 Apr 2011 16:00 1
24145 08 Apr 2011 18:00 1
24146 08 Nov 2011 18:00 1
24147 08 Feb 2012 13:56 1
24148 12 Oct 2013 18:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24146
Begin Snapshot Id specified: 24146
Enter value for end_snap: 24148
End Snapshot Id specified: 24148
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24146_24148.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: new_awr_report_24146_24148.txt
Using the report name new_awr_report_24146_24148.txt
Reprot will be generated now and scroll through the screen. Wait until iut complete.
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
———— ———– ———— ——– ————— ———– —
DEVDB 2330100236 DEVDB 1 11-Mar-11 12:42 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV Linux x86 64-bit 8 8 4 31.29
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 24146 08-Nov-11 18:00:59 125 1.0
End Snap: 24148 12-Oct-13 18:00:50 123 .9
Elapsed: 1,013,759.86 (mins)
DB Time: 7,337,318.40 (mins)
——————–
——————–
——————–
——————–
——————–
End of Report
Report written to new_awr_report_24146_24148.txt
SQL>
How to create Oracle AWR report for a multiple instance (RAC) Oracle database
To create the AWR report for a multiple instance Oracle RAC database (use awrrpti.sql) as:
1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:
a. format of the report (html/text)
b. enter db id
c. enter instance id
d. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter
e. enter starting snapshot id from the displayed list
f. enter ending snapshot id from the displayed list
g. give a name for the report including file extension (txt/html/htm)
4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.
Here are the steps:
[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18 2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24 2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$
[oracle@DEVSERV ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:39:08 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 999 line 300
SQL>
SQL>
SQL>
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 2330100236 1 DEVDB DEVDB DEVSERV
Enter value for dbid: 2330100236
Using 2330100236 for database Id
Enter value for inst_num: 1
Using 1 for instance number
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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
DEVDB DEVDB 24128 07 Apr 2011 00:00 1
24129 07 Apr 2011 01:00 1
24130 07 Apr 2011 02:00 1
24131 07 Apr 2011 03:00 1
24132 07 Apr 2011 04:00 1
24133 07 Apr 2011 05:00 1
24134 07 Apr 2011 06:00 1
24135 07 Apr 2011 07:00 1
24136 07 Apr 2011 08:00 1
24137 07 Apr 2011 09:00 1
24138 07 Apr 2011 10:00 1
24139 07 Apr 2011 11:00 1
24140 07 Apr 2011 12:00 1
24141 07 Apr 2011 13:00 1
24142 07 Apr 2011 14:00 1
24143 07 Apr 2011 15:00 1
24144 07 Apr 2011 16:00 1
24145 08 Apr 2011 18:00 1
24146 08 Nov 2011 18:00 1
24147 08 Feb 2012 13:56 1
24148 12 Oct 2013 18:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24147
Begin Snapshot Id specified: 24147
Enter value for end_snap: 24148
End Snapshot Id specified: 24148
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24147_24148.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_report_24147_24148.txt
Using the report name awr_report_24147_24148.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
———— ———– ———— ——– ————— ———– —
DEVDB 2330100236 DEVDB 1 11-Mar-11 12:42 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV Linux x86 64-bit 8 8 4 31.29
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 24147 08-Feb-12 13:56:12 287 3.1
End Snap: 24148 12-Oct-13 18:00:50 123 .9
Elapsed: 881,524.64 (mins)
DB Time: 5,752,234.99 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ———- ———-
Buffer Cache: 1,536M 1,536M Std Block Size: 8K
Shared Pool Size: 2,560M 2,560M Log Buffer: 157,036K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ————— ————— ———- ———-
DB Time(s): 6.5 2,543.7 123.07 345.85
DB CPU(s): 0.0 0.1 0.00 0.01
Redo size: 34.4 13,407.1
Logical reads: 4.8 1,877.2
Block changes: 0.2 63.0
Physical reads: 0.0 0.2
Physical writes: 0.0 1.9
User calls: 0.0 7.4
Parses: 0.0 15.3
Hard parses: 0.0 0.1
W/A MB processed: 15,009.8 5,851,091.6
Logons: 0.0 0.4
Executes: 0.1 20.7
Rollbacks: 0.0 0.0
Transactions: 0.0
————————————————————-
————————————————————-
————————————————————-
————————————————————-
End of Report
Report written to awr_report_24147_24148.txt
SQL>
How to create a blog – one page info
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:
- Domain name
- Hosting space
- 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.
Recent Comments