Ask DBMS Direct

How to Restore Dell Mini Netbook to Factory Settings

2010-06-09T17:45:00.002+01:00

I have done this successfully and want to share how to Restore Dell Mini Netbook to Factory Settings.

Remember that this process will reformat the hard drive of your Laptop and restore system software to the factory condition. I.e the image of the Operating system and any other factory supplied software as it was before it left the factory and before you completed the setup. Therefore you will loose all data and all software you have already installed. Try this will care.

This will only succeed if the image partition still exists. This will not work if that has already been destroyed it a previous format or corrupted.

The following steps have been tried successfully on Dell Inspiron Mini 10 with Windows 7. It may also work for Windows Wista.

1. Start or Restart your computer if it is already ON.
2. Press the F8 key when you see the Dell logo to display the Advanced Boot Options menu.
3. Select Repair Your Computer option and press Enter
4. Specify the Language Settings that you want on the next screen, and click Next.
5. Enter the Login username and password (if any) on the next screen.
This user must have administrative privileges. Click OK to continue
6. On the next screen, select Dell Factory Image Restore.
7. Click Next on the next screen
8. Select Yes on the next screen
Remember that this process reformat your hard drive and restore system software to factory condition.
9. Click Next to continue the factory restore.
10. The restore process might tak several minutes. After it is completed, click Finish to restart your Laptop.

The next startup will start the Setup process again as it did when it came from Dell.

Resolving FRM-92150 Web client version too new

2009-09-16T17:21:00.003+01:00

I have come across this error many times especially after cloning Oracle Applications from one environment to the other. As usual, I thought I should share the solution we have always used.

You are running Oracle Applications when you receive the error FRM-92150 Web client version too new. One simple solution that works is to clear the Jinitiator Cache:

1. Enable the Java console screen (Use Show Java Console) to note down the Jinitiator version running at the time you get the error.

This is important if you are running multi-client environments that has downloaded different versions of Jinitiator on your PC.

2. Go to Control Panel

3. Select and Open the Jinitiator that matches the version you noted above. (e.g Jinitiator 1.3.1.25)

4. Click the Cache Tab

5. Click the Clear Jar Cache Button.

6. Open a new explorer screen

7. Connect to e-Business suite and open your form screen successfully

This solution will work in other scenarios, however this has always worked using Internet Explorer on Windows XP to access Oracle Application 11.5.10 hosted on Red Hat Linux.

Oracle 11g Technical Briefing

2009-07-13T12:33:00.007+01:00

I know some of you who are currently (or in the past) working on Oracle related projects will be interested in this feedback especially access to the session slides. I am posting this hoping that it will be useful to some of you. You can also respond with your own experience of one of the Partner, Customer or User briefing and training sessions.

Background

I attened one of the Oracle 11g Technical Briefing event recently. If like me, you yawn through some of the Oracle Partner or Sales briefing event, I think I can say that this was more technical and interactively packed with some examples and Q&A triggered by an inquisitive Technical audience.

One of the main reasons I attended was to interact with current Oracle 11g users and technical team to understand their current experience and see how an upgrade to 11g will add value to some of our customers currently running on 10g Release 2.

The event

It started at 9:30 with registrations and completed at 16:30 with Questions, Answers and the usual completion of feedback forms. You may be able to guess what my feedback about the event would be after reading this piece. The sessions were lead by Robin Murgatroyd - Technology Partner Enablement Manager, Oracle Core Technologies and Richard Jacobs - Senior Principal Technologist – Oracle Platform Technology Solutions.

Topics Covered

It covered as much as possible on Oracle 11g Features, future developments and releases. These included:

High Availability - High(er) availability
Maximum Availability and new Flashback Features
Flashback Transaction
Flashback Data Archive
Data Guard
Streams
Online Maintenance

Grid and OLTP
Real Application Clusters
Automatic Storage Management
Improve Performance with Caching
Result Caches
Query Caching
Function Caching
SQL Plan Management and Optimizer Enhancements
New Optimizer Features
Statistics enhancements
Oracle TimesTen

Information Management
Partitioning
Compression
SecureFiles
Miscellaneous
New Development features
Unstructured data

Application Development
PL/SQL Enhancements
SQL Developer Enhancements

Database Security
User Management
Access Control
Data Protection
Monitoring
Vault
Audit Security

Database Manageability and Real Application Testing
Memory Management
AWR Baselines
Automatic SQL Tuning
Advisors
Upgrade Considerations
Enterprise Manager Management Packs Enhancements
VLDB Enhancements in Oracle Database 11g
Real Application Testing

Improvements in Maximize Storage
Oracle Exadata
Advanced Compression

Business Intelligence

Upgrading to Oracle Database 11g
New 11g Background Processes,
Upgrade Considerations,
Other Features Enhancements

Expectations

As you would expect from the above long list of topics, some of the sessions were followed by Hands-on examples while others were the usual theoretical whizzing-through of the slides. A few of the later sections were rushed through while some the earlier ones were very much delayed due to questions and answers from interested parties in the audience. Although I already had some knowledge of the Oracle 11g Features and have tried my hands on some of them while doing personal R&D and installation testing, the Technical Briefing session was a more useful session that gives you the opportunity to interact one-to-one with the Oracle technical personnel. As opposed to normal Training sessions where most of the Audience are there to learn, most people had long list of questions to ask the facilitators.

Attendance

There were over 40 attendees of whom most are DBAs and Technical team from other Oracle partners in the UK. The attendees included teams from the top Oracle Partners, IT, Management Consulting and Outsourcing firms in the World.

Product Releases

For those interested in Data Modelling, Oracle SQL Developer Data Modeler (2.0.0.57.0) was released on July 1, 2009. This is now available for Download from OTN.

There was a hint that Oracle 11g Release 2 is expected to be available sometime from September. This could mean that Oracle 11g Release 2 will be one of the major highlights at Oracle OpenWorld 2009 in October. If you may remember, all predictions for the unveiling of Oracle 11g Release 2 at Oracle OpenWorld 2008 proved to be false alarms and it has taking another one year and we are still talking of “hints”.

Interests

One of the areas I was interested in was Database Manageability and Real Application Testing. Real Application testing helps you fully assess the effect of system changes on real-world applications in the test environments before deploying the change in production.

