Oracle « Różności …

Różności …

19 kwietnia 2016

Rollback

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 09:08

What will happen to rollback session if database shutdown immediate/abort?

Na to pytanie udzielono już odpowiedzi.

bk75 Newbie

bk75 2009-11-28 15:58

I have a long running rollback session after i killed it. The status of session is ‘KILLED’ and when I check the v$transaction, it will take almost 24hours to complete rollback. We cannot wait for so long because we have other dependencies job need the rollback to be completed faster. At the end, we plan to shutdown immediate. Will this help? What will happen to the rollback session if we issue shutdown immediate or shutdown abort? Will the rollback completed successfully?

Hemant K Chitale

Prawidłowa odpowiedź przez Hemant K Chitale dnia 2009-11-28 16:15
If you SHUTDOWN ABORT when there are very many active transactions or transactions being rolled back, then the default FAST_START_PARALLEL_ROLLBACK that occurs on the subsequent Instance Startup will actually mean that the Rollback is executed faster.
However, if it is a single large transaction such as a DELETE against a table with an Index, then it is better to disable FAST_START_PARALLEL_ROLLBACK before restarting the Instance immediately after a SHUTDOWN ABORT. The Parallel Rollback of a single large transaction with an Index can be slower !

FAST_START_PARALLEL_ROLLBACK is an instance parameter which default to LOW (ie a setting of 2 x CPU_COUNT) and can be set to HIGH or FALSE.

Note : If you SHUTDOWN ABORT and STARTUP the database, Oracle only has to do a Rollforward from Redo Logs (for database blocks that haven’t been updated) before it does an OPEN. This is generally a very fast operation. The Rollback of uncomitted transactions is a deferred operation — this means that the Rollback is initiated by SMON or the Parallel Slaves after the Database is OPEN. If a block containing a row that was updated and not committed is referenced by a database session after the OPEN but before it has been rolled back, Oracle does an expedited rollback for that row of the block and then continues doing the rollback of other rows in the background.

Thus, if the transaction had been on Table “A” and the database is SHUTDOWN ABORT and STARTUP, then users can continue accessing and updating all other tables other than Table “A” while the transaction is being rolled back, in the background, after the OPEN.

Hemant K Chitale

Source:
https://community.oracle.com/thread/995750?tstart=0

11 grudnia 2015

oracle em agent unreachable

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 18:20

– To implement the solution, please execute the following steps::
1. Stop the agent
2. Remove all the files from AGENT_HOME/sysman/log/ directory.
3. Backup and edit AGENT_HOME/sysman/config/emd.properties.
4. Add this line:
NMUPM_TIMEOUT=120
5. Uncomment: ThreadPoolModel = LARGE
6. start the agent
7. Monitor the performance of the server

https://community.oracle.com/message/2558818#2558818

10 grudnia 2015

report schema

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 21:25

http://www.dba-oracle.com/t_rman_46_schema_report.htm

9 grudnia 2015

srvctl

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 21:59

https://oracleexamples.wordpress.com/2010/01/22/most-used-srvctl-commands/

What is srvctl? it is the Server Control Utility, we use SRVCTL to start and stop the database and instances, manage configuration information, and to add, move or remove instances and services.

These are some of the srvctl commands I frequently use, this is not a complete reference guide.

I organized the commands as follow:

  • To start a rac database
  • To stop a rac database
  • To check status and configurations
  • To start and stop instances
  • To start, stop and manage services

Start a rac database (order: nodeapps – asm – database)

srvctl start nodeapps -n nodename
srvctl start asm -n nodename
srvctl start database -d dbname
options are: srvctl start database -d dbname -o open | -o mount | -o nomount

Stop a rac database (order: database – asm – nodeapps)
srvctl stop database -d dbname -o immediate
options are: srvctl stop database -d dbname -o normal | -o transactional | -o immediate | -o abort

srvctl stop asm -n nodename
options are: srvctl stop asm -n nodename -o immediate

srvctl stop nodeapps -n nodename

To check status and configurations
Nodeapps:
srvctl status nodeapps -n nodename

srvctl config nodeapps -n nodename

ASM:
srvctl status asm -n nodename
srvctl config asm -n nodename

Database:
srvctl status database -d dbname
srvctl config database -d dbname (shows instances name, node and oracle home)

Instance:
srvctl status instance -d dbname -i instancename

Services:
srvctl status service -d dbname

To start and stop instances

srvctl start instance -d dbname -i instancename
srvctl stop instance -d dbname -i instancename

To start, stop and manage services
srvctl status service -d dbname
srvctl config service -d dbname
srvctl start service -d dbname -s servicename
srvctl stop service -d dbname -s servicename

srvctl relocate service -d dbname -s servicename -i instancename -t newinstancename [-f]

11 grudnia 2014

grant select on all tables

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 16:58

You could, of course, leverage dynamic SQL, i.e.

FOR x IN (SELECT * FROM user_tables)
LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>';
END LOOP;

Of course, if you’re doing this sort of thing, you’d normally grant the privileges to a role and grant that role to one or more users.

Login as your user name and then run the following

declare
cursor c1 is select table_name from user_tables;
cmd varchar2(200);
begin
for c in c1 loop
cmd := ‘GRANT SELECT ON ‘||c.table_name||’ TO YOURUSERNAME’;
execute immediate cmd;
end loop;
end;

22 listopada 2014

