Creating a Physical standby Database

A physical Standy is a database which acts like a backup server for the Production. Incase, if the Production Server gets crashed then we can open(Failover or Swithchover) the standby database and then start using it as a Production Database.

On the Source Machine:

1) Create a directory named standby.(to copy the parameter,password,control files and RMAN backup)


[oracle@ccc_dba_sys03 ~]$ cd $ORACLE_HOME/dbs
[oracle@ccc_dba_sys03 dbs]$ cp init<SID>.ora /standby/ Example : cp initabc.ora /standby/
[oracle@ccc_dba_sys03 dbs]$ cp orapw<SID> /standby/ cp orapwabc /standby/


2) Take a complete RMAN backup of the database and place it in standby directory

[oracle@ccc_dba_sys03 ~]$ rman target /
RMAN> backup as compressed backupset database format'/standby/%U' include current controlfile;

3) After completing RMAN backup we have to create a standby control file.


[oracle@ccc_dba_sys03 ~]sqlplus / as sysdba
SQL> alter database create standby controlfile as '/standby/standby.ctl';


On the Target System :


1) Install Linux on the system with proper mount points


2) Configure the /etc/yum.repos.d/abc.repo file and Install all the required rpm's.


(http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/install_overview.htm)

For 32-bit Linux


[abc.repo]
name=abc Standby Server Repository
baseurl=ftp://ip address/pub/ (For the ip, please consult your sys-admin)
enabled=1
gpgcheck=0


For 64-bit Linux


[abc.repo]
name=abc Standby Server Repository
baseurl=ftp://ip address/pub/oel5.5_64/ (For the ip, please consult your sys-admin)
enabled=1
gpgcheck=0


The following packages (or later versions) must be installed:


On Linux x86:

binutils-2.17.50.0.6-2.el5
compat-libstdc++-33-3.2.3-61
elfutils-libelf-0.125-3.el5
elfutils-libelf-devel-0.125
gcc-4.1.1-52
gcc-c++-4.1.1-52
glibc-2.5-12
glibc-common-2.5-12
glibc-devel-2.5-12
glibc-headers-2.5-12
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.1-52
libstdc++-4.1.1
libstdc++-devel-4.1.1-52.e15
make-3.81-1.1
numactl-devel-0.9.8.i386
sysstat-7.0.0

On Linux x86-64:


binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.1
gcc-c++-4.1.1
glibc-2.5-12
glibc-2.5-12 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5-12 (32 bit)
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libgcc-4.1.1
libgcc-4.1.1 (32 bit)
libstdc++-4.1.1
libstdc++-4.1.1 (32 bit)
libstdc++-devel 4.1.1
make-3.81
numactl-devel-0.9.8.x86_64
sysstat-7.0.0


3) Create the proper Group(dba) and Users(oracle) and give permissions to the user.


[root@ccc_dba_sys03 ~]# adduser oracle
[root@ccc_dba_sys03 ~]# passwd oracle
[root@ccc_dba_sys03 ~]# groupadd dba
[root@ccc_dba_sys03 ~]# usermod -g dba oracle
[root@ccc_dba_sys03 ~]# mkdir /oracle
[root@ccc_dba_sys03 ~]# chown -R oracle:dba /oracle
[root@ccc_dba_sys03 ~]# chmod -R 775 /oracle
[root@ccc_dba_sys03 ~]#


4) Login as the user(Oracle) created and Create the environment file(Example : abcstby.env)


[oracle@ccc_dba_sys03 ~]# vi abcstby.env


export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=abcstby
:wq (save and quit)


5) Run the enironment file(abcstby.env)


[oracle@ccc_dba_sys03 ~]# . abcstby.env


5) Install the Oracle software(No database) only by giving the proper paths for the installation of the s/w.


6) Now copy/move the standby directory from the source machine to target machine.


7) Now copy the parameter and password file to $ORACLE_HOME/dbs location and rename those file with the new oracle_sid value.
Ex: Standby] mv initabc.ora $ORACLE_HOME/dbs/initabcstby.ora
Standby] mv orapwabc $ORACLE_HOME/dbs orapwabcstby


8) Now edit the parameter file and change the paths/locations according to the standby server and also add one parameter called db_unique_name and specify the new oracle_sid value to this parameter. We will not change the db_name value.


Ex: db_unique_name='abcstby'


9) Create required directories as per parameter file and also copy the control file from standby directory to the specified location in parameter file.


10) Now connect to SQL prompt,
[oracle@ccc_dba_sys03 ~] sqlplus / as sysdba
SQL>


11) Startup the database in nomount stage and then alter the database to mount stage.


SQL>startup nomount


ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1336820 bytes
Variable Size 352324108 bytes
Database Buffers 1241513984 bytes
Redo Buffers 11833344 bytes


SQL>alter database mount;


Database altered.


SQL>


12) Now check few options using the following queries,


SQL> select open_mode from v$database;


open_mode
MOUNTED


SQL> select database_role from v$database;


database_role
PHYSICAL STANDBY


SQL>


13) Now check the location ie., on which path previously the database is storing its datafile and redo log file.


13-a) SQL> select name from v$datafile; Ex: /oradata/abc/datafiles/system.dbf


13-b) SQL> select member from v$logfile; Ex: /oradata/abc/redologs/redo01.log


14) Now shutdown the database.


SQL> shut immediate


Database closed.
Database dismounted.
ORACLE instance shut down.


15) Now open the parameter file to change the locations of datafiles and redo log files. Add two parameters in parameter file.

db_file_name_convert='Primary database datafiles path which is show step 13-a','standby database datafile path ie where we want to store the datafiles'


log_file_name_convert='Primary database log files path which is show step 13-b','standby database redo log files path ie where we want to store the redo log files'


Ex: db_file_name_convert='/oradata/abc/datafiles/','/data/abcstby/datafiles/'
log_file_name_convert='/oradata/abc/redologs/','/data/abcstby/redologs/'


Now save and quit the file.

16) Now start the database in mount stage and check for the new locations give in parameter file.


SQL> startup mount
    

ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1336820 bytes
Variable Size 352324108 bytes
Database Buffers 1241513984 bytes
Redo Buffers 11833344 bytes
Database mounted.


SQL> select name from v$datafile; Ex: /data/abcstby/datafiles/system.dbf


SQL> select member from v$logfile; Ex: /data/abcstby/redologs/redo01.log


Now both the above queries should show the standby db path instead of primary db path.

17) Now exit from SQL prompt(do not shutdown the database) and connect to RMAN prompt.


SQL> exit


[oracle@ccc_dba_sys03 ~]rman target /


RMAN>


18) Now we have to catalog all the RMAN backup pieces of primary database which we have copied from primary db to standby severs standby directory.


RMAN> catalog backuppiece 'path were we have stored that backup pieces temporarily in standby server';


Ex : RMAN> catalog backuppiece '/data/standby/UE45fse2/';


To catalog a complete folder, then use the following command


RMAN> catalog start with '/path of the folder where the rman pieces are loacted/';


Ex : RMAN> catalog start with '/data/standby/';


19) Now list all the backup sets available,


RMAN> list backupset summary;


20) Now crosscheck the backup sets to list out the expired backups.
    

RMAN> crosscheck backupset;


21) Now delete the expired backup sets.


RMAN> delete expired backup;


22) Now we have to restore the database.


RMAN>restore database;


23) After restoring the database, come out of RMAN prompt and connect to SQL prompt and take the database into recover mode


RMAN> exit


[oracle@ccc_dba_sys03 ~] sqlplus / as sysdba


SQL> alter database recover managed standby database disconnect from session;


Database altered.


SQL>


24) Now we have to check the log sequence number.


SQL> select sequence#,process,status,block# from v$managed_standby;


In the output it will show wait_for_log and we have to copy the archives from that sequence number to the present running log sequence number in primary db to standby server.


25) Copy the pending archives from primary server to standby server and paste it in the archive directory which we have specified in parameter file.


26) Now we have to apply the new archives using RMAN,


RMAN> catalog start with 'path where we have copied the new archives';


And give yes to start cataloging