I would encourage everyone to make out time to attend one of the Oracle Partner Events (especially the free ones) when it is announced notwithstanding your level of knowledge in the subject area. It will give you access to a platform to ask pressing questions that you would normally not get quick answers from Oracle Forums or Oracle Metalink especially in relation to product stability, experience from those already upgraded from the older release (Oracle 10g in our customer's case) and lessons learned from those who have been using it since Release 1 started shipping.

Session Notes

As you would guess, I did take a few notes during the sessions. The important question is how can I share it with you? Well, the bad news is that some of the texts I wrote on a notepad are unreadable because I was rushing them at some point. I normally would not have written a line, but I was curious to use some as reminder of questions I asked at the end. The good news is that you can find the slides for the entire presentation more useful in the usual place online at Oracle Partner Events and Training.

The slides for the event will normally be available within a week of the event. You will also find slides of similar 11g briefing held in April and other subjects that may interest you at the same place. Also useful will be the January 2009 Oracle Database 11g Product Family White Paper.

Feel free to check and read through whenever you can

Changing Segment Space Management from MANUAL to AUTO Using Enterprise Manager

2009-07-10T12:45:00.004+01:00

You can reorganise the Tablespace online or offline to change Segment Space Management from MANUAL to AUTO

Assuming you are using Oracle 10g, the best way to do this is to Use Enterprise Manager.

1. From Enterprise Manager, select Tablespaces.
2. Select the Tablespace you want to change for Manual to Auto.
3. From the Actions drop down, select Reorganize and click GO.
4. From the next screen, click on Set Attributes.
5. Under Segment Space Management change the Manual to Auto and Click OK
6. Click Next and change the required settings for Method (Online or Offline rebuild)
7. Click Next to generate the reorganize script and sh0w recommendation
8. Click Next Schedule. To Review the script
9 Click Submit job to reorganize

One of the important things to note is that if you want to use Online reorganise, tables in the Tablespace must meet certain requirements like having integrity constraints defined on them (Primary Keys etc). If they do not meet this, it will warn you than it cannot be reorganised online. Therefore the best in this case is to use Offline reorganize.

Remember to read more on this and only do it if you know what you are doing.

Running Oracle 9i EM Console from a Web Browser

2009-07-01T13:10:00.008+01:00

Oracle 9i Enterprise Manager Web Site

As usual, I want to share this information that a lot of DBAs and Users were not aware of. Before Oracle 10g Database Control and Grid Control, Oracle already introduced EM Management via HTTP using the Oracle 9i Enterprise Manager Website.

This instruction references the document Running the Oracle Enterprise Manager Console from a Web Browser

Apart from the Use of the Management Server via the Java Console, with Oracle 9i Enterprise Manager, an administrator is not limited to managing targets from a particular machine where the product has been installed. Instead, administrators can deploy the Enterprise Manager Web Site in order to run the Enterprise Manager Console from any supported web browser.

In order to run Oracle Enterprise Manager from a web browser, you must perform the following installation and configuration steps.

Client Install

Ensure that a supported web browser is installed on the client machines that will run web-enabled Enterprise Manager.
Supported web browsers can be equal to or above the list below:
Netscape Navigator Release 4.7 for Windows NT, Windows 2000, Windows XP, and Windows 98
Microsoft Internet Explorer Release 5.0 for Windows NT, Windows 2000, and Windows 98
Microsoft Internet Explorer Release 5.5 for Windows NT, Windows 2000 and Windows 98
Microsoft Internet Explorer Release 6.0 for Windows XP

Server-Side Install
Ensure that the Enterprise Manager web site component has been installed.
By default, it is installed with the Management Server under the Oracle_Home/oem_webstage/ directory. In addition, the Enterprise Manager web site automatically installs a preconfigured Oracle HTTP server to act as its web server. The Oracle HTTP server is automatically started. This is the same HTTP server that is used by the Enterprise Manager Reporting web site.
Start the Oracle HTTP Server by performing the following steps:

On Windows:
To start the Oracle HTTP Server:
From Control Panel > Administrative Tools.
Double-click Services.
Select the OracleHTTPServer_ service.
Click Start to start the Oracle HTTP Server.

On UNIX:
You can start the Oracle HTTP Server from the command line using the command:
$Oracle_Home/Apache/Apache/bin/apachectl start

To stop the Oracle HTTP Server, perform the following steps:

On Windows:
To stop the Oracle HTTP Server:
From Control Panel > Administrative Tools.
Double-click Services.
Select the OracleHTTPServer_ service.
Click Stop to stop the Oracle HTTP Server.

On UNIX:
You can stop the Oracle HTTP Server from the command line using the command:
$Oracle_Home/Apache/Apache/bin/apachectl stop

If you want to use a web server other than the default configured Oracle HTTP Server Release 1.3.22.0.0a for the browser-based Enterprise Manager, you must install and manually configure another supported web server. Additional supported web servers include:
Oracle Internet Application Server Release 1.0.2.2.2 for Sun SPARC Solaris, Windows NT, and Windows 2000
Apache Release 1.3.22 for Sun SPARC Solaris, Windows NT, and Windows 2000
Microsoft Internet Information Server Release 4.0 for Windows NT
Microsoft Internet Information Server Release 5.0 for Windows 2000

Starting OEM with Internet Explorer

It is possible to run Web Version of Enterprise Manager Console on the current Management Server.

You can do that by using http://servername:port.

The default port is 3339.
This will present you with the Enterprise Manager home Page with "Launch the Oracle Enterprise Manager Console" and "Access Oracle Enterprise Manager Reports”

If the above Page does not come up, check to see if the OracleTTPServer service is started. Ensure that this is started.

Click on the Launch Console Button for the Oracle Management Server entry.
You will see the following pop-up screen

The above will be followed by the Logon prompt below. If the Logon prompt does not come up, then check that you have Oracle Jinitiator installed.

If it is not installed, download the Jinitiator using the Download Plug-in link under Useful links on the OEM home page above. When prompted, Click open to install directly or save to install later.

After Installing Jinitiator, Click on the Launch Console Button for the Oracle Management Server entry again. This should pop-up the Logon Screen.

Logon as you would normally do with the Desktop version. This will present you with the normal Enterprise Manager Console. Note that it will look like the Standard Desktop version.

Hint. This must use Oracle Jinitator to function properly. For more information, read the Enterprise Manager Documentation on how to configure and use within Firewall. If you want to use a web server other than the default preconfigured Oracle HTTP server for browser-based Enterprise Manager, you must install and manually configure another supported web server. See OEM documentation for details.

Troubleshooting the Web Browser

This section contains information about troubleshooting the web browser.

Console Hangs

If you start the web browser, log in to the Console, but the Console hangs, and the following is displayed in JInitiator Console:@org.omg.CORBA.INITIALIZE[completed=MAYBE, reason=java.net.BindException: Cannot assign requested address]
You must perform the following steps:
Check that the JInitiator uses browser settings for the proxy.
From the Windows Start menu, click Programs-> JInitiator Control Panel. A window appears.
Choose the Proxies tab.
View the contents.
Append the domain name to the web server address you type in the browser, for example, @.us.oracle.com
Edit the browser settings and add that domain to "no proxy settings for" or edit the browser settings and choose Direct Connection to Internet.

Console Does Not Open Web Browser

If user.browser is not defined properly in the clientconfig.properties file, the Netscape Navigator will not open from within the Enterprise Manager Console. The default Enterprise Manager browser will be opened instead.

If viewing reports from the Console does not work, it may be because the Netscape Navigator is using a script and not the actual program.
You must perform the following task in order for Unix to know how to open the Netscape Navigator.

Set the following property in ClientConfig.properties:user.browser=/usr/local/packages/netscape/netscape
The user.browser should be set to the actual name of the browser, not a script.
/usr/local/bin/netscape is a script and not an executable.
user.browser should be pointed to an executable and not to a shell script.

Oracle Script Runner

2009-03-16T22:47:00.014Z

I have been very busy with varous projects hence this is my first posting in 2009.

As usual, I thought of having a UNIX script I can use to call any Oracle script and generate either text or html output. I came up with this draft and have been using it. Please use it with caution because it is under continous development. It needs a lot of plushing as you can see. It is not the best, but very useful.

#!/bin/ksh
##############################################################
#
# This is a Generic Script that will call any SQL script and run it on the specified
# SID or ALL Databases on the Server
#
# It will require passing the name of the
# SCRIPT,USERID,OUTPUTTYPE,SENDEMAIL,DELOLDFILE, ORACLESID
# Where SCRIPT is the name of the script to run.
# USERID is oracle username/password
# OUTPUTTYPE is either to send output file as html or text
# SENDEMAIL (Y or N) is whether to email result to specified DBAs
# Requires that mail system is working on this client
# DELOLDFILE (Y or N) is whether to Delete old versions of the same report
# ORACLESID is either the database SID to run the script against
# or ALL (run against all SID marked Y in oratab and started on the server)
#
# Example script_runner.sh test.sql user1/pass1 html Y Y testdb
# This produces a compressed html report for only testdb
# test.sql.testdb.200205061200.html.gz
# If testdb is replace with ALL, it produces the report for each SID
# in oratab marked Y and running on the server
#
# Report file is in the format script_name.sid.yyyymmddhhmi.outputtype.gz
#
# To run this script successfully, the following must be changed as requried
# ORACLEDB_HOME, ORATAB, SCRIPTPATH, and DBAS
#
# This was developed for my personal use and has only been Tested on Solaris.
# Please use it with care.
# You can modify it for your own use, but you must leave this header information
# in it as a permission from DBMS Direct to reuse it
# Send us you comments about the script.
# Thanks http://www.dbmsdirect.com/
# http://www.unictechnologies.com/
# http://dbmsdirect.blogspot.com/
#
# Script Name - script_runner.sh
#
# Update History
#
# Date Author Company Version
# 21/06/2002 DBMS Direct 1.0
# 06/05/2008 DBMS Direct 2.0
#
##############################################################

###################################
# Customisable Initialisation
#
export SCRIPTFILE=$1
export USERID=$2
export OUTPUTTYPE=$3
export SENDEMAIL=$4
export DELOLDFILE=$5
export ORACLESID=$6
export ORACLEDB_HOME=/u01/app/oracle/product/10.2.0.2
export ORATAB=/var/opt/oracle/oratab
export SCRIPTPATH=/export/home/oracle/dbmsdirect
export SCRIPT=$SCRIPTPATH/$SCRIPTFILE
export DBAS='dbms.direct@unictechnologies.com'
export TDAY=`date '+%Y%m%d%H%M'`
export MDAY=`date '+%d-%m-%Y %H-%M'`
#
# End of Customisable Initialisation
###################################

# Get all running instances

instances_running()
{
if [ "$ORACLESID" == ALL ];
then
for sid in `grep -v '^#' ${ORATAB} grep ':Y$' awk '{FS=":"} {print $1}'`
do
for i in `ps -efegrep 'ora_...._'${sid}grep -v grepawk '{print $NF}'`
do

echo $i
done sed 's/ora_...._//' sort -u
done
else
echo $ORACLESID
fi
}

# Run Daily Check on all Running Instances

run_script()
{
for i in `instances_running`
do
REPORTH=${SCRIPT}.$i.${TDAY}.${OUTPUTTYPE}
REPORTZ=${REPORTH}.gz

if [ "$DELOLDFILE" == Y ];
then
# Delete old versions of the same report
/usr/bin/rm ${SCRIPT}.$i.*.${OUTPUTTYPE}.gz
fi

echo $i . /usr/local/bin/oraenv >/dev/null
ORACLE_HOME=${ORACLEDB_HOME}
if [ "$OUTPUTTYPE" == text ];
then
${ORACLE_HOME}/bin/sqlplus -s ${USERID} << EOF
WHENEVER SQLERROR EXIT
SET MARKUP html OFF
SET TERM ON
SET HEAD ON
SET FEEDBACK OFF
SET PAGESIZE 9999
SET LINESIZE 200
SPOOL ${REPORTH}
@${SCRIPT} \
SPOOL OFF
EOF
elif [ "$OUTPUTTYPE" == html ];
then
${ORACLE_HOME}/bin/sqlplus -s ${USERID} << EOF
WHENEVER SQLERROR EXIT
SET MARKUP html ON
SET TERM ON
SET HEAD ON
SET FEEDBACK OFF
SET PAGESIZE 9999
SET LINESIZE 200
SPOOL ${REPORTH}
@${SCRIPT} \
SPOOL OFF
EOF
fi
# Create Zip file

/usr/bin/gzip -f ${REPORTH}

# Mail Zip file to DBA if requested

if [ "$SENDEMAIL" == Y ];
then
#/usr/bin/mailx -s "${$i} - ${SUBJECT}" ${DBAS} < $REPORTH
/usr/bin/uuencode ${REPORTZ} ${REPORTZ} /usr/bin/mailx -s "$i - Database Result - $MDAY" ${DBAS}
fi
done
}

# Begin Running Script
run_script

Using Logminer Example

2008-05-14T16:38:00.007+01:00

This is one in the list of my Example Series in this blog. Check this blog for more possible examples.

If your database is in Archivelog Mode and you have already enabled Supplemental Logging before the rows were create(This is done using ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;), You can use LogMiner to find the changes in the database for any user. My test example here finds changes made by TEST user from today. You can change the username to any user and add more columns from V$LOGMNR_CONTENTS view.
SQL> connect /as sysdba
Connected.
SQL>

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > TRUNC(SYSDATE);

NAME
---------------------------------------------------------------------------------------
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC

10 rows selected.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> set lines 200
SQL> set pages 9999
SQL> col USERNAME format a10
SQL> col XID format a10
SQL> col SQL_REDO format a40
SQL> col SQL_UNDO format a40
SQL>
SQL> SELECT username,session#,
to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp,
(XIDUSN '.' XIDSLT '.' XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS WHERE username = 'TEST'
2
SQL>

--- Your results Display here

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.
SQL>

Row-level Dependency Tracking ROWDEPENDENCIES Example

2008-05-02T12:27:00.007+01:00

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

Oracle 10g provided one of the best features I like using. This is the pseudo column ORA_ROWSCN. Using scn_to_timestamp with ORA_ROWSCN, you can find the timestamp your block or row was changed. But this does not come automatically without the usual changes and space (about 6 bytes per row).

By default, Oracle 10g tables are created with the clause NOROWDEPENDENCIES. This means that Block-level Dependency Tracking is used. You can create your table with ROWDEPENDENCIES thereby enabling Row-level Dependency Tracking.

SQL> CREATE TABLE TESTTAB
(COL1 NUMBER(5),
COL2 VARCHAR2(30),
COL3 DATE)
ROWDEPENDENCIES;
2 3 4 5
Table created.

SQL>

I will demonstrate with EMP table which has NOROWDEPENDENCIES

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM SMITH 7369 2000
02-MAY-08 11.49.00.000000000 AM ALLEN 7499 1600
02-MAY-08 11.49.00.000000000 AM WARD 7521 1250
02-MAY-08 11.49.00.000000000 AM JONES 7566 2975
02-MAY-08 11.49.00.000000000 AM MARTIN 7654 1250
02-MAY-08 11.49.00.000000000 AM BLAKE 7698 2850
02-MAY-08 11.49.00.000000000 AM CLARK 7782 2450
02-MAY-08 11.49.00.000000000 AM SCOTT 7788 3000
02-MAY-08 11.49.00.000000000 AM KING 7839 5000
02-MAY-08 11.49.00.000000000 AM TURNER 7844 1500
02-MAY-08 11.49.00.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM JAMES 7900 950
02-MAY-08 11.49.00.000000000 AM FORD 7902 3000
02-MAY-08 11.49.00.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update emp set sal=3000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was NO row level tracking

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM SMITH 7369 3000
02-MAY-08 11.54.10.000000000 AM ALLEN 7499 1600
02-MAY-08 11.54.10.000000000 AM WARD 7521 1250
02-MAY-08 11.54.10.000000000 AM JONES 7566 2975
02-MAY-08 11.54.10.000000000 AM MARTIN 7654 1250
02-MAY-08 11.54.10.000000000 AM BLAKE 7698 2850
02-MAY-08 11.54.10.000000000 AM CLARK 7782 2450
02-MAY-08 11.54.10.000000000 AM SCOTT 7788 3000
02-MAY-08 11.54.10.000000000 AM KING 7839 5000
02-MAY-08 11.54.10.000000000 AM TURNER 7844 1500
02-MAY-08 11.54.10.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM JAMES 7900 950
02-MAY-08 11.54.10.000000000 AM FORD 7902 3000
02-MAY-08 11.54.10.000000000 AM MILLER 7934 1300

14 rows selected.

I will now demonstrate with EMPDPD table which has ROWDEPENDENCIES

SQL> create table empdpd ROWDEPENDENCIES as select * from emp;

Table created.

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM SMITH 7369 3000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update empdpd set sal=4000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was Row level tracking

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.56.20.000000000 AM SMITH 7369 4000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL>select table_name,dependencies from user_tables;

TABLE_NAME DEPENDEN
------------------------------ --------
EMP DISABLED
DEPT DISABLED
BONUS DISABLED
SALGRADE DISABLED
DUMMY DISABLED
EMPDPD ENABLED
TESTTAB ENABLED
EXTERNAL_TEST1 DISABLED
TEST1 DISABLED
TEST2 DISABLED
EXTERNAL_TEST DISABLED

11 rows selected.

SQL>

Note: using ROWDEPENDENCIES will normally add about 6 bytes to each row. You need to read moer from Oracle Documentation for limitations/restrictions

Physical Standby Database Example

2008-04-25T14:10:00.004+01:00

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

This is an example Physical Standby Database Creation. This is mostly from the logs I created. You can use it as a guide or hint. It does not show all the step-by-step, but it should help. Because it is logs, some processes may be duplicated. I will try to clean it up as much as I can.

This is based on Oracle 10.2.0.2 on Windows XP on my test database. Both Primary and Standby is on the Same Machine. As you know, this is a test database and show not be configured to be on same database for Production. This does not show how the Primary Database was created.

To complete this, I followed Oracle® Data Guard Concepts and Administration

Here is the Primary Database Initialisation Paranmeter

testdb.__db_cache_size=37748736
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=79691776
testdb.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\testdb\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\testdb\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\testdb\control01.ctl','C:\oracle\oradata\testdb\control02.ctl','C:\oracle\oradata\testdb\control03.ctl'
*.core_dump_dest='C:\oracle\admin\testdb\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='standby','testdb'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='TESTDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fal_client='TESTDB'
*.fal_server='STANDBY'
*.job_queue_processes=4
*.log_archive_config='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\standby','C:\oracle\oradata\testdb'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='testdb'
*.sga_target=127772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\testdb\udump'

Create Standby Database Intialisation Parameter from Primary

SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;File created.

Here is the amended Standby Initialisation Paranmeter

standby.__db_cache_size=29360128
standby.__java_pool_size=8388608
standby.__large_pool_size=4194304
standby.__shared_pool_size=83886080
standby.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\standby\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\standby\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\standby\control01.ctl','C:\oracle\oradata\standby\control02.ctl','C:\oracle\oradata\standby\control03.ctl'
*.core_dump_dest='C:\oracle\admin\standby\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='testdb','standby'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='standby'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.fal_client='STANDBY'
*.fal_server='TESTDB'
*.job_queue_processes=4
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=testdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\testdb','C:\oracle\oradata\standby'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='standby'
*.sga_target=127772160
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\standby\udump'

Create Password File for Standby

C:\>orapwd file=C:\oracle\ora10g\database\PWDstandbydb.ora password=oracl3

Swtich Logfile

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Get Current Log Sequence

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
117

SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;

File created.

Connect to RMAN (I am not using Catalog)

C:\Documents\dbmsdirect\Testing\StandBy>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 11:04:10 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2403050596)

Startup MOUNT the Primary Database

RMAN> STARTUP MOUNT

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 130023424 bytes

Fixed Size 1288148 bytes
Variable Size 96471084 bytes
Database Buffers 29360128 bytes
Redo Buffers 2904064 bytes

Backup current controlfile for Standby

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Starting backup at 28-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=100 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCNNF_TAG20071128T110608_3NTLX352_.BKP tag=TAG20071128T110608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 28-NOV-07

Open Primary Database

RMAN> ALTER DATABASE OPEN;

database opened

Archive the current log so to ensure that backup is consistent and recoverable

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

Backup Database and all archivelogs

RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T110717_3NTLZ9RT_.BKP tag=TAG20071128T110717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 28-NOV-07

RMAN> backup database plus archivelog delete input;

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
input archive log thread=1 sequence=114 recid=87 stamp=639833067
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112428_3NTMZH70_.BKP tag=TAG20071128T112428 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC recid=72 stamp=637507805
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC recid=73 stamp=637508008
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC recid=74 stamp=637508021
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC recid=75 stamp=637509664
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC recid=76 stamp=637509671
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC recid=77 stamp=637509676
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_08\O1_MF_1_105_3M6DNLSO_.ARC recid=78 stamp=638121625
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_20\O1_MF_1_106_3N5PMM5P_.ARC recid=79 stamp=639147639
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_107_3NOCZDMX_.ARC recid=80 stamp=639661045
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_108_3NOD7V4F_.ARC recid=81 stamp=639661317
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_109_3NODT9KL_.ARC recid=82 stamp=639661904
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_110_3NOF23DK_.ARC recid=83 stamp=639662160
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_111_3NOMM5PR_.ARC recid=84 stamp=639668842
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_112_3NTLYVL7_.ARC recid=85 stamp=639832029
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_113_3NTLZ4L0_.ARC recid=86 stamp=639832036
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_114_3NTMZCGZ_.ARC recid=87 stamp=639833067
Finished backup at 28-NOV-07

Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\TESTDB\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\TESTDB\USERS01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\TESTDB\DISC_EUL1.ORA
input datafile fno=00005 name=C:\ORACLE\ORADATA\TESTDB\TBSALERT01.ORA
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCSNF_TAG20071128T112530_3NTN5C4T_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 28-NOV-07

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=115 recid=88 stamp=639833263
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTN5GRZ_.ARC recid=88 stamp=639833263
Finished backup at 28-NOV-07

RMAN> backup current controlfile for standby format='C:\Documents\dbmsdirect\Testing\StandBy\stby_cfile.%U';

Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-NOV-07

RMAN> backup archivelog all delete input;

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=116 recid=89 stamp=639833685
input archive log thread=1 sequence=117 recid=90 stamp=639833686
input archive log thread=1 sequence=118 recid=91 stamp=639833741
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTNLNWB_.ARC recid=89 stamp=639833685
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_117_3NTNLOXB_.ARC recid=90 stamp=639833686
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_118_3NTNNF9M_.ARC recid=91 stamp=639833741
Finished backup at 28-NOV-07

RMAN> exit

Connect to RMAN and Auxilliary

C:\Documents\dbmsdirect\Testing\StandBy>rman target / auxiliary sys/xxxx@standby

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 13:38:52 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2403050596)
connected to auxiliary database: TESTDB (not mounted)
RMAN>

