Wait Events : checkpoint busy waits or archiver busy waits

While such wait events occur  in AlertLog file, one must consider to proceed with ARCHIVER TUNING

1. Check the number of Online Redolog Members and size of the online redo logs. 

Excessive size and the number of online redo log groups will give archiver more time to catch up. Hence Adding more online logs does not help a situation where the archiver cannot keep up with LGWR process.

It can help if there are bursts of redo generation since it gives ARCH more time to average its processing rate over time.

2. In such cases you can add multiple archiver (ARCh) processes

Create 'alter system archive log all'. This will spawn archive processes at some fixed interval may be required. These processes once spawned will assist archiver in archiving any un-archived log in that thread of redo. Once it has been completed, the temporary processes will go away.

3. Evaluate checkpoint interval and frequency

There are several possible actions include adding DBWR processes,  increasing db_block_checkpoint_batch, reducing db_block_buffers. Turning on or allowing async IO capabilities definitely helps alleviate most DBWR inefficiencies.

4. Check OS supportability of asynchronous I/Os

Async reads should help tremendously. Async writes may help if OS supports asynchronous I/Os on file systems.

You can check with your vendor if the current version of your operating system supports async IO to file systems (ufs).

5. Check for system or IO contention.

Check CPU waits and usage, disk  level bottlenecks. Also check operating system manuals for the appropriate commands to monitor system performance.

For example, you can use UNIX  commands such as "sar  5 5 5"  “sar –d ”or "iostat" to identify disk bottlenecks.

Accessing a schema without knowing the password

Most of the times that you may need to logon to a database user / schema owner, to do so emergency maintenance,,  but you don’t know the password.

There is an alternative where you can use alter session set current schema “Schema-Name” ;

Other than this you can use the below process, where you record the current password encryption, change the password, logon and do your maintenance.

Let’s try an example here
      

Create an account:

SQL> conn / as sysdba

Connected.

 

SQL>  create user nikhil identified by mypass1 ;

User created.

SQL> grant connect , resource to nikhil ;

Grant succeeded.

 

Now if I want to change the password, then I should know the ‘Old Password’

SQL> conn nikhil/mypass1 ;
Connected.


SQL> password 

Changing password for NIKHIL

Old password:

Oops..!

In such cases you can use the user$ view under sys user which will give us the encrypted password so that we will preserve the old password

Let’s see..

SQL> conn / as sysdba

Connected.



SQL> select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'NIKHIL';



NAME

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

COMMAND

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

NIKHIL

alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE8

7105AB962A873A59A576E8;FD135DE4875002BA';

Now I will set a temporary password for the account to perform the activity.. Later will set the old password using this encrypted values.

SQL> alter user nikhil identified by demopassword ;

User altered.


SQL> conn nikhil/demopassword ;

Connected.

Now connect as sysdba,  and revert the password using the encrypted values

SQL> conn / as sysdba

Connected.



SQL> alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE87105AB962A873A59A576E8;FD135DE4875002BA';

User altered.



SQL> conn nikhil/mypass1 ;

Connected.

SQL>

Alternatively, we can use the DBMS_METADATA package to get the encryption;

SQL> set long 10000

select dbms_metadata.get_ddl('USER','NIKHIL') command from dual;SQL>



COMMAND

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



   CREATE USER "NIKHIL" IDENTIFIED BY VALUES 'S:2549CDA4335FCEF7814FD9832AD653A9

37AAE87105AB962A873A59A576E8;FD135DE4875002BA'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP"