Standby database creation steps for oracle standard edition in windows

Manual standby creation in oracle standard edition, manual log shipping method for creation standby database

In general, we use Data guard for Oracle standby database creation in enterprise edition, comes to standard edition we are unable to create the Disaster Recovery site using Data guard, because this feature disabled in oracle standard edition.

In Oracle standard edition standby database can creates using log shipping method, here we go with the complete steps.

For standby database creation proceed with rman recovery and user managed recovery process.

Steps to create standby DB in windows: 

1.       Make sure primary database is in archive log mode.

2.       Install oracle binaries at standby side and create same folder structure at standby server as like primary.

3.       Create pfile from spfile and send it to standby server ‘C:\app\oracle\product\11.2.0\database\’.

4.       Create standby control file send standby controlfile to DR server.

Sql> alter database create standby controlfile as ‘Ç:\app\standbycontrol.ctl’;

5.       Update the controlfile information in pfile.

6.       Create oracle service using oradim command and start the service.

oradim -NEW -SID ORCL -STARTMODE manual -PFILE C:\app\oracle\product\11.2.0\database\INITORCL.ORA"

7.       Take the backup of database from primary side.

Sql> alter database begin backup;

Copy all datafiles to standby server

Sql> alter database end backup;

8.       Start the standby database in mount state.

Sql> shut immediate

Sql> startup nomount

Sql> alter database mount standby database;

9.       Create standby archive log folder as a network drive at primary database side.

10.   Schedule the archives copy script at primary server and recover database script at standby server side.

11.   Archives_copy.bat

copy C:\app\oracle\oradata\ORCL\archive\*.0001 E:\dba\backup\arc_backup

12.   Recover_database.bat

@echo off

C:

cd \oracle\product\11202\BIN

set ORACLE_HOME=C:\oracle\product\11202

set ORACLE_SID=orcl

sqlplus / as sysdba @D:\oradata\orcl\archive\recover.sql

3. recover.sql

spool D:\oradata\orcl\archive\log.recovery replace

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;

recover standby database;

auto

spool off

exit;

Read More

Oracle database migration from server to server using RMAN

Datafiles move from one disk to another disk, Database move from one disk to another disk, switch database to rman incrementally updated backup copy images.

Complete database migration from one server to another using rman backup in scheduled window / downtime:

Source server:

1.       Check the all application services are in down or not. Have make sure all the application services down before starting.

Sql>select process, program, username from v$session where username not like '%oracle%';

2.       Make sure the switch log file / check point at database level.

Sql> alter system switch logfile;

Or

Sql> alter system checkpoint;

3.       Take the rman full backup.

Rman > backup database plus archivelog;

Above command takes the full database backup. Also takes backup of spfile and controlfile but have to configure controlfile auto backup on then it takes the backup as $ORACLE_HOME/dbs/c-dbid-date.

4.       Using scp send the all backup files to target server.

5.       Copy all password file, listener, tnsnames, sqlnet.ora file to target server.

 Target server:

1.       Install oracle binaries, place the password file in $ORACLE_HOME/dbs location, listener, tnsnames and sqlnet.ora file in $ORACLE_HOME/network/admin location.

2.       Set the bash_profile.sh for oracle user, export ORACLE_SID=orcl (instance name).

3.       Connect to the rman.

Rman target sys/pwd

Rman> set dbid 43123423;

Rman> restore spfile from ‘/u01/backup/c-43123423-20170823’ to ‘/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileorcl.ora’;

Sql> startup nomount

Rman> restore controlfile from ‘/u01/backup/c-43123423-20170823’; (please make sure the path of the controlfile from pfile)

Sql> startup mount;

Rman>  run { 

catalog start with '/backup location/';

set newname for database to '/new database files location/%b';

restore database;

switch datafile all;

recover database;

}

Above run block

1.    Catalog start with ‘backup sets location’ : rman catalogs the backup copies.

2.    Set newname for database to ‘/destination/%b’ : it renames the all datafiles location to new location in target server.

%b specifies stripped datafiles to single location

3.    Restore database: restore the database

4.    Switch datafile all: it switches all the datafile to new location

5.    Recover database: it recovers the database

4.       Rename the all redo log files.

Sql> select member from v$logfile;

Sql> alter database rename file '/previous path/redo01.log' to '/new path/redo01.log';

5.       Open database using open reset logs.

Sql> alter database open reset logs;

6.       Create temporary tablespace make as default.

Read More

Oracle DBA 6 years experience interview questions

1. What is the pga?
2. What are the pga parameters?
3. What is the relation between pga and temporary tablespace?
4. If incase PGA memory is having 2gb, and the statement required more memory then what hapen?
5. What is the smon?
6. What are the states of the redo logfiles?
7. What happens if we deletes all the redo log members? how to restore and recover?
8. What happens once we place the tablespace in begin backup mode?
9. why archives generates heavily at begin backup mode?
10.what is the difference between avialable and expired in rman backup?
11. what is the crosscheck?
12. What is the checkpoint?
13. what is the difference between cpu and psu patches?
14. What are the steps to applying the psu patches?
15. What process can fetch data into buffer cahce from datafiles?
16. is uncommitted data can writes into datafiles?
17. What is the chache coherence? What maintaince this?
18. What is the GRD, where it placed and which instance it resides?
19. what is node eviction? what causes for the node eviction?
20. What parameters need to create standby?
21. Which background process applies redo at standby side?
22.In which view we can verify the psu patch applied or not?
23. What are the statistics? what is the use of it and what are the incremental statistics?
24. How to migrate database from one os to another os?
25. Why we can run the roo.sh and rootinst.sh in oracle installation?
26. Tell me the select statement execution process?
27. tel me about update statement in oracle?
28. what are the sequence of steps in rac 11gr2?
29. What are the rac specific background process?
30. How to move the datafile created at file system to RAC (ASM)?
31. What is your recently resolved performance issue?

1.how to know which databases are using asm instance
2.show parameter in exp
3.check the status of the  exp dump file
4.restore datafile to perticuler location using rman
5.how to check size of a directory in linux
6.status of  partitioned disk after it assigened to asm disk group
7.how to stop job in expdp
8.how many types of load balances available in RAC

9.when we issue startup upgrade command what will happen
10.what is db replay
11.compression
12.rman 11g features
13.total db size
14.rman backup size and time
15.logical backup size,time and schema size
16.where can we find node membership polling machanism in RAC
17.how much database size  grows daily

18.are the idexes are always benficial or not
19.how to stop crs without connecting to root user
20.how to see listener status if the listener is passwor protected
21.how to improve performance of a impdp command
22.what is default time and location for ocr backup
23.difference between cpu,psu,bug fixes patch

24.How to check backup type in list backup command.
25.Can we rename asm instance
26.Asm instance control file
27.Can we create more than one asm instance on one box
28.What is conflict check in opatch

29.Crs should be up or down to take voting disks backups using dd command
30.Why expdp is faster than normal exp
31.Difference between active dataguard and normal dataguard
32.Will rman create temporary tablespace after cloing or restore process.
33.Difference between 10g load balance machanism and 11gr2 load balance machanism

34.What is the use of pwd file
35.Why mrp process runs in single on standby 3 node rac
36.Name of view that stores plsql compilation errors
37.Diffrence between cloning and refresh
38.How to demonstrate to the developper taht if they created index on perticular column it would hep
39.Order of background process when we issue startup command
40.What is the shortest way to restore a table if we have only rman backup of db size 1.5tb
41.How will you recover spfile if we dont have backup and it got corrupted
42.How does scan identify that node is a least loaded node
43.How to change the rebalance value in asm
44.Where cluster checks the details of asm diskgroups before starting the css process and pass these names to kfed
45.Which process starts 1st after issuing  crs start command
46.What will be state of database if we kill dwr background process
47.How to find scan name of a cluster
48.How to find number of nodes in cluster from os level with out using rac or database commands
49.How to find location of inventory file
50.What information contains in inventory.xml file
51.What is use of napply in opatch
52.What is use of CONSISTENT parameter in exp
53.How to find sql query of  session if  i have only  os process id
54.How to create spfile for asm instance

Read More

Postgresql 9.2 streaming replication step by step

Using below steps we can create postgresql high availability setup.

Postgres replication setup:

Primary server configuration:

step1: edit postgresql.conf

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50
archive_mode = on
archive_command = 'cp %p /archive_log/archive/%f'

step2: edit pg_hba.conf

host replication postgres 192.168.X.X/32 trust ----DR server IP address

step3:restart postgres service

as a root user go to /etc/init.d/

#service postgresql-9.2 restart

step4:Next thing we need to do is take data snapshot of data from master and then move that to slave server.

psql -c "SELECT pg_start_backup('replbackup');"
(GO TO ROOT AND TAR DATA)
tar cfvP /opt/Postgresql/9.2/data.tar /opt/Postgresql/9.2/data
psql -c "SELECT pg_stop_backup();"

step5:Now move this data to slave/standby database server.

scp /tmp/db_file_backup.tar root@opensourcedbms_pg_slave:/tmp/

Standby server configuration:

step1:First stop server if it is running

#service postgresql-9.2 stop

step2:Rename the existing data directory.

mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data.old

step3:Unzip master/primary server data snapshot file that is copied into this server.

tar xvfP /tmp/db_file_backup.tar

step4:Remove postmaster.pid so standby server does not see the primary server’s pid as its own.

rm -f /var/lib/pgsql/9.2/data/postmaster.pid

step5:Now edit configuration file and tweak hot_standby variable.

vi /var/lib/pgsql/9.2/data/postgresql.conf

hot_standby = on

step6:cp /usr/pgsql-9.2/share/recovery.conf.sample /var/lib/pgsql/9.2/data/recovery.conf

step7:Edit this recovery.conf file and update standby server settings:

vi /var/lib/pgsql/9.2/data/recovery.conf

standby_mode = on
primary_conninfo = 'host=192.168.1.4 port=5432 user=postgres password=postgres' ------Give the primary database IP address

step8:Update permissions on recovery.conf file

chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf

step9: Start the postgresql service

service postgresql-9.2 start

Using Below queries check the replication status

select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;

select * from pg_stat_replication ;

select pg_last_xlog_replay_location(); (standby side)

select pg_is_in_recovery();( to know the recovery mode)

To Know the log delay:

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

Read More

What is a controlfile in oracle, how to recreate the controlfile

Controlfile :

Control file is small binary file, it contains physical structure of the oracle database. Each database must contain one or more control files. It contains 

- The database name

- Datafiles and redolog files information of associated database.

- Timestamp of the database creation.

- The current log sequence number

- Check point information.

We can specify the controlfile names using the “control_files” parameter.

Control_files=(/u01/oracle/prod/control01.ctl, /u02/oracle/prod/control02.ctl, /u03/oracle/prod/control03.ctl)

Size of the control file can vary from release to realse. Configuration parameters maxlogfiles, maxlogmembers, maxdatafiles and maxinstances affect the control file size.

How to multiplexing the controlfiles:

1. Shut down the database.

2. Edit the control_file parameter and set the new locations.

For example having only one controlfile at /u01/app/oracle/oradata/control1.ctl, this file we need to copy to new location and mention that new location at parameter.

$cp /u01/app/oracle/oradata/control1.ctl /u02/app/oracle/oradata/control2.ctl

$ cp /u01/app/oracle/oradata/control1.ctl /u03/app/oracle/oradata/control3.ctl

3. Restart the database.

Creation of new control file:  

We can create the new control file in 2 situations.

1. All the available control files corrupted and have no backup of the control file.

2. Rename the database.

Renaming of the Database using controlfile:

We can create the controlfile using create controlfile statement. We can rename the database base using creation control file. We do mention the new name at create controlfile statement.

CREATE CONTROLFILE SET DATABASE prod

LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',

'/u01/oracle/prod/redo01_02.log'),

GROUP 2 ('/u01/oracle/prod/redo02_01.log',

'/u01/oracle/prod/redo02_02.log'),

GROUP 3 ('/u01/oracle/prod/redo03_01.log',

'/u01/oracle/prod/redo03_02.log')

RESETLOGS

DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,

'/u01/oracle/prod/rbs01.dbs' SIZE 5M,

'/u01/oracle/prod/users01.dbs' SIZE 5M,

'/u01/oracle/prod/temp01.dbs' SIZE 5M

MAXLOGFILES 50

MAXLOGMEMBERS 3

MAXLOGHISTORY 400

MAXDATAFILES 200

Read More