Category Archives: Database

Database

Can not connect to WMI provider You do not have permission or the server is not accessible

Can not connect to WMI provider You do not have permission or the server is not accessible

Error:

Can not connect to WMI provider. You do not have permission or the server is not accessible. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Could not find the specified module. [0x8007007e]

Solution:

Find the file “framedyn.dll” and copy it to the folder:

C:\Windows\System (Windows 95/98/Me)

C: \ WinNT \ System32 (Windows NT/2000)

C: \ Windows \ System32 (Windows XP/Vista/7)

If you use a 64-bit version of Windows, you should also place in framedyn.dll

C:\Windows\SysWOW64\

Make sure overwrite any existing files (but make a backup copy of the original file).

Restart your computer.
If the problem persists, try the following:
1. Open Windows Start menu and select “Run …”.
2. Type CMD and press Enter (or if you use Windows ME, type COMMAND)).
3. Type regsvr32 framedyn.dll and click Enter.

Download ‘framedyn.dll‘

 

Database

Oracle DB Console – OEM

Oracle DB Console - OEM

 

You receive one of the following errors when creating dbconsole

ORA-24344, ORA-04063, PLS-00201

Solution:

To resolve this, grant the following permissions to the public:

sqlplus / nolog

conn / as sysdba

grant execute on UTL_SMTP to public;

grant execute on UTL_TCP to public;

grant execute on utl_file to public;

grant execute on DBMS_SQL to public;

grant execute on DBMS_OBFUSCATION_TOOLKIT to public;

Now, run the command to recreate the DB Repos again:

Windows:

%ORACLE_HOME%\ bin\emca -config dbcontrol db -repos recreate

Unix / Linux:

$ ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate

Note that it would be a good idea to revoke these permissions and grant them directly to SYSMAN after the creation of OEM repository.

Reconfigure the Oracle Enterprise Manager

For many reasons you may need to drop the OEM and create it again, for example, to move an instance from one server to another. Here are the simple steps to reconfigure the OEM.

Solution:

– Drop the old repositories and deconfigure the old OEM:

$ sqlplus / nolog

SQL> conn / as sysdba

SQL> drop user sysman cascade;

SQL> DROP PUBLIC SYNONYM setemviewusercontext;

SQL> DROP ROLE mgmt_user;

SQL> DROP PUBLIC SYNONYM mgmt_target_blackouts;

SQL> DROP USER mgmt_view;

– Now you are ready to set it up once again:

$ emca -config dbcontrol db create -repos

OEM Configuration Guide for Instance

To manually configure OEM to an instance of Oracle database is used EMCA script, which can be found in $ ORACLE_HOME / bin (Linux) or %ORACLE_HOME%\bin (Windows). Run the script below:

$ emca create -repos

Fill in the required information, then run the script as below to set the OEM for instance:

$ emca -config dbcontrol db

Fill out the required information again and if all goes well, you will see a message like this:

INFO: The Enterprise Manager URL is http: //hostname:1158/em

Now, to access the OEM, just open a browser and enter the URL provided by EMCA … in my example: http: //hostname:1158/em

*** The first time I did this I got the error message:

SEVERE ‘job_queue_processes’ must be greater than or equal to 1

To solve this, add the line job_queue_processes = N, where N is greater than or equal to 1.

 

Back to previous menu

Database

NLS_CHARACTERSET Changing Character Set in Oracle 11g

NLS_CHARACTERSET Changing Character Set in Oracle 11g

 

Changing Character Set in Oracle 11g

What is the character set of the Oracle DB (NLS_CHARACTERSET)?

A: NLS_CHARACTERSET in Oracle database character sets which can be stored in the database using the data types CHAR, VARCHAR2, LONG, and CLOB. That is, it does not define the language, but the range of characters that can be used.

You can change the character set using the csscan utilities and csalter (Database Character Set Scanner utilities), which are provided by Oracle. Its uses are recommended, as in versions 10g and 11g should not be used ALTER DATABASE CHARACTER SET command to make this change.

The amendment is in two stages:

1. date scanning (csscan): The scanning time is the previous identication of potential problems in changing the character set and data truncation. In this step, for example, it can be identified tables that need to have increased the size of the columns or even the characters that are not in the register of the new character set.

2.  date conversion (csalter): The date conversion is the conversion of data to the new character set.

Note: Before performing the conversion date, you should conduct a full backup of BD, as there may be a data corruption.

Changing Charaset using Set Database Character Set Scanner Utilities (csscan / csalter)

