Solution 1 : Just type $lsnrctl status this command will show the location of your listener file. OR By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and the %ORACLE_HOME%\network\admin directory … continue reading
Oracle database consist of datafiles,redo log files, control files and temp files. And sum of all these file is the size of the oracle database. i.e select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 … continue reading
Possbile Solutions: Basically your system.dbf is corrupted / incomplete.. needs recovery1. kill all connects from OS level2. ps -ef |grep pmonans kill the database service that is running…3. Do you have any RMAN backup or archivelogs?If YES – Recover database with the archivelogsif NO – try to recover using all the redo logs..Commnad: recover database … continue reading
Full BackupsA full backup is different from a whole database backup. A full data file backup is a backup that includes every used data block in the file. RMAN copies all blocks into the backup set or image copy, skipping only data file blocks that have never been used. For a full image copy, the … continue reading
SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG) SQL> insert into myobjects select * from myobjects; 919664 rows created. SQL> commit; Commit complete. SQL> select count(*) from myobjects; COUNT(*) ———- 1839328 >>>> need to check this record count after recovery SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST … continue reading
Recovery from complete loss of all online redo log files using RMAN: Database name and version SQL> select instance_name,version from v$instance; INSTANCE_NAME VERSION —————- —————– opsdba 10.2.0.2.0 SQL> select member from v$Logfile; MEMBER ——————————- /u02/ORACLE/opsdba/redo03.log /u02/ORACLE/opsdba/redo02.log /u02/ORACLE/opsdba/redo01.log Lets delete all the redo log files , from the above specified location i.e /u02/ORACLE/ opsdba:/u02/ORACLE/opsdba>rm redo*.log If … continue reading
Point in time recovery using RMAN (until a log sequence number) Recovery Objective SQL> conn scott/tiger Connected. SQL> select count(*) from myobjects; COUNT(*) ———- 249410 Switch a logfile SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. Note the current log sequence number (13) SQL> archive log list Database log mode … continue reading
POINT IN TIME RECOVERY via RMAN Scenario: DBA dropped the tablespace TEST which is important for application. Requirement: We need to restore the database before the TEST tablespace was dropped. Solution: SQL> drop tablespace test including contents and datafiles; Tablespace dropped. DBA realized the mistake; He will refer alert log for the exact timing when … continue reading
RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN Database Details —————— Database Name=OPSDBA Machine Name=ITLINUXDEVBLADE07 DBID=1499754868 (select dbid from v$database) SIMULATING CRASH ———————— opsdba:/u01/ORACLE/opsdba>ls -l total 1948980 drwxr-x— 2 oracle dba 4096 Feb 12 13:35 arch -rw-r—– 1 oracle dba 7389184 Feb 12 13:57 control01.ctl -rw-r—– 1 oracle dba 7389184 Feb … continue reading
RMAN Restore on another machine with different file system – same database name Primary/Target Machine ITLINUXDEVBLADE08 Secondary/Auxiliary Machine ITLINUXDEVBLADE07 Database Name OPSDBA Note For this example, we will be using TSM in order to backup and duplicate the database to the new machine. Hence TSM needs to be configured on both the machines. If you … continue reading
