Navaneeth's Blog

Tuesday, February 25, 2014

Manage using Oracle Database Service for Segregating application users and monitoring performance

exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'test2',NETWORK_NAME=>'test2');

exec dbms_service.START_SERVICE(SERVICE_NAME=>'test2');

alter system set service_names=PSETLXMT,Ntest1,test2;

oracle@PSETLXMT> show parameter local_listener

NAME TYPE VALUE

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

local_listener string (ADDRESS=(PROTOCOL=tcp)(HOST=DG1224)(PORT=1628))

Update the below in the tnsnames.ora file :

NTEST1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = DG1224)(PORT = 1628))

)

(CONNECT_DATA =

(SID_NAME=PSETLXMT)

(SERVICE_NAME = NTEST1)

)

TEST2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = DG1224)(PORT = 1628))

)

(CONNECT_DATA =

(SID_NAME=PSETLXMT)

(SERVICE_NAME = test2)

)

$ sqlplus test/test123#@NTEST1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 29 12:35:21 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

test@NTEST1>

$ sqlplus test/test123#@test2

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 29 12:35:57 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

test@test2>

able to see the sessions which connects using the service name in the v$session

oracle@PSETLXMT> select sid,serial#,schemaname,service_name from v$session where type !='BACKGROUND';

SID SERIAL# SCHEMANAME SERVICE_NAME

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

99 1021 FTP_DV_ETL SYS$USERS

293 8047 SYS SYS$USERS

387 44311 SYS SYS$USERS

581 17421 FTP_DV_ETL SYS$USERS

676 22123 FTP_DV_ETL SYS$USERS

772 22383 FTP_DV_ETL SYS$USERS

868 19337 TEST test2

964 12479 FTP_DV_ETL SYS$USERS

1059 12187 TEST test1

1156 4117 FTP_DV_ETL SYS$USERS

1444 2519 FTP_DV_ETL SYS$USERS

In the ashr report as well.

Wednesday, June 13, 2012

Oracle 9.2.0.6 to 11.2.0.2 upgrade steps

The below steps followed for upgrading a production database which is of size 400GB.

Preupgrade steps :
--------------------------------------

9206 oracle home = /usr/oracle/ora_9_2_0_5

> do the count on the PG022 server on 9.2.0.6 database before going for upgrade.

step 1: Shut down the existing database STRASPRD in old 9.2.0.6 home.

>shutdown immediate

step 2: copy the datafile from old filesystem to new filesystem

> script copy_DF_strasprd.sh

step 3a: Backup the spfile with new name.

> copy spfilestrasprd.ora spfilestrasprd20120519.ora.bkp

step 3b : change the controlfile locations in the pfile/spfile in 9.2.0.6 home with new database

step 4: connect sqlplus and startup nomount

> sqlplus /nolog 
> startup nomount

step 4: Recreate the controlfile.

> script control_file_creation_script

step 5: Mount the database

>alter database mount

step 6: Recover using backup controlfile and open resetlogs

> recover database using backup controlfile until cancel;

> alter database open resetlogs;

step 7: create a new temporary tablepsace - remove the copy in DF copy script file.


create temporary tablespace TEMP tempfile '/prd/HK/strauss/undo/temp_01.dbf' size 1000M autoextend on next 1000M maxsize 8000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

step 8: check the datafiles count and logfiles, controlfile locations as per the new filesystem.

select name from v$datafile;
select * from v$controlfile;
select * from v$logfile;
select * from v$reocver_file;

step 9: check the count of invalid obejcts and database components versions and status before going for upgrade.

select owner,count(*),object_type from dba_objects where status !='VALID' group by owner,object_type;
15:21:37 SQL> select owner,count(*),object_type from dba_objects where status !='VALID' group by owner,object_type;

OWNER                            COUNT(*) OBJECT_TYPE
------------------------------ ---------- ------------------
SYS                                     1 TRIGGER
SYS                                     1 PROCEDURE
SYS                                     8 PACKAGE BODY
SYSTEM                                371 PACKAGE
SYSTEM                                394 PACKAGE BODY
IBROKER                                17 PACKAGE
IBROKER                                21 PACKAGE BODY

