DBA Admin: 2014

Wednesday 12 November 2014

Creating Standby Database Creation using RMAN backup

Data Guard Configuration:

Primary mechine details :
hostname- rac1.com
ip- 192.168.87.130
tnsname - rac1
service name - orcl (db_name)
db_unique_name - orcl

Standby mechine details:
hostname- rac2.com
ip - 192.168.87.132
tnsname- rac2
service name - orcl (db_name)
db_unique_name - stby

Primary network configurations:

listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

L1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.130)(PORT = 1521))
)
)

SID_LIST_L1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = ORCL)
)
)

tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC2 =
(DESCRIPTION =
(ADDRESS_LIST =
ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.130)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORCL)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.132)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORCL)
)
)

XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bhasskar-PC)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = catalog)
)
)

Follow the similar configuration for the Standby database

Prerequisites:

1. Check whether you have a privileges or not in standby server.
2. Check whether oracle Database software is installed or not.
3. Check whether mount point have enough space or not.

Creating Physical Standby Database:

For Primary :

1. Check Oracle Net must be configured
Start the listener

2. Create a password file.

3. Database must be in archivelog mode.

Otherwise enable the archive log mode when the database in mount start
SQL> ALTER DATABASE ARCHIVELOG;

4. Enable FORCE LOGGING and SUPPLIMENTARY LOGGING.
SQL> ALTER DATBASE FORCE LOGGING;

5. Create a standby redo log files more than Online redo logfiles.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
('/...../standby_redo01.rdo','/...../standby_redo01.rdo',

'/...../standby_redo01.rdo','/...../standby_redo01.rdo');

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

6. Start the Database in READ WRITE mode.

7. Check whether database using spfile.
SQL>show parameter spfile

8. Change the parameters of the database which is running using spfile.

Add below parameter

*.DB_UNIQUE_NAME='ORCL'
*.FAL_CLIENT='ORCL'
*.FAL_SERVER='stby' #standby database name
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,stby)'

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_2='SERVICE=rac2 LGWR async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'

Note: No need to restart the database because all the above parameters are dynamic parameters.

9. create a pfile, control file backups.
SQL:> CREATE PFILE FROM SPFILE;
SQL:> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/rman/standby_c.ctl';

10. Take RMAN backup for the database.

connect to rman, Target database and catalog database

$ rman target / catalog rcat/rcat@xe
rman> backup database plus archivelog;
rman> backup current controlfile for standby;
exit

11. create a zip for rman backup created in /u02/rman
rman.zip

12. copy the pfile, password file and rman.zip files to standby mechine
$ scp initORCL oracle@192.168.84.132: $pwd
$ scp orapwORCL.zip oracle@192.168.84.132: $pwd
$ scp rman.zip oracle@192.168.84.132: $pwd

For Standby :

1. Configure Oracle Net at standby server.
2. copy rman.zip file to /u02/rman/ location
unzip rman.zip file
3. copy the initORCL.ora and orapwORCL into ORACLE_HOME/dbs/ location

4. Rename pfile and edit the pfile

$ vi initstby.ora
orcl.__db_cache_size=67108864
orcl.__java_pool_size=12582912
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'
orcl.__pga_aggregate_target=167772160
orcl.__sga_target=255852544
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=163577856
orcl.__streams_pool_size=0

#*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.30'
*.db_block_size=8192
*.DB_NAME='ORCL' #dont change
*.db_domain=''
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u02/rman'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'

change the below parameter for standby

*.control_files='/u01/app/oracle/oradata/sb/control_standby.ctl' # change
*.DB_UNIQUE_NAME='stby' #change
*.FAL_CLIENT='stby' #change
*.FAL_SERVER='ORCL' #change

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby' #change

*.log_archive_dest_2='SERVICE=rac1 LGWR async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'#change
:wq

5. start the database using pfile in nomount

$export ORACLE_SID=stby

$sqlplus / as sysdba

SQL> startup nomount

6. create a spfile
create spfile from pfile.

7. shut the database

8. start the database in nomount state
$export ORACLE_SID=stby
$sqlplus / as sysdba

SQL> startup nomount

9. Connect to RMAN.

$ rman target sys/tiger@rac1 catalog rcat/rcat@xe auxiliary /

rman> duplicate target database for standby
backup location='/orabackup/db_full'
nofilenamecheck dorecover;

so standby database is created and goes to mount state

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

Finally checking:

In primary
sql> select max(sequence#) from v$log_history;
14

sql> alter database switch logfile;
sql> alter database switch logfile;

sql> select max(sequence#) from v$log_history;
16

In Standby :

sql> select max(sequence#) from v$log_history;
16

success

Saturday 6 September 2014

Creating ACTIVE DUPLICATE database

Prerequisites Specific to Active Database Duplication

When you execute DUPLICATE with FROM ACTIVE DATABASE, at least one normal target channel and at least one AUXILIARY channel are required.

When you connect RMAN to the source database as TARGET, you must specify a password, even if RMAN uses operating system authentication. The source database must be mounted or open. If the source database is open, then archiving must be enabled. If the source database is not open, then it must have been shut down consistently.

When you connect RMAN to the auxiliary instance, you must provide a net service name. This requirement applies even if the auxiliary instance is on the local host.

The source database and auxiliary instances must use the same SYSDBA password, which means that both instances must have password files. You can create the password file with a single password so you can start the auxiliary instance and enable the source database to connect to it.

The DUPLICATE behavior for password files varies depending on whether your duplicate database acts as a standby database. If you create a duplicate database that is not a standby database, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance. If you create a standby database, then RMAN copies the password file to the standby host by default, overwriting the existing password file. In this case, the PASSWORD FILE clause is not necessary.

You cannot use the UNTIL clause when performing active database duplication. RMAN chooses a time based on when the online data files have been completely copied, so that the data files can be recovered to a consistent point in time.

Source database name = orcl
Duplicate database name = test
Catalog database name= catalog
tnsname for target =orcl
tnsnames for catalog= catalog

Step 1:

Create a backup of the source database, if a suitable one doesn't already exist.

$ rman target=/

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Step 2:
Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtest password=password entries=10

Step 3: Add the listener for the test database
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = test)
)
)

Add the tnsname for the test database

tnsnames.ora:
CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)

Restart the listener
$ lsnrctl stop
$ lsnrctl start

Step 4: create pfile from the "orcl" database
SQL> create pfile from spfile;

Step 5: copy pfile to target server and rename the pfile as inittest.ora

Edit inittest.ora file for duplicate database.

*.control_files='/disk1/app/oracle/oradata/test/control01.ctl'

*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'

*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/disk1/app/oracle/admin/test/udump'
*.compatible='10.2.0.1.0'
*.core_dump_dest='/disk1/app/oracle/admin/test/cdump'
*.db_block_size=8192
db_file_name_convert='/disk1/app/oracle/oradata/orcl','/u01/app/oracle/oradata/test'
log_file_name_convert='/disk1/app/oracle/oradata/orcl','/u01/app/oracle/oradata/test'

Note : parameters marked in red are important

Step 6: Set the environment for the dulplicate database.

$export ORACLE_SID=test

Step 7: Start the database
$sqlplus / as sysdba
Sql>startup nomount

Step 8: Connect to RMAN
$rman target=system/system@orcl catalog=rman/rman@catalog auxiliary system/system@test

connected to target database: ORCL (DBID=231273269)
connected to recovery catalog database
connected to auxiliary database: TEST(DBID=99469477)

