Building Standby database ( without backup) from Primary Database

Oracle 11g has many feature which allow DBA's manage there database more esily and quickly. One of the feature which made DBA's life much easy is build standby database without backup i.e. building from Primary/Active Database.

This feature comes with following benifits.
* No Need to backup
* NO need to sheep backup to Standby server,
* No hessle of Mounting backup to Standby server as nfs or anything like that.

Building standby database is done using RMAN command DUPLICATE which we used before to duplicate database.


Step by Step.

1. Create pfile from spfile on the primay database.

2. scp pfile to new server.
3. edit pfile
4. Create spfile
5. startup nomount;
6. Create listener and Tnsnames.
7. Start standby database using RMAN duplicate database
$ rman

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Feb 10 22:18:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> CONNECT TARGET SYS/password@DB_DG_SITEB
connected to target database: DBPRD (DBID=39856714)

RMAN> Connect auxiliary SYS/password@DB_DG_SITEA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Above error occured if you forget to copy password file from primary site.
Fix this by coping password file from primary/production site.

SITEA>scp $ORACLE_HOME/dbs/orapwDBPRD1 oracle@SITEB:$ORALE_HOME/dbs/orapwDBPRD1

RMAN> Connect auxiliary SYS/password@DB_DG_SITEA
connected to auxiliary database: DBPRD (not mounted)




DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE;

Ho to Manually Configure Database Vault Administrator Configuration for Grid Control

Deploying Database Vault Administrator to the Database Console OC4J Container

To manually deploy Database Vault Administrator to the Database Console OC4J container:

1. Stop Oracle Database Console.
   
   • UNIX: Go to the $ORACLE_HOME/bin directory and run the following command:
     

     ./emctl stop dbconsole
     

   • Windows: In the Administrative Services, select the Services utility, and then right-click the OracleDBConsolesid service. Select Stop from the menu.
2. Create a backup copy and then open the $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_service_name/config/server.xml file.
3. Add the following line before the  element:
   
   On Windows systems, replace $ORACLE_HOME with the absolute path to your Oracle Database home.
4. Create a backup copy and then open the $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_service_name/config/http-web-site.xml file.
5. Add the following line before the element:
   

6. Restart Oracle Database Console.
   
   • UNIX: Go to the $ORACLE_HOME/bin directory and run the following command:
     

     ./emctl start dbconsole
     

   • Windows: In the Administrative Services, select the Services utility, and then right-click the OracleDBConsolesid service. Select Start from the menu.

After you complete these steps, you can start Oracle Database Vault Administrator by using the following URL:

https://hostname:port/dva

For example:

https://myserver:1158/dva

If you are unsure of the port number, open the ORACLE_HOME/host_sid/sysman/config/emd.properties file and search for REPOSITORY_URL. In most cases, the host name and port number are the same as Oracle Enterprise Manager Database Control.

Oracle cluvfy failed with error PRVF-2415 on HP-UX

Oracle Cluster Verfication utility may fail with followin error on HP-UX .

--------cluvfy log --------------------------
File "/etc/resolv.conf" is consistent across nodes

Time zone consistency check passed
Checking settings of device file "/dev/async"
Check for settings of device file "/dev/async" failed.
ERROR:
PRVF-2415 : The minor number of device file "/dev/async" is incorrect on the nodes:
Check failed on nodes:
server3,server1
Starting check for The SSH LoginGraceTime setting ...

Problem: By Default device ID is incorrect for HP-UX 11.31, and need to be created as per oracle minor and major device.

Solution :

Oracle Database 11g: Capture & Replay

Oracle Database 11g : Database Capture and Database Replay Feature.
A.1 Capture the workload from production database.

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'TEST_CAPTURE01',
                           dir => 'DUMPDIR',
                           duration => 60);
END;
/

B.1  Process the Workload. You must process the captured workload before replay. Its recommended to perform this activity on Test system to avoid any adverse impact on production db performance.

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DUMPDIR');
END;
/


