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

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;

Change DB ID in oracle

There are two ways to change the DB ID.

The DB ID is the unique identifier of a DB. This is a randomly generated number that is assigned to the database at the time of creation. typically this would be a 10 digit number.

If there are scenarios to change the DB ID then please follow any of the two ways discussed below.

Case 1:
-------

Usage of nid utility which is in $ORACLE_HOME/bin location
-----------------------------------------------------------
DB Name - TEST

Login to sqlplus as sysdba


$> sqlplus / as sysdba

SQL>select dbid form v$database; -- to check the current DB ID of the database.

SQL>shutdown immediate

start the database in mount state so that the control file is read and all the other datafiles are read.

SQL> startup mount;

SQL> exit

on the OS command prompt execute the following command.

$>nid target=TEST

It will prompt for password. password is the sys account's password.

password - sys password

Once the password is accepted it will ask whether to change the DB ID or not. Press 'Y'

change database ID of database DBNAMR (Y/[N]) => Y

proceeding with operation
..........
.........
.
.

DBNEWID- completed successfully.



Here you will see that the DB ID is modified in control files and changed changed in datafiles.

Also, all the previous backups and archivelog backups will be unusable.


connect to sqlplus

sqlplus / as sysdba

open the database using reset logs as the DBID is changed the DB must be opened using resetlogs

SQL> alter database open resetlogs;

SQL> select dbid form v$database; -- now a new ID is assigned to the database.

SQL> shutdown immediate;

SQL> startup;

The DB ID is changed.

Take a full backup of the database and archivelogs if any.
-------------------------
Case 2:
------------------------

Using dbms_backup_restore pacakge.

Login to sqlplus

$> sqlplus / as sysdba

SQL> select dbid from v$database; -- To note the current DB ID

Take the control file to trace backup

sql> alrer database backup controlfile to trace as '/home/oracle/trace.sql' reuse; -- reuse will be used to replace the existing trace files if any on the location.

SQL> shutdown immediate;

SQL> exit

open the trace file and remove all the commented lines and -------------------------------------------------------

$> remove reuse instead put set also remove noresetlogs put resetlogs in create controlfile statement



sqlplus / as sysdba

SQL> startup mount;

SQL> select name,dbid form v$database;

SQL> execute dbms_backup_restore.zeroDbid(0);

PL/SQL procedure successfully completed.

SQL> select name,dbid form v$database; - output comes as is becuase its still in dynamic performance views

SQL> shutdown immediate;

SQL> exit

$> delete all the control files

sqlplus / as sysdba

SQL> @/home/oracle/trace.sql

controlfile created,

SQL> alter database open resetlogs;

database altered

SQL> select name,dbid from v$database;

DB is will be changed.

Take a full backup of the database and archivelogs if any.