Use Diplicate Database to Create the Standby from Primary

RMAN> run {
2> set until sequence = 117 thread = 1;
3> allocate auxiliary channel ch1 type disk;
4> duplicate target database for standby dorecover nofilenamecheck ;
5> }

executing command: SET until clause
using target database control file instead of recovery catalog

allocated channel: ch1
channel ch1: sid=101 devtype=DISK

Starting Duplicate Db at 28-NOV-07

contents of Memory Script:
{
set until scn 2216367;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 28-NOV-07

channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1
channel ch1: restored backup piece 1
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338
channel ch1: restore complete, elapsed time: 00:00:07
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL
Finished restore at 28-NOV-07

sql statement: alter database mount standby database

contents of Memory Script:
{
set until scn 2216367;
set newname for tempfile 1 to
"C:\ORACLE\ORADATA\STANDBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\ORADATA\STANDBY\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA";
set newname for datafile 6 to
"C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to C:\ORACLE\ORADATA\STANDBY\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-NOV-07

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
restoring datafile 00006 to C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530
channel ch1: restore complete, elapsed time: 00:02:25
Finished restore at 28-NOV-07

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
datafile 6 switched to datafile copy
input datafile copy recid=12 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA

contents of Memory Script:
{
set until scn 2216367;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-NOV-07

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=115
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743
channel ch1: restore complete, elapsed time: 00:00:04
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC thread=1 sequence=115
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC recid=1 stamp=639841316
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=116
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC thread=1 sequence=116
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC recid=2 stamp=639841322
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-NOV-07
Finished Duplicate Db at 28-NOV-07
released channel: ch1

RMAN>

To open a standby database for read-only access when it is currently shut down:

Start, mount, and open the database for read-only access using the following statement:
SQL> STARTUP;

To open a standby database for read-only access when it is currently performing Redo Apply:
Cancel Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
SQL> ALTER DATABASE OPEN;

To change the standby database from being open for read-only access to performing Redo Apply:

Terminate all active user sessions on the standby database.
Restart Redo Apply. To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Switchovers Involving a Physical Standby Database

Step 1 Verify it is possible to perform a switchover.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 2 Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Step 3 Shut down and restart the former primary instance.

Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 Verify the switchover status in the V$DATABASE view.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 5 Switch the target physical standby database role to the primary role.

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6 Finish the transition of the standby database to the primary role.

ALTER DATABASE OPEN;

Step 7 If necessary, restart log apply services on the standby databases.

Step 8 Begin sending redo data to the standby databases.

ALTER SYSTEM SWITCH LOGFILE;

Useful Queries

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

select * from V$ARCHIVE_GAP;

select max(sequence#) from v$archived_log where applied='YES';

Select process,status from v$managed_standby;

select * from v$dataguard_status;

select * from v$dataguard_stats;

RMAN Backup and Recovery Example

2008-04-04T13:05:00.006+01:00

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

I have been using this to backup and recover database. I thought as usual, I should share it.
This is a simple script and should be used with care. This assumes a full backup and recover. It does not take other scenarios into consideration.

RMAN Backup Script

I assume you are running Unix.

This script will help you. Save it as shell script (e.g rman_backup.sh) and make required changes.

# Change and to your own (e.g /u01/oracle/backup)

#!/usr/bin/sh

export ORACLE_SID=TEST
export ORACLE_HOME=$ORACLE_HOME

# Add date to be used in logfile
export TDAY=`date +%a`
export backup_dir = /u01/oracle/backup
export LOGFILE=$backup_dir/$SID_clone.log

echo "Backup Started at `date` \n" >$LOGFILE

$ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1

# Connect to the database. Change this to Sys logon if not using /

connect target /

# Allocate Disk channels. Allocate more if you have enough max process to use

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;

#backup the whole source database.
# Use tags for easy separation from other backups during restore
backup
tag whole_database_open
format '$backup_dir/df_%u'
database;

# switch out of the current logfile
sql 'alter system archive log current';

#backup the archived logs
backup
archivelog all
format '$backup_dir/al_%u';

# backup a copy of the controlfile that contains records for the backups just made
backup
current controlfile
tag = cf1
format '$backup_dir/cf_%u';

}
exit

echo "Backup Finished at `date` \n" >>$LOGFILE

RMAN Recovery Script

This one of the routines I use to clone my database from one Server to another. so it may help you, but you have to use it with caution .
hope you are a DBA and can make all the required changes to the scripts. Whereever you see $, it means run from OS. RMAN> means run from RMAN

--I assume the RMAN Backup has been restored to the DISK as well
--I assume your system is Unix
--I assume you will run the commands manually (e.g. copy and paste). If you can script them, thats ok.

Esnure you Rebuild all your configuration as before
Set all your ORACLE_HOME etc.
Prepare all your init ora file as before (restore a previous copy is possible)

Create all the starting mount point as it was for your datafiles.

Logon to RMAN
$ORACLE_HOME/bin/rman

Run the following command. This part can also be scripted if required.

RMAN> connect target /

Startup the Instance with nomount

RMAN> startup nomount;

# add the init parameter file to the above if not on default location

#If you have or know DBID, set the DBID

RMAN> set dbid

Identify and Restore the control file.

RMAN> RESTORE CONTROLFILE FROM 'mount_point//';

Create a password file

$create password file orapwd file=$ORACLE_HOME/dbs/orapw password=

Modify the script below to use to restore the database

export ORACLE_SID=
export ORACLE_HOME==$ORACLE_HOME

$ORACLE_HOME/bin/rman

connect target /

# Mount the database

alter database mount;

# Allocate Disk channels.

RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;

restore database;

# If your rman used a tag,add "from tag " after database above

}

# Check the LOGFILE for errors

# Recover the Database

RMAN> run {
SET UNTIL logseq = thread = 1;
RECOVER DATABASE;
}

# Add Temp files because your backup will not have them
# Example

ALTER TABLESPACE "TEMP"
ADD TEMPFILE '//<_tempfile_name>' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M;

# Run reset logs

RMAN> alter database open resetlogs;

Oracle® Advanced Replication Example

2007-11-27T13:42:00.001Z

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

I was asked questions relating Replication in 10g.

As usual, I thought I should answer the question by using examples. This is following steps using the examples in the following links. I have chosen to use 2 Master sites instead of the 3 in the document.

Create Replication Site
Create a Master Group
Monitoring a Replication Environment

I used 10g Release 10.1 document because it was the first one I picked, but you can use the 10g Release 10.2 document.

Note: I have not hidden my passwords because it is my stand-alone test on a Laptop. I do not recommend exposing your passwords on the system.

This test as mentioned in the documentation assumes that the HR example schema has been created on both databases using the same script/structure. You will find the scripts under $ORACLE_HOME/demo/schema.

This shows the results of the examples and should be read with the document in the above links.

-- Setup Master Site Database TESTDB – This Database is on a different ORACLE_HOME on same Test Laptop as EMREP1. Oracle 10g Realease 10.2.0.2

Step 1 Connect as SYSTEM at a master site TESTDB

SQL> connect system/oracl3
Connected.

SQL>

Step 2 Create the replication administrator at TESTDB

SQL> CREATE USER repadmin IDENTIFIED BY repadmin;

User created.

SQL>

Step 3 Grant privileges to the replication administrator at TESTDB

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT COMMENT ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;

Grant succeeded.

SQL>

Step 4 Register the propagator at TESTDB

SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 5 Register the receiver at TESTDB

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 6 Schedule purge at master site TESTDB

SQL> CONNECT repadmin/repadmin
Connected.
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 7 Create proxy master site users at TESTDB

SQL> connect system/oracl3
Connected.
SQL> CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

User created.

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'proxy_mviewadmin',
4 privilege_type => 'proxy_snapadmin',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

SQL>

-- Setup Master Site Database EMREP1 – This Database is on a different ORACLE_HOME on same Test Laptop as TESTDB. Oracle 10g Realease 10.1.0.4

Step 1 Connect as SYSTEM at a master site EMREP1

SQL> conn system/oracl3
Connected.

SQL>

Step 2 Create the replication administrator at EMREP1

SQL> create user REPADMIN identified by REPADMIN;

User created.

SQL>

Step 3 Grant privileges to the replication administrator at EMREP1

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> GRANT COMMENT ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO repadmin;

Grant succeeded.

SQL>
SQL> GRANT SELECT ANY DICTIONARY TO repadmin;

Grant succeeded.

SQL>

Step 4 Register the propagator at EMREP1

SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 5 Register the receiver at EMREP1

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 6 Schedule purge at master site EMREP1

SQL> CONNECT repadmin/repadmin
Connected.
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 7 Create proxy master site users at EMREP1

SQL> conn system/oracl3
Connected.
SQL> CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

User created.

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'proxy_mviewadmin',
4 privilege_type => 'proxy_snapadmin',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL>
SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

SQL>

-- Create Scheduled Links Between Master Sites at TESTDB

Step 1 Create database links between master sites

SQL> CONNECT SYSTEM/oracl3
Connected.
SQL> CREATE PUBLIC DATABASE LINK emrep1 USING 'emrep1';

Database link created.

SQL> CONNECT repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK emrep1 CONNECT TO repadmin IDENTIFIED BY repadmin;

Database link created.

SQL>

Step 2 Define a schedule for each database link to create scheduled links

SQL> CONNECT repadmin/repadmin
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'emrep1',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

-- Create Scheduled Links Between Master Sites at EMREP1

Step 1 Create database links between master sites

SQL> CONNECT SYSTEM/oracl3
Connected.
SQL> CREATE PUBLIC DATABASE LINK testdb USING 'testdb';

Database link created.

SQL>
SQL> CONNECT repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK testdb CONNECT TO repadmin IDENTIFIED BY repadmin;

Database link created.

SQL>

Step 2 Define a schedule for each database link to create scheduled links

SQL> CONNECT repadmin/repadmin
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'testdb',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

-- Create Master Group in TESTDB

Step 1 Create the schema at master sites at TESTDB

SQL> SET ECHO ON
SQL>
SQL> SPOOL create_mg.out
SQL>
SQL> CONNECT repadmin/repadmin
Connected.
SQL> PAUSE Press to continue when the schema exists at all master sites.
Press to continue when the schema exists at all master sites.
SQL>

Step 2 Create the master group at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPGROUP (
3 gname => 'hr_repg');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 3 Add objects to master group at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'countries',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> BEGIN
SQL> DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
SQL> gname => 'hr_repg',
SQL> type => 'TABLE',
SQL> oname => 'countries',
SQL> sname => 'hr',
SQL> use_existing_object => TRUE,
SQL> copy_rows => FALSE);
SQL> END;
SQL> /
SQL> */
SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'departments',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'employees',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'jobs',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'job_history',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'locations',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'regions',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'dept_location_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_department_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_job_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_manager_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_department_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_employee_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_job_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'loc_country_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

Step 4 Add additional master site for EMREP1 at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE (
3 gname => 'hr_repg',
4 master => 'emrep1',
5 use_existing_objects => TRUE,
6 copy_rows => FALSE,
7 propagation_mode => 'ASYNCHRONOUS');
8 END;
9 /

PL/SQL procedure successfully completed.

SQL>

Step 5 If conflicts are possible, then configure conflict resolution methods

SQL>

SQL> PAUSE Press to continue after configuring conflict resolution methods
Press to continue after configuring conflict resolution methods
or if no conflict resolution methods are required.

SQL>

Step 6 Generate replication support at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'countries',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'departments',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'employees',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'jobs',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'job_history',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'locations',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'regions',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

COUNT(*)
----------
0

SQL>

Step 7 Start replication at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'hr_repg');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

