Oracle Cloning-Conventional Restore method and Rename DB post DB Refresh

-

Oracle Cloning - Conventional Restore method and Rename DB post DB Refresh

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 using Conventional Restore method and Rename DB post DB Refresh.  

In this method , We will first restore target database on the auxiliary server as target DB name and post restore , We will change the name of the target database to auxiliary database.  Below are the steps for cloning using this method:

1) Make the Source Database entry in oratab

2) Set ORACLE_SID environment variable: ORACLE_SID should be set to the proper SID name (target DB)


3) .        Restore spfile from backup: 

To restore the spfile, you first need to startup the database in the nomount state. This starts up the database using a dummy parameter file. After that you can restore the spfile from the backup. Finally you restart the database in nomount state. The restart is required in in order to start the instance using the restored parameter file. Below is snapshot RMAN transcript for the foregoing procedure. Note the difference in SGA size and components between the two startups:

 

RMAN> startup nomount

Note: if you get memory error while running startup nomount command

Set environment variable  ORA_RMAN_SGA_TARGET before executing rman. For example:

 $ export ORA_RMAN_SGA_TARGET=350


Restore spfile from full online backup.

RMAN>restore spfile from ‘H:\Restore\orabackup\flash_recovery_area\\BACKUPSET\2008_09_09\O1_MF_NCSN0_DAILY_FULL_ONLINE_4DCDWVKM_.BKP’;


4) Create pfile from spfile. 

Open Another command prompt window and set oracle_sid=<Target DB>

Open sqlplus and create pfile from spfile.


5) Change the parameter paths in the pfile using OS editor.

Pfile will be created at %ORACLE_HOME%\database\initngmes.ora

Note :

  •  DB_Name  and Instance_name  need to set as source database sid
  • all folder like bdump,cdump and controlfile dir .. name need to set as target database

Contents of Pfile After change :

$ cat inittargtdb.ora
*._trace_files_public=true
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.background_dump_dest='/u01/app/oracle/admin/auxdb/bdump'
*.bitmap_merge_area_size=2097152
*.COMPATIBLE='12.2.0.2'
*.control_files='/u02/oradata/auxdb/auxdb_control_01.ctl','/u03/oradata/auxdb/auxdb_control_02.ctl','/u04/oradata/auxdb/auxdb_control_03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/auxdb/cdump'
*.create_bitmap_area_size=2097152
*.cursor_space_for_time=TRUE
*.db_block_size=16384
*.db_domain='world'
*.db_file_multiblock_read_count=64
*.db_files=750
*.db_name='targtdb'
*.db_recovery_file_dest='/orabackup/flash_recovery_area'
*.db_recovery_file_dest_size=250G
*.db_writer_processes=8
*.EVENT='1555 TRACE NAME ERRORSTACK LEVEL 1'
*.fast_start_mttr_target=300
*.global_names=false
*.hash_area_size=2097152
*.instance_name='targtdb'
*.job_queue_processes=10
*.log_archive_format='ora_arch_auxdb_%t_%s_%r.log'
*.max_dump_file_size='10240'# limit trace file size to 5M each
*.nls_date_format='DD-MON-RR'
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=5000
*.os_authent_prefix=''
*.os_roles=FALSE
*.pga_aggregate_target=4G
*.processes=350
*.query_rewrite_enabled='TRUE'
*.remote_dependencies_mode='signature'
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=FALSE
*.remote_os_roles=FALSE
*.resource_limit=TRUE
*.service_names='auxdb.WORLD'
*.session_cached_cursors=50
*.session_max_open_files=20
*.sga_max_size=9663676416
*.sga_target=9663676416
*.shared_pool_reserved_size=30000000
*.sort_area_retained_size=1564672
*.sort_area_size=2097152
*.sql92_security=TRUE
*.timed_statistics=true# if you want timed statistics
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/auxdb/udump'
*.utl_file_dir='/usr/local/syschk','/u02/app/oracle/admin/auxdb/create','/backup02/export','/u02/home/tcs/utl_log','/u02/home/whseload/output'
*.sga_target=500M
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.streams_pool_size=16777216
*.timed_statistics=TRUE
*.transactions=720
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.USE_INDIRECT_DATA_BUFFERS=TRUE
*.user_dump_dest='d:\oracle\product\admin\auxdb\udump'

