wmiprvse.exe started again during Oracle Patching

wmiprvse.exe started again & again during Oracle Patching

During apply the Oracle patching on window Server, I am getting the following process error wmiprvse.exe while killing from TASKKILL process it started again and again in fraction of time.

I am not able to apply patch it will started with in second after every kill with taskkill command. I tried to stop the Window Management Service from Services.msc window but it’s not able to stop and giving following error:

WMIerror.

Following are the steps I taken to applied the oracle patching.

1. I tried with taskkill to kill the wmiprvse.exe from services.msc window but in my case it not worked the process start again & again.

taskkill /f /IM wmiprvse.exe

2. Tried to stop the service of Window Management Instrumental in Services.msc but its not worked give me following error.

WMIerror

3. Following are the steps i followed to apply patch if first two step not worked in my case.

A. Paused the Window Management Instrumental Service in services.msc

windowmanagementinstruental

B. On separate command prompt window, run the TASKKILL command for killing the wmiprvse.exe process its hanged until Window Management Instrumental Service is stopped in services.msc

taskkill /f /IM wmiprvse.exe

C. Then i start apply the oracle patching process. It hanged after sometime on following screen shot. I highlighted the hanged row as shown below:

OPATCH APPLY

error

D. To resume oracle patching, I need to RESUME the Window Management Instrumental Service for one second then pause by right click.

resumeWMI

E. After PAUSE the WMI service, you will find that Oracle patching is going to start applying patch during patching keep PAUSE the Window Management Instrumental Service.

F. Command prompt which having taskkill command is executed during resume the window management service and kill the new process.

G. After patch successfully applied, RESUME the Window Management Instrumental Service.

Advertisements

RMAN-03009: failure of Control File and SPFILE autobackup

RMAN-03009: failure of Control File and SPFILE autobackup

While running the database error following error occurred:
ERROR:

RMAN-03009: failure of Control File and SPFILE Autobackup command on ch00 channe
l at 08/16/2018 04:53:13
ORA-19504: failed to create file "E:\RMAN\ORA_CFC-2809772908-20180816-00.CTL"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

SOLUTION
Check auto backup is on for the control file. If yes then check the location in second command is this location exists in Operating system.
If not needed then switch off the autobackup otherwise change the location to the exisiting one in operating system.

Show all setting and check the following two lines:

RMAN> Show all;

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\rman\ora_cf%F.ctl';

Set the following:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\rman\ora_cf%F.ctl';

Override the OBSOLETE with KEEP Clause in RMAN backup

Override the OBSOLETE option with KEEP Clause in RMAN backup

Suppose you have configure the retention policy for the backup is 1 days then the backup is deleted after one with delete noprompt obsolete commands after 1 days.
If you want to override the policy you need to mention in backup command with keep clause as:

KEEP UNTIL TIME 'SYSDATE+10' -- for 10 days
KEEP FOREVER -- For forever time limit

Example:
Suppose you are using these followings commands as database backup and archive log back in one command. And retention is 1 days.
And you want to keep archive log backup more number of days like 5 days then you mention archive backup with KEEP clause to keep more than database backup.


---Keep archive log backup upto 5 days

crosscheck backup;
crosscheck archivelog all;
Backup database;
Backup keep until time 'sysdate+5' archivelog all;
backup current controlfile;
delete noprompt obsolete;
delete archivelog until time 'sysdate-5';

Change this KEEP option after backup as follows:

change backup tag full_archive_backup01 keep until time 'sysdate+182';

change backup tag full_archive_backup01 nokeep;

Error
RMAN-06516: time specified in KEEP UNTIL clause must be after today

Cause:
Following Error occurred when you mention sysdate – 5 in command when you execute.

Solution:
It explain keep expire data above the today date means sysdate+5 otherwise use change command for nokeep backup option if you donot need backup.

Manually restart the dispatcher

Manually restart the dispatcher

Oracle Dispatcher in alert log is in hanged state. I manually restart the dispatcher with the following commands.

Handle following error in alert log:
–found dead dispatcher ‘D002’
–Waiting for dispatcher ‘D000’ to shutdown
–ORA-00603: ORACLE server session terminated by fatal error
–ORA-00600: internal error code, arguments: [kjpcre2], [], [], [], [], []

Check the status of dispatcher from following views:

select name, status ,accept ,network from v$dispatcher;

Example:
SQL> select name, status ,accept ,network from v$dispatcher;
NAME STATUS ACC NETWORK
---- ------ --- --------------------------------------------------------
D000 WAIT NO (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=61831))

Note: Dead dispatcher output is status = refuse and accept = NO it means dispatcher is dead.PMON process automatically handle the Dead dispatcher process it restart it when such a condition is arise. You can also do it manually to shutdown the dead processes explicitly. PMON will restart it automatically

Manually down the dispatcher if PMON is not handle

-- Shutdown the dispatcher 'D000'
SQL> alter system shutdown immediate 'D000';
System altered.

--Check the status of dispatcher after shutdown it.
SQL> select name, status ,accept ,network from v$dispatcher;
NAME STATUS ACC NETWORK
---- --------- --- ---------------------------------------------------------
D000 TERMINATE NO (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=61831))

--After few seconds it will show no dispatcher is running:
SQL> select name, status ,accept ,network from v$dispatcher;
no rows selected

Check the default parameter settings

SQL> show parameter dispatcher
NAME TYPE VALUE
---------------- -------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=XEXDB)
max_dispatchers integer

Start the dispatcher process manually if PMON is not started it
Note: We used scope in memory so it does not effect your default setting in spfile when system reboot it goes to default setting.

-- For single dispatcher
ALTER SYSTEM SET DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(CONNECTIONS=500)(HOST=IXC.oracle.com))(SERVICE=XEXDB)" SCOPE=MEMORY;

