Dinesh DBA

Error: Errors in file /oracle/app/oracle/diag/rdbms/crrmprd/crrmprd/trace/crrmprd_j000_8759.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_346" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 47214 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201 ORA-06512: at "SYS.DBMS_STATS", line 47204 2019-05-09 00:41:46.984000 +05:30 cause:  Seed database was most likely not created right by package dbms_stats.init_package not being ran.  Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason),  this kind of errors may be seen in alert log when auto job tries to execute.  ...

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process When we are taking archive log backup with delete then got an error. RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16470_842608348.arc thread=1 sequence=16470 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16471_842608348.arc thread=1 sequence=16471 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process Solution : Cause: The archive logs seems that it is not yet applied in the standby database.                         Select sequence# , applied from v$...

In this blog i am going to showing to everyone how we can easily check the whole database size in oracle using simple SQL queries there are different ways to calculates the database size and showing to user what is the exact size of db’s. In the first ways you can check the database size using the below queries. SQL> select round(sum(used.bytes)/1024/1024) "Database size in MB"    2from (select bytes from V$datafile    3  Union all    4  select bytes from V$tempfile    5  union all    6  select sum(block_size*file_size_blks)bytes from V$controlfile     7  union all   8  select bytes from V$log)used; Database size in MB -------------------         2208

Steps to setup RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database.   Step1  Enabling Archive log. See the following link for the First step  Click Here Step2  Create the tablespace and user in catalog database to hold backup information. SQL> CONNECT sys/password@catdb AS SYSDBA Connected. SQL> CREATE TABLESPACE RMAN 2 DATAFILE '/app/oracat/oradata/catdb/rman01.dbf' SIZE 6208K REUSE 3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M ...

Enabling Archive log in RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database. Step1  Enable the archive log in ORCL database. Step 1.1  We need to build the pfile from spfile to add new entries. If you  already have recent pfile, the you do not need to do this step. Login as sys user and execute this to create pfile. create pfile='/u01/app/oraint/product/12.1.0/dbhome_1/dbs/pfile.ora' from spfile; Step 1.2  Once pfile is created, t...

Recover The Table space From Flashback Database Backup was taken of both controlfile and datafile at 10 am. At 11 am Tablespace was created and at 1pm tablespace was dropped. How to Recover the tablespace using cold backup. Solution - If you actually DROPPED the tablespace, the controlfile you're trying to use will have no record of the tablespace. You can recover with Flash database or You will need to do a point in time recovery using a backup controlfile from a time when the tablespace existed, then recover up to the point before the drop. Using Flashback database :- 1. Configure the Oracle Database for Flashback SQL> shutdown immediate; SQL> startup mount SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN; SQL> create tablespace test_restore datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\MYTEST08.DBF' size 5m; Tablespace created. SQL> create table test tablespace test_restore as select level a1 from dual...

Database Clone Using RMAN on same server with connection to Target and Catalog DB. My Environment Operating System : RHEL 6.4 64 Bit DB Version : 12CR1 [12.1.0.1.0] Primary DB Name : ORCL Primary DB Mount Point : /PRIM Clone DB Name : ORATEST Clone DB Mount Point : /TEST Creating Directory Structures: First need to create directory structure for the clone db. Easy way to create the directory structure is copy the source filesystem and change the path according to the clone db requirement and delete the content of the source at clone db side. Copy ORACLE_BASE and ORACLE_HOME from source to clone db. Create directory structure for control files, fast_recovery_area. Create directory structure for audit files. Establishing Connection: Edit listener.ora, tnsnames.ora and pfile copied from source db and change according to clone db. Below is the content of my clone db listener, tnsnames file and pfile. Once everything is changed, copy the clo...