Dynamic Views

V$SGA	The V$SGA view is useful in determining how much total memory is allocated to the various componentsof the SGA. The following simple query gives you a summary of the SGA memory usage bythe current instance:	SQL> SELECT * FROM V$SGA;
V$SGASTAT	The V$SGASTAT view gives you a detailed breakdown of the SGA memory. It shows you currentmemory allocations broken down into the following main areas:	SQL> SELECT bytes from v$sgastat2 WHERE pool='shared pool' and3 V$SGA name='free memory'
V$SESSION	The V$SESSION view gives you a wealth of information about the users, including their operatingsystem username, terminal name, whether they’re actively executing a transaction or just connectedto the database, and how long their connection has been in place. In Oracle Database 10g,the V$SESSION view also contains several wait-related columns such as WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, and SECONDS_IN_WAIT.
V$SESSION_LONGOPS	The V$SESSION_LONGOPS view shows the status of all operations that run for a long time (morethan six seconds in absolute time). The columns SOFAR and TIME_REMAINING indicate how much ofthe work is done and how long the operation has to go before completing. The following is a samplequery using the view:	SQL> SELECT sid, opname, sofar,totalwork,2 start_time, time_remaining3* FROM V$SESSION_LONGOPS;
V$LOGFILE	The V$LOGFILE view provides information about each redo log file, including its name andwhether the file is valid or not. The STATUS column has the following values:	SQL> SELECT * FROM V$LOGFILE;
V$ARCHIVED_LOG	The V$ARCHIVED_LOG view is essential when you’re looking at information regarding whicharchive logs you have access to. The view contains one entry for every log that your databasearchives. When you restore an archive log, the operation inserts one row	SQL> SELECT name, thread#, sequence#,2 archived, applied, deleted, completion_time3* FROM V$ARCHIVED_LOG;
V$ARCHIVE_DEST	As its name indicates, the V$ARCHIVE_DEST view shows you each archive log destination and itsstatus. This view has a large number of columns, and you need to pay special attention to the followingcolumns:	SQL> SELECT dest_name2 FROM V$ARCHIVE_DEST;
V$SYSSTAT	The V$SYSSTAT view provides you with all the major system statistics: parse statistics, executionrates, full table scans, and other performance indices. The V$SYSSTAT view provides you with thebuffer-cache hit ratios and a number of other hit ratios. Listing 23-31 shows a summary of the mainclasses of statistics contained in the V$SYSSTAT view.	SQL> SELECT * FROM V$SYSSTAT;
V$OSSTAT	The new V$OSSTAT view comes in handy when you wish to check system usage statistics.

Setting Up Standby Database

When you create your STANDBY database, you'll need to create directories for database administration files, database files, and archive logs. You'll also need to prepare the standby instance by copying and configuring a parameter file, creating a password file, and creating Windows services on Windows


Standby init.ora file

db_name = PRACTICE
instance_name = STANDBY
service_names = STANDBY
control_files = ("/oradata/STANDBY/standby.ctl)
log_archive_dest_l = 'location=/oracata/STANDBY/archive'
LOG_ARCHIVE_DEST_2 = "MANDATORY service=STANDBY reopen=30"
standby_archive_dest = "/oradata/STANDBY/archive"
background_dump_dest = /app/oracle/admin/STANDBY/bdump
user_dump_dest = /app/oracle/admin/STANDBY/udump
db_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY" log_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY"
lock_name_space = STANDBY

Mount standby database

LINUX> export ORACLE_SID=STANDBY;
LINUX> sqlplus /nolog
SQL> CONNECT sys/standby AS SYSDBA;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE

Recover standby database
SQL> RECOVER MANAGED STANDBY DATABASE;

If your physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for a log switch boundary and for the standby redo log to be archived. This new functionality is called real-time apply.

SQL> Recover managed standby database using current logfile;

Activate the Standby Database

1. Cancel standby database
   SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
   
2. Activate Standby databse
   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
   SQL> SHUTDOWN;
   SQL> STARTUP;

Steps : For Failover

1. Perform terminal recovery on the standby by issuing managed recovery with the FINISH keyword. The following command is to be used if you have
   
   alter database recover managed standby database finish;
2. If you do not have standby redo logs, or they are not active, you must enter the following command:
   
   alter database recover managed standby database finish skip standby logfile;
3. Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:
   
   alter database commit to switchover to primary;
4. Step 5. Restart the new primary database.

Using Rman to create standby database


RMAN > connect target /
RMAN > run {
backup database
include current controlfile for standby;
sql "alter system current log file";
}


RMAN > connect auxiliary /
RMAN > Duplicate target database for standby dorecover;

Checking archive log statusPrimary
SQL > select status, error from v$archive_dest;

Standby
SQL > select * from v$standby_log;

Confirm Managed Recovery
How can you know if the managed archive propagation process is running properly? You can look in three places:.

1. Archive files Look at the STANDBY database archive destinationfor archive logs being transmitted from the primary. New archivelog files on the PRACTICE database will be reproduced in the/oradata/STANDBY/archive directory.
   
   
2. Standby alert log Check the STANDBY alert.log for archive logs applicationentries. If you haven't seen any activity yet, perform a few log switches on thePRACTICE database. Wait a few minutes and look for evidence that the newarchive logs were transported and applied.
   
   
3. Media Recovery Log /oradata/STANDBY/archive/71.arc Media Recovery Waiting for thread 1 seqtt 72
   Log history The third and final test is to select from v$log_history onboth the primary and standby databases. The following query should return the same number, bearing in mind that the standby might be afew seconds behind:.
   
   SQL> SELECT MAX(sequence^) FROM v$log_history;

Net8 Connect-Time Failover

You can use connect-time Net8 failover to cause clients to connect to a backup instance in cases where the primary instance cannot be reached. This makes the most sense in an OPS (Oracle Parallel Server) environment where multiple instances are all accessing the same database. However, it can be done in a non-OPS environment as well. If you are using Oracle's standby database feature, you can configure a net service name so that clients connect to the standby database whenever the primary database is unreachable. Similarly, you could connect to a backup database maintained using Oracle's replication features.

Dynamically registering global database names with your listeners
One important issue to be aware of is that connect-time failover only works if you are dynamically registering global database names with your listeners. If you are statically configuring global database names, then connect-time failover will not work in a consistent manner:

If you want to use Net8's connect-time failover feature, you need to delete the GLOBAL_DBNAME parameter and allow the database to register itself with the listener automatically. You can list the database in your SID_LIST; you just can't include the GLOBAL_DBNAME parameter.

Listener.ora on ARK1
LSNR817 =

(DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521)) )

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = ARK1)) ) )
SID_LIST_LSNR817 = (SID_LIST = (SID_DESC = (ORACLE_HOME = D:\Oracle\Product\8.1.7) (SID_NAME = ARK1) )

)