--Run multiple dispacter than add parameter dispatcher with value means no of dispatcher started
ALTER SYSTEM SET DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(CONNECTIONS=500)(HOST=IXC.oracle.com))(DISPATCHERS=5)(SERVICE=XEXDB)" SCOPE=MEMORY;

Check status of multiple dispatcher started

SQL> select name, status ,accept ,network from v$dispatcher;

NAME STATUS ACC NETWORK
---- ------ --- ---------------------------------------------------------------
D000 WAIT YES (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=60413))
D001 WAIT YES (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=60414))
D002 WAIT YES (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=60415))
D003 WAIT YES (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=60419))
D004 WAIT YES (ADDRESS=(PROTOCOL=tcp)(HOST=IXC.oracle.com)(PORT=60420))

Modify column datatype in Oracle table

Modify column datatype in Oracle table

Modify the table column Datatype

alter table table_name modify column_name datatype;

Example: We will take an example of TEST1 table in database

SQL> desc test1;
Name Null? Type
------------ -------- -----------
ID_TEMP NUMBER(2)
ID NUMBER(1)

SQL> alter table test1 modify id number(10);
Table altered.

SQL> alter table test1 modify id number(5);
alter table test1 modify id number(5)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

Alter multiple columns in one command

alter table table_name
modify
(
column1_name column1_datatype,
column2_name column2_datatype,
);

Example:

SQL> alter table test1 modify ( id number(11), id_Temp number(3));
Table altered.

You can also specify the constraint with modify command

SQL> alter table test1 modify ( id number(11) not null, id_Temp number(3));
Table altered.

Taskkill Error Access denied

Taskkill Error Access denied

1. Open the Command prompt window with Administrator right.

First check Command prompt is open with administrator right with right click on it.
(RUN AS ADMINISTRATOR)

2. Check with the process user from which it’s running.

tasklist /V

-- Output in Excel Sheet
tasklist /V /FO CSV > E:\output.csv

Output Sample:
--------------
"oracle.exe","10124","Services","0","404,584 K","Unknown","NT AUTHORITY\SYSTEM","0:18:22","N/A"
"conhost.exe","7716","Services","0","500 K","Unknown","NT AUTHORITY\SYSTEM","0:00:00","N/A"

3. Try to kill it.

C:\Users\e3019447>taskkill /F /PID 10124
ERROR: The process with PID 10124 could not be terminated.
Reason: Access is denied.

C:\Windows\system32>TASKKILL /F /FI "USERNAME eq NT AUTHORITY\SYSTEM" /PID 10124
SUCCESS: The process with PID 10124 has been terminated.

OR

TASKKILL /F /FI "USERNAME eq NT AUTHORITY\SYSTEM" /IM oracle.exe

OR

wmic process where name='oracle.exe’ delete

Different Syntax provided by Microsoft to use the Commands:

TASKKILL Commands : TASKKILL /?

TASKKILL /IM notepad.exe
TASKKILL /PID 1230 /PID 1241 /PID 1253 /T
TASKKILL /F /IM cmd.exe /T
TASKKILL /F /FI "PID ge 1000" /FI "WINDOWTITLE ne untitle*"
TASKKILL /F /FI "USERNAME eq NT AUTHORITY\SYSTEM" /IM oracle.exe
TASKKILL /S system /U AMERIFIRST\tron /FI "USERNAME ne NT*" /IM oracle.exe
TASKKILL /S system /U username /P password /FI "IMAGENAME eq note*"

TASKLIST Commands: TASKLIST /?

TASKLIST /M notepad.exe
TASKLIST /V /FO CSV
TASKLIST /SVC /FO LIST
TASKLIST /M wbem*
TASKLIST /S system /FO LIST
TASKLIST /S system /U domain\username /FO CSV /NH
TASKLIST /S system /U username /P password /FO TABLE /NH
TASKLIST /FI "USERNAME ne NT AUTHORITY\SYSTEM" /FI "STATUS eq running"

ORA-01440: column to be modified must be empty to decrease precision or scale

ORA-01440: column to be modified must be empty to decrease precision or scale

Example to generate the error with TEST1 table

--Created table test1
SQL> create table test1 (id number(1));
Table created.

--insert data
SQL> insert into test1 values (1);
1 row created.

--Modified the table with increase scale
SQL> alter table test1 modify id number(2);
Table altered.

--insert data
SQL> insert into test1 values(2);
1 row created.

Getting error while modified the column datatype in decrease length scale

SQL> alter table test1 modify id number(1);
alter table test1 modify id number(1)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

Solution

1. Add a first temporary column with data type used to modified in upper statement.

SQL> alter table test1 add id_temp NUMBER(1);
Table altered.

2. Move the data from original column to first temporary column.

SQL> update test1 set id_temp= id;
2 rows updated.

3. Check the number of rows.

SQL> select count(*) from test1;

COUNT(*)
----------
2

4. Rename the original column to second new temporary column so that we will move temporary column with original name in next step.

SQL> alter table test1 rename column id to id_TEMP1;
Table altered.

Note: We can directly drop id column and rename the first temporary to original column but we add this step to make production data security.(We can skip this step to by dropping first id column)

5. Rename the column which is having original data created first.

SQL> alter table test1 rename column id_temp to id;
Table altered.

6. Drop the extra temporary column.

SQL> alter table test1 drop column id_temp1;
Table altered.

7. Verify the Number of rows is same.

SQL> select count(*) from test1;

COUNT(*)
----------
2

8. Verify the Structure with Describe command.
desc test1;
Name Null? Type
--------- -------- ---------------
ID NUMBER(1)

9. Compile the invalid objects related to that object by executing.

execute utl_recomp.recomp_serial();