Showing posts sorted by relevance for query Server. Sort by date Show all posts

sql server to postgresql converter Thursday, September 15, 2022

SQL server to PostgreSQL data types mapping

postgresql vs sql server syntax differences

Data Types Mapping for Migrating

Microsoft SQL Server to PostgreSQL or vice versa

Microsoft SQL Server and PostgreSQL have some similar data types while others are not. Before migration, it is important to keep in mind the right types representing:

SQL Server Data Types	PostgreSQL Data Types
BIGINT	BIGINT
BINARY(n)	BYTEA
BIT	BOOLEAN
CHAR(n), CHARACTER(n)	CHAR(n), CHARACTER(n)
DATE	DATE
DATETIME	TIMESTAMP(3)
DATETIME2(p)	TIMESTAMP(p)
DATETIMEOFFSET(p)	TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT(p)	FLOAT(p)
IMAGE	BYTEA
INT, INTEGER	INT, INTEGER
MONEY	MONEY
NCHAR(n)	CHAR(n)
NTEXT	TEXT
NUMERIC(p,s)	NUMERIC(p,s)
NVARCHAR(n)	VARCHAR(n)
NVARCHAR(max)	TEXT
REAL	REAL
ROWVERSION	BYTEA
SMALLDATETIME	TIMESTAMP(0)
SMALLINT	SMALLINT
SMALLMONEY	MONEY
TEXT	TEXT
TIME(p)	TIME(p)
TIMESTAMP	BYTEA
TINYINT	SMALLINT
UNIQUEIDENTIFIER	UUID or CHAR(16)
VARBINARY(n), VARBINARY(max)	BYTEA
VARCHAR(n)	VARCHAR(n)
VARCHAR(max)	TEXT
XML	XML

Natural DBA

how to export weblogic domain Thursday, September 15, 2022

Migrate Weblogic Domain to another Server

Migrate WebLogic 12c steps in Linux

Overview

Here I am going to share with you how we can rename WebLogic 11g Instance for cloning purposes or as per any other requirement.

Example

We have 2 x Linux Servers

Server 1 = prodserver.domain.com

Server 2 = testserver.domain.com

Step 1

Edit file vi /oracle/domains/FRDomain/config/fmwconfig/servers/AdminServer/applications/em/META-INF/emoms.properties

And change

1. oracle.sysman.emSDK.svlt.ConsoleServerHost=testserver.domain.com

2. oracle.sysman.emSDK.svlt.ConsoleServerName=testserver.domain.com:7001_Management_Service

Step 2

Open file /oracle/domains/FRDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/rwservlet.properties and change host name entry.

Step 3

Edit Start and Stop Web logic scripts

Open file /oracle/domains/FRDomain/bin/startWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/startManagedWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopManagedWebLogic.sh and change host name entry.

Step 4

Verify both scripts

1) Start Weblogic using script and Form and Report Services.

2) Stop Weblogic using script and Form and Report Services.

Conclusion

In this way, we can make a Test Server for Weblogic Forms and Reports environment.

Natural DBA

tables Thursday, September 15, 2022

SQL Query to get Row Count For all Tables in SQL Server

Overview

I am using the AdventureWorks as default database and will open Query Analyzer after selecting this database

Here is the download link for AdventureWorks for almost all available MS SQL Server versions

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

Open Query Analyzer

Write / Copy the following Query as below

SELECT

QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

, SUM(sPTN.Rows) AS [RowCount]

FROM

sys.objects AS sOBJ

INNER JOIN sys.partitions AS sPTN

ON sOBJ.object_id = sPTN.object_id

WHERE

sOBJ.type = 'U'

AND sOBJ.is_ms_shipped = 0x0

AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY

sOBJ.schema_id

, sOBJ.name

ORDER BY [TableName]

Conclusion

The above SQL Query has returned 71 Tables with their Schema name and Row Count.

Natural DBA

oracle apps r12 cloning steps using rman Tuesday, August 12, 2014

How to Clone Oracle EBS R12.1

CLONING ORACLE EBS R12.1

P R E C L O N E S T E P S

Step 1

Stop Apps Services

[oracle@erp scripts]$ ./adstpall.sh apps/apps

Step 2

Run adpreclone on DB Node

[oracle@erp~PROD_erp]$ perl adpreclone.pl dbTier

Run adpreclone Apps Node

[oracle@erp~PROD_erp]$ perl adpreclone.pl appsTier

Step 3

Taking Cold Backup using Tar

Stop all apps services

Stop db services

Take Tar Backup

AS=ERP-CLONE -TAR_$(date +%d-%m-%Y).tgz

tar -cvzf /backup/$AS /mk01/oracle

Step 4

(a)

Start Apps Services on ERP

[oracle@erp scripts]$ ./adstrtal.sh apps/apps

(b)

Step 5

Logon to Apps Server

http://erp.domain.com:8000 and follow these steps

click System Administrator -> Concurrent -> Manager -> Administer

make sure all services are running with status 1

Additional Steps (if errors )

If error found in Starting apps services then

1. stop all apps services

2. run adautocfg.sh

P O S T C L O N E S T E P S

oracle@test's password:

STEP 1

Stop Apps services

[oracle@test scripts]$ ./adstpall.sh apps/apps

Stop DB and Listener services

[oracle@erp TEST_erp]$ ./addbctl.sh stop TEST

[oracle@erp TEST_erp]$ ./addlnctl.sh stop immediate

STEP 2

Renaming Existing Directories

[oracle@test]$ mv oracle oracle.old

STEP 3

Moving copied Directories of apps, db , inst and diag

[oracle@test:]$ mkdir -p /mk01/oracle/TEST

[oracle@test]$ mv apps /mk01/oracle/TEST/

[oracle@test]$ mv inst /mk01/oracle/TEST/

[oracle@test]$ mv db /mk01/oracle/TEST/

[oracle@test]$ mv diag /mk01/oracle/PROD/

STEP 4

a) Run adcfgclone for DB on TEST SERVER

[oracle@test]$ cd /mk01/oracle/PROD/db/tech_st/11.1.0/appsutil/clone/bin/

[oracle@test bin]$ perl adcfgclone.pl dbTier

Enter the APPS password :

Provide the values required for the creation of the new Database Context file.

Target System Hostname (virtual or normal) [test] :

Target Instance is RAC (y/n) [n] :

Target System Database SID: TEST

Target System Base Directory : /mk01/oracle/TEST

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [1] :

Target System DATA_TOP Directory 1 [/mk01/oracle/TEST/db/apps_st/data] :

Target System RDBMS ORACLE_HOME Directory [/mk01/oracle/TEST/db/tech_st/11.1.0] :

Do you want to preserve the Display [null] (y/n) ? : y

Do you want the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 5

b) Run adcfgclone for Apps Node

[oracle@test bin]$ cd /mk01/oracle/TEST/apps/apps_st/comn/clone/bin/

[oracle@test]$ cd /mk01/oracle/TEST/apps/apps_st/comn/clone/bin

[oracle@testbin]$ perl adcfgclone.pl appsTier

Enter the APPS password :

Provide the values required for the creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [test] :

Target System Database SID: TEST

Target System Database Server Node [test] :

Target System Database Domain Name [domain.com] :

Target System Base Directory : /mk01/oracle/TEST

Target System Tools ORACLE_HOME Directory [/mk01/oracle/TEST/apps/tech_st/10.1.2] :

Target System Web ORACLE_HOME Directory [/mk01/oracle/TEST/apps/tech_st/10.1.3] :

Target System APPL_TOP Directory [/mk01/oracle/TEST/apps/apps_st/appl] :

Target System COMMON_TOP Directory [/mk01/oracle/TEST/apps/apps_st/comn] :

Target System Instance Home Directory [/mk01/oracle/TEST/inst] :

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [disabled] :

Do you want to preserve the Display [erpfinapps:0.0] (y/n) : y

Do you want the target system to have the same port values as the source system (y/n) [y]? : n

Target System Port Pool [0-99] : 5

UTL_FILE_DIR on the database tier consists of the following directories.

1. /usr/tmp

2. /usr/tmp

3. /mk01/oracle/TEST/db/tech_st/11.1.0/appsutil/outbound/TEST_erp

4. /usr/tmp

Choose a value which will be set as APPLPTMP value on the target node [1] :

Do you want to startup the Application Services for ERP? (y/n) [y] :

Starting application Services for ERP:

Running:

/mk01/oracle/TEST/inst/apps/TEST_erp/admin/scripts/adstrtal.sh –nopromptmsg

STEP6

Verify URL (if errors)

Go to Browser and open this page

http://test.domain.com:8005/OA_HTML/AppsLogin

STEP7

Verify URL (if errors)

If an error is found in Starting apps services then:-

1. Stop all Apps Services

2. Run adautocfg.sh

3. Start apps services

Natural DBA

oracle database migration steps Wednesday, October 15, 2014

Migrate Oracle Database Windows to Linux

Convert Oracle Database Windows to Linux

Open oracle database SQL prompt from the command line for checking the current OS Version

Finding Supported platforms (Need to connect to SQLPLUS)

sqlplus “/ as sysdba”

SQL> select * from v$transportable_platform order by 2;

Checking the Number of Objects in our database

Query is

Select count(*) from dba_objects;

As we can see our source database has 79885 objects

Note: We will compare it After the Migration process completion

Verifying the SOURCE database is ready for migration

Database Name: testdb

Platform Name: Microsoft Windows IA (32-bit)

Note: We will shutdown the database first and open it in read-only mode

Shutdown immediate;

Startup mount;

Alter database open read only;

Now below command will make it ready for migration

Set serveroutput on;

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_db('Linux x86 64-bit');

End;

PL/SQL procedure successfully completed. (See below Window);

Check and See if External objects are present

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_external;

End;

PL/SQL procedure successfully completed.

The following directories exist in the database we will use for this activity:

SYS.DATA_PUMP_DIR

We are not using it here in this exercise (We will Skip this Step)

Open RMAN prompt for Starting Conversion

Commands to Run (Windows CMD)

rman target /

Converting Database or Preparing for our Destination OS

Command

convert database new database 'testdb' transport script 'C:\Users\kamran\testdb\trasnport.sql' db_file_name_convert 'D:\app\oradata\testdb\testdb' 'C:\Users\kamran\testdb';