-- Monitor Replication Environment

SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';

DBLINK
--------------------------------------------------------------------------------
EMREP1
TESTDB

SQL>
SQL> COLUMN GLOBAL_NAME HEADING 'Database' FORMAT A25
SQL> COLUMN ADMIN_REQUESTS HEADING 'AdminReqests' FORMAT 9999
SQL> COLUMN STATUS HEADING 'AdminErrors' FORMAT 9999
SQL> COLUMN TRAN HEADING 'DefTransPairs' FORMAT 9999
SQL> COLUMN ERRORS HEADING 'DefTransErrors' FORMAT 9999
SQL> COLUMN COMPLETE HEADING 'PropagatedTrans' FORMAT 9999
SQL> SELECT G.GLOBAL_NAME, D.ADMIN_REQUESTS, E.STATUS, DT.TRAN, DE.ERRORS, C.COMPLETE
2 FROM (SELECT GLOBAL_NAME FROM GLOBAL_NAME) G,
3 (SELECT COUNT(ID) ADMIN_REQUESTS FROM DBA_REPCATLOG) D,
4 (SELECT COUNT(STATUS) STATUS FROM DBA_REPCATLOG WHERE STATUS = 'ERROR') E,
5 (SELECT COUNT(*) TRAN FROM DEFTRANDEST) DT,
6 (SELECT COUNT(*) ERRORS FROM DEFERROR) DE,
7 (SELECT COUNT(A.DEFERRED_TRAN_ID) COMPLETE FROM DEFTRAN A
8 WHERE A.DEFERRED_TRAN_ID NOT IN (
9 SELECT B.DEFERRED_TRAN_ID FROM DEFTRANDEST B)) C;