7 rows selected.

select  COMP_NAME,version,status from dba_registry;
15:24:01 SQL> select  COMP_NAME,version,status from dba_registry;

COMP_NAME                                                                                                                                                                                                                                                       VERSION                        STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------
Oracle9i Catalog Views                                                                                                                                                                                                                                          9.2.0.6.0                      VALID
Oracle9i Packages and Types                                                                                                                                                                                                                                     9.2.0.6.0                      VALID


step 10: Shutdown the database for 9.2.0.8 upgrade.

> shutdown immediate



Upgrade to 9.2.0.8 steps :
-------------------------------------------------------------------

step 1: set the environment variables

export ORACLE_HOME=/prd/strauss/oradump/9.2.0.8
export PATH=/prd/strauss/oradump/9.2.0.8/bin:$PATH
export LD_LIBRARY_PATH=/prd/strauss/oradump/9.2.0.8/lib32
export LD_LIBRARY_PATH_64=/prd/strauss/oradump/9.2.0.8/lib


step 2: copy the spfile to the new 9.2.0.8 oracle home

cp /usr/oracle/ora_9_2_0_5/dbs/spfilestrasprd.ora  /prd/strauss/oradump/9.2.0.8/dbs/spfilestrasprd.ora

step 3b: start the database using 9.2.0.8 home 

export ORACLE_SID=strasprd
sqlplus -v
sqlplus /nolog
sqlplus / as sysdba

step 4: startup the database and check parameters for shared pool and java pool - refer document.

> startup

SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE

 If the system is using a server parameter file: 
 If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB: 

 SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile; 

 If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB: 
 SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile; 

step 6: shutdown immediate and startup in migrate mode

SQL> STARTUP MIGRATE
SQL> SPOOL 9208_cpatch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

Review the 9208_patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script. 
This list provides the version and status of each SERVER component in the database. 
If necessary, rerun the catpatch.sql script after correcting any problems. 

step 7: restart the database

> SHUTDOWN
> STARTUP

step 8: Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. 
This step is optional but recommended. 

> @?/rdbms/admin/utlrp.sql

step 9: check the count of invalid obejcts and database components versions and status before going for upgrade.

select owner,count(*) from dba_objects where status !='VALID' group by owner;
select * from dba_registry;

rerun the utlrp.sql script if needed.


11.2.0.2 Preupgrade steps 
---------------------------------------------------------------------------
>>>>>>> Note: we can execute the below steps from the 9.2.0.8 oracle home. <<<<<<<<<<<<<

Step 1:  Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] -- refer metalink

Log in as oracle and copy the file utlu112i_2.sql from the 11gR2
ORACLE_HOME/rdbms/admin directory to a directory outside of the
Oracle Home.
Execute utlu112i_2.sql and spool the output to a file. Examine the output
of the upgrade information tool.

SQL> @utlu112i_2

check the recommendations and follow.

From this step it differs for Manual upgrade and upgrade using DBUA.

For manual upgrade follow the below steps.

Step 2: Mandatory steps to be followed before upgrade 

CREATE TABLESPACE sysaux DATAFILE '/prd/HK/strauss/admin/sysaux01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Step 3: drop the perfstat and rbs tablespace which is not needed for upgrade.

> drop tablespace perfstat including contents and datafiles;
> drop tablespace rbs including contents and datafiles;
select * from v$tablespace;

Step 4: Convert any dictionary managed tablespace to localy managed tablespace - refer metalink document -> How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace? [ID 735978.1]

SQL>  select tablespace_name,extent_management from dba_tablespaces;
SQL>  SELECT tablespace_name, status FROM dba_tablespaces;

SQL>SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

SQL>SELECT s.segment_name, s.tablespace_name, t.extent_management FROM   dba_segments s, dba_tablespaces t WHERE  s.tablespace_name = t.tablespace_name
AND  s.segment_type = 'ROLLBACK'

SQL>SELECT tablespace_name, status FROM  dba_tablespaces WHERE  contents = 'UNDO';

 >>  Make all the tablespaces readonly before doing conversion of system tablespace aprat from temp.

