col username form A15
col sid form 9990
col type form A4
col lmode form 990
col request form 990
col id1 form 9999990
col id2 form 9999990
break on id1 skip 1 dup
spool check_lock.lis
SELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , lmode, ltrim(to_char(lmode,'990'))) lmode, DECODE(m.request, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , request, ltrim(to_char(request,'990'))) request, m.id1,m.id2FROM v$session sn, V$lock mWHERE (sn.sid = m.sid AND m.request != 0) OR (sn.sid = m.sid AND m.request = 0 AND lmode != 4 AND (id1, id2 ) IN (SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2 ) )ORDER BY id1,id2, m.request;
spool off
clear breaks
Tuesday, March 24, 2009
WRITING TO A TEXT FILE FROM PL/SQL
create or replace procedure Cursos_Loading(p_nothing IN integer) is
v_filehandle utl_file.file_type;
begin v_filehandle := utl_file.fopen('jagat', 'ABC.TXT', 'w', 4000);
for r in 1..10 loop
utl_file.put_line(v_filehandle, 'LINE # : 'r);
end loop;
utl_file.fclose(v_filehandle);
end Cursos_Loading;
/
EXEC Cursos_Loading(10);
v_filehandle utl_file.file_type;
begin v_filehandle := utl_file.fopen('jagat', 'ABC.TXT', 'w', 4000);
for r in 1..10 loop
utl_file.put_line(v_filehandle, 'LINE # : 'r);
end loop;
utl_file.fclose(v_filehandle);
end Cursos_Loading;
/
EXEC Cursos_Loading(10);
Dynamic Script for Kill Inactive Sessions
select 'ALTER SYSTEM KILL SESSION','''' SID ',' SERial# ''''' ;' from V$SESSION where USERNAME NOT IN ('SYS','SYSTEM')and TRUNC(LOGON_TIME)<='&DT' AND STATUS='INACTIVE';
----FOR LOCKING SESSIONS
select 'ALTER SYSTEM KILL SESSION','''' SID ',' SERial# ''''' ;'from V$SESSION where sid in(select session_id from DBA_DDL_LOCKS where owner not in ('SYS','SYSTEM'))
/
----FOR LOCKING SESSIONS
select 'ALTER SYSTEM KILL SESSION','''' SID ',' SERial# ''''' ;'from V$SESSION where sid in(select session_id from DBA_DDL_LOCKS where owner not in ('SYS','SYSTEM'))
/
Generate SQL*Loader Control File for Any Table
set verify off
set heading off
set embedded on
set recsep off
set pages 500
set lines 80
set feedback off
define &&1 = ''
spool c:\your_path_name\&&2..ctl
select'load datainfile ''' 'C:\your_path_name\' upper('&2') '.CSV''' 'into table ' upper('&1') '.' upper('&2') 'replacefields terminated by '',''' 'trailing nullcols'from all_tables where owner = upper('devcpos') and table_name = upper('itemmaster')
/
select decode(a.column_id, '1', '(') a.column_name (case when a.column_id < max(b.column_id) then ',' else ')' END)from all_tab_columns a, all_tab_columns bwhere a.owner = upper('&1') and a.table_name = upper('&2') andb.owner = upper('&1') and b.table_name = upper('&2')group by a.column_name, a.column_idorder by a.column_id
/
spool off
set verify on
set heading on
set embedded off
set recsep wrapped
set pages 64
set lines 80
set feedback 6
undefine 1
undefine 2
set heading off
set embedded on
set recsep off
set pages 500
set lines 80
set feedback off
define &&1 = ''
spool c:\your_path_name\&&2..ctl
select'load datainfile ''' 'C:\your_path_name\' upper('&2') '.CSV''' 'into table ' upper('&1') '.' upper('&2') 'replacefields terminated by '',''' 'trailing nullcols'from all_tables where owner = upper('devcpos') and table_name = upper('itemmaster')
/
select decode(a.column_id, '1', '(') a.column_name (case when a.column_id < max(b.column_id) then ',' else ')' END)from all_tab_columns a, all_tab_columns bwhere a.owner = upper('&1') and a.table_name = upper('&2') andb.owner = upper('&1') and b.table_name = upper('&2')group by a.column_name, a.column_idorder by a.column_id
/
spool off
set verify on
set heading on
set embedded off
set recsep wrapped
set pages 64
set lines 80
set feedback 6
undefine 1
undefine 2
EXPDP Invoking from PL/SQL
One can invoke datapump from PL/SQL -- this might be handy for scheduling a daily or weekly export with DBMS_SCHEDULER.
DECLARE hand NUMBER;
BEGIN hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL', job_name 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.log', directory => 'JAGAT', filetype => 3);
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.dmp', directory => 'DMPDIR', filetype => 1);
END;
/
DECLARE hand NUMBER;
BEGIN hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL', job_name 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.log', directory => 'JAGAT', filetype => 3);
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.dmp', directory => 'DMPDIR', filetype => 1);
END;
/
EXPDP/IMPDP
CREATE DIRECTORY DIR AS '/opt/oracle';
GRANT read, write ON DIRECTORY dmpdir TO scott;
C:\EXPDP DEVCPOS/DEVCPOS DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP SYSTEM/SYSTEM DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP QACPOS/QACPOS DIRECTORY=DIR
C:\expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
C:\IMPDP TEST/TEST@ORCL DIRECTORY=TSTDIR DUMPFILE=BACKUPSCOTT.DMP SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:TEST
GRANT read, write ON DIRECTORY dmpdir TO scott;
C:\EXPDP DEVCPOS/DEVCPOS DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP SYSTEM/SYSTEM DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP QACPOS/QACPOS DIRECTORY=DIR
C:\expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
C:\IMPDP TEST/TEST@ORCL DIRECTORY=TSTDIR DUMPFILE=BACKUPSCOTT.DMP SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:TEST
oradim
1: oradim -new -sid PMHIST -startmode auto -PFILE D:\oracle\product\10.2.0\admin\PMHIST\pfile\initPMHIST.ora
2: oradim -new -sid PMOBILE -startmode auto -PFILE D:\oracle\product\10.2.0\admin\PMOBILE\pfile\initPMOBILE.ora
3: oradim -new -sid JAGAT -intpwd password -startmode a -pfile C:\oraclexe\app\oracle\admin\Jagat\pfile\initJAGATemp.ora
2: oradim -new -sid PMOBILE -startmode auto -PFILE D:\oracle\product\10.2.0\admin\PMOBILE\pfile\initPMOBILE.ora
3: oradim -new -sid JAGAT -intpwd password -startmode a -pfile C:\oraclexe\app\oracle\admin\Jagat\pfile\initJAGATemp.ora
RMAN: Cloning
Let's assume a source database named SRCDB and the target database, named GEMINI. A unix like environment is assumed, but it can be implemented on windows as well, just beware of the particular Oracle implementation on a windows platform (orapwd file name, service creation, path format)
1. Create a password file for the Cloned (GEMINI) instance:
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwGEMINI password=password entries=10
2. Configure tnsnames.ora and listner.ora
Properly identify the database at the tnsnames.ora and have the instance manually registered against the listener.ora files, both files located at the $ORACLE_HOME/network/admin directory.
2.a Manually register the database against the listener
(listener.ora)(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = GEMINI))
2.b Added the target GEMINI to the tnsnames.
oraGEMINI = (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521)) )
(CONNECT_DATA =
(ORACLE_SID = GEMINI) ) )
2.c Reload the listener
lsnrctl reload
3. Create a new init.ora for the cloned database.
Next create an init.ora file for the cloned database. In case the same paths cannot be used on the target host, either because it is the same source host or because those paths are not reproducible on the target, then DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT may be required to be defined
DB_NAME=GEMINICONTROL_FILES=(/u02/oradata/GEMINI/control01.ctl, /u02/oradata/GEMINI/control02.ctl, /u02/oradata/GEMINI/control03.ctl)# Convert file names to allow for different directory structure.DB_FILE_NAME_CONVERT=(/u02/oradata/SRCDB/,/u02/oradata/GEMINI/)LOG_FILE_NAME_CONVERT=(/u01/oradata/SRCDB/,/u01/oradata/GEMINI/)# block_size and compatible parameters must match those of the source databaseDB_BLOCK_SIZE=8192COMPATIBLE=9.2.0.0.0
4. Connect to the cloned instance
ORACLE_SID=GEMINI;
export ORACLE_SIDsqlplus /nologconn / as sysdba
5. Create an SPFILE based on the init.ora
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/GEMINI/pfile/init.ora';
6. Start the database in NOMOUNT mode:
STARTUP FORCE NOMOUNT;
7. Connect to the TARGET, CATALOG and AUXILIARY databases.
By means of the rman three connections are open, one for the Source Database (SOURCEDB), another for the Catalog database (RCAT), and one more for the cloned database (GEMINI)ORACLE_SID=GEMINI; export ORACLE_SID
rman TARGET sys/password@SRCDB CATALOG rman/rman@RCAT AUXILIARY /
8. Complete or Incomplete clone (recover)
From the rman the database using one of the following commands:8.a Clone the database by means of a complete recover.
DUPLICATE TARGET DATABASE TO GEMINI;
8.b Clone the database up to a defined point in time in the past by means of an incomplete
recoverDUPLICATE TARGET DATABASE TO GEMINI UNTIL TIME 'SYSDATE-2';
9. Process finished.
Once the process is finished, the newly created GEMINI database is ready to be used as an independent new cloned database.
1. Create a password file for the Cloned (GEMINI) instance:
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwGEMINI password=password entries=10
2. Configure tnsnames.ora and listner.ora
Properly identify the database at the tnsnames.ora and have the instance manually registered against the listener.ora files, both files located at the $ORACLE_HOME/network/admin directory.
2.a Manually register the database against the listener
(listener.ora)(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = GEMINI))
2.b Added the target GEMINI to the tnsnames.
oraGEMINI = (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521)) )
(CONNECT_DATA =
(ORACLE_SID = GEMINI) ) )
2.c Reload the listener
lsnrctl reload
3. Create a new init.ora for the cloned database.
Next create an init.ora file for the cloned database. In case the same paths cannot be used on the target host, either because it is the same source host or because those paths are not reproducible on the target, then DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT may be required to be defined
DB_NAME=GEMINICONTROL_FILES=(/u02/oradata/GEMINI/control01.ctl, /u02/oradata/GEMINI/control02.ctl, /u02/oradata/GEMINI/control03.ctl)# Convert file names to allow for different directory structure.DB_FILE_NAME_CONVERT=(/u02/oradata/SRCDB/,/u02/oradata/GEMINI/)LOG_FILE_NAME_CONVERT=(/u01/oradata/SRCDB/,/u01/oradata/GEMINI/)# block_size and compatible parameters must match those of the source databaseDB_BLOCK_SIZE=8192COMPATIBLE=9.2.0.0.0
4. Connect to the cloned instance
ORACLE_SID=GEMINI;
export ORACLE_SIDsqlplus /nologconn / as sysdba
5. Create an SPFILE based on the init.ora
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/GEMINI/pfile/init.ora';
6. Start the database in NOMOUNT mode:
STARTUP FORCE NOMOUNT;
7. Connect to the TARGET, CATALOG and AUXILIARY databases.
By means of the rman three connections are open, one for the Source Database (SOURCEDB), another for the Catalog database (RCAT), and one more for the cloned database (GEMINI)ORACLE_SID=GEMINI; export ORACLE_SID
rman TARGET sys/password@SRCDB CATALOG rman/rman@RCAT AUXILIARY /
8. Complete or Incomplete clone (recover)
From the rman the database using one of the following commands:8.a Clone the database by means of a complete recover.
DUPLICATE TARGET DATABASE TO GEMINI;
8.b Clone the database up to a defined point in time in the past by means of an incomplete
recoverDUPLICATE TARGET DATABASE TO GEMINI UNTIL TIME 'SYSDATE-2';
9. Process finished.
Once the process is finished, the newly created GEMINI database is ready to be used as an independent new cloned database.
Steps for creating External Tables
1: Create Directory
CREATE DIRECTORY JAGAT_DIR AS 'C:\BALA';
GRANT READ ON DIRECTORY JAGAT_DIR TO PUBLIC;
GRANT READ ON DIRECTORY JAGAT_DIR TO PUBLIC;
2: Create a text file.--in demo.txt--- in JAGAT_DIR
7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
Create Table ScriptCREATE TABLE ext_tab (empno CHAR(4),ename CHAR(20),
job CHAR(20),deptno CHAR(2))
ORGANIZATION EXTERNAL
( TYPE oracle_loader DEFAULT DIRECTORY JAGAT_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'bad_%a_%p.bad' LOGFILE 'log_%a_%p.log' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS (empno, ename, job, deptno)) LOCATION ('demo.txt') )PARALLELREJECT LIMIT 0NOMONITORING;
CREATE DIRECTORY JAGAT_DIR AS 'C:\BALA';
GRANT READ ON DIRECTORY JAGAT_DIR TO PUBLIC;
GRANT READ ON DIRECTORY JAGAT_DIR TO PUBLIC;
2: Create a text file.--in demo.txt--- in JAGAT_DIR
7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
Create Table ScriptCREATE TABLE ext_tab (empno CHAR(4),ename CHAR(20),
job CHAR(20),deptno CHAR(2))
ORGANIZATION EXTERNAL
( TYPE oracle_loader DEFAULT DIRECTORY JAGAT_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'bad_%a_%p.bad' LOGFILE 'log_%a_%p.log' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS (empno, ename, job, deptno)) LOCATION ('demo.txt') )PARALLELREJECT LIMIT 0NOMONITORING;
Trigger for grant
Below trigger will not allow to Public level grants
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT ON DATABASE
DECLARE g_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
n := ora_grantee(g_list);
FOR i IN 1..n LOOP
IF g_list(i) = 'PUBLIC' THEN
RAISE_APPLICATION_ERROR(-20997,'Public Grants Not Allowed');
END IF;
END LOOP;
END;
/
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT ON DATABASE
DECLARE g_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
n := ora_grantee(g_list);
FOR i IN 1..n LOOP
IF g_list(i) = 'PUBLIC' THEN
RAISE_APPLICATION_ERROR(-20997,'Public Grants Not Allowed');
END IF;
END LOOP;
END;
/
Creating Inter Schema Foreign Key
1: Needs REFERENCE grant on Particular Tables
GRANT REFERENCES (employee_id),UPDATE (employee_id, salary, commission_pct)ON hr.employeesTO JAGAT;
2: Create table with foreign key
CREATE TABLE dependent(dependno NUMBER,dependname VARCHAR2(10),employee NUMBERCONSTRAINT in_emp REFERENCES SCOTT.employees(employee_id) );
GRANT REFERENCES (employee_id),UPDATE (employee_id, salary, commission_pct)ON hr.employeesTO JAGAT;
2: Create table with foreign key
CREATE TABLE dependent(dependno NUMBER,dependname VARCHAR2(10),employee NUMBERCONSTRAINT in_emp REFERENCES SCOTT.employees(employee_id) );
Extracting Metadata
For Metadata of all tables from particular schema-
select dbms_metadatA.get_ddl('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
For Metadata of single table from particular schema-
select dbms_metadata.get_ddl('TABLE','XYZ','JAGAT') from dual; -> where XYZ is table name and JAGAT is user.
For Metadata of all indexes from particular schema-
select dbms_metadatA.get_ddl('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
For Metadata of single index from particular schema-
select dbms_metadata.get_ddl('INDEX','XYZ','JAGAT') from dual; -> where XYZ is index name and JAGAT is user.
select dbms_metadatA.get_ddl('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
For Metadata of single table from particular schema-
select dbms_metadata.get_ddl('TABLE','XYZ','JAGAT') from dual; -> where XYZ is table name and JAGAT is user.
For Metadata of all indexes from particular schema-
select dbms_metadatA.get_ddl('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
For Metadata of single index from particular schema-
select dbms_metadata.get_ddl('INDEX','XYZ','JAGAT') from dual; -> where XYZ is index name and JAGAT is user.
Getting User Script:
select 'create user ' U.username ' identified ' DECODE(password, NULL, 'EXTERNALLY', ' by values ' '''' password '''' ) chr(10) 'default tablespace ' default_tablespace chr(10) 'temporary tablespace ' temporary_Tablespace chr(10) ' profile ' profile chr(10) 'quota ' decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ' on ' default_tablespace decode (account_status,'LOCKED', ' account lock', 'EXPIRED', ' password expire', 'EXPIRED \& LOCKED', ' account lock password expire', null)';'from dba_users U, dba_ts_quotas Q-- Comment this clause out to include system & default userswhere U.username not in ('SYS','SYSTEM','SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS','HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM','QS_CB','QS_CS','PERFSTAT')and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
Getting Tablespace Script:
select 'create tablespace ' df.tablespace_name chr(10) ' datafile ''' df.file_name ''' size ' df.bytes decode(autoextensible,'N',null, chr(10) ' autoextend on maxsize ' maxbytes) chr(10) 'default storage ( initial ' initial_extent decode (next_extent, null, null, ' next ' next_extent ) ' minextents ' min_extents ' maxextents ' decode(max_extents,'2147483645','unlimited',max_extents) ') ;' from dba_data_files df, dba_tablespaces t where df.tablespace_name=t.tablespace_name
File copy from PL/SQL
BEGIN
dbms_file_transfer.copy_file(source_directory_object => 'DIR',source_file_name => 'SAMPLE.TXT',
destination_directory_object => 'BALA',
destination_file_name => 'JAGAT.TXT');
END;
dbms_file_transfer.copy_file(source_directory_object => 'DIR',source_file_name => 'SAMPLE.TXT',
destination_directory_object => 'BALA',
destination_file_name => 'JAGAT.TXT');
END;
Manualy Creating Physical Standby Database
Manualy Creating Physical Standby Database
On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd\database
1
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replacewith the actual Oracle home path, and xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
2
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
2) Edit this pfile to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
LOG_FLE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’
(Note: For DB_FILE_NAME_CONVERT -Specify the location of the standby DB datafiles followed by the primary location;
For LOG_FLE_NAME_CONVERT - Specify the location of the standby DB online redo log files followed by the primary location.)
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
3
- On Unix:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).
III. On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On Unix, create the directory accordingly.
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On Unix, create the directories accordingly.
4) Copy the online logs over.
2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
4
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
(Note: Not all the parameter entries are listed here.
For DB_FILE_NAME_CONVERT - Specify the location of the primary DB datafiles followed by the standby location;
For LOG_FILE_NAME_CONVERT - Specify the location of the primary DB online redo log files followed by the standby location)
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to\database folder, and on UNIX copy it to /dbs directory. And then rename the password file.
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
1) On Primarysystem: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
5
$lsnrctl stop
$lsnrctl start
2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).
- On Unix:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).
12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
6
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
2) On Primary, force a logfile switch:
SQL>alter system switch logfile;
3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
IV. Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target/@STAN;
Rman>backup archivelog all delete input;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
Refer to section II.2, step 2 to update/recreate password file for the Standby database.
Reference:Oracle Data Guard Concepts and Administration 10g Release 2 (10.2)
On the Primary Database Side:
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd
1
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
2
- On Windows:
SQL>create pfile=’
(Note- specify your Oracle home path to replace ‘
- On UNIX:
SQL>create pfile=’
(Note- specify your Oracle home path to replace ‘
2) Edit this pfile to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
LOG_FLE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’
(Note: For DB_FILE_NAME_CONVERT -Specify the location of the standby DB datafiles followed by the primary location;
For LOG_FLE_NAME_CONVERT - Specify the location of the standby DB online redo log files followed by the primary location.)
6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’
SQL>create spfile from pfile=’
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘
3
- On Unix:
SQL> shutdown immediate;
SQL> startup nomount pfile=’
SQL>create spfile from pfile=’
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘
III. On the Standby Database Site:
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On Unix, create the directory accordingly.
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On Unix, create the directories accordingly.
4) Copy the online logs over.
2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)
*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
4
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
(Note: Not all the parameter entries are listed here.
For DB_FILE_NAME_CONVERT - Specify the location of the primary DB datafiles followed by the standby location;
For LOG_FILE_NAME_CONVERT - Specify the location of the primary DB online redo log files followed by the standby location)
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;
6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to
7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual
8. Configure listeners for the primary and standby databases.
1) On Primarysystem: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
5
$lsnrctl stop
$lsnrctl start
2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start
9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN
10. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’
SQL>create spfile from pfile=’
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘
- On Unix:
SQL>startup nomount pfile=’
SQL>create spfile from pfile=’
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘
12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Verify the standby database is performing properly:
6
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
2) On Primary, force a logfile switch:
SQL>alter system switch logfile;
3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
15. To create multiple standby databases, repeat this procedure.
IV. Maintenance:
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target
Rman>backup archivelog all delete input;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
Refer to section II.2, step 2 to update/recreate password file for the Standby database.
Reference:Oracle Data Guard Concepts and Administration 10g Release 2 (10.2)
Subscribe to:
Posts (Atom)