Listener.ora on DIA3
LSNRDIA3 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.138.21)(PORT = 1523)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = DIA3)) ) )
SID_LIST_LSNRDIA3 = (SID_LIST = (SID_DESC = (ORACLE_HOME = /opt/oracle/product/8.1.7) (SID_NAME = DIA3) ) )

Failover Configuration in Tnsnames.ora on Net8 Client
PROD.WORLD =

(DESCRIPTION_LIST =

(FAILOVER = TRUE)

(LOAD_BALANCE = FALSE)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521))

(CONNECT_DATA = (SERVICE_NAME = ARK1)

(SERVER = DEDICATED)

)

)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1523))

(CONNECT_DATA = (SERVICE_NAME = DIA3)

(SERVER = DEDICATED)

)

)

)

When you connect using the service name PROD, Net8 will first try to connect to the database service ARK1 on the host arkum. If that connection fails, Net8 will move on to the database service DIA3 on the host named diamond.

Notice that the description list contains both (FAILOVER = true) and (LOAD_BALANCE = false). (FAILOVER = true) still represents the default behavior. It's included here to make it clear that failover is being used. (LOAD_BALANCE = false), however, does not represent the default behavior in this case. It's included to disable client load balancing, which is enabled by default whenever multiple descriptions are being used. With client load balancing enabled, Net8 would randomly choose descriptions from the description list. By disabling client load balance iou ensure that Net8 tries each DESCRIPTION in the order in which it appears in the list.

Using Materialized Views

The use of materialized views, or snapshots as they were previously known, is the simplist way to achive replication of data between sites. The materialized view is a table whose contents are periodically refreshed using a query against a remote table

Query Rewriting
The QUERY_REWRITE_ENABLED initialization parameter determines whether Oracle will rewrite a queryor not. The default value for this parameter is FALSE,

Refresh Mode
You can choose between the ON COMMIT and ON DEMAND modes of data refresh.

• ON COMMIT: In this mode, whenever a data change in one of the master tables is committed,the materialized view is refreshed automatically to reflect the change.
• ON DEMAND: In this mode, you must execute a procedure like DBMS_MVIEW.REFRESH to updatethe materialized view.The default refresh mode is ON DEMAND.

Refresh Type

You can choose from the following four refresh types:

• COMPLETE: This refresh option will completely recalculate the query underlying the materializedview. Thus, if the materialized view originally took you 12 hours to build, it’ll take aboutthe same time to rebuild it. Obviously, you wouldn’t want to use this option each time a fewrows are modified, dropped, or inserted into your master tables.
• FAST: Under the fast refresh mechanism, Oracle will use a materialized view log to log allchanges to the master tables. It’ll then use the materialized view log to update the mastertables, thus avoiding a complete refresh of the view. You can use other techniques toperforma fast refresh, but the materialized view log is the most frequently used devicefor this purpose.

Creating Materialized Views

SQL> GRANT CREATE DATABASE LINK TO scott;
SQL> GRANT CREATE MATERIALIZED VIEW TO scott;
SQL> GRANT QUERY REWRITE TO scott;

Creating the Materialized View Log

Let’s use the FAST refresh mechanism for our materialized view. This will require the creation of twomaterialized logs, of course, to capture the changes to the two master tables that are going to be thebasis for our materialized view. Here’s how you create the materialized view logs:Here’s how you create the materialized view log:

SQL> CREATE MATERIALIZED VIEW LOG ON products;
SQL> CREATE MATERIALIZED VIEW LOG ON sales;

SQL> CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE REFRESH FORCE
ON DEMAND AS SELECT * FROM emp@tsh1.world;