Def Def
Admin Admin Trans Trans Propagated
Database Reqests Errors Pairs Errors Trans
------------------------- ------- ------ ----- ------ ----------
TESTDB 0 0 0 0 0

SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20
SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25
SQL> COLUMN MASTERDEF HEADING 'MasterDefinitionSite?' FORMAT A10
SQL> SELECT GNAME, DBLINK, MASTERDEF
2 FROM DBA_REPSITES
3 WHERE MASTER = 'Y'
4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')
5 ORDER BY GNAME;

Master
Definition
Master Group Sites Site?
-------------------- ------------------------- ----------
HR_REPG EMREP1 N
HR_REPG TESTDB Y

SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A15
SQL> COLUMN deftran HEADING 'Number ofDeferredTransactionPairs' FORMAT 9999
SQL> COLUMN deftranerror HEADING 'Number ofDeferredTransactionErrors' FORMAT 9999
SQL> COLUMN adminreq HEADING 'Number ofAdministrativeRequests' FORMAT 9999
SQL> COLUMN adminreqerror HEADING 'Number ofAdministrativeRequestErrors'
SQL> COLUMN adminreqerror FORMAT 9999
SQL>
SQL> SELECT G.GNAME,
2 NVL(T.CNT1, 0) deftran,
3 NVL(IE.CNT2, 0) deftranerror,
4 NVL(A.CNT3, 0) adminreq,
5 NVL(B.CNT4, 0) adminreqerror
6 FROM
7 (SELECT DISTINCT GNAME FROM DBA_REPGROUP WHERE MASTER='Y') G,
8 (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT D.DEFERRED_TRAN_ID) CNT1
9 FROM DBA_REPOBJECT RO, DEFCALL D, DEFTRANDEST TD
10 WHERE RO.SNAME = D.SCHEMANAME
11 AND RO.ONAME = D.PACKAGENAME
12 AND RO.TYPE IN ('TABLE', 'PACKAGE', 'MATERIALIZED VIEW')
13 AND TD.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
14 GROUP BY RO.GNAME ) T,
15 (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT E.DEFERRED_TRAN_ID) CNT2
16 FROM DBA_REPOBJECT RO, DEFCALL D, DEFERROR E
17 WHERE RO.SNAME = D.SCHEMANAME
18 AND RO.ONAME = D.PACKAGENAME
19 AND RO.TYPE IN ('TABLE', 'PACKAGE', 'MATERIALIZED VIEW')
20 AND E.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
21 AND E.CALLNO = D.CALLNO
22 GROUP BY RO.GNAME ) IE,
23 (SELECT GNAME, COUNT(*) CNT3 FROM DBA_REPCATLOG GROUP BY GNAME) A,
24 (SELECT GNAME, COUNT(*) CNT4 FROM DBA_REPCATLOG
25 WHERE STATUS = 'ERROR'
26 GROUP BY GNAME) B WHERE G.GNAME = IE.GNAME (+)
27 AND G.GNAME = T.GNAME (+)
28 AND G.GNAME = A.GNAME (+)
29 AND G.GNAME = B.GNAME (+) ORDER BY G.GNAME;