ALTER TABLESPACE ITRADE_DAT READ ONLY;
ALTER TABLESPACE ITRADE_IDX READ ONLY;
ALTER TABLESPACE IBROKER_DAT READ ONLY;
ALTER TABLESPACE IBROKER_IDX READ ONLY;
ALTER TABLESPACE SYSAUX  READ ONLY;

SELECT logins  FROM   v$instance

ALTER SYSTEM ENABLE RESTRICTED SESSION;

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

SELECT extent_management FROM   dba_tablespaces WHERE  tablespace_name = 'SYSTEM';

ALTER SYSTEM DISABLE RESTRICTED SESSION;

ALTER TABLESPACE ITRADE_DAT READ WRITE;
ALTER TABLESPACE ITRADE_IDX READ WRITE;
ALTER TABLESPACE IBROKER_DAT READ WRITE;
ALTER TABLESPACE IBROKER_IDX READ WRITE;
ALTER TABLESPACE SYSAUX  READ WRITE;

for time zone check the values and update as per the recomendation -- refer metalink

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- optional

Step 5: Do all the recomendations provided by @utlu112i_2 script. -- mandatory

Step 6: connect roles is obsolete in 11g please use below query and grant privilege as needed

SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' AND grantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS',
'DBSNMP','LOGSTDBY_ADMINISTRATOR','ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST',
'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR','OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN',
'MDDATA','SI_INFORMTN_SCHEMA','XDB', 'ODM');

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT';

>> The below query generate grant statements for users who has connect role

select 'grant create view,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE,CREATE DATABASE LINK to ' ||grantee ||';' FROM dba_role_privs 
WHERE granted_role = 'CONNECT' and grantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR',
 'ORDSYS','ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM');


>>> check all the necessary recomendations are followed and make sure there is nor issues.

Step 7: gather stats for all schemas which has stale statistics 

>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',OPTIONS=>'GATHER',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT =>'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);

Step 8: Copy the parameter file to the 11.2.0.2 location and change the necessary parameters.

cp /prd/strauss/oradump/9.2.0.8/dbs/spfilestrasprd.ora  /prd/strauss/oracle/11.2.0/dbs/initstrasprd.ora

check - > parameters to be added/changed in 11.2.0.2
arhcivelog location
controlfile locaton
--------------------------------------------------------
Note : 
set shared pool size to at least 448M
set COMPATIBLE parameter to 10.1.0 until after the upgrade has been completed successfully
----------------------------------------------------------------
*.compatible='11.2.0'
*.DB_RECOVERY_FILE_DEST_SIZE=1024M
*.db_recovery_file_dest='/usr/oracle/upgrade11G/fast_recovery_area'   - need to change ,check the current environment
*.diagnostic_dest='/prd/stss/strasprd/data01/diag'
*.log_archive_format='strasprd_T%T_S%S%r.arc'
*.memory_max_target=13G
*.memory_target=13G
*.optimizer_mode='FIRST_ROWS_1000'
*.optimizer_secure_view_merging=FALSE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=600
*.undo_tablespace='UNDOTBS'
*.WORKAREA_SIZE_POLICY='auto'
*.job_queue_processes=1


Step 9:Shutdown the database and listener in 9.2.0.8  

shutdown immediate
lsnrctl stop LISTENER_STRASPRD


11g Upgrade steps:
------------------------------------------------------------

Step 1: set the environment variables for 11.2.0.2

export ORACLE_HOME=/prd/strauss/oracle/11.2.0.2
export PATH=/prd/strauss/oracle/11.2.0.2/bin:$PATH
export LD_LIBRARY_PATH=/prd/strauss/oracle/11.2.0.2/lib -  optional try if errors
export LD_LIBRARY_PATH_64=/prd/strauss/oracle/11.2.0.2/lib -  optional try if errors

cd $ORACLE_HOME
export ORACLE_SID=strasprd

sqlplus -v
sqlplus /nolog
sqlplus / as sysdba

sql>startup upgrade

