Archive

Archive for the ‘Step by Step Documentation’ Category

How to Rewind an OPEN RESETLOGS Operation with Flashback Database?

October 9, 2017 Leave a comment

Hi,

Steps for using Flashback Database to reverse an unwanted “ALTER DATABASE OPEN RESETLOGS” statement is similar to “FLASHBACK DATABASE”. You will use “FLASHBACK DATABASE TO BEFORE RESETLOGS” statement. Of course you must enable flashback database before performing “ALTER DATABASE OPEN RESETLOGS” operation.

To undo an OPEN RESETLOGS operation:

1. Connect SQL*Plus to the target database and verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS. Run the following queries:

SELECT RESETLOGS_CHANGE#
FROM V$DATABASE;

SELECT OLDEST_FLASHBACK_SCN
FROM V$FLASHBACK_DATABASE_LOG;

If RESETLOGS_CHANGE# is greater than OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS operation

2. Shut down the database,

SHUTDOWN IMMEDIATE;

3. mount it

STARTUP MOUNT;

4. Recheck the flashback window. If the resetlogs SCN is still within the flashback window, then proceed to the next step.
Connect RMAN to the target database.
rman target /

5. Perform a flashback to the SCN immediately before the RESETLOGS. You can use the following FLASHBACK DATABASE command:

FLASHBACK DATABASE TO BEFORE RESETLOGS;

As with other uses of FLASHBACK DATABASE (until SCN, until TIME, etc.), if the target SCN is before the beginning of the flashback database window, an error is returned and the database is not modified. If the command completes successfully, then the database is left mounted and recovered to the most recent SCN before the OPEN RESETLOGS operation in the previous incarnation.

6. Open the database read-only in SQL*Plus and perform queries to ensure that the effects of the logical corruption have been reversed.

ALTER DATABASE OPEN READ ONLY;

7. To make the database available for updates again, shut down the database, mount, and open the database with RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

Advertisements

ORA-28043: invalid bind credentials for DB-OID connection

In this article, I will write about resolving ORA-28043 error. Our databases integrated with Oracle Internet Directory and registered in OID under domain dc=tholdap,dc=local
Some users in my company reported that they can’t login to database using OID user accounts. They get ORA-28043 error when attempt to login database. In this situation, first thing to do is enable a trace for this error in database as below.

sqlplus / as sysdba

SQL> alter system set events ‘28033 trace name context forever, level 9’;

Try to reconnect with OID user account and get ORA-28043 error again.

Go trace file location under directory $ORACLE_BASE/diag/rdbms/$SID/$SID/trace

The sample trace file contains following lines.

vi /u01/app/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_ora_9951.trc

ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: SunOS
Node name: dbtest
Release: 5.10
Version: Generic_150400-09
Machine: sun4v
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 2610
Unix process pid: 9951, image: oracle@dbtest

*** ACTION NAME:() 2016-02-24 09:04:40.463
*** MODULE NAME:(Toad.exe) 2016-02-24 09:04:40.463
*** SERVICE NAME:(TESTDB) 2016-02-24 09:04:40.463
*** SESSION ID:(1212.44043) 2016-02-24 09:04:40.463
kzld_discover received ldaptype: OID
kzld found pwd in wallet
KZLD_ERR: Failed to bind to LDAP server. Err=49
KZLD_ERR: 49
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.
~
~

According to trace file, the error message is related to binding to LDAP Server.

kzld_discover received ldaptype: OID
kzld found pwd in wallet
KZLD_ERR: Failed to bind to LDAP server. Err=49
KZLD_ERR: 49
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.

Let me check, if I can connect to ldap server using username and password stored in database wallet.

Now, there is a question: How can we find the username and password stored in our database wallet ?

$ mkstore -wrl $ORACLE_BASE/admin/TESTDB/wallet -viewEntry ORACLE.SECURITY.DN
Oracle Secret Store Tool : Version 11.2.0.3.0 – Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: abcd678xx_

ORACLE.SECURITY.DN = cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local
$ mkstore -wrl $ORACLE_BASE/admin/TESTDB/wallet -viewEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool : Version 11.2.0.3.0 – Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: abcd678xx_

ORACLE.SECURITY.PASSWORD = +HKRbmQ7

mkstore will ask you for wallet password which you entered during database OID registration.

Now, we have the username and password as below.

ORACLE.SECURITY.PASSWORD = +HKRbmQ7
ORACLE.SECURITY.DN = cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local

Now it is needed to verify database connection to LDAP Server.

First let me get port number from ldap.ora file located in directory $ORACLE_HOME/network/admin.

$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/ldap.ora
# ldap.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/ldap.ora
# Generated by Oracle configuration tools.

DIRECTORY_SERVERS= (idmoid.vodafone.local:1389:1636)

DEFAULT_ADMIN_CONTEXT = “dc=tholdap,dc=local”

DIRECTORY_SERVER_TYPE = OID

Run ldapbind command to check connection to LDAP Server.

$ ldapbind -h idmoid.vodafone.local -p 1389 -D cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local -w kSlIt+n2
ldap_bind: Invalid credentials

