Create Control file manually - Oracle

When to create control files ?

=>  Create control files in situations when :

1. You have lost all your control files.
2. When you want to rename your database name (db_name);

Note :although we can use utility called DBNEWID to change the DBNAME. DBNEWID can be used to change :

-- Only the DBID of a database
-- Only the DBNAME of a database
-- Both the DBNAME and DBID of a database

 How to create control files.

You need a create controlfile script for recreating control files.
Code:
SQL*Plus: Release 11.2.0.0 - Production on Fri Feb 28 17:04:00 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
 Enterprise Edition Release 11.2.0.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options


SQL> alter database backup controlfile to trace;

Database altered.

SQL>

This will create a trace file in the udump directory.

In my case it was

E:\oracle\admin\ORCL\udump\orcl_ora_20327.trc

Edit the file to point the path of the datafiles and redologfiles.

Code:
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 14
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
  GROUP 2 'C:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
  GROUP 3 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
DATAFILE
  'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'C:\ORACLE\ORADATA\ORCL\INDX01.DBF',
  'C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
CHARACTER SET WE8MSWIN1252
;

ALTER DATABASE OPEN RESETLOGS;

and rename it to control_file.sql

Then startup the database in nomount mode and run the control_file.sql file as sys as sysdba user

Code:
SQL*Plus: Release 11.2.0.0 - Production

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area   80812648 bytes
Fixed Size                   453224 bytes
Variable Size              54525952 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL> @C:\oracle\ORCL\udump\control_file.sql

Control file created.


Database altered.

SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\ORADATA\ORCL\CONTROL03.CTL


To rename the database change reuse to set in the create control file script as shown below

Code:
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 14
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
  GROUP 2 'C:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
  GROUP 3 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
DATAFILE
  'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'C:\ORACLE\ORADATA\ORCL\INDX01.DBF',
  'C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
CHARACTER SET WE8MSWIN1252
;

ALTER DATABASE OPEN RESETLOGS;

CREATE ORACLE DATABASE SERVICE

dbms_service.create_service :  

We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name.

oracle@LINUX201:[~] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME

---------- ----------------------------------------------------------------

         4 ORCL

         1 SYS$BACKGROUND

         2 SYS$USERS

************************************************************

LEST CREATE A NEW DB SERVICE 

************************************************************

SAMPLE :

BEGIN

DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',

 network_name=>'QPDEV.WORLD'); (or)  network_name=>'QPDEV');

 END;

 /

SQL> begin

 dbms_service.create_service('ORCLTEST','ORCLTEST');

end;

/

PL/SQL procedure successfully completed.

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME

---------- ----------------------------------------------------------------

         4 ORCL

         1 SYS$BACKGROUND

         2 SYS$USERS

SQL> begin

 DBMS_SERVICE.START_SERVICE('ORCL');

end;

/  2    3    4

PL/SQL procedure successfully completed.

************************************************************

CHECK IF THE SERVICE IS CREATED AND STARTED

************************************************************

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME

---------- ----------------------------------------------------------------

         7 ORCLTEST

         4 ORCL

         1 SYS$BACKGROUND

         2 SYS$USERS

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls

samples  shrept.lst  tnsnames.ora

************************************************************

LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:

************************************************************

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCLTEST)

    )

  )

************************************************************

LETS TEST IT :

************************************************************

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))

OK (10 msec)

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Trigger to disable create objects in database starting with TMP or BAK

---- DISABLE TABLE NAMES STARTING WITH TMP OR BAK in Database ----

Intially :

create table tmp_test (fname varchar2(20));

After enabling below trigger, no more tables can be created in database starting with TMP or BAK:

create or replace TRIGGER NO_TMP_TABS_TRIG
BEFORE CREATE
ON DATABASE

DECLARE
 x user_tables.table_name%TYPE;
BEGIN
  SELECT ora_dict_obj_name
  INTO x
  FROM DUAL;

  IF SUBSTR(x, 0, 4) = 'TMP_' or SUBSTR(x, 0, 4) = 'BAK_' THEN
    RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%');
  END IF;
END NO_TMP_TABS_TRIG;


Lets test it :

create table tmp_test (fname varchar2(20));


Error :

Error starting at line : 15 in command -
create table tmp_test (fname varchar2(20))
Error at Command Line : 15 Column : 1
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%.

Active Session History (ASH) performed an emergency flush

ARC1: STARTING ARCH PROCESSES COMPLETE
ARC2: Becoming the heartbeat ARCH
Sun Jun ** 10:46:08 ****

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 134217728 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;



SQL> select total_size/1024/1024,awr_flush_emergency_count from v$ash_info;

TOTAL_SIZE/1024/1024 AWR_FLUSH_EMERGENCY_COUNT
-------------------- -------------------------
                 128                         1


This is not a dynamic paramter :

sql > alter system set "_ash_size"=200M scope=spfile;

SQL> select total_size/1024/1024,awr_flush_emergency_count from v$ash_info;

TOTAL_SIZE/1024/1024 AWR_FLUSH_EMERGENCY_COUNT
-------------------- -------------------------
                 128                         1


