Friday, September 11, 2015

Change password using BRCONNECT

Change password - BRCONNECT


To change the schema password using BRCONNECT an SAP utility to administer the oracle database,

Please execute the following command.

brconnect -u / -c -f chpass -o system -p manager

-u is user to connect to system (/ is root as sysdba)

-c is used to force the action

-f is function, chpass is change password

-o is the schema user

-p is the new password that needs to be set.

Cold backup

How to take a cold backup


$> cd /oracle/backup/SID

$> mkdir -p /oracle/backup/SID/log

create the pfile from the current spfile.

$> sqlplus / as sysdba

SQL> alter system checkpoint;
SQL> shutdown immediate;
SQL> startup mount;
SQL> create pfile='/oracle/backup/SID/pfile`date +%d%m%Y`.ora' from spfile;

Create the backup script

Database needs to be put in mount state.

While backing up the database backup the current control file as well to disk seperately.

$> vi RMAN_cold_backup.sh

 export ORACLE_HOME=/oracle/product/11.2.0
 export ORACLE_SID=SID
 export PATH=$ORACLE_HOME/bin:$PATH
 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
 rman target / log=/oracle/backup/SID/log/SID`date +%d%m%Y`.log <  RUN {
 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
 ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
 ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/oracle/backup/SID/SID_%U’;
 BACKUP AS COMPRESSED BACKUPSET DATABASE;
 BACKUP CURRENT CONTROLFILE FORMAT ‘/oracle/backup/SID/cntrl_%s_%p_%t’;
 RELEASE CHANNEL disk1;
 RELEASE CHANNEL disk2;
 RELEASE CHANNEL disk3;
 }


Change the permission of the file RMAN_cold_backup.sh to have execute permissions.

navigate to the directory which have the script

cd /oracle/backup/SID

./RMAN_cold_backup.sh

once the script completes sucessfully, you can open the database.

$> sqlplus / as sysdba

SQL> alter database open;

Database Opened.

SQL> exit


Drop oracle database

How to Drop Oracle Database


If you want to drop your database it is wise to take an offline backup before you drop your database. if incase it is required to restore the database back we can use this backup.

Cold Backup

Check if database is up and running

$> ps -ef |  grep pmon

if this returns a process with the database sid then the DB is running.

login to sqlplus as sysdba

$> sqlplus / as sysdba

shutdown the database to start it in restrict mode

SQL> shut immediate;

put the database in mount restrict mode

SQL> startup restrict mount;

ORACLE instance started.

Database mounted.

issues the following command to drop the database.

SQL> drop database;

Database dropped.

SQL> exit

Tuesday, September 8, 2015

Standby Roll forward

When there is a big archivelog gap that has to be applied on the standby database, then it is wise to have to go ahead with a roll forward of the standby.

To get the standby sync with the primary please follow the steps below.


On standby database


SQL> alter database recover managed standby database cancel;

SQL> select current_scn from v$database;

-- If there are no READ ONLY Tablespaces/datafiles in database use the below query to find the current SCN number.
SQL> select min(fhscn) from x$kcvfh;

-- If there are any READ ONLY tablespaced/datafiles in database use the below query to find the current SCN number
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
 where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;

If the output from v$database view and x$kcvfh are different please proceed by taking the SCN number from X$KCVFH view.

Now open a new session of the primary database in a new window.


On Primary database


Go to RMAN to take an incremental backup of the database from the SCN number noted in the standby database.


rman target /

RMAN> run {
2> allocate channel C1 device type disk;
3> backup as compressed backupset incremental from SCN 1966714724 database format '/oracle/backup/tostdby_%U' filesperset=10 tag 'FOR STANDBY';
4> release channel C1;
5> }


Take the control file backup for standby database.

rman> backup current controlfile for standby format '/oracle/backup/cntrltostdby_%U' tag 'FOR STANDBY';

exit from RMAN

navigate to the folder in which the backups are taken

$> cd /oracle/backup

copy the files over to the standby database server using the scp command (or by any command that serves the purpose)

$> scp to* oracle@hostname:/oracle/backup
$> scp cntrl* oralce@hotname:/oracle/backup

once the files are copied over to the standby database server. close the primary session.

On standby database server


Remove the control files

rm /oracle/oradata/cntrl/cntrl*
rm /oracle/cntrl1/cntrl*
rm /oracle/cntrl2/cntrl*

login to sql prompt to shutdown the database and start in nomount to restore the control file in respective locations from the backup taken in primary database server.

sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup nomount

SQL> exit

$> rman target /

RMAN> restore standby controlfile from '/oracle/backup/cntrlforstandby_rdqffbhg_1_1';

RMAN> sql 'alter database mount standby database';
RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';

Catalog the backup pieces to the RMAN

RMAN> catalog backuppiece '/oracle/backup/forstandby_qnqff9e6_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_qoqff9tb_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_rbqffalj_1_1';
RMAN> catalog backuppiece '/oracle/backup/forstandby_rcqffb96_1_1';

RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';

RMAN> run{
2 allocate channel C1 device type disk;
3 recover database noredo;
4 release channel C1;
5 }


RMAN> sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION';

RMAN> exit

Once every thing is done successfully, please login to the DB and see if the current archivelogs are being applied.

$> sqlplus / as sysdba



SQL> select max(sequence#) from v$archived_log;
SQL> set lines 200;
SQL> select dest_id,status,error from v$archive_dest;
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from  v$managed_standby;