Number of Number of Number of
Deferred Deferred Number of Administrative
Transaction Transaction Administrative Request
Master Group Pairs Errors Requests Errors
--------------- ----------- ----------- -------------- --------------
HR_REPG 0 0 0 0

SQL> COLUMN repgroup HEADING 'Number ofReplicationGroups' FORMAT 9999
SQL> COLUMN mvgroup HEADING 'Number ofRegisteredMV Groups' FORMAT 9999
SQL> COLUMN mv HEADING 'Number ofRegistered MVs' FORMAT 9999
SQL> COLUMN mvlog HEADING 'Number ofMV Logs' FORMAT 9999
SQL> COLUMN template HEADING 'Number ofTemplates' FORMAT 9999
SQL> SELECT A.REPGROUP repgroup,
2 B.MVGROUP mvgroup,
3 C.MV mv,
4 D.MVLOG mvlog,
5 E.TEMPLATE template
6 FROM (SELECT COUNT(G.GNAME) REPGROUP
7 FROM DBA_REPGROUP G, DBA_REPSITES S
8 WHERE G.MASTER = 'Y'
9 AND S.MASTER = 'Y'
10 AND G.GNAME = S.GNAME
11 AND S.MY_DBLINK = 'Y') A,
12 (SELECT COUNT(*) MVGROUP
13 FROM DBA_REGISTERED_MVIEW_GROUPS) B,
14 (SELECT COUNT(*) MV
15 FROM DBA_REGISTERED_MVIEWS) C,
16 (SELECT COUNT(*) MVLOG
17 FROM (SELECT 1 FROM DBA_MVIEW_LOGS
18 GROUP BY LOG_OWNER, LOG_TABLE)) D,
19 (SELECT COUNT(*) TEMPLATE FROM DBA_REPCAT_REFRESH_TEMPLATES) E;

Number of Number of
Replication Registered Number of Number of Number of
Groups MV Groups Registered MVs MV Logs Templates
----------- ---------- -------------- --------- ---------
1 0 2 0 0