– Backup FULL database  (if something goes wrong, you should restore the database and redo the procedure;

– Check which character set is being used in the environment

SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = ‘NLS_CHARACTERSET’;

As an example let’s assume that this query returned “WE8ISO8859P1” as Character Set current, this will be changed to the WE8MSWIN1252. So you should start the scanning date, excutando “csscan” to the verification of the change impact between these two Character sets.

Note: If this fix is not installed will not be able to run the “csscan” then you should first apply it.

Connect with SQLPLUS with user “SYS” and run the script csminst.sql

SQL> @? /Rdbms/admin/csminst.sql

Then run the “csscan” comand

$ csscan

Character Set Scanner v2.2: Release 11.2.0.3.0 – Production on Wed Feb 27 18:35:08 2013

Copyright (c) 1982, 2011 Oracle and / or its affiliates. All rights reserved.

Username: Password system:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

(1) Full database, (2) User (3) Table, (4) Column: 1> 1

Current database character set is WE8ISO8859P1. Enter new database character set name:> WE8MSWIN125

For the next questions you can press ENTER to use the default value, then starts the scan in the BD tables.

Enumerating tables to scan …

. 1 scanning process SYS.SOURCE $ [AAAABIAABAAAAIRAAA]

…

Creating Database Scan Summary Report …

Creating Individual Exception Report …

Scanner terminated successfully.

They will create three files in the user folder OS: scan.out, scan.err and scan.txt. In them we find information on the results of the csscan. After csscan have validated the change, you must be run csalter to perform data conversion. The script “csalter.plb” is in the “$ ORACLE_HOME/rdbms/admin” and must be performed with the SYS user.

The following will be shown step-by-step completion:

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict; ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1345604 bytes
Variable Size 369100732 bytes
Database Buffers 79691776 bytes
6008832 bytes Redo Buffers
Database mounted.
Database opened.

SQL> @? / Rdbms/admin/csalter.plb

0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary. Please Ensure you have a full backup before initiating this procedure.

Would you like to proceed (Y / N)? Y

old 6: if (UPPER (‘& conf’) <> ‘Y’) then

6 new: if (UPPER (‘Y’) <> ‘Y’) then

Checking validility date …

begin converting system objects

PL / SQL procedure successfully completed.

Alter the database character set …

Csalter operation completed, please restart database

PL / SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

SQL>

After the end of the process, the triggers, the indexes, the contraints entres others, should be altered to their original status in case they become necessary amendments thereto.

Make one last “shutdown” and “startup” of the database.

Check with the query view NLS_DATABASE_PARAMETERS, the current NLS_CHARACTERSET.

SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = ‘NLS_CHARACTERSET’;

VALUE
————–
WE8MSWIN125

 

Back to previous menu

Database

Install SQLPlus and Oracle Client on the MAC OS

Install SQLPlus and Oracle Client on the MAC OS

 

Install SQLPlus and Oracle Client on the MAC OS

Note: In this example we will be installing Oracle Client release 11.2.0.4 64bit

– Download the files on Oracle’s site

instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

instantclient-basic-macos.x64-11.2.0.4.0.zip

– Be sure to download the following files:

Instant Client Package – Basic

Instant Client Package – SQL * Plus

– Extract the files and create an appropriate folder structure

Open Terminal and “unzip” files downloaded

$ Unzip -qq instantclient-basic-macos.x64-11.2.0.4.0.zip

$ Unzip -qq instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

Note: the argument “-qq” tells “unzip” utility to run without screen display. By default the files are extracted into a single directory, after we create well structured directories to keep these files organized. NOT create directory structure the graph, but all the Terminal.

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/bin

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/lib

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/jdbc/lib

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/rdbms/jlib

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/sqlplus/admin

$ Sudo mkdir -p /Applications/oracle/product/instantclient_64/11.2.0.4.0/network/admin Then move the files to these directories as follows:

$ Cd instantclient_11_2 $ sudo mv * ojdbc /Applications/oracle/product/instantclient_64/11.2.0.4.0/jdbc/lib/

$ Sudo mv x * .jar /Applications/oracle/product/instantclient_64/11.2.0.4.0/rdbms/jlib/

$ Sudo mv glogin.sql /Applications/oracle/product/instantclient_64/11.2.0.4.0/sqlplus/admin/

$ Sudo mv * * dylib /Applications/oracle/product/instantclient_64/11.2.0.4.0/lib/

$ Sudo mv * README /Applications/oracle/product/instantclient_64/11.2.0.4.0/ $ sudo mv * /Applications/oracle/product/instantclient_64/11.2.0.4.0/bin/

– Create the file “tnsnames.ora” defining the necessary connections

Copy or create the file “tnsnames.ora” in the destination directory: /Applications/oracle/product/instantclient_64/11.2.0.4.0/network/admin/ Example file contents “tnsnames.ora”

database_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = xxxx) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = instance)
)
)

– Set environment variables

Create or edit the file “.bash_profile”

$ Cd / Users / UserName

$ Vi ~ / .bash_profile

Note: If the file already exists, simply add these lines at the end of the file. If the file does not exist you must create it and include the following lines.

# Oracle client
export ORACLE_HOME = “/ Applications / oracle / product / instantclient_64 / 11.2.0.4.0”
export PATH = $ ORACLE_HOME / bin: $ PATH
export DYLD_LIBRARY_PATH = $ ORACLE_HOME / lib
# Oracle client

Note: To recharge the profile, run the following command: $ source ~ / .bash_profile Assuming the file “tnsnames.ora” is set up correctly, you can now connect to the desired database.

– Connecting with SQLPlus

$ Sqlplus username / password @ database_name

 

Back to previous menu

Database

Manage Path to Oracle Archivelog

Manage Path to Oracle Archivelog

 

To record Oracle Archivelog in more than path, run SQL below:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = ‘LOCATION = /path/to/archive/NameArchive’ scope = spfile;

 

Back to previous menu

Database

Oracle Crosscheck

Oracle Crosscheck

 

To “reset” the log archives to an Oracle backup, just run a crosscheck.

Log into SO (user for Oracle)

$ rman target / nocatalog;

RMAN> crosscheck archivelog all;

To “reset” the log backup for Oracle, just run a crosscheck.

Log into SO (user for Oracle)

$ rman target / nocatalog;

RMAN> crosscheck backup;

 

Back to previous menu

Database

Oracle Database size in GigaByte, MegaByte, KiloByte and Byte

Oracle Database size in GigaByte, MegaByte, KiloByte and Byte

 

View the size of an Oracle database (partial or total in GB, MB, KB and B), run the SQL statements below:

Size of TABLESPACES in GB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024/1024/1024 SIZE_GB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in GB

select sum (bytes) / 1024/1024/1024 SIZE_GB from DBA_SEGMENTS;

TABLESPACES size in MB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024/1024 SIZE_MB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in MB

select sum (bytes) / 1024/1024 SIZE_MB from DBA_SEGMENTS;

TABLESPACES size in KB

select NAME_TABLESPACE tablespace_name, sum (bytes) / 1024 SIZE_KB from group by tablespace_name DBA_SEGMENTS;

Total size of the database in KB

select sum (bytes) / 1024 SIZE_KB from DBA_SEGMENTS;

Size of TABLESPACES B

select NAME_TABLESPACE tablespace_name, sum (bytes) from SIZE_B group by tablespace_name DBA_SEGMENTS;

Total size of the database in B

select sum (bytes) from SIZE_B DBA_SEGMENTS;

 

Back to previous menu

Database

Percentage of use of Oracle Tablespaces

Percentage of use of Oracle Tablespaces

 

% Of Using Oracle Tablespaces: Allocation in MB, Use in MB Free in MB, Use in% in% Free

To view the utilization of table space in%, run the SQL below:

SELECT a.TABLESPACE_NAME “Tablespace Name”
round (a.BYTES / 1024/1024) “MB Allocated”
round ((a.BYTES-lvl (b.BYTES, 0)) / 1024/1024) “Used MB”
nvl (round (b.BYTES / 1024/1024), 0) “MB Free”
round (((a.BYTES-lvl (b.BYTES, 0)) / a.BYTES) * 100.2) “% Used”
round ((1 – ((a.BYTES-lvl (b.BYTES, 0)) / a.BYTES)) * 100.2) “% Free”
FROM (SELECT tablespace_name,
sum (BYTES) BYTES
DBA_DATA_FILES FROM
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
sum (BYTES) BYTES
sys.dba_free_space FROM
GROUP BY tablespace_name) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES) /a.BYTES);

 

Back to previous menu

Database

Size Oracle Database for Data File Type

Size Oracle Database for Data File Type

 

To select the size of Oracle database by type of data file, simply turn the SQL below:

on September serveroutput;
declare
dbf number;
tmpdbf number;
lgf number;
ctl number;
sum number;
begin
select trunc (sum (bytes / 1024/1024), 2) into DBF from v $ datafile;
select trunc (sum (bytes / 1024/1024), 2) into tmpdbf from v $ tempfile;
select trunc (sum (bytes / 1024/1024), 2) into lgf from v $ log l, v $ logfile lf where l.group # = # lf.group;
select trunc (sum (block_size * file_size_blks / 1024/1024), 2) into ctl from v $ controlfile;
select trunc ((DBF tmpdbf + + + lgf ctl) / 1024.2) into sum from dual;
DBMS_OUTPUT.PUT_LINE (chr (10));
DBMS_OUTPUT.PUT_LINE (‘Datafiles:’ dbf || || ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘tempfiles:’ || || tmpdbf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Logfiles:’ || || lgf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Controlfiles:’ || || ctl ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Total Size’ || || sum ‘GB’);
end;

 

Back to previous menu

Database

Manage Oracle JOB

Manage Oracle JOB

 

Create a JOB to run a certain procedure once a day for example, run the following script in SQLPLUS.

jobno variable number;
instno variable number;
begin
dbms_job.submit (: jobno,
‘BEGIN Nome_da_Procedure; END; ‘
, trunc (sysdate) +3/24,
‘SYSDATE + 1,
TRUE
);
END;
/

To check the scheduled jobs.

SELECT * FROM dba_jobs;

To check if it is running.

select * from dba_jobs_running;

To delete jobs.

DELETE FROM WHERE dba_jobs job = 99;

To check when was the last execution.

select JOB, LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC, INTERVAL, FAILURES from dba_jobs;

 

Back to previous menu