Online DBA

  <<Back to Oracle RAC Main Page Cloning Oracle RAC Database to Single Instance Database Existing DB ORCL with 2 node (ORCL11 and ORCL12) Closed DB: ORCLN Step1: Add Static Listener Entry Add Static Listener Entry in GRID_HOME/network/admin/listener.ora and reload the listener  SID_LIST_LISTENER =   (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = ORCLN)      (ORACLE_HOME = /ora_app/product/19c/dbd1)      (SID_NAME = ORCLN)     )   )  lsnrctl reload LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-FEB-2026 09:14:16 Copyright (c) 1991, 2023, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) The command completed successfully [+ASM1] lsnrctl status |grep -i ORCLN Service "ORCLN" has 1 instance(s).   Instance "ORCLN", status UNKNOWN, has 1 handler(s) for this service... Step2:Add TNS entry for ORCLN add...

  <<Back to Oracle Backup & Recovery Main Page Restoring/Recovering  Table  from RMAN Backup oracle 19c Point In Time table Restore/Recovery in Oracle Today I had a situation where application team accidently deleted data from a table in prod database and wanted to restore it from backup. The requirement was to restore the table with different name washout touching the original table so that the application team can compare it and find out the different eventually deleted rows Before 12.2 the task was complicated and it was achieved by restoring the full database on 3rd server and export  the table from restored database and import it in original database. Oracle has now automated the procedure and made it quite easy. Now you just have to run one command and the rest is taken care by oracle Scenarios Container DB Name: CDBDB Pluggable DB Name: PDB1 Table  to be restored: TEST Schema/Owner of the table: EMPLOYEE New Table Name After Restore:...

  <<Back to Oracle Backup & Recovery Main Page After connecting to target database using RMAN you can run show all to display the default RMAN configuration. As you see Backup and recovery environment is preconfigured and the configuration is persistent across the restart  Showing  Default RMAN Configuration localhost:~/ [ORCL1P] rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Fri May 14 11:24:12 2021 Version 18.13.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL1P (DBID=4215484517, not open) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL1P are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP...

  <<Back to Oracle Backup & Recovery Main Page Starting RMAN and connecting to Database Starting RMAN and connecting to Database To start RMAN you need to set the environment and type rman and press enter. You can connect to database either using connect command or using command line option. using command line option localhost:$ export ORACLE_HOME=/ora_app/product/18c/dbd2 localhost:$ export PATH=$ORACLE_HOME/bin:$PATH localhost:$ export ORACLE_SID=ORCL1P localhost:$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 4 08:11:01 2021 Version 18.11.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL1P (DBID=4215484517) RMAN> using connect option localhost:$ rman RMAN> connect target sys@ORCL1P  target database Password:******** connected to target database: ORCL1P (DBID=4215484517) NOTE: To use connect command you need to ensure that  you have proper TNS sentry...

  <<Back to Oracle Backup & Recovery Main Page Introduction to RMAN RMAN (Recovery Manager) is a backup and restore utility provided by oracle.  To start rman you need to set the environment and type rman and press enter as shown below export ORACLE_HOME=<ORACLE_INSTALLATION_DIR_LOC> export PATH=$ORACLE_HOME/bin:$PATH RMAN related terminologies  Lets understand some components in reference with RMAN utility • A target database A target database is a database on which RMAN is performing backup and recovery operations. RMAN always maintains metadata about its operations on a database in the control file of the database known as RMAN repository. • The RMAN client Its the executable that interprets commands, directs server sessions to execute those commands, and records its activity in the target database control file. The RMAN executable is automatically installed with the database and is located in $ORACLE_HOME/bin directory • A fast recovery area (FRA) A di...

  <<Back to Oracle Backup & Recovery Main Page Cause: Previous channel not released Solution: Just restart from rman session and try again  RMAN> exit rman target / and try again ................... RMAN> run { allocate channel ch1 device type disk; BACKUP INCREMENTAL FROM SCN 3459834 DATABASE FORMAT '/ora_work/rman/ORCL1P/ForStandby_%U' tag 'FORSTANDBY'; } allocated channel: ch1 channel ch1: SID=50 device type=DISK Starting backup at 04-MAR-21 using channel ORA_SBT_TAPE_1 released channel: ORA_SBT_TAPE_1 released channel: ORA_SBT_TAPE_2 released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 03/04/2021 09:12:53 ORA-19568: a device is already allocated to this session