SQL> COLUMN LOG_TABLE HEADING 'Log Table' FORMAT A20
SQL> COLUMN LOG_OWNER HEADING 'LogOwner' FORMAT A5
SQL> COLUMN MASTER HEADING 'Master' FORMAT A15
SQL> COLUMN ROWIDS HEADING 'RowID?' FORMAT A3
SQL> COLUMN PRIMARY_KEY HEADING 'PrimaryKey?' FORMAT A7
SQL> COLUMN OBJECT_ID HEADING 'ObjectID?' FORMAT A6
SQL> COLUMN FILTER_COLUMNS HEADING 'FilterColumns?' FORMAT A8
SQL>
SQL> SELECT DISTINCT LOG_TABLE,
2 LOG_OWNER,
3 MASTER,
4 ROWIDS,
5 PRIMARY_KEY,
6 OBJECT_ID,
7 FILTER_COLUMNS
8 FROM DBA_MVIEW_LOGS
9 ORDER BY 1;

no rows selected
SQL>

Hints:

To test that the replication setup works, try DML (Insert, Delete, Update) on any of the tables added as replication objects.

When using the HR example schema. After an update on Job table, you might get ORA-00001 UNIQUE CONSTRAINT VIOLATED error on the Job History table during replication, thereby causing the replication to fail. This is because of a trigger on Job table is trying to make a change that a replication is doing on Job history table in EMREP1 (in my example). A quick fix is to disable the trigger in EMREP1 (not in TESTDB in my example).

Using DBMS_METADATA in Procedure to reference another schema's object

2007-10-25T12:42:00.000+01:00

This is a question already asked by many and I thought I should give an example here. You want to call DBMS_METADATA in PL/SQL created on your schema, but referencing another users object and use the the error like:

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1

It is an expected behviour which is stated in the security Model for dbms_metadata in the Oracle® Database PL/SQL Packages and Types Reference
.

The SELECT_CATALOG_ROLE alone does not give you privilege to use it in a package/procedure for another schema. But it allows you to use it outside.

So you need to use the invokers-right by adding authid current_user

Example:

SQL> connect test/test
Connected.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

SQL> create or replace procedure test_meta
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;
BEGIN test_meta; END;

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1

SQL> create or replace procedure test_meta authid current_user
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT
"BIN$Bj5vCCIBT6uv5ZCEA/Zm0A==$0" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE ROW MOVEMENT

PL/SQL procedure successfully completed.

SQL>

Oracle® 11g Database Hints

2007-10-24T13:27:00.000+01:00

I have finally installed Oracle® Database 11g Release 1 (11.1) for my testing. I will be sharing my experience with this post as I test and try to break it.

Platform: Windows XP SP2
Dell Latitude D810 Laptop
Intel Pentium M 2.0 GHz Processor
1GB RAM
60GB Hard Drive

Installation
My first install is for Windows. As usual, the Installation Guides from the Oracle® 11g Documentation Library was very helpul.

Installation Components
There are installation changes you may want to take note of. These include the addition of the following components: Oracle Application Express, Oracle Configuration Manager,Oracle Database Vault,Oracle Real Application Testing,Oracle SQL Developer and Oracle Warehouse Builder.

Pre installation checks - DHCP
The Pre installation check now includes checking and reporting if your machine has DHCP assigned IP Address. My System has a DHCP assigned IP Address. I got the warning, but ignored it because the IP assigned to me via DHCP is Reserved for my PC on our network. Reserved is not the same as Static.

Hardware Requirements

Physical memory: (RAM) 1 GB minimum
Virtual memory: Double the amount of RAM
Disk space: Basic Installation Type total: 4.55 GB
Disk space: Advanced Installation Types total: 4.92 GB
Video adapter: 256 colors
Processor: 550 MHz minimum (On Windows Vista, 800 MHz minimum)

Take note of the following hardware requirements. This is important because if you plan to installation with preconfigured database, you System might be so slow that you cannot start anything after the install because it will automatically start the database.

SPFILE

Below is some contents of my SPFILETEST11DB.ORA immediately after install, before I started changing it. So, looking at it, it is important that you have at least the 1GB RAM as recommended.

test11db.__db_cache_size=180355072
test11db.__java_pool_size=12582912
test11db.__large_pool_size=4194304
test11db.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
test11db.__pga_aggregate_target=104857600
test11db.__sga_target=327155712
test11db.__shared_io_pool_size=0
test11db.__shared_pool_size=125829120
test11db.__streams_pool_size=0
*.audit_file_dest='C:\oracle\admin\test11db\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='test11db'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11dbXDB)'
*.memory_target=428867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Deprecated Parameters

#background_dump_dest
#user_dump_dest

Deprecated Components

SQL*Plus Gui and iSQL*Plus is not included in the release
Enterprise Manager Java Console is not included in the release

Stanby Database

Oracle 11g allows you to use RMAN to change the db_unique_name of a Standby database using the CHANGE command CHANGE DB_UNIQUE_NAME FROM old_name to new_name. You can use this command after you have changed it in init parameter to update the recovery catalog.

Enterprise Manager Database Control
Also, the new look and feel of Enterprise Manager Console is an interesting. It is now grouped into the following tabs: Home, Performance, Availability, Server, Schema, Data Movement, Software and Support.

Why can I find my Database Tool in the EM Console?

Click my test images below.

EM Proxy and Browser Issues

I have Proxy settings on my Explorer. The bad news is that this is reset each time I log on to our Network. So, I have to change it not to proxy for my localhost, my servername and servername.domain.com. If I do not remember to do this, I keep getting Page Not found errors.

Note that this issue is not because of Oracle 11g. It is normal when you have to access any webserver related stuff on your PC and the browser has to go to the Proxy Server and back to yur PC. So whether it is 11g or 10g, you will get the same error.

Secured DB Console

I did a standard install using preconfigured setting. So by default, DB Console is secured. When I accessed with http, I kept getting a blank screen with squarea. I then checked my logs, changed the URL to use https and it worked.

Oracle® Demos for Scott Tiger

2007-10-04T16:49:00.000+01:00

Ever thought of where to find those Oracle® demos for Scott Tiger ? I am posting it here because a lot of new Oracle Users can hardly find it. So you can pick it up here.

--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
-- demobld.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- From within SQL*Plus, enter:
-- START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT

DBMS Direct at Oracle® Technology Network

2007-10-04T14:12:00.000+01:00

I thought I should provide you with a link to find answers to some of the questions I have already answered or contributed to Oracle® Technology Network Forums.

Search My Oracle® Technology Network Contributions

If you cannot find the required answer, then feel free to ask me.

All the best

Dropping all User Objects Example

2007-09-28T11:34:00.002+01:00

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

-- A lot of times I want to drop all user objects in the schema. I use the following script

-- remember to connect first as the user/owner of the objects you want to drop. Do NOT run this as another user or SYS

declare
del_objs integer;
obj_del integer;
cursor get_objs is
select object_type,
'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_nam
from user_objects
where object_type in ('TABLE',
'VIEW',
'PACKAGE',
'SEQUENCE',
'PROCEDURE',
'FUNCTION',
'SYNONYM', 'MATERIALIZED VIEW')
order by object_type;
cursor get_objs2 is
select object_type, '"'||object_name||'"' obj_nam
from user_objects
where object_type in ('TYPE');
begin
for objrec in get_objs loop
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
end loop;

for objrec in get_objs2 loop
begin
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
exception
when others then
null;
end;
end loop;
end;
/

-- Remember to drop the Queues if you are using AQ
declare
cursor active_queues is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'QUEUE';
cursor active_queue_tables is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'TABLE';
begin
for next_queue in active_queues loop
DBMS_AQADM.STOP_QUEUE (queue_name => next_queue.object_name);
DBMS_AQADM.DROP_QUEUE (queue_name => next_queue.object_name);
end loop;

for next_table in active_queue_tables loop
DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => next_table.object_name, force => TRUE);
end loop;
end;
/

-- Remember to Purge the Recylebin if you are using 10g

purge recyclebin
/

-- Comfirm all is dropped

select * from user_objects
/

Enterprise Manager Upgrade Hints

2007-09-28T10:00:00.000+01:00