Following steps would be require to replay a captured load on Test System.
1) Initialize the Captured workload you like to replay
Execute   DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'REPLAY001', replay_dir => 'DUMPDIR');


2) Query DBA_WORKLOAD_CONNECTION_MAP and ensure you happy with Captured and Replay mapping. update the Remap Connection if necessary using below  procedure.

Execute   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 101,replay_connection => 'dlsun244:3434/bjava21');


3) Update any Parameter to non default value.
EXECUTE  DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);


4) Connect to client, you want to use as Replay client and cd to location where capture & processed files are stored.
cd /u01/app/oracle/dumpdir

5) Run the replay client.
$ORACLE_HOME/bin/wrc username/password


6) Come back to previous session and start the replay.
 EXECUTE  DBMS_WORKLOAD_REPLAY.START_REPLAY ();

7) use Oracle dictionary Views or Enterprise manager to review progress.

Gather Table statistics without without invalidating cursors

Gather stats for EMP table in Scott schema without invalidating the Cursor and Explain plan stored in memory.

execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',NO_INVALIDATE=>TRUE);

PL/SQL procedure successfully completed.

Setting NO_INVALIDATE to TRUE will ensure plan/cursor doesn't get invalidated after stats collection.

Initializing and Tuning Parameters for Parallel Execution

PARALLEL_MAX_SERVERS <=> CPU_COUNT x PARALLEL_THREADS_PER_CPU x 2x 5 <=>Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance.

PARALLEL_EXECUTION_MESSAGE_SIZE <=> 2 KB (port specific) <=> Increase to 4k or 8k to improve parallel execution performance if sufficient SGA memory exists.

PARALLEL_ADAPTIVE_MULTI_USER <=> TRUE <=> Causes parallel execution SQL to throttle DOP requests to prevent system overload.

The following example shows a statement that sets the DOP to 4 on a table:

ALTER TABLE orders PARALLEL 4;
This next example sets the DOP on an index to 4:

ALTER INDEX iorders PARALLEL 4;

This last example sets a hint to 4 on a query:

SELECT /*+ PARALLEL(orders, 4) */ COUNT(*) FROM orders;


The initialization parameter PARALLEL_MIN_PERCENT specifies the desired minimum percentage of requested parallel execution servers. This parameter affects DML and DDL operations as well as queries

cross Platforms - Transportable Tablespaces

 Across Platforms Using Transportable Tablespaces :

Step for Cross platform Transportable tablespace

0.1 mkdir /u01/app/oracle/dumpdir
0.2 Create directory dumpdir as '/u01/app/oracle/dumpdir';

1. expdp  TRANSPORT_TABLESPACES=TEST_EXP DUMPFILE=expdp_TEST_EXP.dmp  DIRECTORY=dumpdir TRANSPORT_FULL_CHECK=y

2. RMAN> CONVERT TABLESPACE sales_1,sales_2 

         TO PLATFORM 'Microsoft Windows NT'

         FORMAT '/temp/%U';

3. Copy to Destination Server/Host

4. RMAN> CONVERT DATAFILE 

         '/hq/finance/work/tru/tbs_31.f',

         '/hq/finance/work/tru/tbs_32.f',

         '/hq/finance/work/tru/tbs_41.f'

         TO PLATFORM="Solaris[tm] OE (32-bit)"

         FROM PLATFORM="HP TRu64 UNIX"

         DB_FILE_NAME_CONVERT=

         "/hq/finance/work/tru/", "/hq/finance/dbs/tru"

         PARALLELISM=5;

5. IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir

   TRANSPORT_DATAFILES=

   /salesdb/sales_101.dbf,

   /salesdb/sales_201.dbf

   REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)

6. ALTER TABLESPACE sales_1 READ WRITE;

   ALTER TABLESPACE sales_2 READ WRITE;