<<Back to Oracle Backup & Recovery Main Page How to Restore and Recover a Database on a New Host with a Different Directory Structure from Tape backup Step1> Install oracle software (same version as old host) on new host To perform binary installation you can refer below post Oracle 12c Software Only Installation in Silent Mode Step2> Apply required patches Step3> set environmental variables export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome1 export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=ORCL1P Step4> Create initfile create init file under $ORACLE_HOME/dbs with db_name parameter in it. cat $ORACLE_HOME/dbs/initORCL1P.ora db_name=ORCL1P Step5> invoke rman as follows invoke RMAN connect to target and catalog database. set dbid you want to restore rman target /  catalog rman/xxxxxx@catalogdb RMAN> set dbid=915313462; Step6> restore spfile as pfile run { allocate  channel ch1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=??_db....

<<Back to Oracle Backup & Recovery Main Page LGWR: Minimum of 1 LGWR standby database required- RMAN Restore After database restore when tried to open , encountered below error. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 17034 Session ID: 387 Serial number: 7 Checking at Alterlog LGWR: Minimum of 1 LGWR standby database required Errors in file /ora_log/diag/rdbms/shfus1pp/SHFUS1P/trace/SHFUS1P_lgwr_17009.trc: ORA-16072: a minimum of one standby database destination is required Sun Jan 17 11:14:29 2021 ARC1 started with pid=20, OS id=17046 LGWR (ospid: 17009): terminating the instance due to error 16072 Sun Jan 17 11:14:30 2021 System state dump requested by (instance=1, osid=17009 (LGWR)), summary=[abnormal instance termination]. System State dumped to trace file /ora_log/diag/rdbms/shfus1pp/SHFUS1P/trace/SHFUS1P_diag_16999_20210117111430.trc Dumping diagnostic data in directory=[cdmp_2021011...

<<Back to Oracle Backup & Recovery Main Page RMAN-06808: SECTION SIZE cannot be used when piece limit is in effect Cause:  MAXPIECESIZE and SECTION SIZE Parameter can not be used together. Solution: Use either MAXPIECESIZE or SECTION SIZE  

<<Back to Oracle Backup & Recovery Main Page ORA-19505: failed to identify file "": RMAN Duplicate Cause: Missing directory structure on auxiliary database server Solution: Identify all missing directories and create them. All the directory structures for data files on target systems must be available on auxiliary server as well. On Target  SQL> select name from v$datafile; data/ORCL1P/dbfile/SYSTEM_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/SYSTEM_01.DBF data/ORCL1P/dbfile/SYSAUX_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/SYSAUX_01.DBF data/ORCL1P/dbfile/UNDO_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/UNDO_01.DBF data/ORCL1P/dbfile/USERS_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/SYSTEM_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/SYSAUX_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/UNDO_01.DBF .. .. On Auxiliary  mkdir -p data/ORCL1P/dbfile/ mkdir -p data/ORCL1P/dbfile/pdbseed/ORCL1P/ mkdir -p data/ORCL1P/dbfile/PDB01/ORCL1P/ ... .. Starting backup at 07-JUL-20 channel prmy1: startin...

<<Back to Oracle DataGuard Main Page Recover Standby Database from non Default Archive Location You can use below command to start recovering the standby database from non default archive location. The command is useful if you restored/copied the archive logs to a non default location due to space constraints    SQL> RECOVER FROM '/ora_arch_stage/ORCL1P/arc1' STANDBY DATABASE;

<<Back to Oracle DataGuard Main Page How to Use RMAN Recovery Catalog in a Data Guard Configuration If you decided to use recovery catalog, both databases must use the same recovery catalog. You just need to register the current Primary database into recovery catalog and perform backup from either Primary (Site A) or Standby (Site B) while connecting to Recovery catalog. Resync the controlfile with catalog to make backup (taken at Site A ) available at Site B and vice-versa That's said let us see how to do that and what RMAN configuration parameter we must use Step1> Connect to rman catalog and primary database rman target sys/xxxx@Primary_DB catalog rman/xxxx@CAT_DB Step2> Register the database in Recovery catalog RMAN> register database; Step3> Configure Connect Identifier for Primary and Standby database RMAN> configure db_unique_name ORCL1PP connect identifier 'ORCL1PP.oracle.com'; RMAN> configure db_unique_name ORCL1PS connect iden...