So the problem was due to Invalid credentials. All right! How we can solve this issue?

There is two way:

1- Go to OID and update password for cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local at attribute userPassword with value received from ORACLE.SECURITY.PASSWORD

2- Re-register the database using DBCA as below

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=dirManager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

dbca -silent -configureDatabase -sourceDB TESTDB -registerWithDirService true -dirServiceUserName cn=dirManager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

After re-registering database, ldapbind will be successful

$ ldapbind -h idmoid.vodafone.local -p 1389 -D cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local -w +HKRbmQ7
bind successful

Now, users can login database successfully.

I want to note that, if there is spaces in your CN (Directory Service username) then you must write \20 instead of spaces.

For example: Lets assume that our directory service username is “directory manager”. So we must write it as below in DBCA

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=directory\20manager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

You will get an error without “\20” as below

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=directory manager -dirServicePassword OracleTHO11 walletPassword abcd678xx_
manager is an invalid command line argument.

Migration From SQL Server To Oracle Using SQL Developer

November 29, 2012 17 comments

Migration is the process of copying the schema objects and data from a non-Oracle database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database.

To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer:

– Migrating using the Migration Wizard

– Copying tables to Oracle

Migrating Using the Migration Wizard

The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows:

– Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2),

– Converting it to Oracle format,

– Generating DDL,

– Performing the conversion.

After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database.

1- I created sample SQL Server database named TALIPTEST.

2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants.

 CREATE USER MIGRATIONS IDENTIFIED BY “migration”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

grant create session to migrations;

grant resource to migrations;

grant create view to migrations;

For multischema migrations, you must grant the privileges with the ADMIN option as follows.

grant resource to migrations with admin option;

grant create role to migrations with admin option;

grant alter any trigger to migrations with admin option;

grant create user to migrations with admin option;

3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open  sqldeveloper.exe file to open SQL Developer.

5- Right click to the “Connections” and click the “New Connections”.

6-  Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository.

8- Schema objects needed for migration will be created in MIGRATIONS schema.

9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . Extract the dowloaded zip file named jtds-1.2-dist.zip

10- Click “Tools” and then “Prefenrences”.

11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.

12- Select jar file located in jTDS driver folder.

13- Click “OK” button.

14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows.

15- And finally, let us create the database connection which we will migrate to.

16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle”  or you can click “Tools>Migration>Migrate…”.

17- Click “Next” button.

18- Select migration repository database connection.

19- Give a name for migration project and select a directory to write output.

20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts.

21- Select SQL Server database which we want to migrate to Oracle.

22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected.

23- Select target database connection.

24- If you select offline migration then offline migration script will be generated in the project output directory.

25- Select the connections to be used for online data move.

26- Click “Finish” button so start migration.

27- Migration and conversion process will be perfomed as follows.

Now, Our SQL Server database in Oracle 🙂

Copying tables to Oracle

Right click the table located in third party database and click “Copy To Oracle”

Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data.

Copy to Oracle database is finished.

Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Talip Hakan Öztürk

My Second ORACLE Book is Published in Turkish…

August 30, 2012 1 comment

In this book,  I addressed the DBA tasks  in separate sections such as Monitoring, RMAN backup and recovery, Oracle ASM, Data Guard, Database Security, and Version upgrade.

The best method of learning is the practical learning. With this in mind I gave particular importance to the screen shots. Thus, you can do practic by following the book.

Comments and suggestions about the book is very valuable for me. You can send your comments and suggestions to my e-mail address talip_hakan_ozturk@hotmail.com

Hope to get the most from my Oracle books…

http://www.idefix.com/kitap/oracle-database-11g-r2-ileri-veritabani-yonetimi-talip-hakan-ozturk/tanim.asp?sid=Y6RLXNTFSV6V5T0ZZV0V

Talip Hakan Öztürk

Oracle ACE, 10g/11g OCP

Real Application Testing – Database Replay (3)

Hi Friends,

We learned how to capture the workload on the Oracle Enterprise Manager  and preprocessing the workload using RAT-Database Replay feature. In this article, we will learn the replaying workload on the Oracle Enterprise Manager. This step is performed on the test system. The test system must be made of the planned change (upgrade, hardware change, the database parameter changes, etc.). Now let’s implement a step by step.

1-  Login to OEM

2-Click “Database” link.

3- Click “Software and Support” page. Click “Database Replay” link under “Real Application Testing”

4-Click “Go to task” button across the “ReplayWorkload”

4-Choose directory object “CAPTUREDIR” and click ”Set Up Replay” button.

5- You must complete prerequisites on the screen. Click “Continue” button.

6-You will get a warning that replay operation should be performed in a completely isolated test system. Click “Continue” button.

7-Click Next

8- Specify connect descriptor for client connection. You can test connection using “Test Connection” button.

9-Prepare replay clients.  Replay client (wrc) is a multithread program where each thread submits a captured session workload. Click Next.

10- The database will wait for connection from the wrc client program.

11-Connect using WRC. You can also connect to the database remotely  using TNS alias. You can find wrc executable in the 11g client software.

