Learn Database Concepts

 How to Drop/Rename Standby Redolog file in Oracle 11g While performing the dataguard Broker, we need to drop the standby database while switchover the standby . As it seems an easy task but it is bit tricky . Below are the steps to drop the redolog file from standby database : On Standby Database : SQL> select member,type from v$logfile; MEMBER                                                                     TYPE                     ----------------------------------             ...

Oracle Active Data Guard and Oracle GoldenGate Oracle Active Data Guard and Oracle Golden Gate are strategic capabilities within Oracle's software portfolio. While they generally fall into the category of replication technologies, each has a very different area of focus. Oracle Active Data Guard provides the best data protection and availability for Oracle Database in the simplest most economical manner by maintaining an exact physical replica of the production copy at a remote location that is open read-only while replication is active. Active Data Guard eliminates compromise inherent in storage remote-mirroring or logical replication solutions though deep integration with Oracle Database and through the simplicity achieved by complete focus on providing real-time data protection and availabi...

SCRIPT TO CREATE TOAD USER : I really had to struggle a lot to get this script.Creating these objects as toad user will allow u to gathers stats for disk ,data file and table spaces growth and trending. This scipt need to be run as toad user to create all the tables & ddl needed for toad user and space management graphs. REM This script was created by version 10.6.1.3 of the TOAD Server Side Objects Wizard DEFINE OWNER = 'TOAD' Prompt ============================================================================ Prompt Creating the TOAD User Prompt ============================================================================ Prompt Creating the TOAD User CREATE USER TOAD IDENTIFIED BY toad1234 DEFAULT TABLESPACE TOAD TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TOAD; Prompt Granting System Privileges to TOAD Grant UNLIMITED TABLESPACE to TOAD; Grant ALTER SESSION to TOAD; Grant CREATE SEQUENCE to TOAD; Grant CREATE SESSION to TOAD; Grant CREATE SYNONYM to TOAD; Grant CREATE TR...

we can use a simple trick to check if the standby database is up to date or not : The trick is we are going to create a table and populate it every minute on primary server with a scheduled job using a  simple procedure.When we query the standby server we will see the table synced and if it is not the server is struck some where and has an issue. Table DDL :  CREATE TABLE "ORACLE"."DG_SYNC_STATUS"    (    "TSTAMP" TIMESTAMP (6),     "DB_UNIQUE_NAME" VARCHAR2(30 BYTE),     "PRIMARY_SCN" NUMBER,     "STANDBY_SCN" NUMBER,     "PROTECTION_MODE" VARCHAR2(20 BYTE),     "PROTECTION_LEVEL" VARCHAR2(20 BYTE)    )  TABLESPACE "USERS" ;  then we will create a simple procedure to populate the table every minute and this also will cleanup rows older than sysdate -3 so you don't have to worry about cleaning it up or space issues. create or replace PROCEDURE  ...

SQL> alter database add standby logfile '/u02/oracle/oradata/stdby01.log' size 512M; Database altered. SQL> alter database add standby logfile '/u02/oracle/oradata/stdby02.log' size 512M; Database altered. SQL> alter database drop standby logfile '/u02/oracle/oradata/stdby01.log'; Database altered. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP#    THREAD#  SEQUENCE# ARC STATU ------ ---------- ---------- --- -----      6          0          0 YES UNASS                                  IGNED SQL> alter database drop standby logfile '/u02/oracle/oradata//stdby02.log'; Database altered. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_L...

How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases Lets now try to export tables from different schemas in Oracle 10g database on a Linux server. [oracle@localhost ~]$ sqlplus / as sysdba We are assuming that a1 and a2 are 2 schemas  with a common table t1 [oracle@sap1]> expdp ‘”/ as sysdba”‘ directory=DATAPUMPDEMO_exp dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1                                                     Export: Release 10.2.0.4.0 – 64bit Production on Thursday, 23 June, 2013 15:35:01 Copyright (c) 2003, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Ent...

Oracle Tablespace High water mark Tablespace High water mark Tablespace HWM col tablespace_name format a15 col file_size format 99999 col file_name format a50 col hwm format 99999 col can_save format 99999 SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name, ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de, (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs WHERE ddf.file_id = ebf.file_id AND de.file_id = ebf.file_id AND de.block_id = ebf.maximum ORDER BY 1,2); TABLESPACE_NAME FILE_NAME FILE_SIZE HWM CAN_SAVE --------------- -------------------------------------------------- --------- ------ -------- ANUJTEST /opt/app/oracle/oradata/orcl/anujtest.d...