Oracle RAC  Restrict Mode ...

Oracle Database restricted mode 

[oracle@mrac2 ~]$

[oracle@mrac2 ~]$ srvctl status database -d mrac

Instance mrac1 is running on node mrac1

Instance mrac2 is running on node mrac2

[oracle@mrac2 ~]$ srvctl status database -d mrac -v

Instance mrac1 is running on node mrac1. Instance status: Open.

Instance mrac2 is running on node mrac2. Instance status: Open.

[oracle@mrac2 ~]$ srvctl stop database -d mrac

[oracle@mrac1 ~]$ srvctl status database -d mrac  -v
Instance mrac1 is not running on node mrac1
Instance mrac2 is not running on node mrac2

[oracle@mrac2 ~]$ srvctl start database -d mrac -o restrict

[oracle@mrac2 ~]$

[oracle@mrac2 ~]$ srvctl status database -d mrac -v

Instance mrac1 is running on node mrac1. Instance status: Restricted Access.

Instance mrac2 is running on node mrac2. Instance status: Restricted Access.

set head off verify off echo off pages 150 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select distinct

'DATABASE_NAME..............................................: '|| NAME             ,

'INSTANCE NAME..............................................: '|| INSTANCE_NAME    ,

'LOG_MODE...................................................: '|| LOG_MODE         ,

'OPEN MODE..................................................: '|| OPEN_MODE        ,

'INSTANCE_NAME..............................................: '|| INSTANCE_NAME    ,

'HOSTNAME...................................................: '|| HOST_NAME        ,

'STATUS.....................................................: '|| STATUS           ,

'LOGINS.....................................................: '|| LOGINS           ,

'STARTUP-TIME...............................................: '|| STARTUP_TIME     ,

'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,

'GUARD STATUS...............................................: '|| GUARD_STATUS     ,

'FLASHBACK ON...............................................: '|| FLASHBACK_ON     ,

'PROTECTION MODE............................................: '|| PROTECTION_MODE  ,

'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE

from gv$instance i, v$database d

-- where 1=1  

-- and i.inst_id=d.inst_id

order by 2;

set head on verify on echo on feedback on

DATABASE_NAME..............................................: MRAC

INSTANCE NAME..............................................: mrac1

LOG_MODE...................................................: NOARCHIVELOG

OPEN MODE..................................................: READ WRITE

INSTANCE_NAME..............................................: mrac1

HOSTNAME...................................................: mrac1.localdomain

STATUS.....................................................: OPEN

LOGINS.....................................................: RESTRICTED

STARTUP-TIME...............................................: 31-12-2016 11:07:26

DATAGUARD BROKER...........................................: DISABLED

GUARD STATUS...............................................: NONE

FLASHBACK ON...............................................: NO

PROTECTION MODE............................................: MAXIMUM PERFORMANCE

CONTROLFILE TYPE...........................................: CURRENT

DATABASE_NAME..............................................: MRAC

INSTANCE NAME..............................................: mrac2

LOG_MODE...................................................: NOARCHIVELOG

OPEN MODE..................................................: READ WRITE

INSTANCE_NAME..............................................: mrac2

HOSTNAME...................................................: mrac2.localdomain

STATUS.....................................................: STARTED

LOGINS.....................................................: ALLOWED

STARTUP-TIME...............................................: 31-12-2016 12:01:27

DATAGUARD BROKER...........................................: DISABLED

GUARD STATUS...............................................: NONE

FLASHBACK ON...............................................: NO

PROTECTION MODE............................................: MAXIMUM PERFORMANCE

CONTROLFILE TYPE...........................................: CURRENT

set linesize 200

col HOST_NAME for a30

SQL> SQL> col STATUS for a20

SQL> SQL> select instance_name, host_name, archiver, thread#, decode(STATUS,'STARTED','STARTUP***NOMOUNT',status) status,LOGINS, STARTUP_TIME from gv$instance ;

Oracle datafile info

Oracle datafile and other Imp file information ..

Datafile Info 

file info ...

datafile info 

COLUMN  today NOPRINT New_Value strToday

SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;

COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName

SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;

TTITLE LEFT     '______________________________________________________________________________________________________________________' -

SKIP 2 CENTER   strToday -

SKIP CENTER     'File Report (all physical files)' -

SKIP CENTER     strDatabaseName -

SKIP LEFT       '______________________________________________________________________________________________________________________' -

SKIP LEFT       ''

SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'

COLUMN filename        FORMAT a80                    HEADING 'Filename'

COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'

COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'

COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'

COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'

BREAK ON report

COMPUTE sum OF filesize  ON report

SELECT /*+ ordered */

    d.tablespace_name                     tablespace

  , d.file_name                           filename

  , d.bytes/1024/1024                     filesize_mb

  , d.autoextensible                      autoextensible

  , (d.increment_by * e.value)/1024/1024  increment_by

  , d.maxbytes/1024/1024                  max_mb

FROM sys.dba_data_files d , v$datafile v

  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e

WHERE  (d.file_name = v.name)

UNION

SELECT

    d.tablespace_name                     tablespace 

  , d.file_name                           filename

  , d.bytes/1024/1024                     filesize_mb

  , d.autoextensible                      autoextensible

  , (d.increment_by * e.value)/1024/1024  increment_by

  , d.maxbytes/1024/1024                  max_mb

FROM  sys.dba_temp_files d

  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e

UNION

SELECT

    '[ ONLINE REDO LOG ]'

  , a.member

  , b.bytes/1024/1024

  , null

  , TO_NUMBER(null)

  , b.bytes/1024/1024

FROM  v$logfile a, v$log b

WHERE   a.group# = b.group#

UNION

SELECT

    '[ CONTROL FILE    ]'

  , a.name

  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  v$controlfile a

union

SELECT

    '[ BLOCK TRACKING]'||a.status

  , a.filename

  , TO_NUMBER(null)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  V$BLOCK_CHANGE_TRACKING a

union 

SELECT

    '[ FLASHBACK DATABASE]'

  , a.name

  , a.bytes/1024/1024

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  V$FLASHBACK_DATABASE_LOGFILE a

union

SELECT

    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')

  , a.DISPLAY_VALUE

  , TO_NUMBER(null)

  , null

  , TO_NUMBER(null)

  , TO_NUMBER(null)

FROM  sys.v_$parameter a WHERE name = 'spfile'

ORDER BY 1,2

/
========================================================

On ASM !!!!!!!!!!!!!!!!!!!

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set linesize 300 pagesize 300
col full_alias_path for a80
col system_created for a15
col alias_directory for a15
col FILE_TYPE for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,  system_created, alias_directory, file_type,CREATION_DATE
from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory,c.type file_type,c.CREATION_DATE
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
where alias_directory = 'N'
-- and file_type ='ARCHIVELOG'
-- and file_type ='DATAFILE'
-- and file_type ='TEMPFILE'
-- and file_type ='PARAMETERFILE'
-- and file_type ='CONTROLFILE'
-- and file_type ='ONLINELOG'
-- and file_type ='PASSWORD'
-- and file_type in ( 'ARCHIVELOG','DATAFILE','TEMPFILE','PARAMETERFILE','CONTROLFILE','ONLINELOG','PASSWORD')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        -- and a.name = 'DATABASENAME'
                )
connect by prior rindex = pindex;
=====

----- with PDBS Datafiles 

COLUMN  today NOPRINT New_Value strToday
SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT  /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT 
   v.PDB_NAME||'.'|| d.tablespace_name    tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.cdb_data_files d , DBA_PDBS v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.CON_ID = v.PDB_ID)
union
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/


===============
---- pluggable datafile !!!!

COLUMN  today NOPRINT New_Value strToday
SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT  /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT 
   v.PDB_NAME||'.'|| d.tablespace_name    tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.cdb_data_files d , DBA_PDBS v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.CON_ID = v.PDB_ID)
union
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/

====================

alter session set nls_date_format='dd-mon-rr hh24:mi';

undefine tablespace_name

set pages 500 lines 250 term off

col fn new_value fname

select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;

col file_name           for a60

col AUTOEXTENSIBLE      for a18

col BIGFILE             for a15

col ENCRYPTED           for a10

col TABLESPACE_NAME     for a20  ---

col COMPRESS_FOR        for a10

col file_name         for &fname   heading "File Name"

select file_id, b.block_size, a.tablespace_name,a.file_name, trunc(a.bytes/(1024*1024*1024),2) size_gb, trunc(a.maxbytes/(1024*1024*1024),2) max_gb , a.autoextensible

,bigfile

--,encrypted,compress_for

,contents,a.status,creation_time 

from dba_data_files a ,dba_tablespaces b ,v$datafile v

where 1=1  

and a.tablespace_name=b.tablespace_name

and a.file_id=v.file#

--and file_id=31

-- and a.tablespace_name in ('UNDOTBS1')

-- and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name'))

order by 2;

==============

set linesize 300 pagesize 300

col Datafile_name for a50 

SELECT   t.tablespace_name "Tablespace", 'Datafile' "File Type",

         t.status "Tablespace Status", d.status "File Status",

         ROUND ((d.max_bytes - NVL (f.sum_bytes, 0)) / 1024 / 1024) "Used MB",

         ROUND (NVL (f.sum_bytes, 0) / 1024 / 1024) "Free MB",

         (d.bytes/1024/1024) file_mb,

         (d.maxbytes/1024/1024) MaxMB,

         t.initial_extent "Initial Extent", t.next_extent "Next Extent",

         t.min_extents "Min Extents", t.max_extents "Max Extents",

         t.pct_increase "Pct Increase",

         d.file_name "Datafile_name",

         d.file_id,

d.autoextensible

    FROM (SELECT   tablespace_name, file_id, SUM (BYTES) sum_bytes

              FROM dba_free_space

          GROUP BY tablespace_name, file_id) f,

         (SELECT   tablespace_name, file_name, file_id, MAX (BYTES) max_bytes, bytes, maxbytes,

                   status, autoextensible

              FROM dba_data_files

          GROUP BY tablespace_name, file_name, file_id, bytes, maxbytes, status, autoextensible) d,

         dba_tablespaces t

   WHERE t.tablespace_name = d.tablespace_name

     AND f.tablespace_name(+) = d.tablespace_name

     AND f.file_id(+) = d.file_id

     AND t.tablespace_name like upper(nvl('%&tbsp_name%',t.tablespace_name))

     AND d.file_name like nvl('%&file_name%',d.file_name)

order by 1;

 

Tablespace                     File Typ Tablespac File Stat    Used MB    Free MB    FILE_MB      MAXMB Initial Extent Next Extent Min Extents Max Extents Pct Increase Datafile_name                                         FILE_ID AUT

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

USERS                          Datafile ONLINE    AVAILABLE      21747       4676    26422.5 32767.9844          65536                       1  2147483645              +DATA/iirac/users01.dbf                                     7 YES

====

select distinct substr(name, 1, instr(name, '/',-1)) PATH

from (

select name from v$datafile

union all

select NAME from v$controlfile

union all

select MEMBER name from v$logfile

union all

select name from v$tempfile

union all

SELECT FILENAME FROM V$BLOCK_CHANGE_TRACKING

union all

select name from v$flashback_database_logfile

) order by 1;

set pagesize 0

SELECT 'D,' || c.NAME || ':' || t.NAME || ':' || c.dbId  || ',' || d.STATUS || ',' || d.ENABLED || ',' || TO_CHAR(d.BYTES) || ',' || 

