create user without C## in the 12c container database

In oracle to create a normal user/profiles in oracle 12c container database. we need to set the session to below so that we can create a normal user...
alter session set "_ORACLE_SCRIPT"=true; 

then try to create a user. this time it will work...


Hope this helps....

generating large number of *.aud file under Grid_Home().. (12c and 11g)

Recently, what we observed was.. the audit files under grid_home were generating too many.

GRID_HOME/rdbms/audit....

By default in 11g the audit_sys_operations is "FALSE" but as you upgrade/go to 12C then this is "TRUE"

Having said that, this will be logging all the audit activity when ever something log's in... so we need to set this to "FALSE"

>   alter system set audit_sys_operations=FALSE" scope=spfile.  --- this need a bounce..

once this is done.... this will slow down the audit info writing to the files.... but it will still generate the files... 

 as per oracle..this is a BUG....

Large Number of Audit Files are getting generated in 12c Oracle Restart (Doc ID 2049448.1)

so we need to apply... this doesn't require any bounce of crs....

1     crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)" –unsupported    (for 12c)

1      crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)"  (for 11g)...

       monitor the directory for the change....

      

       Hope this Helps.....

   

   

    

Renaming or moving the controlfiles in oracle

1) sqlplus in to the database and get the location of the current control files...
     SQL> show parameter control_files;
2) make the desired directory structure where you want to move the controlfiles.
3) then in sqlplus, set the new location for the control files. AND Create a Pfile from existing SPFILE before making any CHANGES.
   SQL> ALTER SYSTEM SET control_files='+Apt/CONTROLFILE/control01.ctl','+TEMP/CONTROLFILE/control02.ctl' SCOPE=SPFILE;

4) shutdown the database;
5) start the database in nomount;
6) In another window, start rman ... rman target /
7) restore the control files to the above mentioned controlfiles from step 3 with a  current control file from old location from step 1.
  rman target /
restore controlfile to '+Apt/CONTROLFILE/control01.ctl'' from 'Apt/TESTDB/controlabc123';
restore controlfile to '+TEMP/CONTROLFILE/control02.ctl' fromn 'TEMP/TESTDB/controlabc123';

8) open the database in Mount.
9) open database and validate the changes.



Hope this Helps .....

Renaming or moving tempfile in oracle(ASM/Non-ASM)

1) sqplus to the database and make sure the Tempfiles that you want to rename or change the location.
   SQL> select name from v$tempfile;
2) create a pfile before making the changes.
3) shutdown the database.
4) make the directory structure where you want to move the temp files..
5) start the database in Mount.
    SQL> startup mount
6) alter database rename file '+APT/TESTDB/TEMPFILE/tempabc' to '+TEMP/TESTDB/TEMPFILE/temp01.dbf'

for filesysystem also you can follow the same procedure. just give the location that you want to.

7) open the database and make sure the temp file is created at the desired location.



In 12C:-

Below are the steps to move the Temp files… for container / PDB.

set linesize 200

col file_name for a60

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

1)

Create a tempfile of a decent size.

alter tablespace TEMP add tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' size 2G;

2)

take the tempfile offline which you want to move.

alter database tempfile '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

3)

Copy the tempfile in the ‘+ASM’ to the desired location. Note create the folders in TEMP directory.

cp '+DATIND/TESTDBC/TEMPFILE/TEMP.441.943798245' '+TEMP/TESTDBC/TEMPFILE/temp01.dbf'

4)

Rename the tempfile you already moved.

alter database rename file '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' to '+TEMP/TESTDBC/TEMPFILE/temp01.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

5)

Make the tempfile online again.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temp01.dbf' online;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Note:- do these steps for other tempfiles in the temp(do 2/3/4/5 steps).

Once done drop the created tempfile in step 1.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

alter tablespace TEMP drop tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Hope this helps......

how to setup oracle (11.XX)(using No ASM and ASM) manually...using Oracle Binaries tar'd

Ø  This Doc will help you to install same version of oracle on any new server using the oracle Binaries where oracle is already installed on other servers...

Ø  First we need to take a tar of oracle home binaries of db home and grid home (if using ASM) from the already installed oracle which is in functional use.

Ø  Move the tar copies to the new server.

Ø  Make sure you don’t have any old oracle references on the New machine. Remove all the oracle related files under /etc(ls –ltr ora*).

Ø  Untar the binaries where you want your Oracle Home need to be… DB_home and Grid_Home(if ASM is used).

Ø  Once they are untar’d in respective home.

Ø  If on AIX, run the “SLIBCLEAN” should be run as root user.

Ø  Run the rootpre.sh from the grid_home/clone/rootpre.sh (untar location).

Ø  Run the below command for the GRID_HOME(if asm is used)

