Oracle DBA Part 3

Resolve archive gap between PRIMARY and STANDBY

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database.
When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the logfiles in standby to resolve the gap.
Please use below query to find out archive gap on Standby:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

 

Thread  Last Sequence Received   Last Sequence Applied     Difference

———-  ———————-                          ———————        ———-  

1                   9545                                             9535                   10

 

SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;

 MAX(SEQUENCE#)

————–

9535

COPY ARCHIVELOG FILE TO STANDBY FROM PRIMARY:
$ scp log_file_n.arc oracle@standby:/log_location/log_file_n.arc
REGISTER LOGFILE AT STANDBY:
SQL> alter database register logfile ‘/log_location/log_file_n.arc’; logfile registered
Repeat the same process for all the log files which are missing at standby.
If archive gap difference is huge(say more than 500 logs) , Please check this http://oracledbamasters.wordpress.com/2013/10/22/resolve-huge-archive-gap-primary-standby/

By Oracle-DBA-masters | Also posted in DataGuard | Tagged archive gap between PRIMARY and STANDBY, Resolve archive gap | Comments (1)

SIMPLE DATABASE CREATION

CREATION OF DATABASE IN COMMAND LINE:
1) Set Environment file:
#su – oracle
$pwd
export ORACLE_SID=PROD
export ORACLE_HOME=/u01/app/oracle/PRODuct/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
2) Create Parameter file:
$cd $ORACLE_HOME/dbs
$cpinit.ora initPROD.ora
vi $ORACLE_HOME/dbs/initPROD.ora
db_name = PROD
control_files=/u01/PROD/control1.ctl,/u01/PROD/control2.ctl
undo_management = AUTO
undo_tablespace = UNDOTBS1
diagnostic_dest=/u01/PROD/
:wq!
3) Start the instance:
Edit the file $ORACLE_HOME/sqlplus/admin/glogin.sql and add in line as below.
setsqlprompt “_USER’@'_CONNECT_IDENTIFIER> “
sqlplus / as sysdba
startup nomount
Note : In the nomount state oracle reads all initialization parameter values from the pfile(initPROD.ora)
4) Create the database:
vi create.sql
create database test
logfile group 1 (‘/u01/PROD/redo1.log’) size 100M,
group 2 (‘/u01/PROD/redo2.log’) size 100M,
group 3 (‘/u01/PROD/redo3.log’) size 100M
datafile ‘/u01/PROD/system.dbf’ size 500M autoextend on next 10M maxsize unlimited extent management local
sysauxdatafile ‘/u01/PROD/sysaux.dbf’ size 100M autoextend on next 10M maxsize unlimited
undotablespace undotbs1 datafile ‘/u01/PROD/undotbs1.dbf’ size 100M
default temporary tablespace temp tempfile ‘/u01/PROD/temp01.dbf’ size 100M;
sqlplus / as sysdba
@create.sql
5) Run catalog and catproc:
After Successful creation of the database we need to execute catalog.sql and catproc.sql.These two scripts updates the data dictionary tables and views. And pupbld.sql must be executed from Systemuser.This script updates users product information.
sqlplus / as sysdba
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
connect system/manager
@?/sqlplus/admin/pupbld.sql
####### Use Below dictionary view to see database information #####
sys@PROD>select * from v$version;
sys@PROD>select * from v$tablespace;
sys@PROD>select name from v$datafile;
sys@PROD>select name from v$tempfile;
sys@PROD>select name from v$controlfile;
sys@PROD>set wrap off
sys@PROD>select * from v$logfile;