Partial recovery of Oracle database using RMAN

-

Partial recovery of database using RMAN


For oracle DBA ,Their could be situations where application team can request to restore partial database up to a point in Time.. (Means few application tablespace). In todays blog , We will discuss about how we can resolve such situation.

Assumption is that we are doing partial database restore on the different server then production server.

Below are the steps , We are going to follow for partial recovery:

Step 1) Create pfile from spfile and make modifications as per the storage in New machine like the new disk group name etc.


more inittedtdb.ora
tedtdb.__db_cache_size=16240345088
tedtdb.__java_pool_size=67108864
tedtdb.__large_pool_size=67108864
tedtdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tedtdb.__pga_aggregate_target=12884901888
tedtdb.__sga_target=19327352832
tedtdb.__shared_io_pool_size=0
tedtdb.__shared_pool_size=2415919104
tedtdb.__streams_pool_size=134217728
*._trace_files_public=TRUE
*.audit_file_dest='/u01/app/oracle/admin/tedtdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA_CLONE/tedtdb/controlfile/current.ctl'
*.db_16k_cache_size=33554432
*.db_8k_cache_size=33554432
*.db_block_size=32768
*.db_cache_size=6815744000
*.db_create_file_dest='+DATA_CLONE'
*.db_create_online_log_dest_1='+DATA_CLONE'
*.db_create_online_log_dest_2='+DATA_CLONE'
*.db_domain=''
*.db_file_multiblock_read_count=128
*.db_files=10000
*.db_name='tedtdb'
*.db_recovery_file_dest='/orabackup/flash_recovery_area'
*.db_recovery_file_dest_size=107374182400000
*.db_securefile='ALWAYS'
*.db_writer_processes=4
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tedtdbXDB)'
tedtdb1.instance_number=1
tedtdb2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA_CLONE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_checkpoint_timeout=999999
*.log_checkpoints_to_alert=TRUE
*.log_file_name_convert='+REDO1','+DATA_CLONE','+REDO2','+DATA_CLONE'
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=2000
*.os_authent_prefix=''
*.os_roles=FALSE
*.pga_aggregate_target=12884901888
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=FALSE
*.remote_os_roles=FALSE
*.sec_protocol_error_further_action='DELAY'
*.sessions=1105
*.sga_max_size=19327352832
*.sga_target=19327352832
*.shared_pool_size=2411724800
*.sql92_security=TRUE
tedtdb2.thread=2
tedtdb1.thread=1
*.undo_retention=86400
tedtdb1.undo_tablespace='UNDOTBS1'
tedtdb2.undo_tablespace='UNDOTBS2
db_file_name_convert=',"+DATA_TST'",'+DATA_CLONE'

Step 2 ) Below parameter needs to be changed OR added in pfile:- Only one instance to be started up.


*.cluster_database=false
*.tedtdb1.undo_tablespace='UNDOTBS1'
*.tedtdb2.undo_tablespace='UNDOTBS2
*.db_file_name_convert=',"+DATA_TST'",'+DATA_CLONE'
*.tedtdb1.instance_number=1
*.tedtdb2.instance_number=2
*.db_create_file_dest='+DATA_CLONE'
*.db_create_online_log_dest_1='+DATA_CLONE'
*.db_create_online_log_dest_2='+DATA_CLONE'
*.log_file_name_convert='+REDO1','+DATA_CLONE','+REDO2','+DATA_CLONE'

Step 3) Restore the control file and mount the database.

Step 4) Catalog the backup that is been copied to the machine.

Step 5) To find out mandatory tablespaces:


select distinct tablespace_name from dba_segments where owner in ('SYS','SYSTEM');

Mandatory ones are :- SYSTEM,SYSAUX,UNDO,USERS & the one you want to recover.

To find out the datafiles to be restored and create the set newname command in newly created disk group:

select 'set newname for datafile '''||file#||''' to '''||replace(name,'tedtdb1','+DATA_CLONE') ||''';' from v$datafile where ts#=49;

select 'set newname for tempfile '''||file#||''' to '''||replace(name,'tedtdb','+DATA_CLONE) ||''';' from v$tempfile;

Step 6) Restore Datafiles