I have just upgraded my test Grid Control from release 2 (10.2.0.2) to release 3 (10.2.0.3). The first thing did was to read the Readme. This provided most of the hints I need to know and to avoid before I start. For instance, patches to apply and those to rollback.

EM Grid Control Release 3 (10.2.0.3.0) Readme

After that, I picked the release note itself and still went through thoroughly. I know we do not all have the time to do it, but I see it as very cost effective because you spend less time in problem diagnosis after meeting all the requirements.

EM Grid Control Release 3 (10.2.0.3.0) Release Note (Linux and Windows)

I did not have a patch to apply or rollback because the ones mentioned in the release note does not apply to my setup.

I have answered a lot of question relating to upgrades in the Oracle forum and most of the time, they are simply related to pre-install requirements and compatibility. Although the Readme and release notes as rightly pointed out by one of the users, is not always correct, but I see it as the first step before I start looking elsewhere.

Although I had a smaller test system, at the end of it, it went smoothly without a hitch. If I have any problems with it later, then I will look into it as a separate issue because I know the upgrade of both OMS, Agent and repository went OK.

Platform: Windows XP SP2
Dell Latitude D810 Laptop
Intel Pentium M 2.0 GHz Processor
1GB RAM
60GB Hard Drive

Environment: Personal Upgrade Testing for Training purpose.

The same Grid Control installation has gone through various upgrades on my system. Release 10.1.0.3, 10.1.0.4, 10.2.0.2 and now 10.2.0.3. I think I am ready for the next upgrade. What do you think ?

Oracle Database FAQs

2007-08-13T16:20:00.006+01:00

Question: I am new to Oracle. What should I do first to get started
Answer: Find and read the Oracle Documentation relevant to your area of interest in Oracle. Oracle Product Documentations are Online at the Oracle website.

See Getting Started: DBA and Oracle Documentation Libraries

Question: Where can I find Oracle Database Documentations?
Answer: The following links will help you

Oracle® 11g Documentation
Oracle® 10g Documentation
Oracle® 9i Documentation
Oracle® 8i Documentation
Oracle® 8 Documentation
Oracle® 7 Documentation

Question: Can I easily install the Software and create the database without using the installation guides
Answer: It is important to read any product's documentation before you start installation. It saves you and your organisation the time you will spend searching the internet and forums for answers to avoidable problems. Most of the Software and Hardware requirements and known issue will be stated in the Installation Guide, ReadMe or Release note.

Questions: Can I skip any of the prerequisite checks?
Answer: You should only skip them whee and when advised by Oracle. The fact that installation completed successfully after skipping some checks does not guarantee that a related problem will not show up later.

Questions: How can I upgrade from Oracle Release A to Oracle Release B
Answer: Every Oracle Release comes with an Upgrade Guide and releated release notes. There is no need for guess work on it. The first thing to do is to pickup up and read the these documents before you start.

Question: When I have two Oracle Homes, which listener should I use
Answer: Use the listener of the later Release.

Question: I have got an error while running a program. What should I do first
Answer: Look at the Oracle Error Messages Manual as the first stop. If you the exaplanation did not help, then search Oracle Metalink and Oracle Forum for similar errors before you post any questions. If searching does not help, then post a question in the correct forum. If you cannot find a solution, then Open Service Request (SR - formerly TAR).

Question: What information do I need to provide when posting a question to Oracle Metalink or Oracle Forum
Answer: State your Product, Version, Release, Platform/Operating System. Mention the latest Patchset and CPU applied. Mention what you did or changed before the error occurred and what you have already done after the error occurred. Ensure you do not write in capital letters as it indicated shouting.

See Oracle Forums Etiquette

Question: Do you have any Hint on Oracle 11g Instalaltion?
Answer: The following links will help you

Oracle® 11g Database Hints

Question: Where can I find Enterprise Manager Documentation?
Answer: The following links will help you

Enterprise Manager Documentation Library

Question: Where can I find Oracle Examples?
Answer: The following links will help you

Oracle® By Example (OBE)

Question: Where can I find Oracle Database Error Messages?
Answer: The following links will help you

Oracle® Database Error Messages

Question: Where can I find information on Duplicating Databases?
Answer: The following links will help you

Creating and Updating Duplicate Databases with RMAN

Question: Where can I find information on Application Server Single Sign On?
Answer: The following links will help you

Configuring Enterprise Manager for Use with OAS Single Sign-On

Question: Where can I find information on Enterprise Security?
Answer: The following links will help you

Configuring Enterprise Manager for Use with Enterprise User Security

Question: Which of the documentations is equvalent to quick guides for DBAs
Answer: In the Documentation Libraries, look fo rthe 2 Day DBA guides. They are simpler to use than the main big guides.

You can use the links at the right pane of this blog to find other topics of interest.

All the best in your Oracle role.

Tackling the Month End Processes

2007-08-09T11:10:00.000+01:00

Getting ready for the month End Processes for one of our clients is one of the tasks that keep us busy at this time of the month. Although the tasks involved in the processes have been automated and streamlined, you still have to ensure that things go smoothly.

This Month end processes will started on the 7th and will be completed on the 16th. My part will be 1 day before the d-day and the d-day itself (14th). Other post tasks will be monitoring what other teams are doing.

It is fun as usual, but could be a different story if things go wrong.

Fingers Crossed.

Client and DBA Knowledge Transfer

2007-08-08T10:06:00.000+01:00

Will be spending 2 hours in a knowledge transfer session with team members this morning lead by one of the gurus here. This I understand will cover all the services provided for the new client as covered in the service definition with most concentration on the actual Database Adminitration tasks to be provided.

One of the team members just joked "take an anti sleeping tablet with you". Yes, it helps sometimes. You know what I mean.

But I am looking forward to the sessions. Will close in time for lunch though.

Using atomic_refresh Parameter in Oracle Materialzed View Refresh

2007-08-07T15:18:00.001+01:00

I was doing a refresh today that involves materialised views from the second schema and a colleague asked me this question. I thought I should share oracle's answer to it.

Why use atomic_refresh=>FALSE?

SQL>execute dbms_mview.refresh('schema.mv_name','C',atomic_refresh=>FALSE);

"Refreshing single MV is not atomic even if ATOMIC_REFRESH = true (the default). This can lead to wrong results in sessions querying against the materialized view as the row count can suddenly change to 0 (as the refresh truncates the MV). An atomic refresh should not affect read consistency in this way"

In 10g a Complete Refresh of single Materialsed View will do a delete instead of truncate. To force the refresh to do a truncate instead of a delete parameter atomic_refresh must be set to false.

That means that since it will be doing truncate instead of delete, the refresh will go faster.

"The dafault value for atomic_refresh in dbms_mview.refresh in 8.1.7, 9i and 10g is TRUE, but in 8i and 9i Oracle does not check the atomic parameter, but because of some enhancements it will do this in 10g"

Just Another Good DBA Day

2007-08-07T11:07:00.001+01:00

Just hoping it will be a more interesting day today.

Have just completed my usual morning checks and trying to deal with a support request that have no useful detail on it. Yes, the usual DBA requests that have not got enough information to determine what and where. Sometimes it is like the question in Oracle Forums that you have to ask "What release and Which platform" etc.

This particular request for users to be created without saying where or for which environment. It may not even be within the service deifinition of the projects I support, could be someone else's responsibility or could even be for a different client/account. As usual, I have asked questions and forwarded it to someone who might know for advice.

Well, I am currently working on a Database Shared Service Centre with various clients/accounts to support. This makes the job interesting, but sometimes more demanding.

It's nice a sunny here this morning, but may get rainy later in the day because it is beginning to show some signs of the sun disappearing. Yesterday was good.

Have a good day.

Ask DBMS Direct

2007-08-06T16:31:00.000+01:00

I thought I should do something more interesting apart from regular work. Then I said, "Why not start a blog that will help other DBA, Developers and Users".

It has always sounded like an interesting idea. So, here I am.

I will try as much as I can to update this blog. If you find it boring or uninteresting any day do not be sad, visir another day.

Welcome to my blog.