TO_CHAR(d.BYTES - NVL(ff.fbytes,0)) || ',' || TRIM(' ' FROM d.NAME) || ',' || TRIM(' ' FROM d.FILE#) AS PDB_TS_DF 

FROM v$datafile d, v$tablespace t, v$CONTAINERS c,(SELECT f.CON_ID CON_ID, f.file_id file_id, SUM(f.bytes) fbytes 

FROM CDB_FREE_SPACE f GROUP BY f.file_id,f.CON_ID) ff 

WHERE c.CON_ID = d.CON_ID and d.CON_ID = t.CON_ID 

and d.TS#=t.TS# AND ff.file_id (+)= d.FILE#  

ORDER BY PDB_TS_DF;

Moving SPFILE from file system to ASM (+DATA)

[oracle@oraasm11g ~]$ srvctl config database -d anuj

Database unique name: anuj

Database name: anuj

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile:                          ------- No spfile in configration 

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Database instance: anuj

Disk Groups: DATA

Services:

[oracle@oraasm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 27 19:43:16 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, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /dbhome_1/dbs/spfileanuj.ora

full path !!

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora

SQL> create spfile='+DATA' from spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora'

                           *

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora';  ----this is not a pfile 

create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora'

*

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00123: invalid character 0 found in the input file

This error was expected !!!! ( there is no command to create  from spfile to spfile )

SQL> create pfile from spfile;

File created.

SQL> !ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

total 40

-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r-----. 1 oracle asmadmin   24 Jun 12  2016 lkVIHAAN

-rw-r-----. 1 oracle oinstall 1536 Jun 12  2016 orapwvihaan

-rw-r-----. 1 oracle oinstall   39 Jun 12  2016 initvihaan.ora

-rw-r-----  1 oracle oinstall 1536 Dec 25 16:27 orapwanuj

-rw-r-----  1 oracle oinstall   24 Dec 25 16:28 lkANUJ

-rw-rw----  1 oracle oinstall 1544 Dec 27 18:59 hc_anuj.dat

-rw-rw----. 1 oracle asmadmin 1544 Dec 27 19:40 hc_vihaan.dat

-rw-r-----  1 oracle oinstall 2560 Dec 27 19:41 spfileanuj.ora

-rw-r--r--  1 oracle asmadmin  917 Dec 27 19:48 initanuj.ora <<<<<--- 

Now creating pfile to spfile in diskgroup ...

SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initanuj.ora' ;

File created.

to check the path !! go to diskgroup 

[root@oraasm11g ~]# su - grid

[grid@oraasm11g ~]$ asmcmd -p

ASMCMD [+] > pwd

+

ASMCMD [+] > ls -s

Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

   512   4096  1048576     15358    11438                0           11438              0             N  DATA/

ASMCMD [+] > cd data

ASMCMD [+data] > ls -s

Block_Size  Blocks  Bytes  Space  Name

                                  ANUJ/

                                  ASM/

                                  VIHAAN/

ASMCMD [+data] > cd ANUJ

ASMCMD [+data/ANUJ] > ls -s

Block_Size  Blocks  Bytes  Space  Name

                                  BACKUPSET/

                                  CONTROLFILE/

                                  DATAFILE/

                                  ONLINELOG/

                                  PARAMETERFILE/

                                  TEMPFILE/

ASMCMD [+data/ANUJ] > cd PA*

ASMCMD [+data/ANUJ/PARAMETERFILE] > pwd

+data/ANUJ/PARAMETERFILE

ASMCMD [+data/ANUJ/PARAMETERFILE] > ls -s

Block_Size  Blocks  Bytes    Space  Name

       512       5   2560  1048576  spfile.278.931722561

or 

from find command  

 ASMCMD [+] > find +data sp*

+data/ANUJ/PARAMETERFILE/spfile.278.931722561

su - oracle 

[oracle@oraasm11g ~]$ srvctl modify database -d anuj -p +DATA/ANUJ/PARAMETERFILE/spfile.278.931722561

[oracle@oraasm11g ~]$ srvctl config database -d anuj

Database unique name: anuj

Database name: anuj

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/ANUJ/PARAMETERFILE/spfile.278.931722561

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Database instance: anuj

Disk Groups: DATA

Services:

===============

http://anuj-singh.blogspot.com/2023/    --- Oracle Create spfile from memory

Oracle oradebug

SQL> !ps -e -o pcpu,user,pid,args | sort -k 1 | grep oracle
0.0 oracle 15876 -bash
0.0 oracle 15902 sqlplus
0.0 oracle 15978 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0.0 oracle 15982 ora_w000_orcl
0.0 oracle 16387 /bin/bash -c ps -e -o pcpu,user,pid,args | sort -k 1 | grep oracle
0.0 oracle 16389 sort -k 1
0.0 oracle 16390 grep oracle
0.0 oracle 29333 /opt/app/oracle/product/11.2/bin/tnslsnr LISTENER -inherit -----
0.0 oracle 5867 -bash
0.0 oracle 6002 ora_pmon_orcl
0.0 oracle 6004 ora_vktm_orcl
0.0 oracle 6008 ora_gen0_orcl
0.0 oracle 6010 ora_diag_orcl
0.0 oracle 6012 ora_dbrm_orcl
0.0 oracle 6014 ora_psp0_orcl
0.0 oracle 6016 ora_dia0_orcl
0.0 oracle 6018 ora_mman_orcl
0.0 oracle 6020 ora_dbw0_orcl
0.0 oracle 6022 ora_lgwr_orcl
0.0 oracle 6024 ora_ckpt_orcl
0.0 oracle 6026 ora_smon_orcl
0.0 oracle 6028 ora_reco_orcl
0.0 oracle 6030 ora_mmon_orcl
0.0 oracle 6032 ora_mmnl_orcl
0.0 oracle 6034 ora_d000_orcl
0.0 oracle 6036 ora_s000_orcl
0.0 oracle 6101 ora_arc0_orcl
0.0 oracle 6103 ora_arc1_orcl
0.0 oracle 6105 ora_arc2_orcl
0.0 oracle 6107 ora_arc3_orcl
0.0 oracle 6117 ora_qmnc_orcl
0.0 oracle 6137 ora_q000_orcl
0.0 oracle 6139 ora_q001_orcl
0.0 oracle 6143 ora_cjq0_orcl
0.0 oracle 6685 ora_smco_orcl
0.0 root 15872 su - oracle
0.0 root 5859 su - oracle
100 oracle 16388 ps -e -o pcpu,user,pid,args

SQL> oradebug setospid 29333 ---- for LISTENER
ORA-00075: process "Unix process pid: 29333, image: " not found in this instance

SQL> oradebug setospid 6101
Oracle pid: 20, Unix process pid: 6101, image: oracle@apt-amd-02 (ARC0)

SQL> oradebug event 10046 trace name context off;
Statement processed.

SQL> oradebug TRACEFILE_NAME
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc0_6101.trc

SQL> oradebug suspend
Statement processed.

SQL> oradebug resume
Statement processed.

SQL> oradebug event 10046 trace name context off;
Statement processed.

connect to a pluggable database without password .. 

connect to a pluggable database without password 

This note explains how one can configure SEPS ( Secure External Password Store) to connect to a pluggable database. 

This is useful when creating passwordless connections for expdp. 

on sqlnet.ora

Example ...

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY =<wallet location directory>)

)

)

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

