ORA-00000: normal, successful completion
Cause: Normal exit
Action: None
All posts by mpbod
I added a new datafile to the primary database, but not automatically added on standby
I added a new datafile to the primary database, but not automatically added on standby.
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 129 is unknown – rename to correct file
ORA-01110: data file 129: ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’
ORA-01157: cannot identify/lock data file 129 – see DBWR trace file
ORA-01111: name for data file 129 is unknown – rename to correct file
ORA-01110: data file 129: ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’
Solution:
Note: It can run on umount state
1- Check the name of the datafile on standby database
SQL> select name from v$datafile; —- This command should show the datafile name as UNNAMEDxxxxx
2- On the standby database
SQL> alter system set standby_file_management=’manual’;
3- On the standby database rename the datafile
SQL> alter database create datafile ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’ as ‘C:\ORACLE\ORA10G\DATABASE\actual_dbfile_name.dbf’
4- On the standby database
SQL> alter system set standby_file_management=’auto’;
5- On the standby database
SQL> recover managed standby database disconnect;
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‘
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.
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
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
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;
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;
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;
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);