27) After applying the pending archives we have to again check the new log sequence number.


SQL> select sequence#,process,status,block# from v$managed_standby;


Now it will show apply_log and after applying all logs it will show wait_for_log.


And now we have to check the applied log sequence number with the primary db log sequence number. And both the number must be same.


28) Everyday we have to copy the new archives from the primary server to standby sever by checking the log sequence number in standby server and primary server.

Manual Database Creation with screen shots

After the installation of Oracle Software on the system, We can create the database either manually or using DBCA.
 Here, we are seeing how to create a database manually.


Setting Parameters :

Login as root user

  

   #groupadd -g 500 dba

   #useradd -u 501 -g 500 -s /bin/bash -d /home oracle

   #passwd oracle

   #chmod -R 777 /home

   #chown  -R oracle:dba /home

   #vi /etc/sysctl.conf

# ADD THE FOLLOWING LINES

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

# semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=262144

net.core.rmem_max=262144

net.core.wmem_default=262144

net.core.wmem_max=262144

Run the following command to change the current kernel parameters:

/sbin/sysctl -p

#init 6 (restart the system)

Login as oracle user

  

$vi .bash_profile

# Add the following lines

export ORACLE_HOME=/home/oracle/product/10.2.0/db_1

export ORACLE_SID=demo

$. .bash_profile

$sqlplus '/as sysdba'

connected to an idle instance

SQL > exit

$cd $ORACLE_HOME/dbs

Copy the init.ora file to init$ORACLE_SID.ora and then open the init$ORACLE_SID.ora and modify the following parameters

            db_name=demo

            shared_pool_size=90m

            control_files=/home/demo/control1.ctl,/home/demo/control2.ctl

dbs~] $cp init.ora init$ORACLE_SID.ora

dbs~] $ vi init$ORACLE_SID.ora

Before the modification of the init file, it looks like 

Make the necessary changes, 

Add the new paths and new name for the database ex: demo

User_dump_dest=/home/prd/udump

Core_dump_dest=/home/prd/cdump

Background_dump_dest=/home

After changing the parameter in the parameter file ,just save and exit.

Note: we can create  a directory at oracle home

i.e $cd

$mkdir demo

$cd demo

$mkdir udump bdump cdump

connect as sysdba and start the database in nomount mode

$ . .bash_profile

$sqlplus / as sysdba

SQL> startup nomount

Database is created in nomount stage.

Here sga created and background processes are started.

To know the base tables

SQL>select * from user$;

To know the users

SQL>select name from user$;

When database instance is started issue the create database script at SQL prompt.

Execute the following scripts.

            SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

                             Or

            SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

                           Or 

            SQL>@/home/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

                           and            

            SQL>@?/rdbms/admin/catproc.sql

            

            Connect system/manger

            SQL> @?/sqlplus/admin/pupbld.sql

Now you have created a manual database and is ready to use :)

TABLESPACES AND DATAFILES

 * Tablespace consists of one or more datafiles 

*  Data files belong to only one database.

Tablespace Types 

  The Primary types of tablespaces in an Oracle database are permanent, undo, and temporary. 

Permanent: 

The SYSTEM and SYSAUX tablespaces are two examples of permanent tablespaces. 

Any segments that need to be retained by a user or an application beyond the boundaries of a session or transaction should be stored in a permanent tablespace.

SYSTEM: 

The SYSTEM tablespace is used by the oracle database server to manage the database. It contains the administrative information about the database. These are all contained in the SYS schema, and can be accessed only by the user SYS, or other administrative users with the required privilege. 

SYSAUX: 

This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tables in prior releases of Oracle, now use the SYSAUX tablespace. Every Oracle Database 10g or higher-level database must have a SYSAUX tablespace. 

TEMP: 

This tablespace is used to store temporary tables and indexes when processing SQL statements. It would, for example, be used for sort work space. 

                 Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. 

                      In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when the user account is created, then Oracle assigns this tablespace to the user. 

UNDOTBS1: 

This is the undo tablespace used by the database server to store undo information. Every database must have an undo tablespace that is created during database creation. 

                  Multiple undo tablespaces can exist in a database, but only one undo tablespace can be active at any given time. Undo tablespaces are used for rolling back transactions etc. 