Ø  perl ./clone/bin/clone.pl  -silent  ORACLE_BASE=/usr/home/oracle  ORACLE_HOME=/usr/home/oracle/product/11.2.0.4/grid ORACLE_HOME_NAME=Ora11g_gridinfrahome1  INVENTORY_LOCATION=/usr/home/oraInventory

Ø  Once the above cloning process is completed with no issues/errors. Run the below commands as root user.

/usr/home/oraInventory/orainstRoot.sh

/usr/home/oracle/product/11.2.0.4/grid/root.sh

The 2^nd command root.sh will provide you another script to run the generated log file.

/usr/home/oracle/product/11.2.0.4/grid/perl/bin/perl –I /usr/home/oracle/product/11.2.0.4/grid/perl/lib  –I  /usr/home/oracle/product/11.2.0.4/g

rid/crs/install  /usr/home/oracle/product/11.2.0.4/grid/crs/install/roothas.pl

Make sure you don’t have any errors…

Ø  This completes Grid Home setup.

Ø  Now, we need to setup ASM. For this you atleast need one disk. (Not needed if not using ASM).

Ø  Run the below command to do so… with appropriate passwords and disks and diskgroopname.

Ø  asmca -silent -configureASM -sysAsmPassword SYSASMPASSWORD –asmsnmpPassword ASMSNMPPASSWORD -diskString '/dev/rhdisk*' -diskGroupName DISKGROUPNAME -disk DISKNAME -redundancy EXTERNAL

Ø  Create other disk groups now if you have any.

Ø  Make sure you have everything up and if needed create listener also. Create spfile files and other steps you need here.

Ø  Once the above piece is completed. Now we are ready for the Database Piece. Go to the untar’d DB_home location

Ø  perl ./clone/bin/clone.pl -silent ORACLE_BASE=/usr/home/oracle ORACLE_HOME=/usr/home/oracle/product/11.2.0.4/db_1 ORACLE_HOME_NAME=Ora11g_home1 INVENTORY_LOCATION=/usr/home/oraInventory

Ø  once done, run the below command as root user

                                       /usr/home/oracle/product/11.2.0.4/db_1/root.sh

Ø  This completes the setup of DB_Home.

Ø  Now create a database from the backup of any database or you can use dbca to create one…

*************************Hope this Helps*******************************************

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor In Oracle 12C

Just came across about this new listener-to-sid issue when we upgraded the oracle DB to 12c version.
initially when we were using  oracle verison 11 i did not see this issue. As we moved to oracle 12c i saw application is behaving weidly with below error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

solution:- 
we need to add an entry in the listener.ora file and reload the listener to take effect.

USE_SID_AS_SERVICE_LISTENER_NAME=ON



Hope this Helps......

how to get table count for all tables in a schema/user using simple methods ......

Getting a table count might be looking simple but if you want to get table count for all tables in the database..which is time consuming...
you can get this by below methods.... use as you think is better..

1) you can get the count of all tables using dba_tables using NUM_ROWS. but this will not give right count as this tables gets updated with the dictionary job/stats job update only....
select table_name,num_rows from dba_tables where owner='ABC' order  by table_name;


2)  you can write a small sql generated sql to get the statements and spool the output.
set heading off;
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
from user_tables
order by table_name;

3) you can use pl/sql query to get the correct count of all tables at any given time....it directly queries the table as you do... you need to be in that specific schema.

set SERVEROUTPUT ON
DECLARE
sql_stmt varchar2(256);
v_b varchar2(100);
cursor c_f is SELECT TABLE_NAME from DBA_TABLES where owner = 'ABC';
  v_a DBA_TABLES.TABLE_NAME%type;

BEGIN
open c_f;
  loop
    fetch c_f into v_a;
    exit when c_f%notfound;

sql_stmt :='SELECT count(*)  from '|| v_a ||'';

execute immediate sql_stmt into v_b;
--execute immediate sql_stmt into tab_count;
---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';
--DBMS_OUTPUT.PUT_LINE ( ' || abc.table_name ||  ',' tab_count ');

DBMS_OUTPUT.PUT_LINE ( v_a   || ': ---> ' ||    v_b );

  END LOOP;
END;
/


use below script if above doesnt work. you can run this as sys owner too..



set SERVEROUTPUT ON size 1000000
set feedback on echo off verify off
DECLARE
sql_stmt varchar2(256);
tab_count varchar2(100);

cursor c_f is (SELECT owner,TABLE_NAME from DBA_TABLES where owner = '&owner');
  v_a DBA_TABLES.TABLE_NAME%type;

BEGIN
for abc in c_f
  loop
   
sql_stmt := '(SELECT count(*)  from  '||abc.owner||'.'||abc.table_name||')';
execute immediate sql_stmt into tab_count;

DBMS_OUTPUT.PUT_LINE ( ''||abc.table_name||'' || ': ------>   ' ||  ''||tab_count||'');
  END LOOP;
END;
/






Hope this helps and saves some time....