Error Opening RPD Online: [nQSError: 46036] Internal Assertion

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 13:12

http://bidirect.blogspot.com/2013/07/error-opening-rpd-online-nqserror-46036.html

6 listopada 2014

Oracle BI

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 10:18

http://www.oracle.com/us/solutions/business-analytics/business-intelligence/overview/index.html

http://www.oracle.com/technetwork/middleware/bi-foundation/overview/index.html?ssSourceSiteId=ocomen

10 października 2014

ora-00604 timezone region not found sql developer

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 07:06

check timezone in your database

add following entry do sqldeveloper.conf with appropriate timezone name

AddVMOption -Duser.timezone=America/Santiago

restart sqldeveloper

VOILA!

11 sierpnia 2014

APEX upgrade

Zaszufladkowany do: ApEx,Bazy danych,Oracle,Programowanie — Tagi: , — Jacek @ 13:20

http://infotechinspiration.blogspot.com/2010/07/upgrade-oracle-apex-40-in-11g-database.html

21 lipca 2014

datapump expdp impdp

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 09:33

CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp hr/HR directory=DATADUMP dumpfile=FMC_LOGICAL_VESSELS.DMP remap_schema=VTRACK:HR exclude=index,constraint,grant,trigger

expdp user/pass schemas= directory=dumpdir \
dumpfile=.dmp \
logfile=expdp_.log

impdp user/pass schemas=schema1 directory=dumpdir \
remap_schema=schema1:schema2 \
dumpfile=schema1.dmp \
logfile=impdp_schema2.log

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

http://oradbaeasy.wordpress.com/2009/06/11/importing-and-exporting-oracle-database-using-datapump-utility/

expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

17 lipca 2014

table to csv

Zaszufladkowany do: Bazy danych,Linux,Oracle,Oracle — Tagi: — Jacek @ 18:19

#!/usr/bin/bash

FILE=”emp.csv”

sqlplus -s scott/tiger@XE <

SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF

SPOOL $FILE

SELECT * FROM EMP;

SPOOL OFF
EXIT
EOF

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed in every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP “,” => Setting the column separator to “,”. With this setting, the list displayed by the SELECT clause will be comma separated. This is the most important setting of this script.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value which is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say “25 rows selected”. In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SELECT * FROM EMP => The query which gives the entire table contents of EMP. If only a part of the table is desired, the query can be updated to get the desired result.

SPOOL OFF => To stop writing the contents of the sql session to the file.

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html#sthash.XLJ9iSpO.dpuf

2 czerwca 2014

Oracle on Solaris 10 : Fixing the ‘ORA-27102: out of memory’ Error

Zaszufladkowany do: Bazy danych,Oracle,Oracle,Solaris — Tagi: , — Jacek @ 21:12

https://blogs.oracle.com/mandalika/entry/oracle_on_solaris_10_fixing

ASM

Zaszufladkowany do: Bazy danych,Oracle,Oracle — Tagi: — Jacek @ 17:48

http://blog.sina.com.cn/s/blog_4ea0bbed0100zd6d.html

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG94156

17 lutego 2014

joins

Zaszufladkowany do: Bazy danych,Oracle,Programowanie,SQL — Tagi: — Jacek @ 06:30

NATURAL JOIN

INNER JOINS

SELECT employee_id, last_name,
department_name
FROM
hr.employees JOIN hr.departments
USING (department_id) ;

SELECT l.city, d.department_name
FROM
locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

SELF JOIN
Sometimes you need to join a table to itself. To find the name of each employee’s manager,
you need to join the EMPLOYEES table to itself, or perform a self-join.

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);

NONEQUIJOINS


To return the unmatched rows, you can use
an OUTER join. An OUTER join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other table satisfy the join
condition.
There are three types of OUTER joins:
•LEFT OUTER
•RIGHT OUTER
•FULL OUTER

SELECT e.last_name, e.department_id, d.department_name
FROM
employees e LEFT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the EMPLOYEES table, which is the left table, even if there
is no match in the DEPARTMENTS table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the DEPARTMENTS table, which is the table at the right,
even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no
match in the EMPLOYEES table.

——— CARTESIAN PRODUCT ————

SELECT last_name, department_name
FROM
employees CROSS JOIN departments ;

22 stycznia 2014

ORACLE – archive log mode.

Zaszufladkowany do: Bazy danych,Oracle,Oracle — Tagi: — Jacek @ 20:49
SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

ORACLE – Renaming and moving files.

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 09:15

http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php

14 listopada 2013

Oracle 11g installation on Centos 6.4

Zaszufladkowany do: Bazy danych,Linux,Oracle — Tagi: , — Jacek @ 09:55

http://www.tecmint.com/oracle-database-11g-release-2-installation-in-linux/

25 kwietnia 2013

Kolejność działań

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 07:50

24 listopada 2012

Oracle Enterprise Manager in Linux.

Zaszufladkowany do: Bazy danych,Linux,Oracle — Tagi: , — Jacek @ 22:15

emctl start dbconsole

emctl status agent

emctl stop agent

https://host:1158/em/console/aboutApplication

ORA-00845: MEMORY_TARGET not supported on this system

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 21:52

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or #df -k command.
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm
We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

Źródła:
http://arjudba.blogspot.com/2009/01/ora-00845-memorytarget-not-supported-on.html
Starsze wpisy »

Strona startowa: www.jaceksen.pl