How to Change Maxdatafiles, Maxlogfiles after Creating Database

1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
——–

You should only need to recreate your control file under very special
circumstances:

- All current copies of the control file have been lost or are corrupted.

- You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

- You are restoring a backup in which the control file is corrupted or
missing.

- Oracle Customer Support advises you to do so.

- If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

ORA – 00202 Oracle Problem with Control File

ORA-00202: controlfile: ‘C:\ORACLE\ORADATA\ORACLE91\CONTROL02.CTL’

ORA-27091: skgfqio: unable to queue I/O

ORA-27070: skgfdisp: async read/write failed

OSD-04006: ReadFile() failure, unable to read from file

O/S-Error: (OS 23) Data error (cyclic redundancy check).

i find this problem on one of my server database. this problem will make oracle fail to mount and open. if you try to connect via sqlplus, you will get error message : Oracle is on initialization startup/shutdown. so you can not enter the database.

below is the step that i do, to resolve the problem.(oracle ver 9i and higher)

1. connect to database using sqlplus with SYS as SYSDBA

2. shutdown immediate the database.

3. create a backup copy of all control file(usually consist of 3 control file with extension CTL) on folder ORADATA\

4. rename CONTROL02.CTL (corrupt control file) to CONTROL02.bak

5. rename one of working control file to become CONTROL02.CTL and then copy this file to folder ORADATA\

6.then startup the database via sqlplus using SYS as SYSDBA

this step only working if one of controlfile is corrupt and need to be replace. and also your controlfile is multiplex. Make sure that the error message is exactly the same with what is written above.

^^

Minh Tran

Tutorial :: Using AT commands to Send and Receive SMS

Tutorial :: Using AT commands to Send and Receive SMS
This AT command tutorial is written to support our Teltonika T-ModemUSB, a USB2.0 GSM modem based on the Nokia 12i GSM module – fast EDGE technology is supported. Some of the most popular applications are SMS based telemetry, security and news broadcasting.

Steps using AT commands to send and receive SMS using a GSM modem from a computer

1. Setting up GSM modem
2. Using the HyperTerminal
3. Initial setup AT commands
4. Sending SMS using using AT commands
5. Receiving SMS using using AT commands
6. Using a computer program to send and receive SMS

After succesfully sending and receiving SMS using AT commands via the HyperTerminal, developers can ‘port’ the ASCII instructions over to their programming environment, eg. Visual Basic, C/C++ or Java and also programmically parse ASCII messages from modem.

1. Setting up your GSM modem

Most GSM modems comes with a simple manual and necessary drivers. To setup your T-ModemUSB, download the USB GSM Modem Quick Start ( Windows ) guide (460kB PDF). You would be able to send SMS from the Windows application and also setup GPRS connectivity. The GSM modem will map itself as a COM serial port on your computer.

Windows based control panel to setup GSM modem, GPRS and send SMS

2. Using the HyperTerminal

Hint :: By developing your AT commands using HyperTerminal, it will be easier for you to develop your actual program codes in VB, C, Java or other platforms.

Go to START\Programs\Accessories\Communications\HyperTerminal (Win 2000) to create a new connection, eg. “My USB GSM Modem”. Suggested settings ::

- COM Port :: As indicated in the T-Modem Control Tool
- Bits per second :: 230400 ( or slower )
- Data Bits : 8
- Parity : None
- Stop Bits : 1
- Flow Control : Hardware

You are now ready to start working with AT commands. Type in “AT” and you should get a “OK”, else you have not setup your HyperTerminal correctly. Check your port settings and also make sure your GSM modem is properly connected and the drivers installed.

3. Initial setup AT commands

We are ready now to start working with AT commands to setup and check the status of the GSM modem.

AT	Returns a “OK” to confirm that modem is working
AT+CPIN=”xxxx”	To enter the PIN for your SIM ( if enabled )
AT+CREG?	A “0,1″ reply confirms your modem is connected to GSM network
AT+CSQ	Indicates the signal strength, 31.99 is maximum.

4. Sending SMS using AT commands

We suggest try sending a few SMS using the Control Tool above to make sure your GSM modem can send SMS before proceeding. Let’s look at the AT commands involved ..

AT+CMGF=1	To format SMS as a TEXT message
AT+CSCA=”+xxxxx”	Set your SMS center’s number. Check with your provider.

To send a SMS, the AT command to use is AT+CMGS ..

AT+CMGS=”+yyyyy” <Enter>
> Your SMS text message here <Ctrl-Z>

The “+yyyyy” is your receipent’s mobile number. Next, we will look at receiving SMS via AT commands.

5. Receiving SMS using AT commands

The GSM modem can be configured to response in different ways when it receives a SMS.

a) Immediate – when a SMS is received, the SMS’s details are immediately sent to the host computer (DTE) via the +CMT command

AT+CMGF=1	To format SMS as a TEXT message
AT+CNMI=1,2,0,0,0	Set how the modem will response when a SMS is received

When a new SMS is received by the GSM modem, the DTE will receive the following ..

+CMT :  “+61xxxxxxxx” , , “04/08/30,23:20:00+40″
This the text SMS message sent to the modem

Your computer (DTE) will have to continuously monitor the COM serial port, read and parse the message.

b) Notification – when a SMS is recieved, the host computer ( DTE ) will be notified of the new message. The computer will then have to read the message from the indicated memory location and clear the memory location.

AT+CMGF=1	To format SMS as a TEXT message
AT+CNMI=1,1,0,0,0	Set how the modem will response when a SMS is received

When a new SMS is received by the GSM modem, the DTE will receive the following ..

+CMTI: “SM”,3	Notification sent to the computer. Location 3 in SIM memory
AT+CMGR=3 <Enter>	AT command to send read the received SMS from modem

The modem will then send to the computer details of the received SMS from the specified memory location ( eg. 3 ) ..

+CMGR: “REC READ”,”+61xxxxxx”,,”04/08/28,22:26:29+40″
This is the new SMS received by the GSM modem

After reading and parsing the new SMS message, the computer (DTE) should send a AT command to clear the memory location in the GSM modem ..

AT+CMGD=3 <Enter>   To clear the SMS receive memory location in the GSM modem

If the computer tries to read a empty/cleared memory location, a +CMS ERROR : 321 will be sent to the computer.

6. Using a computer program to send and receive SMS

Once we are able to work the modem using AT commands, we can use high-level programming ( eg. VB, C, Java ) to send the AT ASCII commands to and read messages from the COM serial port that the GSM modem is attached to.

Developer Tip : To speed up development of your solution, download trial versions of SerialTools for C# and VB.NET or SerialTools ActiveX for Windows - with sample serial port ( modem ) reading and writing samples.

Error When exporting data from Oracle DB 9i 9.2.0.5

When exporting data from Oracle DB 9i 9.2.0.5, encountered by the error:

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user QLNS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user QLNS
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

Solution:

Run catalog.sql,catproc.sql and catexp.sql ( logged in as sys).

Then you should export as normal.

Regards,

Minh Tran

Get & set NLS_LANG_XXX from / to Oracle DB

1. To get currently used character set, issue:

SELECT * FROM nls_database_parameters WHERE parameter LIKE ‘%CHARACTERSET%’.

nls_database_parameters is a view.

2. Check and update, for example, to set nls_characterset = UTF8,nls_nchar_characterset = UTF8, use table props$:

UPDATE props$ SET value$ = ‘UTF8′ WHERE NAME LIKE ‘%CHARACTERSET%;

commit;

There you go…

Minh Tran