Tag Archives: Steps

Steps to apply Oracle database Patch on windows platform

Apply Oracle Patch on windows platform

1. Set oracle_home,patch_home, PATH and ORACLE_SID

SET ORACLE_HOME=E:\oracle\11.2.0\dbhome_1
SET PATH=E:\oracle\11.2.0\dbhome_1\bin;E:\oracle\11.2.0\dbhome_1\opatch;%PATH%
SET ORACLE_SID=ORCL
SET PATH=%ORACLE_HOME%\perl\bin;%PATH%

2. Check the Patch compatibility.
Go to patch location where it downloaded, unzip it.

cd E:\patch
opatch prereq CheckConflictAgainstOHWithDetail -ph ./20233168

3. Check the Opatch lsinventory detail
It will give you the already applied patch list

opatch lsinventory -detail

4. Check the opatch version and check the README of the patch you downloaded.
— Check that your opatch version is greater or same as mentioned in README.

opatch version

5. Take a snapshot of Oracle Services from SERVICE.MSC
 
6. Stop the listener.

-- Check status of listener
lsnrctl status
-- Stop the listener
lsnrctl stop

7. Take the backup of database (optional)

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup format 'F:\rman\full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'F:\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'F:\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}

8. Create pfile as current Spfile.

create pfile='E:\oracle\initpfile.ora' from spfile;

9. Check the invalid objects before patching

select count(*) from dba_objects where status='INVALID';

10. Check the registry$history table for check already patching history.

-- Used in case of 11g version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

-- Used in case of 12c version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select patch_id, version, status, Action, Action_time from dba_registry_sqlpatch order by action_time;

11. Check the DBA Registry component is valid before patching:

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

12. Shutdown the database:

shutdown immediate;

13. Take the services down in Windows platform:
–You can do from services.msc also

net stop OracleServiceIC
net stop OracleDBConsoleIC
net stop OracleJobSchedulerIC
net stop "Oracle IC VSS Writer Service"
net stop OracleREMExecService
net stop "Oracle Object Service"
net stop ocfs (required only for OCFS installation)
net stop OraFenceService
net stop OracleMTSRecoveryServicenet stop msdtc
net stop winmgmt
sc config Winmgmt start= disabled

14. Take the backup of Oracle home
–copy and paste in another folder for backup
 
15. Take the backup of Oracle Inventory
–copy and paste in another folder for backup
 
16. Apply the patch
Go to patch directory
— Follow the readme steps for Installation the patch

cd 20233168
opatch apply

17. Check the opatch log file generated at %ORACLE_HOME%\cfgtoollogs\opatch for error
 
18. Start the Oracle database Service for Post installation steps mentioned in readme.
 
19. Execute the Post steps for bundle patch

-- In 11g readme steps need to follow for each patch have different steps:
cd %ORACLE_HOME%\Bundle\Patch36
STARTUP
SQL> @catwinbundle.sql
SQL> QUIT
-- In 12c readme steps need to follow:
cd %ORACLE_HOME%\opatch
datapatch -verbose

20. Check the log files for errors:
$ORACLE_BASE/cfgtoollogs/catbundle
catbundle_WINBUNDLE__APPLY_.log
catbundle_WINBUNDLE__GENERATE_.log
 
21. Compile the invalid objects and verify with before patching count.

cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
CONNECT / AS SYSDBA
@utlrp.sql
select count(*) from dba_objects where status='INVALID';

22. Check the db_registry;

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

23. Verify the registry$history table for patch applied:

-- Used in case of 11g version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

-- Used in case of 12c version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select patch_id, version, status, Action, Action_time from dba_registry_sqlpatch order by action_time;

24. Start the oracle and windows Services.
— you can start from services.msc window

net start OracleServiceIC
net start OracleDBConsoleIC
net start OracleJobSchedulerIC
net start "Oracle IC VSS Writer Service"
net start OracleREMExecService
net start "Oracle Object Service"
net start ocfs (required only for OCFS installation)
net start OraFenceService
net start OracleMTSRecoveryServicenet start msdtc
net start winmgmt

25. Check the listener connectivity

sqlplus sys@IC as sysdba

Rollback plan:

1. Copy the old oracle home back.
 
2. Restore the database backup

Restore Script Through RMAN:
=============================
run{
allocate channel t1 type DISK;
set until time "to_date('2015/02/27 12:52:00','yyyy/mm/dd HH24:MI:SS')";
restore controlfile from 'F:\rman\cntr_18_1';
release channel t1;
}alter database mount;

run {
allocate channel t1 type DISK;
set until time "to_date('2015/02/27 12:52:00','yyyy/mm/dd HH24:MI:SS')";
restore database;
release channel t1;
}

run {
allocate channel t1 type DISK;
set until time "to_date('2015/02/27 12:52:00','yyyy/mm/dd HH24:MI:SS')";
recover database;
release channel t1;
}

alter database open resetlogs;

Advertisements

Enable JavaScript for Windows Internet Explorer

Enable JavaScript for Windows Internet Explorer

Error: Your browser does not have Javascript enabled. Javascript is required to use this application.

For this you need to enable active script in scripting heading

Error: JavaScript is required. Enable JavaScript to use OAM Server.

For this error you also need to enable java applet in scripting heading.

1. Select Internet Options from the Tools menu.

c1

2. Click the Security tab and go to Custom Level

C2

3. Scroll down to Scripting, near the bottom of the list.Under Active Scripting, choose Enable.

C3

4. Enable the java applet as follows:

C5