---- 

WALLET_LOCATION = (SOURCE =(METHOD = FILE) (METHOD_DATA = (DIRECTORY =/u01/app/oracle/wallet)))

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

 mkstore -wrl . -create

Oracle Secret Store Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:

mkstore -wrl "/u01/app/oracle/wallet" -createCredential vihaan anuj vihaan

mkstore -wrl "<wallet location directory>" -createCredential <Pluggable DB service name> <username> <password>

mkstore -wrl "/u01/app/oracle/wallet" -createCredential vihaan anuj vihaan

Oracle Secret Store Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Create credential oracle.security.client.connect_string1

on tnsnames.ora

Example ..

<Pluggable DB service name> =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port#>))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = <Pluggable DB SID>)

)

)

vihaan =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = vihaan)

      (SERVER = dedicated)

    )

  )

[oracle@ora-prim wallet]$ tnsping vihaan

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-DEC-2016 17:40:47

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

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vihaan) (SERVER = dedicated)))

OK (10 msec)

[oracle@ora-prim admin]$ sqlplus /@vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 17:41:57 2016

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

Last Successful login time: Thu Dec 08 2016 17:34:26 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

expdp /@vihaan dumpfile=INT1.DMP directory=anujdir logfile=INT1.LOG tables='INTERVAL_SALES' CONSISTENT=Y  ESTIMATE=STATISTICS reuse_dumpfiles=y

Export: Release 12.1.0.2.0 - Production on Thu Dec 8 18:20:00 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2016-12-08 18:20:00', 'YYYY-MM-DD HH24:MI:SS')"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "ANUJ"."SYS_EXPORT_TABLE_01":  /********@vihaan dumpfile=INT1.DMP directory=anujdir logfile=INT1.LOG tables=INTERVAL_SALES flashback_time=TO_TIMESTAMP('2016-12-08 18:20:00', 'YYYY-MM-DD HH24:MI:SS') ESTIMATE=STATISTICS reuse_dumpfiles=y

Estimate in progress using STATISTICS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

.  estimated "ANUJ"."INTERVAL_SALES":"P0"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P1"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P2"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P3"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P261"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P262"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P263"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P264"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P265"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P266"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P267"          4.683 KB

Total estimation using STATISTICS method: 51.51 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "ANUJ"."INTERVAL_SALES":"P0"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P1"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P2"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P3"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P261"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P262"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P263"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P264"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P265"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P266"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P267"          7.812 KB       1 rows

Master table "ANUJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for ANUJ.SYS_EXPORT_TABLE_01 is:

  /home/oracle/INT1.DMP

Job "ANUJ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 8 18:21:07 2016 elapsed 0 00:00:51