Online DBA

  <<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: TEST_0701 Restore Timestamp: 01-JUL

  <<Back to Oracle RAC Main Page How to recreate GIMR diskgroup oracle RAC CREATE DISKGROUP GIMR NORMAL REDUNDANCY SITE S1 FAILGROUP GIMR_S1 DISK 'AFD:DISK1' SITE S2 FAILGROUP GIMR_S2 DISK 'AFD:DISK2' ATTRIBUTE 'compatible.asm'='12.1.0.0' , 'compatible.rdbms'='12.1.0.0'; CREATE DISKGROUP GIMR * ERROR at line 1: ORA-15018: diskgroup cannot be created ORA-15030: diskgroup name "GIMR" is in use by another diskgroup Reason:  I dropped the disk group GIMR using sqlplus and tried to create it again with the same name ended with above error message  Solution Though the diskgroup does not exists I dropped it again using asmcmd . Created the diskgroup again it worked  ASMCMD>  dropdg -r -f GIMR;

  <<Back to Oracle RAC Main Page How to re-create oracle RAC -MGMTDB database  Verify Status of -MGMTDB  prompt@node1:~/ [+ASM1]  srvctl status mgmtdb Database is enabled Instance -MGMTDB is running on node node1 prompt@node1:~/ [+ASM1] export ORACLE_SID=-MGMTDB prompt@node1:~/ [-MGMTDB] sqlplus "/as sysdba" Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 MGMT_PDB_01                 READ WRITE NO SQL> select comp_id,status from dba_registry; COMP_ID                        STATUS ------------------------------ -------------------------------------------- CATALOG                        VALID CATPROC                        VALID RAC                            OPTION OFF XDB                            VA

  <<Back to Oracle ASM Main Page ASMCMD-9521: AFD is already configured while labeling the disk #export ORACLE_HOME=/u01/app/product/19c/grid #export ORACLE_BASE=/u01/app # $ORACLE_HOME/bin/asmcmd afd_label FRA_01_RAC_001  /dev/dm-10 --init   ASMCMD-9521: AFD is already configured After checking the disk status I found the disk is available to lable #$ORACLE_HOME/bin/asmcmd afd_lslbl /dev/dm-10 No devices to be scanned. The Permission is also fine # ls -lrt /dev/dm-10  lrwxrwxrwx 1 root root 8 Sep 28 10:19 /dev/dm-10  Lets read the header of this disk # $ORACLE_HOME/bin/kfed read /dev/dm-10  kfbh.endian:                          0 ; 0x000: 0x00 kfbh.hard:                            0 ; 0x001: 0x00 kfbh.type:                            0 ; 0x002: KFBTYP_INVALID kfbh.datfmt:                          0 ; 0x003: 0x00 kfbh.block.blk:                       0 ; 0x004: blk=0 kfbh.block.obj:                       0 ; 0x008: file=0 kfbh.check:                           0 ; 0x00c: 0x0000000

  <<Back to DB Administration Main Page  Scheduler Jobs not Running Post DB Patching on Windows  After Database patching it was identified that the scheduled jobs using oracle job scheduler was not running any more Tried to run the job manually was successful  SQL>exec dbms_scheduler.run_job('<JOBNAME>') After some troubleshooting come across that the following tables/view were missing  sys.scheduler$_job_refresh sys.scheduler$_lwjob_refresh sys.scheduler$_window_refresh Solution executing below script created the missing tables which fixed the scheduler issue and all jobs running again SQL>@?/rdbms/admin/catschv.sql

  <<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 FORMAT FOR DEVICE

  <<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 for database (ORCL