Oracle Active database Cloning - RAC DB

-

Oracle Active database Cloning 

For DBA Refreshing Table, Schema and Database are routine activity. You can refresh/Clone database using below method:

1.Conventional Restore method and Rename DB post DB Refresh
2.Conventional Duplicate Database method
3. Active database Cloning 
4.Backup Base Cloning

We will cover all these methods of cloning in individual blogs. In This blog , We will discuss about how to refresh/Clone Database from active Database. 

In Active database Cloning, Auxiliary Database is cloned from the live target database without the need of the backup to be copied to Auxiliary DB Server. The duplication work is being performed by the auxiliary channel and these channels corresponds to the server session in auxiliary DB instance. 

Below are the step followed to perform active database duplication of the RAC Database.

1) Create ASM Directories.

alter diskgroup DATA add directory '+DATA/CLONEDB';
alter diskgroup DATA add directory '+DATA/CLONEDB/DATAFILE';
alter diskgroup DATA add directory '+DATA/CLONEDB/CONTROLFILE';
alter diskgroup DATA add directory '+DATA/CLONEDB/ONLINELOG';
alter diskgroup DATA add directory '+DATA/CLONEDB/TEMPFILE';
alter diskgroup DATA add directory '+DATA/CLONEDB/PARAMETERFILE';
alter diskgroup RECO add directory '+RECO/CLONEDB';
alter diskgroup RECO add directory '+RECO/CLONEDB/CONTROLFILE';
alter diskgroup RECO add directory '+RECO/CLONEDB/ONLINELOG;

2)Create Auxilary Database parameter file.

. oraenv
TRGTDB1
SQL>create pfile='/tmp/initCLONEDB.ora' from spfile;

--Edit parameter file /tmp/initCLONEDB.ora with correct DB name and comment all the Cluster parameter.

Parameter file should look like below

[oracle@oraclehost01 ~]$ cat /tmp/initCLONEDB.ora
#CLONEDB1.__db_cache_size=7549747200
#CLONEDB2.__db_cache_size=6442450944
#CLONEDB2.__java_pool_size=234881024
#CLONEDB1.__java_pool_size=234881024
#CLONEDB2.__large_pool_size=1375731712
#CLONEDB1.__large_pool_size=1375731712
#CLONEDB2.__pga_aggregate_target=3959422976
#CLONEDB1.__pga_aggregate_target=3959422976
#CLONEDB2.__sga_target=11811160064
#CLONEDB1.__sga_target=11811160064
#CLONEDB2.__shared_io_pool_size=0
#CLONEDB1.__shared_io_pool_size=0
#CLONEDB1.__shared_pool_size=2348810240
#CLONEDB2.__shared_pool_size=3456106496
#CLONEDB2.__streams_pool_size=0
#CLONEDB1.__streams_pool_size=0
*._subquery_pruning_enabled=TRUE
*.audit_file_dest='/u01/app/oracle/admin/CLONEDB/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+RECO/CLONEDB/controlfile/current.361.865848547','+DATA/CLONEDB/controlfile/current.1222.865848549'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_create_online_log_dest_2='+DATA'
*.db_domain=''
*.db_name='CLONEDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)'
*.dml_locks=20416
*.enable_goldengate_replication=TRUE
#CLONEDB1.instance_number=1
#CLONEDB2.instance_number=2
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=100
*.parallel_min_servers=30
*.parallel_servers_target=100
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=3932160000
*.processes=1000
*.remote_listener='CLSCAN1:1521'
*.remote_login_passwordfile='exclusive'
*.result_cache_max_size=59080704
*.sessions=4640
*.sga_max_size=11811160064
*.sga_target=11811160064
*.shared_pool_reserved_size=85563801
*.shared_pool_size=1711276032
#CLONEDB2.thread=2
#CLONEDB1.thread=1
*.transactions=5104
undo_tablespace='UNDOTBS1'
#CLONEDB2.undo_tablespace='UNDOTBS2'

NOTE: Cluster Specific parameters are disables initially. As Database will be first cloned as single database instance and later converted to RAC DB.

--Create Dignostic directory if any any (On both Node of cluster)

mkdir -p /u01/app/oracle/admin/CLONEDB/adump

--Add below parameter in parameter

*.DB_FILE_NAME_CONVERT ='+DATA/TRGTDB/datafile/','+DATA/CLONEDB/datafile/'
*.LOG_FILE_NAME_CONVERT ='+DATA/TRGTDB/onlinelog/','+DATA/CLONEDB/onlinelog/','+RECO/TRGTDB/onlinelog/','+RECO/CLONEDB/onlinelog/'

3) Start Auxiliary database instance

. oraenv
CLONEDB
SQL>startup nomount pfile='/tmp/initCLONEDB.ora'
SQL>Create spfile='+DATA/CLONEDB/spfileCLONEDB.ora' from pfile='/tmp/initCLONEDB.ora';

-- Create pfile in $ORACLE_HOME/dba as given below:

[oracle@oraclehost01 dbs]$ cat initCLONEDB.ora
SPFILE='+DATA/CLONEDB/spfileCLONEDB.ora'

--Verify Spfile created in Disk Group in correct directory

SQL>Shutdown;
SQL>Startup nomount

4) Create password file for Auxiliary database (Make Sure password is same as that of the target database)

. oraenv
CLONEDB
orapwd file="${ORACLE_HOME}/dbs/orapw${ORACLE_SID}" password="oracle123#" entries=5

5) Make oracle NET connectivity changes (As we are duplication on the same server no need to create listener)