Note : This will require a DB restart to take the above changes effective.

Oracle Table Monitoring - DML ACTIVITY

Monitoring tracks the approximate number of deletes, updates & inserts operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. 

Enabling table monitoring :

create table TEST as select OBJECT_name from all_objects;
table TEST created.

select count(*) from test;

  COUNT(*)
----------
    120118


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  Enable FLUSH_DATABASE
  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  anonymous block completed

  ENABLE MONITORING ON TABLE:

  alter table TEST monitoring;

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  insert into ABCC (select * from ABCC);

  delete from TEST where rownum<1000;

    select count(*) from test;

    COUNT(*)
----------
    119119


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

  anonymous block completed

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES TRUNCATED TIMESTAMP
------------------------------ ---------- ---------- ---------- --------- ---------
TEST                                    0          0        999 NO        18-JUN-1



Note :

Starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

create shared disks (vdi) on Virtualbox for ASM - vboxmanage command line

                                        vboxmanage command line 

For our scenario :

Lets create shared disks (vdi) on Virtualbox for ASM setup. 

If you are getting error like below make sure you have virtual-box set in your environmental path:

In my case I have to add "C:\Program Files\Oracle\VirtualBox\" to my path. To add it to your environmental path goto :

Startup >> computer (right click) >> properties >> advanced system settings >> environmental variables >>  

1. Click on start
2. Right click on Computer
3. Click Properties
4. Click Advanced system settings
5. Click Environment Variables
6. Go to system variables
7. find Path and edit it
8. add your file location at the end of the box.

Add your virtual box installed path, usually c\programe files \ oracle

Now test the vboxmanage cmd :

You will get something like this if you have any virtual machines created.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\arvind>vboxmanage list vms
"linux1" {69b544d2-bf64-4f83-a525-a7f2dd2007e7}


Now  lets create a shared asm drive for ASM :

Note : Always pick a different location, but make sure they are outside the existing VM directory.

$ mkdir C:/VirtualBox/
$ cd C:/VirtualBox/
$
$ # Create the disks and associate them with VirtualBox as virtual media.

VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed

C:\Virtualdisks>VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI
 --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: c8a5ef46-7304-4ea0-b685-e1efbf6ab41e

$ # Connect them to the VM.

VBoxManage storageattach linux1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asm1.vdi --mtype shareable


$ # Make shareable.

$ VBoxManage modifyhd asm1.vdi --type shareable

We now have shared disks added to Virtual box.

We can also add these shared disks via Graphical interface :

See below for instructions:


Step 1 :

Select >> add hard disks >>

Step 2:


select vdi

Step 3


select fixed size

Step 4

it will be added now.

Step 5


select (virtual box top left) File >> virtual Media manager >> select disk you want to modify >> right click >> click modify

Step 6


Select modify and close.

Note : 

If you are going to use this for RAC setup. make sure you attach this shared disks to both of your environments.

Oracle Database files to be excluded in Anti-virus or any third party tools scan

Oracle Database files that needed to be excluded in Anti-virus scan or any third party tools scan:

If you do not exclude these files, there is maximum chance that your oracle files will be held into lock, while the scan is happening and oracle might have access on them when this scan happens and it might lead to i/o exceptions. In fact this is applicable to any Database system.
This might also lead to chances where your Database system might crash.


Always exclude these files :

Data files   Data files generally have a *.dbf extension
Example :

....\oracle\oradata\*.dbf

Redo files   Redo files have a *.log extension

NOTE: Redo logs will exist if the Oracle Development toolkit or backup and recovery are used)  
Example :

....\oracle\Inventory\logs\*.log

Control files   Control files have a *.ctl extension
Example :

....\oracle\oradata\*.ctl

YOU CAN GET THE PATHS FROM BELOW QUERIES :

select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;
show parameter control_files;
show parameter log_archive_dest;
show log_archive_format;


Note : If you are writing audit files to server then exclude *.aud:

show parameter audit

IMPORTANT:  

Most Anti-virus companies does not advise excluding entire directories (such as the Oracle database directory and sub directories) from scanning as this poses a potential high security risk. Additionally you should not exclude any temp files or folders as these can be a target for security risks.

References :

McAfee :

https://kc.mcafee.com/corporate/index?page=content&id=KB54817

Norton :

https://support.symantec.com/en_US/article.TECH134383.html

LAST DDL change time on a table in ORACLE

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details. 

While you can also query the same details from user_objects/dba_objects.

Lets create a table test1

sql > create table test1 (id number);

table TEST1 created.


sql >  select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL        
-------------------------------------------------    ---------------------     --------------
TEST1                              TABLE                     05-06-15 15:54:37        05-06-15 15:54:37 


Now lets try adding a column to table.

sql > alter table test1 add (id1 number);

table TEST1 altered.

sql > 
select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME      OBJECT_TYPE         CREATED                  LAST_DDL        
---------------------   ----------------------  ---------------------        -------------------
TEST1                       TABLE                         05-06-15 15:54:37          05-06-15 15:55:14 

We can see that Oracle now keeps track of latest DDL changes. It can give you an accurate time_stamp with above query.