Setting “ADMIN_RESTRICTIONS_listener_name=on” disables the runtime
modification of parameters in listener.ora.
Got error message “TNS-12508: TNS:listener could not resolve the COMMAND given” when trying to switch off listener log.
LSNRCTL> set log_status off Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1521))) TNS-12508: TNS:listener could not resolve the COMMAND given LSNRCTL>
Read more of this content when you subscribe today.
User password can be restored back to its original one by using password hash value.
The password is unknown, and it is encrypted in application configurations. User doesn’t want to change it at the moment.
$ oerr ora 28002
28002, 00000, "the password will expire within %s days"
// *Cause: The user's account is about to expire and the password
// needs to be changed
// *Action: change the password or contact the DBA
//
SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE
from dba_users
where username='TESTUSER';
USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE
---------------- -------------------- ---------------- ---------
TESTUSER EXPIRED(GRACE) 18-MAR-17
Get hash password value from USER$.
SQL> select password from user$ where name='TESTUSER'; PASSWORD -------------------- 512795B1F6AC27B8
Reset the password with the original one:
SQL> alter user testuser identified by values '512795B1F6AC27B8'; User altered.
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
from dba_users
where USERNAME='TESTUSER';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
-------------- --------------- -----------
TESTUSER OPEN 14-MAY-17
It is a good practice for DBA to periodically collect stats of fixed objects, data dictionary, SYS and SYSTEM schemas,
The following query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database:
SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1
Also it uses huge temp space :
SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024/1024
from V$TEMPSEG_USAGE
order by BLOCKS desc ;
USERNAME TABLESPACE BLOCKS*8/1024/1024
---------- ------------------------------- ------------------
DBSNMP TEMP 30.649414
It is the exact same issue as per (Doc ID 2201982.1). so the following actions are taken :
SQL> exec dbms_stats.gather_fixed_objects_stats;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats ('SYS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
Finally flush the cursor out of memory for next running :
SQL> select address, hash_value,PLAN_HASH_VALUE, executions,
loads, version_count, invalidations, parse_calls
from v$sqlarea
where sql_id='dx4nqvbtu06bx';
ADDRESS HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS
---------------- ---------- --------------- ---------- -----
VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
00000001418FA048 4087355773 1896464546 213 5
1 1 213
SQL> exec dbms_shared_pool.purge('00000001418FA048,4087355773','C');
PL/SQL procedure successfully completed.
SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads,
version_count, invalidations, parse_calls
from v$sqlarea
where sql_id='dx4nqvbtu06bx';
no rows selected
Have a check of the new execution time. We can see the new execution plan is created, and the execution time is only 0.25 second.
SQL> select address, hash_value,PLAN_HASH_VALUE, executions,
ELAPSED_TIME,ELAPSED_TIME/executions/1000000 TimePerExecution
from v$sqlarea
where sql_id='dx4nqvbtu06bx';
ADDRESS HASH_VALUE PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME
---------------- ----------- --------------- ---------- ------------
TIMEPEREXECUTION
----------------
00000001418FA048 4087355773 75529090 2 504858 .252429
We know how to flash all shared pool by :
SQL>alter system flush shared_pool;
Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.
Read more of this content when you subscribe today.
To start managed recovery process (MRP) with real time apply ( RTA):
11c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
12c: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;
This warning will be seen in alert log if we issue the same command in 12c as we have done in 11c:
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Instead we can use the following command to eliminate the WARNING:
SQL> alter database recover managed standby database disconnect nodelay; Database altered.
Make DBA Life Easy 