How to Restore Dell Mini Netbook to Factory Settings

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

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

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

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

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

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

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

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

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

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

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).