USERS: 

This tablespace is used to store permanent user objects and data. 

In the preconfigured database, the USERS tablespace is the default tablespace for all objects created by nonsystem users. 

For the SYS and SYSTEM users (the system users), the default permanent tablespace remains SYSTEM. 

* Tablespace is a logical area where we create tables, indexes etc. 

* Each tablespace is associated with one datafile (.dbf) 

* When the entire datafile is occupied then we can either increase the existing file size (resize) or add one more datafile using ALTER tablespace command. 

* In Oracle 10g we can rename a tablespace 

* By default any tablespace datafile is getting updated because of users DML transactions 

(Oracle will generate Redo log information which will be written to current redo log) LOGGING=YES (Brand new table space) when we created a table space. 

                     In case if we decide that the transactions need not be LOGGED then we can choose LOGGING=NO at the tablespace level. 

* When we create a tablespace then we see PLUGGEDIN=NO, where as when we transport a tablespace from one database to another then we can observe PLUGGEDIN=YES at the target database. 

* From Oracle 8 onwards we can create a tablespace which will hold temporary objects (usually meant to support for sorting operations). Users are not allowed to create any Permanent objects like table or Index in the TEMP tablespace since type is TEMP. 

Examples: 

$sqlplus “/as sysdba” 

SQL>startup<enter> 

SQL>desc v$database; 

Note: start with v$ are dynamic views. 

Dynamic views can open at any stage i.e. mount, nomount or open. 

How to change from nomount to mount? 

SQL>alter database mount; 

How to open a database? 

SQL>alter database open; 

SQL>select instance_name, status from v$instance; 

How to shutdown a database? 

SQL>alter database close; This will come to mount stage. This is similar to shutdown. 

If you try to open 

SQL>alter database open; 

ERROR: database has been previously opened and closed 

How to dismount the database? 

SQL>alter database dismount; 

That is nomount stage. 

SQL>shut abort; 

Oracle instance shut down. 

SQL>startup; 

Oracle database open. 

SQL>select name, open_mode from v$database; 

SQL> desc dba_tablespaces; 

How to create a tablespace? 

SQL>create tablespace exampletbs datafile  ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ size 1m; 

How to add a datafile to an existing tablespace? 

SQL>alter tablespace exampletbs add/rename/drop datafile 

‘/u01/app/oracle/oradata/HRDEV/exapmletbs1.dbf’ size 1m; 

SQL>desc dba_data_files; 

SQL>select tablespace_name, file_name, file_id from dba_data_files; 

How to drop a datafile? 

SQL>alter tablespace exampletbs drop datafile 6; 

Or 

SQL>alter tablespace exampletbs drop datafile

‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’; 

How to drop an entire tablespace including contents? 

SQL>drop tablespace exampletbs including contents; 

It will drop tablespace but not remove physical datafiles. 

To drop tablespace as well as datafiles use the following SQL. 

SQL>drop tablespace exampletbs including contents and datafiles; 

If tablespace is empty (there is no datafile ) then 

SQL>drop tablespace exampletbs; 

How to reuse existing datafile. 

Create one more tablespace. 

SQL>create tablespace exampletbs datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ reuse; 

How to reuse existing datafile. 

How to alter datafile? 

SQL>alter database datafile 5 resize 10m; 

Or 

SQL>alter database datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ resize 15m; 

How to rename a datafile? 

SQL>alter tablespace exampletbs offline; 

SQL>exit 

Go to datafile location. 

HRDEV]$cp exampletbs1.dbf exampletbs2.dbf 

OR 

Go to Home directory. 

$cd 

$sqlplus ‘/as sysdba’ 

SQL>alter tablespace exampletbs rename datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ to ‘/u01/app/oracle/oradata/HRDEV/exampletbs3.dbf’; 

How to make tablespace online? 

SQL>alter tablespace exampletbs online; 

How to rename a tablespace? This feature is only from oracle 10g. 

SQL>alter tablespace exampletbs rename to exampletbsdba;