Ulfat`s DataBase Blog

Today I could not remember sysman password for database grid control. How carefully may I change sysman password ? Just follow. 1. Stop OMS (go to OMS dir) [oracle@gridrepo bin]$ ./emctl stop oms Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. Stopping WebTier... WebTier Successfully Stopped Stopping Oracle Management Server... Oracle Management Server Successfully Stopped Oracle Management Server is Down 2. Config oms password with -change_repos_pwd parameter [oracle@gridrepo bin]$ ./emctl config oms -change_repos_pwd Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. -change_in_db option not specified. Password not changed in backend. Enter Repository User's New Password : Updating repository password in Credential Store... Repository password in Credential Store updated successfully. ...

A brief post about add||enable||disable service using SRVCTL. [oracle@oralab1 dbhome_1]$ srvctl config database orcl [oracle@oralab1 dbhome_1]$ srvctl config database -d orcl Database unique name: orcl Database name: orcl Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: +DATA/orcl/spfileorcl.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Database instance: orcl Disk Groups: DATA Services: --There is no any service(s). --create new service: SQL> begin 2 dbms_service.create_service('NEWORCL','NEWORCL'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select name, enabled from dba_services; NAME ENA --------------------------------- --------------------------- SYS$BACKGROUND NO SYS$USERS NO NEWORCL ...

After got API for sending SMS and developed packages we tried to send URL, but we faced below known error. SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual; ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1722 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 1 Starting Oracle 11g Oracle introduce FGA (Fine Grained Access) for using UTTL_HTTP, UTL_MAIL etc packages. After reading Oracle notes , we need to create ACL and then assgin it. Let`s check our db registery. SQL> set linesize 400 SQL> col comp_name format a40 SQL> select comp_name, status from dba_registry; COMP_NAME STATUS ---------------------------------------- -------------------------------------------- OWB VALID Oracle Application Express VALID Oracle Enterprise Manager VALID OLAP Catalog VALID Spatial VALID Oracle Multimedia V...

Today suddenly I saw more disk usage. After investigation I found what used more space. [oracle@fc-db-tst1 FPREPROD]$ du -sh * 9.4G alert 4.0K cdump 4.0K hm 4.0K incident 4.0K incpkg 4.0K ir 4.0K lck 3.5M metadata 4.0K metadata_dgif 4.0K metadata_pv 4.0K stage 4.0K sweep 7.2G  trace [oracle@fc-db-tst1 alert]$ adrci ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 08:17:10 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ADR base = "/u01/app/oracle" adrci> show homes ADR Homes: diag/rdbms/fpreprod/FPREPROD adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD: ************************************************************************* ADRID                 SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                       ...

Today I found on my friend`s database some application user`s default tablespace is SYSTEM which is not recommended by Oracle. Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP. SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%'; USERNAME DEFAULT_TABLESPACE APPLWEB SYSTEM APPLDESK SYSTEM APPLTEST SYSTEM Now, use below little bit PLSQL block. BEGIN FOR i IN ( SELECT username FROM dba_users WHERE default_tablespace in ('SYSTEM') and username like 'APP%' ) LOOP EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS'; END LOOP; END; / Check SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%'; USERNAME DEFAULT_TABLESPACE APPLWEB USERS APPLDESK USERS APPLTEST USERS

Today when I want to change max size of datafile I got strange error. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed. After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB). Or you may use 31GB too. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;

There are have a lot of ways to stop/start primary and standby databases. I will show you one of them. We needed to migrate physically servers to new plaza. So, I had to stop all with clean. Parameters: Primary Stanby db_name FCDBPROD FCDBPROD instance_name FCDBPROD FCDBSTND open_mode READ WRITE MOUNTED database_role PRIMARY PHYSICAL STANDBY ip 10.10.10.10 10.10.10.20 Data guard broker was configured. DGMGRL> show configuration Configuration - DGMANAGER   Protection Mode: MaxPerformance   Databases:     FCDBPROD - Primary database     FCDBSTND - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 1. Stop applying. # I do it on Standby side DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF"; 2. Shutdown Primary database [oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD [oracle@fcdbdb ~]$ dgmgrl / DG...