SQL> @/prd/strauss/oracle/11.2.0/rdbms/admin/catupgrd.sql

sql> shutdown immediate

sql> startup

11g post upgrade steps :

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

07:31:35 SQL> alter system set optimizer_mode='FIRST_ROWS_1000' scope=spfile;

System altered.

Elapsed: 00:00:00.01
07:32:03 SQL> alter system set optimizer_secure_view_merging=FALSE scope=spfile;

System altered.

Elapsed: 00:00:00.00
07:32:08 SQL> alter system set compatible='11.2.0' scope=spfile;

System altered.

Elapsed: 00:00:00.01
07:32:17 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:32:26   2  07:32:26   3  07:32:26   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:32:31 SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         1

Elapsed: 00:00:00.01
07:32:40 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
07:32:45 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:32:50 SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         1

Elapsed: 00:00:00.00
07:35:59 SQL> exec DBMS_DST.BEGIN_PREPARE(11);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
07:37:30 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:37:37   2  07:37:37   3  07:37:37   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

Elapsed: 00:00:00.00
07:37:38 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:38:28   2  07:38:28   3  07:38:28   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

Elapsed: 00:00:00.00
07:38:30 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.09
07:38:43 SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.01
07:38:57 SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.02
07:39:07 SQL> set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/07:39:25 SQL> 07:39:25   2  07:39:25   3  07:39:25   4  07:39:25   5  07:39:25   6  07:39:25   7

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.66
07:39:28 SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

Elapsed: 00:00:00.00
07:39:44 SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.00
07:40:01 SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
07:44:20 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:44:29   2  07:44:29   3  07:44:29   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:44:30 SQL> shutdown immediate
Database closed.
Database dismounted.
07:46:18 SQL> set serveroutput on
07:46:19 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
07:46:26   2  07:46:26   3  07:46:26   4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.02
07:46:26 SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:00:00.19
07:46:43 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.03
07:46:52 SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.02
07:46:59 SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.01
07:47:07 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.01
07:47:17 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:47:26 SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
07:47:42 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:48:03   2  07:48:03   3  07:48:03   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       1
DST_UPGRADE_STATE              UPGRADE

Elapsed: 00:00:00.01
07:48:04 SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

Elapsed: 00:00:00.36
07:48:14 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
07:48:55 SQL> startup
ORACLE instance started.

Total System Global Area 1.2831E+10 bytes
Fixed Size                  2160072 bytes
Variable Size            6375344696 bytes
Database Buffers         6442450944 bytes
Redo Buffers               11247616 bytes
Database mounted.
Database opened.
07:49:20 SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
Session altered.

Elapsed: 00:00:00.02
07:49:21 SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;alter session set "_with_subquery"=materialize
                                             *
ERROR at line 1:
ORA-00911: invalid character


Elapsed: 00:00:00.00
07:49:26 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:49:33 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
07:49:37 SQL> set serveroutput on
VAR numfail number
07:49:46 SQL> 07:49:46 SQL> BEGIN
07:49:46   2  DBMS_DST.UPGRADE_DATABASE(:numfail,
07:49:46   3  parallel => TRUE,
07:49:46   4  log_errors => TRUE,
07:49:46   5  log_errors_table => 'SYS.DST$ERROR_TABLE',
07:49:46   6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
07:49:46   7  error_on_overlap_time => FALSE,
07:49:46   8  error_on_nonexisting_time => FALSE);
07:49:46   9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
07:49:46  10  END;
07:49:46  11  /
Failures:0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
07:49:48 SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/07:50:15 SQL> 07:50:15   2  07:50:15   3  07:50:15   4  07:50:15   5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
07:50:17 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:50:31   2  07:50:31   3  07:50:31   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:50:32 SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

Elapsed: 00:00:00.01
07:50:41 SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
         1

Elapsed: 00:00:00.00
07:50:54 SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;
1 row updated.

Elapsed: 00:00:00.01
07:51:12 SQL>

Commit complete.

sql> @/prd/strauss/oracle/11.2.0.2/rdbms/admin/utlrp.sql

For Upgrade using DBUA follow the below steps.

1. start the database and listener in 9.2.0.8 home, the database should be in openmode in 9.2.0.8.

2. Add the entry in the /var/opt/oracle/oratab file with the 9.2.0.8 home.

3.Now set the oracle home for New oracle 11g

4.start the DBUA.

5. The DBUA should recognize the 9208 database and show the database for upgrading.

6. Proceed with next as per the requirment.

Note: For upgrade using DBUA you no need to do any preupgrade steps like creating SYSAUX and changing parameters or Database timzone upadte, DBUA automaticaly does all these while upgrading.

You need to take care of the connect privileges since its deprecated in 11g.

Regards,

Navaneeth 

Tuesday, April 3, 2012

Standby database redo transfer by different modes

Redo transferred by different modes to standby database:

   Here in the standby database, if realtime apply is enabled, the redo data will be applied from the redolog file directly before archiving.Otherwise it will be archived and after it will be applied to the standby database.

And the redo applying(either by block wise or SQL wise) will be differed based on the physical and logical standby database.

1.Using Archiver Processes (ARCn) to Archive Redo Data

By default, redo transport services use ARCn processes to archive the online redo log files on the primary database. ARCn archival processing supports only the maximum performance level of data protection in Data Guard configurations. You must use the LGWR process to transmit redo data to standby locations that operate in other data protection modes.

Archiving to Local Destinations Before Archiving to Remote Destinations

2.Using the Log Writer Process (LGWR) to Archive Redo Data

You can optionally enable redo transport services to use the LGWR process to transmit redo data to remote destinations.

Using the LGWR process differs from ARCn processing (described in Section 5.3.1), because instead of waiting for the online redo log to switch at the primary database and then writing the entire archived redo log at the remote destination all at once, the LGWR process selects a standby redo log file at the standby site that reflects the log sequence number (and size) of the current online redo log file of the primary database. Then, as redo is generated at the primary database, it is also transmitted to the remote destination. The transmission to the remote destination will either be synchronous or asynchronous, based on whether the SYNC or the ASYNC attribute is set on the LOG_ARCHIVE_DEST_n parameter. Synchronous LGWR processing is required for the maximum protection and maximum availability modes of data protection in Data Guard configurations. 

 LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

Thanks & Regards,

Navaneeth

Wednesday, March 28, 2012

Command to disable or enable firewall

netsh firewall set opmode mode = mode 

Mode = enable or disable

Thank you

Regards,

Navaneeth

Rename a datafile in the primary and propogate the changes to the standby database

Rename a datafile in the primary database and manually propagate the changes to the standby database.

PRIMARY

1. Set STANDBY_FILE_MANAGEMENT=MANUAL on both Primary and Standby Database. 

SQL> show parameter standby_file_manag

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> col value format a50

SQL>  select name,value from v$parameter where name like 'db_unique_name';

NAME               VALUE

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

db_unique_name   orcl11g

SQL>

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL>  show parameter standby_file

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

2. Take the Tablespace offline on the Primary Database: 

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC

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

         0 SYSTEM                         YES NO  YES

         1 SYSAUX                         YES NO  YES

         2 UNDOTBS1                       YES NO  YES

         4 USERS                          YES NO  YES

         3 TEMP                           NO  NO  YES

         6 FDB                            YES NO  YES

6 rows selected.

SQL> alter tablespace users offline;

Tablespace altered.

3. Rename Datafile on Primary Site: 

   copy the file using OS command to the new location

4. Rename the Datafile in the Primary Database. 

SQL> alter tablespace users rename datafile 'D:\app\nvcob\oradata\orcl11g\USERS01.DBF' TO 'D:\app\nav6cob\oradata\orcl11g_1\USERS01.DBF';

Tablespace altered.

5. Bring the Tablespace back online: 

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

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

STANDBY 

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> select name,value from v$parameter where name like 'db_unique_name';

NAME                                                                             VALUE

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

db_unique_name                                                                   11gstdby

SQL> show parameter standby_file

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

SQL>

1. Stop Redo Apply on Standby Database.

SQL> recover managed standby database cancel;

Media recovery complete.

2. Shutdown the Standby Database.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

3. Move the Datafile to the new location at the Standby site.

Copy the file using OS command to the new location.

4. Start and mount the Standby Database.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1375792 bytes

Variable Size             209715664 bytes

Database Buffers          318767104 bytes

Redo Buffers                5804032 bytes

Database mounted.

5.Rename the Datafile in the Standby Database control file. 

SQL> alter database rename file 'D:\app\nav6cob\oradata\11gstdby\USERS01.DBF' to 'D:\app\nav6cob\oradata\11gstdby_1\USERS01.DBF';

Database altered.

6. On the Standby Database, restart Redo Apply: 

SQL> recover managed standby database disconnect from session;

Media recovery complete.

7. Set STANDBY_FILE_MANAGEMENT=AUTO on both Primary and Standby Database. 

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE# NAME

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

          4 D:\APP\NAV6COB\ORADATA\11GSTDBY_1\USERS01.DBF

SQL>

Oracle export/import done from Higher to lower version

Export :

[oracle@HN300949l ~]$ expdp test/test DUMPFILE=test10092011.dmp DIRECTORY=EXPDP SCHEMAS=test version=10.2.0.1

Export: Release 11.2.0.1.0 - Production on Mon Oct 10 12:52:15 2011

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** DUMPFILE=test10092011.dmp DIRECTORY=EXPDP SCHEMAS=test version=10.2.0.1

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 55.68 MB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."DEDUPLICATE_TAB" 9.552 MB 1000 rows

. . exported "TEST"."NOCOMPRESS_TAB" 9.552 MB 1000 rows

. . exported "TEST"."KEEP_DUPLICATES_TAB" 9.552 MB 1000 rows

. . exported "TEST"."COMPRESS_TAB" 9.552 MB 1000 rows

. . exported "TEST"."SF_TAB" 5.335 KB 1 rows

. . exported "TEST"."BF_TAB" 5.335 KB 1 rows

. . exported "TEST"."MY_CUSTOMERS" 6.335 KB 1 rows

. . exported "TEST"."TEST" 6.382 KB 1 rows

. . exported "TEST"."OUTER_NTAB" 0 KB 0 rows

. . exported "TEST"."INNER_NTAB" 0 KB 0 rows

. . exported "TEST"."STU_TAB" 0 KB 0 rows

. . exported "TEST"."PER_TAB" 0 KB 0 rows

. . exported "TEST"."TIME_TABLE" 0 KB 0 rows

Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:

/u01/app/oracle/test10092011.dmp

Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:57:30

****************************************** -----------------------------------------------------------*********************************************************

Import :

D:\oracle\product\10.2.0\db_1\BIN>

D:\oracle\product\10.2.0\db_1\BIN>impdp 'sys/database as sysdba' directory=DATA_PUMP_DIR dumpfile=test10092011.dmp

Import: Release 10.2.0.1.0 - Production on Monday, 10 October, 2011 13:50:20

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": 'sys/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=test10092011.dmp

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."DEDUPLICATE_TAB" 9.552 MB 1000 rows

. . imported "TEST"."NOCOMPRESS_TAB" 9.552 MB 1000 rows

. . imported "TEST"."KEEP_DUPLICATES_TAB" 9.552 MB 1000 rows

. . imported "TEST"."COMPRESS_TAB" 9.552 MB 1000 rows

. . imported "TEST"."SF_TAB" 5.335 KB 1 rows

. . imported "TEST"."BF_TAB" 5.335 KB 1 rows

. . imported "TEST"."MY_CUSTOMERS" 6.335 KB 1 rows

. . imported "TEST"."TEST" 6.382 KB 1 rows

. . imported "TEST"."OUTER_NTAB" 0 KB 0 rows

. . imported "TEST"."INNER_NTAB" 0 KB 0 rows

. . imported "TEST"."STU_TAB" 0 KB 0 rows

. . imported "TEST"."PER_TAB" 0 KB 0 rows

. . imported "TEST"."TIME_TABLE" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 13:51:04

Regards,

Navaneeth