Till version 11g the log rotation may been easily done by lsnrctl and set log_file to new name. With advent of ADR if used it blocks this way.
Excellent article on the theme IMHO at http://msutic.blogspot.com/2009/06/truncating-rotating-flushing.html.
Thursday, 19 July 2012
Wednesday, 18 July 2012
Su-like technology for Oracle db
From time to time I meet with the situation when it comes to perform many changes on many db schemas provided as a set of scripts.
- The simplest way to do this is to logon sequentially to chosen schema and perform there all the necessary changes. This attitude has some drawbacks:
- one needs to know a password to every schema visited
- one needs to perform authentication to db many times
- one needs to run many scripts
- first improvement is to create master script, use CONNECT <<user>> command of sqlplus - but this still do not resolve providing passwords
- so we may create a more powerful account, gathering all the needed privileges and use
ALTER SESSION SET CURRENT_SCHEMA=<<user>>; instead of CONNECT - this is a step in right direction and as long as scripts use simple objects and functionalities this way addresses all the inconveniences. Another ability is to prefix all the used objects.
- still yet we are far from perfectness - there are some functionalities which work wrong - the simplest example here is usage of private db links. Here we may use so called proxy users. Starting from 10gR2 these are available through sqlplus. We don't need a powerful user. We return back to use CONNECT, this time slightly modified. First we need an account with CREATE SESSION privilege. We need to alter all schemas, which we plan to make available:
ALTER USER <<user>> GRANT CONNECT THROUGH <<proxy user>> (we may revoke it with REVOKE CONNECT THROUGH). This time we connect to any so configured user through
CONNECT <<proxy user>>[<<user>>]/<<proxy user password>>. With this addressing all the problems is easy - we need to authenticate as only one user, so even though reconnecting many times, may use sqlplus variable substitution in order to provide a password only once.
Subscribe to:
Posts (Atom)