12-After connection, OEM will detect it. And you can continue.

13-Click Submit button to start replay process.

14- You can monitor replay status on the wrc client.

15- You will see “Replay finished” message on the screen after the completion replay process.

16- Click Report tab to compare period reports.

 We learned how to use the “Database Replay” feature on the OEM.  I will write same operations using the API in the next article.

Talip Hakan Öztürk

How to Capture the Workload using the Oracle Enterprise Manager? Database Replay-1

How to Preprocess the Workload for Replay using the Oracle Enterprise Manager? Database Replay-2

How to Replay the Workload using the Oracle Enterprise Manager? Database Replay-3

Real Application Testing – Database Replay (2)

Hi Friends,

We learned how to capture the workload on the Oracle Enterprise Manager using RAT-Database Replay feature. In this article, we will learn the process of preprocessing the workload on the Oracle Enterprise Manager. Preprocessing prepares a captured workload for replay. This operation must be done once after each capture.This step is performed on the test system to replay. Now let’s implement a step by step.

Prerequisites

1- Allocate physical disk space on the test system. Create a directory on OS.

# mkdir /data1/dbreplay

2- Copy the captured files into /data1/dbreplay  directory.

3- Create Oracle directory object.

SQL> CREATE DIRECTORY capturedir AS ‘ /data1/dbreplay ‘;

Now you can proceed to preprocess the workload on OEM.

1- Login to OEM

2- Click “Database” link.

3- Click “Software and Support” page. Click “Database Replay” link under “Real Application Testing”


4-Click “Go to task” button across the “Preprocess Workload”

5-Choose directory object “CAPTUREDIR” and click “Preprocess Workload” button.

6-Click Next.


7- Write OS username and password and click next. 
8-Click Submit button.

9- A job will proceed to preprocess captured files for replay. You can click “View Job” link to monitor job status

In the next article I will write the Replay and Analyse phase.

To meet in the next article …

Talip Hakan Öztürk

 

How to Capture the Workload using the Oracle Enterprise Manager? Database Replay-1

How to Preprocess the Workload for Replay using the Oracle Enterprise Manager? Database Replay-2

How to Replay the Workload using the Oracle Enterprise Manager? Database Replay-3

Real Application Testing – Database Replay (1)

May 10, 2012 1 comment

Hi Friends,

Oracle Real Application Testing, an option that comes with Oracle Enterprise Editition. Oracle Real Application Testing helps you to test the real-life workload after changes on the database such as database upgrades, OS upgrades, parameter changes, hardware replacement, etc. There are two features “SQL Perfomance Analyzer” and “Database Replay” will help us fine-tuning on the database before passing production.

Now let’s learn RAT – “Database Replay” feature closely.

When can you use RAT – “Database Replay” feature?

System Changes

– Hardware replacement such as CPU, RAM, etc.

– Database and OS upgrades

– Storage changes (OCFS2 – ASM)

– OS changes (Windows – Linux)
Configuration Changes

– Single Instance – RAC
– Patch installation
– Database parameter change

Which database versions are supported?

The workload capture process is supported on the Oracle Database 10g R2 (10.2.0.4) and above versions. The worload replay process is supported on the Oracle Database 11g R1 and above versions.

Which workloads are supported?

 
– All DDL, DML and PL/SQL statements
– Login and Logoff
– Lob columns
– SQL*Loader – Direct Path Load operations
– Distributed transactions
 
In this article, We will learn how to capture the workload using the Oracle Enterprise Manager .
 
Let the following the preparations for the capture process.
 
1- Take full backup of production database. You can use methods such as RMAN Duplicate, Snaphost Standby, Data Pump

2- Allocate physical disk space. Cretae a directory on OS.

# mkdir /data1/dbreplay

3- Create Oracle directory object.

SQL> CREATE DIRECTORY capturedir AS ‘ /data1/dbreplay ‘;

Now you can proceed to capture the workload on OEM.

1- Login to OEM

2- Click “Database” link.

3- Click “Software and Support” page. Click “Database Replay” link under “Real Application Testing”

4- Click “Go to task” button

5- Check the boxes under “Acknowledge”. Before proceeding to capture the workload you should be met prerequisites.

6- It is recommended to restart our database. But we can continue to live in our system without having to restart. If we want to implement a filter to capture the process so we can select a service or a user.

7- Select capture directory.

8- Write OS username and password.

9- Click “Submit” button to start capture.

10- Run applications which you want to capture.

11-To stop capture process, click the “Stop Capture” button. You will see .wmd files in capture directory on OS.

12- Click Yes button.

13- It will ask for exporting AWR data. Click yes to export AWR data.

14- A job will take export of AWR data.

Move all files in capture directory to the test system. In the next article I will write the Pre-Processing phase.

To meet in the next article …

Talip Hakan Öztürk

How to Capture the Workload using the Oracle Enterprise Manager? Database Replay-1

How to Preprocess the Workload for Replay using the Oracle Enterprise Manager? Database Replay-2

How to Replay the Workload using the Oracle Enterprise Manager? Database Replay-3

%d bloggers like this: