How To Put Comments Using Spfile

More than once I have been asked, co-workers, or people in a forum if you can put comments on an SPFILE. In the old times it was very usual to comment when changing a parameter (with the date of the change and the old value in case there were performance problems) or when you put some new parameter. Seeing these changes was easy, since the init.ora is at the end a text file. The problem we can have with SPFILE since this is a bianrio file, ofcourse to you can make a strings to SPFILE and see some things. The correct thing is to dump SPFILE to a text file.

So, to put a comment in SPFILE the same alter sentence allows us with the comment clause, look at the example below;

alter system set “optimizer_adaptive_features”=true comment =’21.06.2017 Changed by Antonio NAVARRO’ scope=both sid=’*’;

HTH – Antonio NAVARRO

 

Multiple Alias Comma Separated For A Connect String

In this entry I like show a simple way of use multiple alias for the same connect string, usually you use the following structure

FINANCIAL  =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
         (COMMUNITY= TCPCOM)(PROTOCOL = TCP)(HOST = zeus.world.es)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sapdb)
    )
  )
EMPLOYEES  =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
         (COMMUNITY= TCPCOM)(PROTOCOL = TCP)(HOST = zeus.world.es)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sapdb)
    )
  )
TAX  =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
         (COMMUNITY= TCPCOM)(PROTOCOL = TCP)(HOST = zeus.world.es)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sapdb)
    )
  )

 

But you can get the same result by use comma separated;

FINANCIAL,EMPLOYEES,TAX  =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
         (COMMUNITY= TCPCOM)(PROTOCOL = TCP)(HOST = zeus.world.es)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sapdb)
    )
  )

HTH – Antonio NAVARRO

 

How To Delete A Entry From SPFILE

Last day I updated the spfile, but I made a mistake, I write a non-existing SID, I want wrote IND1 (Instance number one) but wrote IND11. Now I have a entry for instance IND11. This is a typical error.

For remove it from spfile you must to reset the parameter as show below;

alter system reset memory_target scope=spfile sid=’IND11′;

Now I have a nice spfile. Next time I must to be more carefull.

HTH – Antonio NAVARRO.

ORA-19870 ORA-19504 ORA-17502 ORA-15041 When Restoring Backup

I was restoring a backup to Flex ASM instance when I get the next errorstack;

 
channel t4: restoring datafile 00069 to +CLOUD_PROD_DATA_DISKG
channel t4: restoring datafile 00075 to +CLOUD_PROD_DATA_DISKG
channel t4: restoring datafile 00077 to +CLOUD_PROD_DATA_DISKG
channel t4: reading from backup piece dbwebdb_c0s464nj_1_1
channel t1: ORA-19870: error while restoring backup piece dbclout_c1f432tc_1_1
ORA-19504: failed to create file "+CLOUD_PROD_DATA_DISKG"
ORA-17502: ksfdcre:4 Failed to create file +CLOUD_PROD_DATA_DISKG
ORA-15041: diskgroup "CLOUD_PROD_DATA_DISKG" space exhausted

I checked the space. Usually before start the restore look at the filesystem or ASM to check free space. From asmcmp (I like with -p parameter);

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      5116       52                0              52              0             N  ACFS_CLOUD_PROD_TRZ_DG/
MOUNTED  EXTERN  N         512   4096  4194304      1020       52                0              52              0             N  ACFS_CLOUD_PROD_UDD_DG/
MOUNTED  NORMAL  N         512   4096  1048576     30717    21271            10239            5516              0             Y  OCRVTD_DG/
MOUNTED  EXTERN  N         512   4096  4194304     71676    71544                0           71544              0             N  CLOUD_PROD_ARCCLD1_DG/
MOUNTED  EXTERN  N         512   4096  4194304     71676    71548                0           71548              0             N  CLOUD_PROD_ARCCLD2_DG/
MOUNTED  EXTERN  Y         512   4096  4194304    747476   425468                0          425468              0             N  CLOUD_PROD_DATA_DISKG/
MOUNTED  EXTERN  N         512   4096  4194304      5116     4372                0            4372              0             N  CLOUD_PROD_RC1_DG/
MOUNTED  EXTERN  N         512   4096  4194304      5116     4372                0            4372              0             N  CLOUD_PROD_RC2_DG/

Of course, the column Rebal(ance) has value Y, ummm I suppose a new disk was added to the diskgroup but no rebalanced. With the nexts commands you can rebalance all the DG.

Execute the rebalance of my diskgroup;

rebal CLOUD_PROD_DATA_DISKG

To monitoring rebalancing;

lsop

I tryed the restores again with a happy end.

HTH – Antonio NAVARRO

ORA-27102 When starting Database On Solaris

Last morning I got the next error when I was restarting a database;

 
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 6291456000

Oracle try to allocate all memory in an unique chunck but there is not a chunck of this size avaliable. Operating system report this error to Oracle and Oracle show it us;

I have red about it is a bug (20635316) in ML/MOS. You have like workaround the hidden parameter

_use_osm = false

Of course, it is a hidden parameter and you must be carefull with it. Don’t set up in production enviroment.

HTH – Antonio NAVARRO

How To Drop A RAC Database (12c)

In this sample I like show you how to delete a database in RAC mode, it is a little different from standalone.

We need change de cluster database parameter;

show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

exit

Stop the database in all nodes, in this case I have two, you can only have one instance active to drop database, in other case you will get an error;

srvctl stop database -d DB121

Mount in restrict the database like previous step to the delete;

sqlplus / as sysdba
startup mount exclusive restrict

-- Make sure you are deleting the correct database
select instance_name from gv$instance;
SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
DB121_1

drop database;
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

exit

After that remove database from the clusterware;

srvctl remove database -db DB121
Remove the database DB121? (y/[n]) y

Like final step, you must to cleanup other files, manually, like archives, password file,….

HTH – Antonio NAVARRO

I Cannot Delete Into ASMCMD

A coworker ask me about a problem he had when trying delete into the asmcmd. At shell prompt he can deleted without problems but from asmcmd doesn’t. He get the control and interrogation symbol like show below;

 
grid@mortir./grid/home/grid $ asmcmd -p                   
ASMCMD [+] > lsdg dddssss^?^?^?^?                                                                                                                  
ASMCMD-8001: diskgroup 'ddd' does not exist or is not mounted
ASMCMD [+] > exit                                            

The problem here is that the erase is not set correct. He needed to define the stty erase for the backspace key.

 
grid@mortir./grid/home/grid $
grid@mortir./grid/home/grid $ stty erase ^?

HTH – Antonio NAVARRO