5. Click OK to leave Security Settings. Click OK to leave Internet Options.

C4

6. You will need to close and restart your browser for the settings to take effect.

Export stats from production to testing environment

Export stats from production to testing environment for make similar environment for Performance testing

1. It helps to check the performance issue going on production.
2. It used for testing purpose if any new deployment is gone on production environment.
3. Check the execution plan of query and move to performance server to run advisory reports.

Steps of Activity:

On Production Environment:

1. Create the stats table on the production environment
exec dbms_stats.create_stat_table('HR','EMPSTATISTICS');
 
2. Verify the objects created into the production environment
select object_name,owner,object_type from dba_objects where object_name = 'EMPSTATISTICS';
 
3. Run the dbms_stats package to export the stats of the schema to table

begin
dbms_stats.export_schema_stats(ownname => 'HR',stattab => 'EMPSTATISTICS');
end;
/

 
4. Export the table with expdp utility.

expdp tables=hr.empstatistics directory=dbbackup dumpfile=empstatistics.dmp logfile=empstatistics.log

 
5. Drop the table from the production environment.

exec dbms_stats.drop_stat_table('HR','EMPSTATISTICS');

 
6. Ftp or Move the dump file to the TEST environment
On Test Environment:

7. Import the table to the testing environment.(if your schema is different then remap_schema is used in impdp)
impdp directory=dbbackup dumpfile=empstatistics.dmp logfile=empstatistics.log
 
8. Import the stats into the schema inwhich you want. (I created new schema HR_NEW similar to HR so i am importing into HR_NEW);
exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');
 
9. Error if you got the following error:

ORA-20000: no statistics are imported

We can try for one table also as command given below:

EXEC DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS');

Error:
ORA-20000: no statistics are imported

SQL> EXEC DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS');
BEGIN DBMS_STATS.import_table_stats(ownname => 'HR_NEW',tabname => 'EMP',stattab => 'EMPSTATISTICS'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 3784
ORA-06512: at "SYS.DBMS_STATS", line 4617
ORA-06512: at "SYS.DBMS_STATS", line 12758
ORA-06512: at line 1

Solution:
You need to unlock the schema stats first to override the error occurred above.

exec dbms_stats.unlock_schema_stats('HR_NEW');

If still getting error after it you need to check the username if its different in production and test environment you need to follow following steps:
If your user name is different then the production database then you need to update the column value of empstatistics table for resolved it.

select c5 from empstatistics;

empstatistics having production statistics and c5 column contain user name of production you need to modified it to new test environment username HR_NEW

update empstatistics set c5 = 'HR_NEW';
commit;

10. IF you version of oracle is different test environment is upper version the following error occur:ORA-20002

SQL> exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'HRSTATISTICS');
BEGIN dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'HRSTATISTICS'); END;

*
ERROR at line 1:
ORA-20002: Version of statistics table HR_NEW.EMPSTATISTICS is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 11648
ORA-06512: at "SYS.DBMS_STATS", line 11665
ORA-06512: at "SYS.DBMS_STATS", line 12800
ORA-06512: at line 1

Solution:

SQL> exec dbms_stats.upgrade_stat_table(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');

11. You can simple try again the import schema stats into new user:

SQL> exec dbms_stats.import_schema_stats(ownname => 'HR_NEW',stattab => 'EMPSTATISTICS');

12. Now you can do your testing on new stats similar to production environment
like execution plan, how sql query perform after deployment, check advisory report.

Dataguard switchover and failover steps in Oracle

Dataguard Switch-over and fail over steps

 
A switch-over allows the primary database to switch roles with its standby database. There is no data loss during a switch-over.
You can switch back to the original Primary database later by performing another switch over.
 
In case of primary database failure, you will need to perform failover to transition the standby database to the primary role. After a failover, the original primary database can no longer participate in the Data Guard configuration. So if the original Primary database is still accessible, you should always consider a switchover first.
 
This document only talks about switchover involving physical standby database. In this example, the original primary data is called PRIM and the original standby database is called STAN.
 
Verify Before Switch-over:
 
1. Verify the primary database instance is open.

SQL> Select Database_role from v$Database;
It will return “PRIMARY”,
 
2. Verify the standby database instance is mounted.

SQL> Select Database_role from v$Database;
It will return “PHYSICAL STANDBY”,

3. Verify there are no active users connected to the databases.
 
4. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

SQL>select sequence#, applied from v$archived_log;

Perform SWITCH LOGFILE if necessary.

In order to apply redo data to the standby database as soon as it is received, use Real-time apply.
 
Switchover Steps

1. Initiate the switchover on the primary database PRIM:

PRIM>connect /@PRIM as sysdba
PRIM> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;

Open another prompt and connect to SQLPLUS:
STANDBY>connect /@STAN as sysdba
STANDBY> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:

PRIM>SHUTDOWN IMMEDIATE;
PRIM>STARTUP MOUNT;

4. After step 3 completes, you can open the new Primary database STAN:

STANDBY>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.

SQL> Select Database_role from v$Database;

5. On the new primary database STAN, perform a SWITCH LOGFILE to start sending redo data to the standby database PRIM.

SQL>ALTER SYSTEM SWITCH LOGFILE;

 
Steps for FAILOVER the Dataguard environment
 
1. Initiate the failover on the standby database STAN:

SQL>connect /@STAN as sysdba
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2. Immediately after issuing command in step 2, shut down and restart the standby instance STAN:

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

STAN is now transitioned to the primary database role.Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.
SQL> Select Database_role from v$Database;