6) Create spfile from pfile.


RMAN> startup force nomount


7) Restore control file from backup

RMAN>restore controlfile from ‘H:\Restore\orabackup\flash_recovery_area\\BACKUPSET\2008_09_09\O1_MF_NCSN0_DAILY_FULL_ONLINE_4DCDWVKM_.BKP’;


RMAN> shutdown
RMAN> exit
C:\> sqlplus
Sql > startup mount;


8) Catalog the RMAN backup directory to new created database .

 

RMAN > CATALOG START WITH 'H:\Restore\orabackup\flash_recovery_area\\BACKUPSET\2008_09_09' NOPROMPT ;


9) Create command to set new names for datafiles/logfile to new location on new server.


Note : You can use the below commands to make the

 SET NEWNAME FOR DATAFILE command and place the same in run command 

select 'set newname for datafile '''||name||''' to '''||replace(name,'Prod_SID','Auxiliary_SID ') ||''';' from v$datafile ;
select 'set newname for tempfile '''||name||''' to '''||replace(name,'Prod_sid','Auxiliary_SID') ||''';' from v$tempfile ;

e.g

select 'set newname for datafile '''||name||''' to '''||replace(name,'remp1','remd1') ||''';' from v$datafile ;
select 'set newname for tempfile '''||name||''' to '''||replace(name, 'remp1','remd1') ||''';' from v$tempfile ;


10) Restore and Recover database from the backup.

run
{  
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK ;
SET NEWNAME FOR DATAFILE 1 TO 'H:\oradata\auxdb\data\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'H:\oradata\auxdb\data\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'H:\oradata\auxdb\data\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'H:\oradata\auxdb\data\DRSYS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'H:\oradata\auxdb\data\TOOLS01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'H:\oradata\auxdb\data\USERS01.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'H:\oradata\auxdb\data\auxdb.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'H:\oradata\auxdb\data\auxdb_02.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'H:\oradata\auxdb\data\auxdb_03.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'H:\oradata\auxdb\data\auxdb_04.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'H:\oradata\auxdb\data\auxdb_05.DBF';
restore database ;
switch datafile all;
set until time "to_date('24-02-2022 09:14:00','dd-mm-yyyy hh24:mi:ss' )";
recover database ;
}


Time need to be put as per backup existing at server.

 Execute the command at RMAN prompt

 Recover database: Obviously recovery is dependent on the available archived (and online) redo logs. we can recover only up to the time of the backup. Further, since this is an incomplete recovery, we will have to open the database with reset logs.


11) Rename the Log files path :

select 'alter database rename file '''||MEMBER||''' to '''||replace(MEMBER,'ghnomst1','ghnomsp1') ||''';' from v$logfile ;



12) Open database with resetlogs as incomplete recovery is performed.
RMAN> alter database open resetlogs;


Need to Disable the block tracking to open the database.

 Snapshot below :


Check the Temp files if not ..kindly create as Prod have or better create backup controlfile and you will get the command to create temp file

 

13) Change the name of the database:

Note : Database is open now with Source Database SID Which need to change the source database Sid

Backup the database.

Mount the database after a clean shutdown:

 

SHUTDOWN IMMEDIATE

STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege.

 oraware->nid target=sys as sysdba dbname=auxdb

Shutdown and open the database with RESETLOGS:

SHUTDOWN IMMEDIATE

Reset the DB_NAME and Instance_name of Refreshed DB in pfile 

STARTUP MOUNT pfile = ‘PATH’

 ALTER DATABASE OPEN RESETLOGS;

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