Rman >run{
run {
allocate channel ch device type disk;
allocate auxiliary channel aux device type disk;
duplicate database to "test" FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
}

OUTPUT
allocated channel: aux1
channel aux1: sid=156 devtype=DISK
Starting Duplicate Db at 01-JAN-10
contents of Memory Script:
.......................................................
........................................................
....................................................
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db

Step 9: Create spflile from memory

Step 10: check whether is there any invalid objects or not.

sql> select count(*) from dba_objects where stauts like 'INVALID';

Step 11: check dba_directory for invalid paths. if there is any resolve them.

Step 12: check dba_db_links. if there is any resolve them.

Step 13: Add an entry in /etc/oratab file

Step 14: Register with the rman recovery catalog if necessary.

NOTE:
Remove "DB_UNIQUE_NAME" from parameter file
It gives cant open database in exclusive mode' issue

Friday 5 September 2014

Creating DUPLICATE (Clone) Database Using RMAN Backup

Prerequisites Common to All Forms of Duplication

RMAN must be connected as AUXILIARY to the instance of the duplicate database. The instance of the duplicate database is called the auxiliary instance. The auxiliary instance must be started with the NOMOUNT option.

The source host is the database on which the source database resides. The destination host is the host on which you intend to create the duplicate database. If you intend to create the duplicate database on the source host, then set the CONTROL_FILES initialization parameter appropriately so that the DUPLICATE command does not generate an error because the source control file is in use. Also, set all *_DEST initialization parameters appropriately so that the source database files are not overwritten by the duplicate database files.

The source and duplicate databases must be on the same platform. In the context of DUPLICATE, 32-bit and 64-bit versions of the same platform are considered the same platform. For example, Linux IA (32-bit) is considered the same platform as Linux IA (64-bit). However, after duplicating a database between 32-bit and 64-bit platforms, you must run the utlirp.sql script to convert the PL/SQL code to the new format. This script is located inORACLE_HOME/rdbms/admin on Linux and UNIX platforms.

The DUPLICATE command requires one or more auxiliary channels. These channels perform the work of the duplication on the auxiliary database instance. In the following circumstances, RMAN uses the channel configuration from the source database for auxiliary channels:

You have not used ALLOCATE CHANNEL to manually allocate auxiliary channels.
You have not used CONFIGURE to configure auxiliary channels.

If you have configured automatic target channels to use CONNECT strings, then RMAN attempts to replicate the channel allocation on the auxiliary instance. However, if you must control or vary the channel allocation for duplication, you should manually allocate auxiliary channels.

If the COMPATIBLE initialization parameter is set greater than or equal to 11.0.0, then by default RMAN duplicates transportable tablespaces that were not made read/write after being transported. Otherwise, RMAN cannot duplicatetransportable tablespaces unless they have been made read/write after being transported.

The following database encryption features both use the wallet: transparent data encryption, which functions at the column level, and tablespace encryption. If you are duplicating an encrypted tablespace, then you must manually copy the wallet to the duplicate database.

Purpose of Database Duplication

A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

Test backup and recovery procedures
Test an upgrade to a new release of Oracle Database
Test the effect of applications on database performance
Create a standby database
Generate reports

As part of the duplicating operation, RMAN automates the following steps:

Creates a default server parameter file for the auxiliary instance if the following conditions are true:
- Duplication does not involve a standby database.
- Server parameter files are not being duplicated.
- The auxiliary instance was not started with a server parameter file.
Restores from backup or copies from active database the latest control file that satisfies the UNTIL clause requirements.
Mounts the restored or copied backup control file from the active database.
Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance. This step applies to backup-based duplication.
Restores and copies the duplicate data files and recovers them with incremental backups and archived redo log files to a noncurrent point in time.
Shuts down and restarts the database instance in NOMOUNT mode.
Creates a new control file, which then creates and stores the new DBID in the data files.
Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database.

Check this Link:

http://docs.oracle.com/html/E10643_07/rcmsynta020.htm

DUPLICATE DATABASE CREATION ON SAME SERVER:
============================================
Target database name = db10g
Duplicate database name = db10g_dup
Catalog database name= catalog
tnsname for target =db10g
tnsnames for catalog= catalog

prerequisites:

1. take full RMAN backup for target database.

rman> BACKUP DATABASE;

2. take standby controlfile backup.

rman> BACKUP CURRENT CONTROLFILE FOR STANDBY;

3. Copy (SCP) database backup and controlfile to the same backup location (mount point) in source server.

-----------------------------

Step 1: create pfile from the "db10g" database
SQL> create pfile from spfile;

Step 2: Rename the pfile as initdb10g_dup.ora
Edit initdb10g_dup.ora file for duplicate database.

*.audit_file_dest='/disk1/app/oracle/admin/db10g_dup/adump'
*.background_dump_dest='/disk1/app/oracle/admin/db10g_dup/bdump'
*.control_files='/disk1/app/oracle/oradata/db10g_dup/contro_dup.ctl'

*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='db10g_dup'

*.db_recovery_file_dest='/disk1/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db10gXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/disk1/app/oracle/admin/db10g_dup/udump'
*.compatible='10.2.0.1.0'
*.core_dump_dest='/disk1/app/oracle/admin/db10g_dup/cdump'
*.db_block_size=8192
db_file_name_convert='/disk1/app/oracle/oradata/db10g','/disk1/app/oracle/oradata/db10g_dup'
log_file_name_convert='/disk1/app/oracle/oradata/db10g','/disk1/app/oracle/oradata/db10g_dup'

Note : parameters marked in red are important

Step 3: Start the listener
$ lsnrctl start

Step 4: Set the environment for the dulplicate database.
$export ORACLE_SID=db10g_dup

Step 5: Start the database
$sqlplus / as sysdba
Sql>startup nomount

Step 6: Connect to RMAN
$rman target=system/system@dba10g catalog=rman/rman@catalog auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 1 08:39:20 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBA10G (DBID=231273269)
connected to recovery catalog database
connected to auxiliary database: DB10G _DUP(DBID=99469477)

Rman >run{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE TO db10g_dup;
}

Step 7: Create spfile from memory. If necessary.

Step 8: check whether is there any invalid objects or not.
sql> select count(*) from dba_objects where stauts like 'INVALID';

Step 9: check dba_directory for invalid paths. if there is any resolve them.

Step 10: check dba_db_links. if there is any resolve them.

Step 11: Add an entry in /etc/oratab file

Step 12: Register with the rman recovery catalog if necessary.

NOTE:

Remove "DB_UNIQUE_NAME" from parameter file if cloning in same server.
It gives cant open database in exclusive mode' issue

DBA Admin