run
{
set newname for datafile 88 to '+DATA_CLONE';
set newname for datafile 111 to '+DATA_CLONE';
set newname for datafile 113 to '+DATA_CLONE';
set newname for datafile 183 to '+DATA_CLONE';
set newname for datafile 1 to '+DATA_CLONE';
set newname for datafile 2 to '+DATA_CLONE';
set newname for datafile 5 to '+DATA_CLONE';
set newname for datafile 244 to '+DATA_CLONE';
set newname for datafile 3 to '+DATA_CLONE';
set newname for datafile 4 to '+DATA_CLONE';
set newname for datafile 6 to '+DATA_CLONE';
set newname for datafile 7 to '+DATA_CLONE';
set newname for datafile 8 to '+DATA_CLONE';
set newname for tempfile 1 to '+DATA_CLONE';
set newname for tempfile 2 to '+DATA_CLONE';
set newname for tempfile 3 to '+DATA_CLONE';
set newname for tempfile 4 to '+DATA_CLONE';
set newname for tempfile 5 to '+DATA_CLONE';
restore datafile 88;
restore datafile 111;
restore datafile 113;
restore datafile 183;
restore datafile 1;
restore datafile 2;
restore datafile 5;
restore datafile 244;
restore datafile 3;
restore datafile 4;
restore datafile 6;
restore datafile 7;
restore datafile 8;
restore tempfile 1;
restore tempfile 2;
restore tempfile 3;
restore tempfile 4;
restore tempfile 5;
switch datafile all;
switch tempfile all;
}

Step 7) Recover database until require time

run
{
set until time "to_date('16-11-2021 15:30:00','dd-mm-yyyy hh24:mi:ss')";
recover database SKIP FOREVER TABLESPACE TS_ETLREF_IDX_ALL,TS_BAS_WEBAPP_DTA_ALL,TS_BAS_TXN_DTA_2009,TS_ANL_TXN_IDX_2008,TS_BAS_TXN_DTA_2008,TS_ANL_MST_DTA_ALL,TS_ANL_TXN_DTA_ALL,TS_ANL_MVS_DTA_ALL,TS_ANL_MST_IDX_ALL,TS_ANL_TXN_IDX_ALL,TS_ANL_MVS_IDX_ALL,TS_ANL_TXN_DTA_2011,TS_BAS_TXN_DTA_MINVAL,TS_ANL_TXN_DTA_2010,TS_ANL_TXN_IDX_2009,TS_ANL_TXN_IDX_2010,TS_ANL_TXN_DTA_2009,TS_ANL_TXN_IDX_2011,TS_ANL_TXN_DTA_2008,TS_ANL_TXN_DTA_MAXVAL,TS_ANL_TXN_DTA_MINVAL,TS_BAS_TXN_IDX_2008,TS_BAS_TXN_IDX_2009,TS_BAS_TXN_IDX_2010,TS_BAS_TXN_IDX_2011,TS_SOX_MST_DTA_ALL,TS_BAS_TXN_DTA_2012,TS_BAS_TXN_IDX_2012,TS_BAS_TXN_DTA_MAXVAL,OGG_TBS,TS_BAS_RM_DTA_ALL,TS_BAS_RM_IDX_ALL,TS_ANL_TXN_DTA_2012,TS_ANL_TXN_IDX_2012,TS_BAS_WEBAPP_IDX_ALL,TS_ANL_TXN_DTA_2013;
}

Step 8) open database with resetlogs.


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

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 --datadir=/var/lib
Read more

Oracle 19c New Features

-
Oracle 19c New Features As Oracle Support have made 12.2 version of Oracle ,its has become important for us to upgrade our 12c database to 19c.And before upgrade any database to latest version , We need to know the latest new features of that particular version.  In the current blog , We are going to discuss about new features of the Oracle 19C database : 1)Automatic Indexing  This is one of the important Oracle database 19c New feature. Automatic Indexing is one of the great feature included in 19c . It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload. This feature improves database performance by managing indexes automatically in an Oracle Database. Oracle has provided new package DBMS_AUTO_INDEX for this. This enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements: EXEC DBMS_AUTO_INDEX.CONFIGURE
Read more

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 about
Read more