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......