To Change agent port in Oracle Enterprise Manager Cloud Control 12C

Agent port is an integral part of Agent target name and EMD_URL property of Agent's monitored targets in Repository tables. Every port change would lead to duplicate targets in Repository and would need manual corrections.

Agent port change is one of the least frequent operations one would do. Using emctl set property can change  EMD_URL property value however Display name remain the same (old name)
But can see the new EMD_URL value with new Portnumber:

Steps to change agent port

$/u03/agent_12c/core/12.1.0.2.0/bin/emctl stop agent  

2. Set  EMD_URL property

$/u03/agent_12c/core/12.1.0.2.0/bin/emctl setproperty agent -name EMD_URL -value https://oracleDBlinux.domain.com:3870/emd/main/
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

3. Verify the properties and targets.xml file

$ grep EMD_URL  /u03/agent_12c/core/12.1.0.2.0/sysman/config/emd.properties
EMD_URL=https://oracleDBlinux.domain.com:3870/emd/main/


Verify modified the EMD_URL value in targets.xml

$ grep -i 'oracle_emd' /u03/agent_12c/core/12.1.0.2.0/sysman/emd/targets.xml
        <Target TYPE="oracle_emd" NAME="oracleDBlinux.domain.com:3870" DISPLAY_NAME="oracleDBlinux.domain.com:3870" ON_HOST="" EMD_URL="https://oracleDBlinux.domain.com:3870/emd/main/" TIMEZONE_REGION="" IDENTIFIER="TARGET_GUID=3472BF9E47A45FGGRGD47AA90073F387"/>

Taregts.xml is not yet modified  



4. Start agent

$/u03/agent_12c/core/12.1.0.2.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ................... started.

$/u03/agent_12c/core/12.1.0.2.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.2.0
OMS Version       : 12.1.0.2.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u03/agent_12c/agent_inst
Agent Binaries    : /u03/agent_12c/core/12.1.0.2.0/
Agent Process ID  : 8734
Parent Process ID : 8645
Agent URL         : https://oracleDBlinux.domain.com:3870/emd/main/
Repository URL    : https://oracleEM_OMS.domain.com:4902/empbs/upload
Started at        : 2013-06-21 13:49:44
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2013-06-21 13:49:55
Last attempted upload                        : 2013-06-21 13:49:55
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 1
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 96.32%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2013-06-21 13:49:54
Last successful heartbeat to OMS             : 2013-06-21 13:49:54
Next scheduled heartbeat to OMS              : 2013-06-21 13:50:54

---------------------------------------------------------------
Agent mis Running and Ready

5. Verify the new port number in EMD_URL property

$/u03/agent_12c/core/12.1.0.2.0/bin/emctl getproperty agent -name EMD_URL
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
EMD_URL=https://oracleDBlinux.domain.com:3870/emd/main/

 On Console Agent Display name will be shown with old name and EMD_URL will be shown with new port#

Editing targets.xml file

Strictly not recommended to edit the targets.xml file. This may block agent or impact agent functionality or agent would become outof sync with repository or upload failure

Converting to cluster database and adding instances

Converting to cluster database and Adding instances :

Source DB :
=========

DB_NAME=orcl
ORACLE_HOME= /data01/app/oracle/product/11.2.0.4/db_1/
SPFILE/PFILE= /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora
ORALCE_SID=ORCL
Cluster DB=FALSE
HOSTNAME=NODE1

Target DB:
========
DB_NAME=orcl
ORACLE_HOME= /data01/app/oracle/product/11.2.0.4/db_1/
SPFILE/PFILE= +DATA
cluster_database=true
orcl2.instance_number=2
orcl1.instance_number=1
Hostname : node1 , node2

Step 1: Check the cluster database parameter for source DB and shu the DB to make changes in pfile:

SQL> show parameter cluster ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database     boolean FALSE
cluster_database_instances     integer 1
cluster_interconnects     string
 
Step 2 : Make the cluster parameter true in the pfile , also add other rac specific parameters as highlighted below:

[oracle@node1 trace]$ cat /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora
*.audit_file_dest='/data01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.269.874539303','+FRA/orcl/controlfile/current.256.874539311'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=3670016000
*.diagnostic_dest='/data01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2' 
*.cluster_database=true
orcl2.instance_number=2
orcl1.instance_number=1
orcl2.thread=2
orcl1.thread=1

Step 3: Startup DB using the modified pfile and Create spfile from pfile ; 

SQL> create spfile='+DATA' from pfile ; 

File created.

you can check the location of spfile create using asmcmd :

ASMCMD> pwd
+DATA/ORCL/PARAMETERFILE
ASMCMD> ls
spfile.264.874545139
ASMCMD>

Step 4: Shutdown and Create pfiles for both instance orcl1 and orcl2 :
Modify pfile to have only entry of spfile as below , SPFILE path should be shared and accessible from both node1 and node2 :

[oracle@node1 trace]$ mv /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl1.ora

[oracle@node1 trace]$ cat /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl1.ora
SPFILE='+DATA/ORCL/PARAMETERFILE/spfile.264.874545139'
[oracle@node1 trace]$
[oracle@node1 trace]$ cat /data01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl2.ora
SPFILE='+DATA/ORCL/PARAMETERFILE/spfile.264.874545139'

Step5 : Startup instance using SPFILE :

SQL>  startup;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2257880 bytes
Variable Size  608177192 bytes
Database Buffers  226492416 bytes
Redo Buffers    2355200 bytes
Database mounted.
Database opened.


SQL> show parameter spfile ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA/orcl/parameterfile/spfile.264.874545139
SQL>

Step 6: Add logfile thread for orcl2 and enable the thread:

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 5 size 50m ;

Database altered.


SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group  6 size 50m;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

Step 7: Add database to cluster using srvctl :

oracle@node1 trace]$ which srvctl
/data01/app/oracle/product/11.2.0.4/db_1/bin/srvctl


[oracle@node1 trace]$ srvctl add database -d orcl -n node1 -o /data01/app/oracle/product/11.2.0.4/db_1/ -p +DATA/orcl/parameterfile/spfile.264.874545139

[oracle@node1 trace]$ srvctl config database -d orcl
Database unique name: orcl
Database name: node1
Oracle home: /data01/app/oracle/product/11.2.0.4/db_1/
Oracle user: oracle
Spfile: +DATA/orcl/parameterfile/spfile.264.874545139
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@node1 trace]$

Step8 : Add instance orcl1 and orcl2 for database orcl :

[oracle@node1 trace]$ srvctl add instance -d orcl -i orcl1 -n node1
[oracle@node1 trace]$ srvctl add instance -d orcl -i orcl2 -n node2

step 9 : Check the database configuration and try to start orcl1 & orcl2 using srvctl :

[oracle@node1 trace]$ srvctl config database -d orcl
Database unique name: orcl
Database name: node1
Oracle home: /data01/app/oracle/product/11.2.0.4/db_1/
Oracle user: oracle
Spfile: +DATA/orcl/parameterfile/spfile.264.874545139
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl1,orcl2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Step 9 : You can start both instance one by one using srvctl and fix any issue if there :

[oracle@node1 trace]$ srvctl start instance -d orcl -i orcl2
[oracle@node1 trace]$ srvctl start instance -d orcl -i orcl2

Step 10: Stop both instance and start using srvctl :

[oracle@node1 trace]$ srvctl stop database  -d orcl
[oracle@node1 trace]$ srvctl start database  -d orcl

[oracle@node1 trace]$ srvctl status database  -d orcl
Instance orcl1 is running on node node1
Instance orcl2 is running on node node2

SPSET to update spfile path

If the spfile path in GPNP profile is incorrect , you can start the ASM instance as follows:

To check the spfile location is gpnp you can use the command as below:

[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-
+DATA/node-cluster/asmparameterfile/registry.253.866681801


1. Create a pfile with one line in it that identifies the path to the spfile

Create a pfile $ORACLE_HOME/dbs/pfileasm.ora with entry :

SPFILE='+DATA/asm/asmparameterfile/asmspfile.ora'

2. Start up the instance using the init pfile :

SQL > Startup pfile='$ORACLE_HOME/dbs/pfileasm.ora' ;

3. After the instance is started you can update the spfile location using SPSET command using ASMCMD :

ASMCMD > spset +DATA/asm/asmparameterfile/asmspfile.ora

The GPnP profile

The GPnP profile is a small XML file located in GRID_HOME/gpnp/<hostname>/profiles/peer under the name profile.xml. It is used to establish the correct global personality of a node. Each node maintains a local copy of the GPnP Profile and is maintanied by the GPnP Deamon (GPnPD) .

/data01/app/11.2.0/grid_11204/gpnp/node1/profiles/peer/profile.xml

[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool get
Warning: some command line parameters were defaulted. Resulting command line: 
         /data01/app/11.2.0/grid_11204/bin/gpnptool.bin get -o-

<?xml version="1.0" encoding="UTF-8"?>
<gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="4" ClusterUId="3ae91d6243b0df45ff7af75dc0ef0fa3" ClusterName="node-cluster" PALocation="">
<gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*">
<gpnp:Network id="net1" IP="192.168.56.0" Adapter="eth0" Use="public"/>
<gpnp:Network id="net2" IP="192.168.10.0" Adapter="eth1" Use="cluster_interconnect"/>
</gpnp:HostNetwork>
</gpnp:Network-Profile>
<orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/>
<orcl:ASM-Profile id="asm" DiscoveryString="/dev/oracleasm/disks*" SPFile="+DATA/node-cluster/asmparameterfile/registry.253.866681801"/>

<ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
 <ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/>
 <ds:Reference URI="">
 <ds:Transforms>
  <ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
  <ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/>
 </ds:Transform>
 </ds:Transforms>
 <ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/>
 <ds:DigestValue>Qo8cEYWiMWje2ivla0Y3iaY85WY=</ds:DigestValue>
 </ds:Reference>
 </ds:SignedInfo>
 <ds:SignatureValue>ZPkyemS5LEm5kDgoYTl8wPdaJzKk3S06nLvfywuRTMlivF75af1GYicSOfYpRdZ675tUU4W2UkdUzKVB2OKkHPIlfVXndxcfMaiyTNBO88hHeXINC3RL0GvabPjtWlVJ9SyAAH3hahInJn7F8ScFedtxtdMGg+IEyQ6gqn6lJiI=</ds:SignatureValue>
</ds:Signature>
</gpnp:GPnP-Profile>

Success.


[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool lfind
Success. Local gpnpd found.
[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool find

Found 2 instances of service 'gpnp'.
 mdns:service:gpnp._tcp.local.://node1:61702/agent=gpnpd,cname=node-cluster,host=node1,pid=3034/gpnpd h:node1 c:node-cluster
 mdns:service:gpnp._tcp.local.://node2:55239/agent=gpnpd,cname=node-cluster,host=node2,pid=3133/gpnpd h:node2 c:node-cluster
[grid@node1 peer]$ 
[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool getpval -asm_spf
Warning: some command line parameters were defaulted. Resulting command line: 
         /data01/app/11.2.0/grid_11204/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-

+DATA/node-cluster/asmparameterfile/registry.253.866681801
[grid@node1 peer]$ /data01/app/11.2.0/grid_11204/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-
+DATA/node-cluster/asmparameterfile/registry.253.866681801

Datafile Resize Script

set linesize 1000 pagesize 0 feedback off trimspool on
with
      hwm as (
      -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
      select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
      from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
     ),
     hwmts as (
      -- join ts# with tablespace_name
      select name tablespace_name,relative_fno,hwm_blocks
     from hwm join v$tablespace using(ts#)
    ),
    hwmdf as (
     -- join with datafiles, put 5M minimum for datafiles with no extents
     select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
     from hwmts right join dba_data_files using(tablespace_name,relative_fno)
    )
   select
    case when autoextensible='YES' and maxbytes>=bytes
    then -- we generate resize statements only if autoextensible can grow back to current size
     '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
      ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
      ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
    else -- generate only a comment when autoextensible is off
     '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
      ||'M from '||to_char(ceil(bytes/1024/1024),999999)
      ||'M after setting autoextensible maxsize higher than current size for file '
      || file_name||' */'
    end SQL
   from hwmdf
   where
    bytes-hwm_bytes>1*1024*1024*1024 -- resize only if at least 1Gb can be reclaimed
   order by bytes-hwm_bytes desc
   /


Query 2 :
========

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum         
      from dba_extents         
      group by file_id) b,
      dba_extents c,
     (select value db_block_size         
      from v$parameter         
      where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
and a.AUTOEXTENSIBLE='YES'
order by a.tablespace_name,a.file_name);

[FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location

SEVERE: [FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location.

Check the installation log file . From the installation log we found :

CAUSE: The installer detects that the storage type of the location (/fst01/storage/ocr) is not supported for Oracle Cluster Registry.
ACTION: Provide a supported storage location for the Oracle Cluster Registry.
*ADDITIONAL INFORMATION:*
 - node2:Mount options did not meet the requirements for this platform
 [Expected ="rw|!ro,hard|!soft,suid|!nosuid,rsize>=32768,wsize>=32768,tcp|proto=tcp,v3|nfsv3|vers=3|nfsvers=3,timeo>=600,acregmin=0&acregmax=0&acdirmin=0&acdirmax=0|actimeo=0" ;
 Found = "rw,nosuid,relatime,vers=3,rsize=32768,wsize=32768,namlen=255,hard,noacl,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=10.11.15.10,mountvers=3,mountport=4046,mountproto=tcp,local_lock=none,addr=10.11.15.10"]
 The problem occurred on nodes: node1,node2
 - Cause: Cause Of Problem Not Available
 - Action: User Action Not Available
 - node2:PRVG-0361 : Incorrect NFS mount options "nosuid" used for "netapp1.nfs.linuxbox.com":"/vol/nodehome" mounted on: "/fst01/storage" The problem occurred on nodes: node1,node2
 - Cause: Cause Of Problem Not Available
 - Action: User Action Not Available
Summary of the failed nodes
node2
node1


Solution : Issue is clearly mentioned in the installation log and recommendation is also included .
So just mount the shared storage with recommended options .

Before   : 
================== 
netapp1.nfs.linuxbox.com:/vol/nodehome /fs01 nfs vers=3,tcp,timeo=600,retrans=2,rsize=32768,wsize=32768,bg,hard,intr,nosuid,noacl 0 0 

Mount with below options to resolve the issue  : 
================== ========= ========= =========  

filer2.nfs.linuxbox.com:/vol/nodehome /fs01 nfs rw,rsize=32768,wsize=32768,hard,bg,nointr,vers=3,tcp,timeo=600,actimeo=0 

Oracle RECYCLEBIN

The recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table.

SQl> Select object_type,object_name from dba_objects where object_name='RECYCLEBIN';

OBJECT_TYPE     OBJECT_NAME
-----------------------------------------------
SYNONYM         RECYCLEBIN


SQL> desc recyclebin
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER


Related recyclebin objects:

SQL> SELECT SUBSTR(object_name,1,50),object_type,owner
FROM dba_objects
WHERE object_name LIKE '%RECYCLEBIN%';
/
SUBSTR(OBJECT_NAME,1,50)                           OBJECT_TYPE         OWNER
---------------------------      ------------------- ------------ ----------
RECYCLEBIN$                                        TABLE               SYS
RECYCLEBIN$_OBJ                                    INDEX               SYS
RECYCLEBIN$_TS                                     INDEX               SYS
RECYCLEBIN$_OWNER                                  INDEX               SYS
USER_RECYCLEBIN                                    VIEW                SYS
USER_RECYCLEBIN                                    SYNONYM             PUBLIC
RECYCLEBIN                                         SYNONYM             PUBLIC
DBA_RECYCLEBIN                                     VIEW                SYS
DBA_RECYCLEBIN                                     SYNONYM             PUBLIC

9 rows selected.


THE RECYCLE BIN

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.

There are no issues with dropping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS owned objects

EXAMPLE

SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> sho user
USER is "BH"

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
  2  FROM user_recyclebin
  3  /
no rows selected

SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
  2  FROM user_recyclebin
  3  /
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                         DROPSCN DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ---------- -------------------
BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1                               DROP      TABLE                     8.1832E+12 2004-03-10:11:03:49

SQL> sho user
USER is "SYS"

SQL> SELECT owner,original_name,operation,type
  2  FROM dba_recyclebin
  3  /

OWNER                          ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BH                             T1                               DROP      TABLE

We can also create a new table with the same name at this point.



PURGING

In order to completely remove the table from the DB and to release the space the new PURGE command is used.

From BH user:
SQL> PURGE TABLE t1;
Table purged.

OR

SQL> PURGE TABLE "BIN$1UtrT/b1ScbgNAgAIKds8A==$0";
Table purged.

From SYSDBA user:
SQL> SELECT owner,original_name,operation,type
  2  FROM dba_recyclebin
  3  /
no rows selected

From BH user:
SQL> SHOW recyclebin
SQL>

 There are various ways to PURGE objects:

PURGE TABLE t1;
PURGE INDEX ind1;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)

For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.

For more information on the PURGE command refer to the 10g SQL Reference

DISABLING RECYCLEBIN

We can DROP and PURGE a table with a single command

From BH user:
SQL> DROP TABLE t1 PURGE;
Table dropped.

SQL> SELECT *
  2  FROM recyclebin
  3  /
no rows selected

There is no need to PURGE.

On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
         Parameter                            Value                   Default?
---------------------------- ---------------------------------------- --------
_recyclebin                  TRUE                                     TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1

From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
         Parameter                            Value                   Default?
---------------------------- ---------------------------------------- --------
_recyclebin                  FALSE                                     TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
no rows selected

There is no need to PURGE.

As with anyother underscore parameter, setting this parameter is not recommended unless
advised by oracle support services.

On 10gR2 and higher; recyclebin is a initialization parameter and bydefault its ON.
We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.

To download oracle software or patch using Wget on Linux box

To download oracle software or patch directly on client’s Linux server you may use the below steps :

1. Add  Cookies.txt Extension in your crome browser. You can get is from crome web store  https://chrome.google.com/webstore/detail/cookiestxt/njabckikapfpffapmjgojcnbfjonfjfg?hl=en
2. One extension  will be added to google crome on top right side of browser .
3. Go to the oracle download page , login using the metalink id and Accept License Agreement .and click on the software binary to start download on your machine .
4. Now capture the cookies by Clicking on Cookies.txt extension . It will show you something like below :

# HTTP Cookie File for domains related to oracle.com.
# This content may be downloaded or pasted into a cookies.txt file and used by wget
# Example:  wget -x --load-cookies cookies.txt http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html
#
www.oracle.com FALSE   /technetwork/database/enterprise-edition/downloads      FALSE   0       testSessionCookie      Enabled
.oracle.com    TRUE    /       FALSE   1425008363     s_nr    1422416363929
.support.oracle.com    TRUE    /       FALSE   1580104749     mos.notifications        false
.oracle.com    TRUE    /       FALSE   1425097299.611 Order_MarketingInfluencer        WWOUSEOGOOGLECOIN
.oracle.com    TRUE    /       FALSE   1425097299.611054        Order_MarketingCampaignSuccess WWOUSEOGOOGLECOIN
.oracle.com    TRUE    /       FALSE   1425097299.611086      p_mcc        WWOUSEOGOOGLECOIN:NA:WWOUSEOGOOGLECOIN
.oracle.com    TRUE    /       FALSE   1737865303     optimizelyEndUserId        oeu1422505303647r0.8225775167811662


5. Just click on the “downloaded” link to download the cookies.txt file on your PC .
6. Copy the cookies.txt file to client’s linux server in the directory where you want to download the oracle software .
7. Issue the below command to download :


[root@linux12c grid_12c]# wget -x --load-cookies cookies.txt http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip

--2015-02-05 13:39:44--  http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip
Resolving download.oracle.com... 64.145.87.72, 64.145.87.11
Connecting to download.oracle.com|64.145.87.72|:80... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://edelivery.oracle.com/akam/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip [following]
--2015-02-05 13:39:44--  https://edelivery.oracle.com/akam/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip
Resolving edelivery.oracle.com... 23.76.230.140
Connecting to edelivery.oracle.com|23.76.230.140|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip?AuthParam=1423161704_f18babafa52442c5aec7ead56289003a [following]
--2015-02-05 13:39:44--  http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zip?AuthParam=1423161704_f18babafa52442c5aec7ead56289003a
Reusing existing connection to download.oracle.com:80.
HTTP request sent, awaiting response... 200 OK
Length: 1747043545 (1.6G) [application/zip]
Saving to: âdownload.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zipâ

100%[=========================================================================================================================================================>] 1,747,043,545 11.0M/s   in 2m 43s

2015-02-05 13:42:35 (10.2 MB/s) - âdownload.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_1of2.zipâ

[root@linux12c grid_12c]# wget -x --load-cookies cookies.txt http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip
--2015-02-05 13:45:11--  http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip
Resolving download.oracle.com... 64.145.87.72, 64.145.87.11
Connecting to download.oracle.com|64.145.87.72|:80... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://edelivery.oracle.com/akam/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip [following]
--2015-02-05 13:45:11--  https://edelivery.oracle.com/akam/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip
Resolving edelivery.oracle.com... 23.76.230.140
Connecting to edelivery.oracle.com|23.76.230.140|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip?AuthParam=1423162031_de94d7ea4c7ca02b6a1986593b23227a [following]
--2015-02-05 13:45:11--  http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zip?AuthParam=1423162031_de94d7ea4c7ca02b6a1986593b23227a
Reusing existing connection to download.oracle.com:80.
HTTP request sent, awaiting response... 200 OK
Length: 646972897 (617M) [application/zip]
Saving to: âdownload.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zipâ

100%[===========================================================================================================================================================>] 646,972,897 4.57M/s   in 2m 5s

2015-02-05 13:47:19 (4.93 MB/s) - âdownload.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_grid_2of2.zipâ

8. Verify the size of files downloaded and checksum : match the cksum with value from oracle website
 
Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.2.0) for Linux x86-64
 
linuxamd64_12c_grid_1of2.zip (1,747,043,545 bytes) (cksum - 1194876808)
 
linuxamd64_12c_grid_2of2.zip (646,972,897 bytes) (cksum - 2519919927)


wget executes under directory /staging/grid_12c/ and Files will be downloaded under : /staging/grid_12c/download.oracle.com/otn/linux/oracle12c/121020

[root@linux12c 121020]# ls -lrth
total 2.3G
-rw-r--r--. 1 root root 1.7G Jul 11  2014 linuxamd64_12102_grid_1of2.zip
-rw-r--r--. 1 root root 618M Jul 11  2014 linuxamd64_12102_grid_2of2.zip
[root@linux12c 121020]#
[root@linux12c 121020]# cksum linuxamd64_12102_grid_1of2.zip
1194876808 1747043545 linuxamd64_12102_grid_1of2.zip
[root@linux12c 121020]# cksum linuxamd64_12102_grid_2of2.zip
2519919927 646972897 linuxamd64_12102_grid_2of2.zip
[root@linux12c 121020]# pwd
/staging/grid_12c/download.oracle.com/otn/linux/oracle12c/121020

Or we can use directly as below :

wget --http-user="myMetalinkID@xyz.com" --http-password="MyPassword" --output-document="p21419221_121020_Linux-x86-64_1of10.zip" "https://updates.oracle.com/Orion/Download/process_form/p21419221_121020_Linux-x86-64_1of10.zip?file_id=81606884&aru=19220015&userid=o-myMetalinkID@xyz.com&email=myMetalinkID@xyz.com&patch_password=&patch_file=p21419221_121020_Linux-x86-64_1of10.zip"

wget --http-user="myMetalinkID@xyz.com" --http-password="MyPassword" --output-document="p21419221_121020_Linux-x86-64_2of10.zip" "https://updates.oracle.com/Orion/Download/process_form/p21419221_121020_Linux-x86-64_2of10.zip?file_id=81606884&aru=19220015&userid=o-myMetalinkID@xyz.com&email=myMetalinkID@xyz.com&patch_password=&patch_file=p21419221_121020_Linux-x86-64_2of10.zip"

Check Presence of particular archivelog in rman backup

RMAN>  list backup of archivelog sequence=4666 thread 1 ;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size
------- ----------
160401  14.26G

  List of Archived Logs in backup set 160401
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    4666    68644635802 30-JAN-15 68655657966 30-JAN-15

  Backup Set Copy #1 of backup set 160401
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:02:08     30-JAN-15       NO         TAG20150130T081019

    List of Backup Pieces for backup set 160401 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    252655  1   EXPIRED     /backup/oracle/Sumit/Sumit_archivelog_ucpu0j77_1_1_20150130

  Backup Set Copy #2 of backup set 160401
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:02:08     03-FEB-15       NO         TAG20150130T081019

    List of Backup Pieces for backup set 160401 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    252672  1   AVAILABLE   /backups/oracle/Sumit/Sumit_archivelog_ucpu0j77_1_1_20150130

Flash Recovery Area Usage

SET PAGESIZE 66;
SET LINESIZE 80;

REPHEADER PAGE CENTER 'Flash Recovery Area Usage';

COLUMN FILE_TYPE FORMAT a20;
COLUMN FILE_TYPE HEADING 'File Type';

COLUMN USED_MB HEADING 'Used MBytes';
COLUMN USED_MB FORMAT 9999999999.99;
COLUMN RECLAIMABLE_MB HEADING 'Reclaimable Mbytes';
COLUMN RECLAIMABLE_MB FORMAT 99999999999.99;

COLUMN NUMBER_OF_FILES HEADING 'Number of files';

BREAK ON REPORT
COMPUTE SUM LABEL 'Totals:' OF USED_MB RECLAIMABLE_MB ON REPORT;

SELECT
  rau.file_type,
  rfd.space_used * rau.percent_space_used / 1024 / 1024 as USED_MB,
  rfd.space_reclaimable * rau.percent_space_reclaimable / 1024 / 1024 as RECLAIMABLE_MB,
  rau.number_of_files as NUMBER_OF_FILES
FROM
  v$recovery_file_dest rfd, v$flash_recovery_area_usage rau;


     Flash Recovery Area Usage

File Type                      Used MBytes Reclaimable Mbytes Number of files
-------------------- ----------- ------------------ -------------------------- ------------------
CONTROL FILE              247.44       .00  1
REDO LOG                       .00        .00  0
ARCHIVED LOG               876.35        29.75   4
BACKUP PIECE               24640.90 .00  2
IMAGE COPY               .00         .00  0
FLASHBACK LOG       .00         .00  0
FOREIGN ARCHIVED LOG      .00          .00  0
    ----------- ----------------------------- ----------------------------- ----------
Totals:                           25764.69      29.75


SQL>  select name ,SPACE_LIMIT/1024/1024/1024  SPACE_LIMIT_GB , SPACE_USED/1024/1024/1024 SPACE_USED_GB , ceil((space_used/space_limit)*100) used_prc from  v$recovery_file_dest   ;

SPACE_LIMIT_GB SPACE_USED_GB   USED_PRC

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

           225    161.083057         72