ORACLE - ActiveDataGuard

Physical Standby Database:
I) Create Standby Database using  DUPLICATE DATABASE :
At Primary database (JDB):
1) Enable force logging (Priamry and Standby)
2) Create password file and Add connect string to tnsnames.ora/listener.ora file (Priamry and Standby)
3) Create standby redo logs (Priamry and Standby)
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 200M;
4) Modify Primary pfile or spfile:
alter system set log_archive_dest_1='location="USE_DB_RECOVERY_FILE_DEST",
valid_for=(ALL_LOGFILES,ALL_ROLES)' scope = BOTH;
alter system set log_archive_dest_2='service=JDR LGWR ASYNC NOAFFIRM
db_unique_name="JDR" valid_for=(ONLINE_LOGFILES,PRIMARY_ROLES)' scope=BOTH;
create pfile='/dba/hibeprd.pfile' from spfile;
5) Take Primary Database backup and move to standby server :
create pfile from spfile;
backup as backupset device type disk database ;
backup as backupset device type current controlfile for standby;

At standby database (JDR):
6)Modify pfile for standby :
*.db_name='JDB'
*.log_archive_dest_1='location="USE_DB_RECOVERY_FILE_DEST",
valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.control_files='+JDATA/jdr/controlfile/current'

Add to standby init.ora:
*.db_unique_name='JDR'
*.standby_file_management='AUTO'
*.db_file_name_convert='+JDATA/JDB/','+JDATA/JDR/'
*.log_file_name_convert='+JDATA/JDB/','+JDATA/JDR/'
*.fal_client='(DESCRIPTION for JDR database)'
*.fal_server='(DESCRIPTION for JDB database)'
*.log_archive_config='dg_config=(JDR,JDB)'
*.service_names='JDB','JDR'
*.dg_broker_start=TRUE
Note: commnet folloing 2 parameter if it is RAC:
#*.cluster_database_instances=2
#*.cluster_database=true

startup nomount;
rman target sys/password@JDB auxiliary / <<EOF
duplicate target database for standby nofilenamecheck;
exit;
EOF

6) Primary and Standby database: Enable dataguard both
alter system set DG_BROKER_START=true scope=both;
Note: For DATA GUARD BROKER SETUP - See my notes on Data Guard

II) Create standby database using Duplicate...ACTIVE DATABASE:
It will create standby database over the network using ative(primary) database files.

duplicate target database for standby active database
spfile
set db_unique_name='jdb_stdby'
set fal_client and fal_server
set standby_file_management='AUTO'
set log_archive_config and log_archive_dest_2;

III) Start MRP:
alter database recover managed standby database disconnect from session;

IV) Active Dataguard (need oracle license) : Open the Standby Database in READ ONLY
and start the recovery:
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database disconnect;

Oracle 11gR2 - New Featuers for DBA

1) Enhanced Online Index Creation and Rebuild :Before Oracle 11g, rebuilding an index online on a table with extensive DML leads to unpredictable results because table exclusive lock (X) is required by rebuilding process. In Oracle 11g, rebuilding an index acquire shared exclusive lock (SX) on the table allowing DML to go on uninterrupted. This new enhancement applies in the following statements:
1. Create index online
2. Rebuild index online
3. Create materialized view log


2) DDL_LOCK_TIMEOUT - Avoid ORA-00054: resource busy error message
Modify a table:
Step 1) Alter table jtable add ( last_name varchar2(20));
*
ORA-0054:resource busy and aquire with nowait specified or timeout expired
Step 2) Alter session set ddl_lock_timeout=30; (seconds)
Step 3) Alter table jtable add ( last_name varchar2(20));
Table altered.

3) Invisible Indexes:  To avoid drop and re-create the index :   Invisible indexes are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE at the instance or session level.

Index is invisible to optimizer:
create index index_name on table_name (col_name) INVISIBLE;
alter index index_name INVISIBLE;
OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE

Index is visible to optimizer:
alter index index_name VISIBLE;
OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE

select VISIBILITY from DBA|all|user_INDEXES views.

4) Adding columns with a default value:
alter table table_name add col_name varchar2(6)  'ABC' not null;
If table has 900 million rows, it will generate larger amount of undo and redo hence performance overhead.
In Oracle 11g, the default value will be stored in the data dictionary instead of the table itself. This will save space and significantly reduce time to execute the modification statement. Update the data dictionary with default value - hence no redo and undo. Oracle 11g will not issue an update all the records of the table.

5) Read-only Tables: Make table read only,modify DDL (create index/partition) and then make it read write : If you make table read only, insert, delete and update are disallowd otherwise ORA-12081.

alter table table_name read only;
alter table table_name read write;

6) Segment-less Objects: Segments are not created by default when table is created but rather when the 1st data is inserted. Exmple:
create table jtest (jcol number); Table created.
select bytes from user_segments where segment_name = 'jtest'; no row selected.
insert into jtest values (101);  1 row inserted.
select bytes from user_segments where segment_name ='jtest'; bytes : 65536
To create the segment when table is created, set parameter deffrred_segment_creation=false;

7) SQL * PLUS error logging:
sql> SET ERRORLOGGIN ON
sql> select * from from dual;
select * from from dual
              *
ERROR at line 1:
ORA-00903: invalid table name

sql> SHOW ERRORLOGGING
errorlogging is ON TABLE SYS.SPERRORLOG

sql> SELECT USERNAME, STATEMENT, MESSAGE FROM SPERRORLOG;
USERNAME    STATEMENT                MESSAGE
SYS         select * from from dual  ORA-00903: invalid table name

sql> SET ERRORLOGGIN OFF


Ref. Oracle Docs/Metalink