Make changes in listener.ora as given below:

 (SID_DESC =
      (GLOBAL_DBNAME = CLONEDB)
      (SID_NAME = CLONEDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.2/dbhome_1)
    )

Add below entry on both node of the cluster:

CLONEDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclehost02)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CLONEDB)))

6) Clone Auxiliary database with Target database.

. oraenv
CLONEDB

rman
connect TARGET sys/oracle123#@TRGTDB
connect auxiliary sys/oracle123#@CLONEDB
run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
set newname for tempfile 3 to '+DATA';
set newname for tempfile 4 to '+DATA';
set newname for tempfile 6 to '+DATA';
set newname for tempfile 7 to '+DATA';
set newname for tempfile 8 to '+DATA';
set newname for tempfile 9 to '+DATA';
DUPLICATE TARGET DATABASE TO CLONEDB FROM ACTIVE DATABASE;
release channel ch1;
release channel ch2;
release channel d1;
release channel d2;
}

7) Verify the undo tablespace, thread and logfile. If not there create.

select THREAD#,GROUP#,BYTES/1024/1024/1024 from v$log;
select GROUP#,MEMBER from  v$logfile;
select name from v$tablespace;

8)Create Pfile from Running Auxilary database

create pfile='/tmp/test.ora' from spfile='+DATA/CLONEDB/spfileCLONEDB.ora';

-- Make change in the parameter file via adding the parameter required to make it cluster database.Below is parameter file


*.cluster_database=true
*._subquery_pruning_enabled=TRUE
*.audit_file_dest='/u01/app/oracle/admin/CLONEDB/adump'
*.audit_trail='db'
*.compatible='12.2.0.2'
*.control_files='+RECO/CLONEDB/controlfile/current.602.883768743','+DATA/CLONEDB/controlfile/current.2483.883768743'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_create_online_log_dest_2='+DATA'
*.db_domain=''
#*.DB_FILE_NAME_CONVERT='+DATA/TRGTDB/datafile/','+DATA/CLONEDB/datafile/'
*.db_name='CLONEDB'#Reset to original value by RMAN
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)'
*.dml_locks=20416
*.enable_goldengate_replication=TRUE
#*.LOG_FILE_NAME_CONVERT='+DATA/TRGTDB/onlinelog/','+DATA/CLONEDB/onlinelog/','+RECO/TRGTDB/onlinelog/','+RECO/CLONEDB/onlinelog/'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=100
*.parallel_min_servers=30
*.parallel_servers_target=100
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=3932160000
*.processes=1000
*.remote_listener='CLSCAN1:1521'
*.remote_login_passwordfile='exclusive'
*.result_cache_max_size=59080704
*.sessions=4640
*.sga_max_size=11811160064
*.sga_target=1181116006
*.shared_pool_reserved_size=85563801
*.shared_pool_size=1711276032
*.transactions=5104
CLONEDB1.undo_tablespace='UNDOTBS1'
CLONEDB2.undo_tablespace='UNDOTBS2'
CLONEDB2.thread=2
CLONEDB1.thread=1
CLONEDB1.instance_number=1
CLONEDB2.instance_number=2

9)Shut Down Auxilary database ,Create spfile from new parameter file and start database via setting enviorment to instance.

. oraenv
CLONEDB
SQL>shutdown immediate;
SQL>create spfile='+DATA/CLONEDB/spfileCLONEDB.ora' from pfile='/tmp/test.ora';

----Change name of current pfile in $ORACLE_HOME/dbs to point to instance.

mv initCLONEDB.ora initCLONEDB1.ora

---- Copy above parameter file to node oraclehost03

scp initCLONEDB1.ora oraclehost03:/u01/app/oracle/product/12.2.0.2/dbhome_1/dbs/initCLONEDB2.ora

---Add instace entry to the /etc/oratab file

--On node oraclehost01

CLONEDB1:/u01/app/oracle/product/12.2.0.2/dbhome_1:N

--On node oraclehost03

CLONEDB2:/u01/app/oracle/product/12.2.0.2/dbhome_1:N

. oraenv
CLONEDB1
startup nomount;
alter database mount;
alter database open;

10) Add Database to Cluster.

. oraenv
CLONEDB1
shut immediate;

srvctl add database -d CLONEDB -o /u01/app/oracle/product/12.2.0.2/dbhome_1 -p '+DATA/CLONEDB/spfileCLONEDB.ora'
srvctl add instance -d CLONEDB -i CLONEDB1 -n oraclehost01
srvctl add instance -d CLONEDB -i CLONEDB2 -n oraclehost03

----Verify database configuration using.

srvctl config database -d CLONEDB

--Start database..

srvctl start database -d CLONEDB

---Verify database status.

 srvctl status database -d CLONEDB

Select d.name "DB NAME",i.INSTANCE_NUMBER,i.INSTANCE_NAME,i.HOST_NAME,d.OPEN_MODE from gv$database d, gv$instance i where i.INST_ID=d.INST_ID;

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for Refresh

Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --d...
Read more

Change Oracle DB Name ,DBID and Both

-
Change Oracle DB Name ,DBID and Both Oracle DBA changing database name ,DBID or both are common activity which are needed to be performed regularly.  With the introduction on the NID utility this has become easy to make such changes.In this blog , We will show you how to change : 1. DB Name and ID  2. DB Name 3. DBID  using NID utility. Change DB Name and DBID: 1) Take Full backup of the database and mount database. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area  251658240 bytes Fixed Size                  2236368 bytes Variable Size             161210416 bytes Database Buffers           83886080 bytes Redo Buffers       ...
Read more