CREATE OR REPLACE TRIGGER rtsrv_cus_sgn_before
BEFORE INSERT OR DELETE
ON rtsrv_cus_sgn
FOR EACH ROW
BEGIN
IF INSERTING
THEN
INSERT INTO rtsrv_cus_sgn_trg
(rcs_wbl_num, rcs_dlv_eno, rcs_cfm_cnd,
rcs_sgn_ymd, rcs_crt_seq, rcs_crt_dtm
)
VALUES (:NEW.rcs_wbl_num, :NEW.rcs_dlv_eno, :NEW.rcs_cfm_cnd,
:NEW.rcs_sgn_ymd, rcs_crt_seq.NEXTVAL, SYSDATE
);
END IF;
IF DELETING
THEN
UPDATE rtsrv_cus_sgn_trg
SET rcs_del_dtm = SYSDATE,
rcs_del_seq = rcs_del_seq.NEXTVAL
WHERE rcs_wbl_num = :OLD.rcs_wbl_num;
END IF;
END;
2009년 3월 16일 월요일
TRIGGER hddht_mtr_blk_before
CREATE OR REPLACE TRIGGER hddht_mtr_blk_before
BEFORE INSERT OR DELETE
ON hddht_mtr_blk
FOR EACH ROW
BEGIN
IF INSERTING
THEN
IF :NEW.hmb_div = 'B2'
THEN
INSERT INTO hddht_mtr_blk_trg
(seq, hmb_div, hmb_dat, hmb_date,
hmb_rgt_mdh
)
VALUES (:NEW.seq, :NEW.hmb_div, :NEW.hmb_dat, :NEW.hmb_date,
:NEW.hmb_rgt_mdh
);
END IF;
END IF;
IF DELETING
THEN
IF :NEW.hmb_div = 'B2'
THEN
UPDATE hddht_mtr_blk_trg
SET hmb_del_dtm = SYSDATE
WHERE seq = :OLD.seq;
IF SQL%NOTFOUND
THEN
INSERT INTO hddht_mtr_blk_trg
(seq, hmb_div, hmb_dat,
hmb_date, hmb_rgt_mdh, hmb_del_dtm
)
VALUES (:OLD.seq, :OLD.hmb_div, :OLD.hmb_dat,
:OLD.hmb_date, :OLD.hmb_rgt_mdh, SYSDATE
);
END IF;
END IF;
END IF;
END;
BEFORE INSERT OR DELETE
ON hddht_mtr_blk
FOR EACH ROW
BEGIN
IF INSERTING
THEN
IF :NEW.hmb_div = 'B2'
THEN
INSERT INTO hddht_mtr_blk_trg
(seq, hmb_div, hmb_dat, hmb_date,
hmb_rgt_mdh
)
VALUES (:NEW.seq, :NEW.hmb_div, :NEW.hmb_dat, :NEW.hmb_date,
:NEW.hmb_rgt_mdh
);
END IF;
END IF;
IF DELETING
THEN
IF :NEW.hmb_div = 'B2'
THEN
UPDATE hddht_mtr_blk_trg
SET hmb_del_dtm = SYSDATE
WHERE seq = :OLD.seq;
IF SQL%NOTFOUND
THEN
INSERT INTO hddht_mtr_blk_trg
(seq, hmb_div, hmb_dat,
hmb_date, hmb_rgt_mdh, hmb_del_dtm
)
VALUES (:OLD.seq, :OLD.hmb_div, :OLD.hmb_dat,
:OLD.hmb_date, :OLD.hmb_rgt_mdh, SYSDATE
);
END IF;
END IF;
END IF;
END;
SYSDBA and SYSOPER Privileges in Oracle
제목: SYSDBA and SYSOPER Privileges in Oracle
문서 ID: 50507.1 유형: REFERENCE
마지막 갱신 날짜: 02-MAR-2009 상태: PUBLISHED
Checked for relevance on 02-March-2009
0) Introduction
~~~~~~~~~~~~~~~
This article describes the different ways you can connect to Oracle as an administrative
user. It describes the options available to connect as SYSDBA and SYSOPER.
A checklist to troubleshoot SYSDBA/SYSOPER connections is documented separately :
Note 69642.1 - UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Oracle 8.1 was the last release to support the 'CONNECT INTERNAL' syntax :
therefore you must use SYSDBA or SYSOPER privileges in current releases.
1) Administrative Users
~~~~~~~~~~~~~~~~~~~~~~~
There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
(In version 11g this has been augmented by the SYSASM privilege, this basically
works in the same manner technically but will not be addressed here
see Note 429098.1 "11g ASM New Feature" for more information)
SYSDBA and SYSOPER are special privileges as they allow access to a database instance
even when it is not running and so control of these privileges is totally outside of
the database itself.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.
2) Password or Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~
Unless a connection to the instance is considered 'secure' then you MUST use a
password to connect with SYSDBA or SYSOPER privilege.
When the passwordfile is initially created with the uility orapwd it holds the password for
user SYS, other users can be added to the password file with the 'GRANT SYSDBA to &USER;' command.
Such a user can then connect to the instance for administrative purposes using
the syntax:
CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER
This is described in more detail in section (5) below.
Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the connection to the instance is local or 'secure' then it is possible to
use the operating system to determine if a user is allowed SYSDBA or SYSOPER
access. In this case no password is required.
The syntax to connect using operating system authentication is:
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Oracle determines if you can connect thus:
On Unix/Linux:
On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.
These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.
When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if
your Unix logon is a member of the 'OSOPER' group and if so allows you
to connect.
Similarly to connect as SYSDBA your Unix logon should be a member of
the Unix 'OSDBA' group.
The OSDBA groups is the same group as has been historically used to
allow CONNECT INTERNAL.
On MS Windows NT/2000/2003/XP:
On MS Windows the OSOPER and OSDBA groups are hard coded groups thus:
Group Name Oracle uses this as...
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
ORA_OPER OSOPER group for all instances
ORA_DBA OSDBA group for all instances
or
ORA_sid_OPER OSOPER group for a specific Oracle SID
ORA_sid_DBA OSDBA group for a specific Oracle SID
When you issue a 'CONNECT / AS SYSDBA' , Oracle checks if your MS Windows logon is a
member of the 'ORA_sid_DBA' or 'ORA_DBA' group.
3) OSDBA & OSOPER Groups on Unix/Linux
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default
to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the
same for all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.
If you wish to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer.
Eg: For an OSDBA group of 'mygroup'
If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):
Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"
If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.
Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"
IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"
To effect any changes to the groups and to be sure you are using the groups
defined in this file relink the Oracle executable.
Be sure to shutdown all databases before relinking:
Eg:
mv config.o config.o.orig
make -f ins_rdbms.mk ioracle
(Note config.o will be re-created by make because of dependencies automatically)
For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:
- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being
used)
- It CANNOT be the group called 'daemon'
Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)
Disabling Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Given the above information about the technical implementation details of OS authenication it is
possible to disable OS authentication by putting non-existant OS group names in the config.c
(or config.s) file, then (re)move the config.o and relink oracle, however this is not supported
for the following reasons:
- Many tools like RMAN rely on the OS authentication to work, in any documentation and references
this behaviour is expected to work.
- If you disable OS authentication like this the administrative connections AS SYSDBA/SYSOPER can only
make use of the passwordfile, if there's something wrong with it no one can login, if you consider
in a broader sense that availability is also part of security then this means it negatively impacts
the security of your system.
- Moreover it only provides a false sense of security since a DBA with access to the oracle software
owner can rebuild the password file or relink oracle to restore it.
Important notes about 'CONNECT / AS SYSDBA'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:
- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.
It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege. Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a password.
There are two ways to tackle this problem:
a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ sqlplus /nolog
SQL> connect / as sysdba
This can also be used in shellscripts thus:
:
newgrp dbagroup <# Commands requiring connect internal privilege
# Eg: dbstart
!
OR
c) For systems where 'newgrp' is not available or does not work from scripts you
can use 'su' instead.
Eg:
:
su - oracle <# Commands requiring administrative connect privilege
!
Note: The user you 'su' to should be able to 'connect / as sysdba' without a
password, for example by having their primary group as the OSDBA group.
Some Oracle releases have problems with identifying the OSDBA group when it is
not the users primary group.
If you encounter problems with connecting and the OSDBA group is set correctly
try making the users primary group the OSDBA group, or use 'newgrp' as in (b)
above.
4) OSDBA & OSOPER Groups on MS Windows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups on NT are simply groups with the name "ORA_DBA",
"ORA_OPER", "ORA_sid_DBA" or "ORA_sid_OPER", where 'sid' is the instance name.
Eg: To make a user an administrative user simply:
a) Ensure there is a line in the SQLNET.ORA file which reads:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
b) Create a LOCAL user
c) Create a local NT group ORA_DBA or ORA_sid_DBA where 'sid' is in upper case
d) Add the user to the ORA_DBA or ORA_sid_DBA group
e) That user should now be able to "connect / as sysdba"
If these requirements are not met, you get an ORA-01031 error.
Domain prefixed usernames
~~~~~~~~~~~~~~~~~~~~~~~~~
It is possible to set up usernames which include the domain as a prefix to the
username.
Eg: "OPS$\".
To do this you need to use the registry entry OSAUTH_PREFIX_DOMAIN and creating
users with USERNAMEs of the form "OPS$\".
This is described in detail in Note 60634.1
5) Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connections require the database to be configured to allow remote DBA
operations. The remote user will have to supply a password in order to connect
as either SYSDBA or SYSOPER. The only real exception to this is on MS Windows
where remote connections may be secure.
Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
'CONNECT username/password AS SYSDBA'
To allow remote administrative connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
5.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The SYSDBA/SYSOPER password protection is controlled by an Oracle 'Password'
file. The basic concept is that a special file is created to hold the 'SYSDBA' and
'SYSOPER' passwords. Users with SYSDBA or SYSOPER privilege granted in the
password file can be seen in the view V$PWFILE_USERS.
To create a password file log in as the Oracle software owner and issue the
command:
orapwd file= password= entries=
using the required password.
On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw$ORACLE_SID
On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA
Except in a Database Vault installation, the location on Windows 32-bit is
%ORACLE_HOME%\dbs\orapw%ORACLE_SID%, see Note 429818.1
The file name is important and should be specified as above.
You should create this file when the database is shut down.
To change a password you can use the syntax: ALTER USER &DBAUSER identified by &newpassword,
the changes will be synchronized in the passwordfile, in case this does not work you can recreate
the passwordfile as follows:
- Check v$pwfile_users and note the SYSDBA and SYSOPER privileges being granted.
- Shut down the database.
- Rename the password file.
- Issue a new ORAPWD command with a new password to set the SYS password
- Grant SYSDBA and/or SYSOPER to the other users from the first step.
5.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote administrative connections set the init.ora parameters thus:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
EXCLUSIVE forces the password file to be tied exclusively to a single instance.
To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE
Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and
should not be used, it is for 'normal' users that use OS authentication and therefore
it is not relevant to this discussion.
Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
from remote machines.
In particular the following are NOT used:
SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
irrelevant
6) Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~
Common Errors
~~~~~~~~~~~~~
ORA-01031: insufficient privileges
Connect Internal has been issued with no password.
For local connections the user is NOT in the DBA group as compiled
into the 'oracle' executable.
For remote connections you must always supply a password.
This error can also occur after a successful connect internal/password if there
REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.
ORA-01017: invalid username/password; logon denied
This is a fairly general error that indicates one of the following:
- REMOTE_LOGIN_PASSWORDFILE is set to NONE
- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started
Deleting/Changing the Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you delete the Oracle password file while the instance is running you will
NOT be able to connect AS SYSDBA from remote machines, even if you re-create the
file.
You must:
- Shutdown the instance (using a local connection)
- Create the new password file
- You can now connect remotely and restart the instance
문서 ID: 50507.1 유형: REFERENCE
마지막 갱신 날짜: 02-MAR-2009 상태: PUBLISHED
Checked for relevance on 02-March-2009
0) Introduction
~~~~~~~~~~~~~~~
This article describes the different ways you can connect to Oracle as an administrative
user. It describes the options available to connect as SYSDBA and SYSOPER.
A checklist to troubleshoot SYSDBA/SYSOPER connections is documented separately :
Note 69642.1 - UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Oracle 8.1 was the last release to support the 'CONNECT INTERNAL' syntax :
therefore you must use SYSDBA or SYSOPER privileges in current releases.
1) Administrative Users
~~~~~~~~~~~~~~~~~~~~~~~
There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
(In version 11g this has been augmented by the SYSASM privilege, this basically
works in the same manner technically but will not be addressed here
see Note 429098.1 "11g ASM New Feature" for more information)
SYSDBA and SYSOPER are special privileges as they allow access to a database instance
even when it is not running and so control of these privileges is totally outside of
the database itself.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.
2) Password or Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~
Unless a connection to the instance is considered 'secure' then you MUST use a
password to connect with SYSDBA or SYSOPER privilege.
When the passwordfile is initially created with the uility orapwd it holds the password for
user SYS, other users can be added to the password file with the 'GRANT SYSDBA to &USER;' command.
Such a user can then connect to the instance for administrative purposes using
the syntax:
CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER
This is described in more detail in section (5) below.
Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the connection to the instance is local or 'secure' then it is possible to
use the operating system to determine if a user is allowed SYSDBA or SYSOPER
access. In this case no password is required.
The syntax to connect using operating system authentication is:
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Oracle determines if you can connect thus:
On Unix/Linux:
On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.
These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.
When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if
your Unix logon is a member of the 'OSOPER' group and if so allows you
to connect.
Similarly to connect as SYSDBA your Unix logon should be a member of
the Unix 'OSDBA' group.
The OSDBA groups is the same group as has been historically used to
allow CONNECT INTERNAL.
On MS Windows NT/2000/2003/XP:
On MS Windows the OSOPER and OSDBA groups are hard coded groups thus:
Group Name Oracle uses this as...
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
ORA_OPER OSOPER group for all instances
ORA_DBA OSDBA group for all instances
or
ORA_sid_OPER OSOPER group for a specific Oracle SID
ORA_sid_DBA OSDBA group for a specific Oracle SID
When you issue a 'CONNECT / AS SYSDBA' , Oracle checks if your MS Windows logon is a
member of the 'ORA_sid_DBA' or 'ORA_DBA' group.
3) OSDBA & OSOPER Groups on Unix/Linux
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default
to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the
same for all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.
If you wish to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer.
Eg: For an OSDBA group of 'mygroup'
If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):
Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"
If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.
Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"
IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"
To effect any changes to the groups and to be sure you are using the groups
defined in this file relink the Oracle executable.
Be sure to shutdown all databases before relinking:
Eg:
mv config.o config.o.orig
make -f ins_rdbms.mk ioracle
(Note config.o will be re-created by make because of dependencies automatically)
For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:
- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being
used)
- It CANNOT be the group called 'daemon'
Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)
Disabling Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Given the above information about the technical implementation details of OS authenication it is
possible to disable OS authentication by putting non-existant OS group names in the config.c
(or config.s) file, then (re)move the config.o and relink oracle, however this is not supported
for the following reasons:
- Many tools like RMAN rely on the OS authentication to work, in any documentation and references
this behaviour is expected to work.
- If you disable OS authentication like this the administrative connections AS SYSDBA/SYSOPER can only
make use of the passwordfile, if there's something wrong with it no one can login, if you consider
in a broader sense that availability is also part of security then this means it negatively impacts
the security of your system.
- Moreover it only provides a false sense of security since a DBA with access to the oracle software
owner can rebuild the password file or relink oracle to restore it.
Important notes about 'CONNECT / AS SYSDBA'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:
- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.
It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege. Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a password.
There are two ways to tackle this problem:
a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ sqlplus /nolog
SQL> connect / as sysdba
This can also be used in shellscripts thus:
:
newgrp dbagroup <# Commands requiring connect internal privilege
# Eg: dbstart
!
OR
c) For systems where 'newgrp' is not available or does not work from scripts you
can use 'su' instead.
Eg:
:
su - oracle <# Commands requiring administrative connect privilege
!
Note: The user you 'su' to should be able to 'connect / as sysdba' without a
password, for example by having their primary group as the OSDBA group.
Some Oracle releases have problems with identifying the OSDBA group when it is
not the users primary group.
If you encounter problems with connecting and the OSDBA group is set correctly
try making the users primary group the OSDBA group, or use 'newgrp' as in (b)
above.
4) OSDBA & OSOPER Groups on MS Windows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups on NT are simply groups with the name "ORA_DBA",
"ORA_OPER", "ORA_sid_DBA" or "ORA_sid_OPER", where 'sid' is the instance name.
Eg: To make a user an administrative user simply:
a) Ensure there is a line in the SQLNET.ORA file which reads:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
b) Create a LOCAL user
c) Create a local NT group ORA_DBA or ORA_sid_DBA where 'sid' is in upper case
d) Add the user to the ORA_DBA or ORA_sid_DBA group
e) That user should now be able to "connect / as sysdba"
If these requirements are not met, you get an ORA-01031 error.
Domain prefixed usernames
~~~~~~~~~~~~~~~~~~~~~~~~~
It is possible to set up usernames which include the domain as a prefix to the
username.
Eg: "OPS$
To do this you need to use the registry entry OSAUTH_PREFIX_DOMAIN and creating
users with USERNAMEs of the form "OPS$
This is described in detail in Note 60634.1
5) Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connections require the database to be configured to allow remote DBA
operations. The remote user will have to supply a password in order to connect
as either SYSDBA or SYSOPER. The only real exception to this is on MS Windows
where remote connections may be secure.
Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
'CONNECT username/password AS SYSDBA'
To allow remote administrative connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
5.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The SYSDBA/SYSOPER password protection is controlled by an Oracle 'Password'
file. The basic concept is that a special file is created to hold the 'SYSDBA' and
'SYSOPER' passwords. Users with SYSDBA or SYSOPER privilege granted in the
password file can be seen in the view V$PWFILE_USERS.
To create a password file log in as the Oracle software owner and issue the
command:
orapwd file=
using the required password.
On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw$ORACLE_SID
On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA
Except in a Database Vault installation, the location on Windows 32-bit is
%ORACLE_HOME%\dbs\orapw%ORACLE_SID%, see Note 429818.1
The file name is important and should be specified as above.
You should create this file when the database is shut down.
To change a password you can use the syntax: ALTER USER &DBAUSER identified by &newpassword,
the changes will be synchronized in the passwordfile, in case this does not work you can recreate
the passwordfile as follows:
- Check v$pwfile_users and note the SYSDBA and SYSOPER privileges being granted.
- Shut down the database.
- Rename the password file.
- Issue a new ORAPWD command with a new password to set the SYS password
- Grant SYSDBA and/or SYSOPER to the other users from the first step.
5.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote administrative connections set the init.ora parameters thus:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
EXCLUSIVE forces the password file to be tied exclusively to a single instance.
To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE
Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and
should not be used, it is for 'normal' users that use OS authentication and therefore
it is not relevant to this discussion.
Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
from remote machines.
In particular the following are NOT used:
SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
irrelevant
6) Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~
Common Errors
~~~~~~~~~~~~~
ORA-01031: insufficient privileges
Connect Internal has been issued with no password.
For local connections the user is NOT in the DBA group as compiled
into the 'oracle' executable.
For remote connections you must always supply a password.
This error can also occur after a successful connect internal/password if there
REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.
ORA-01017: invalid username/password; logon denied
This is a fairly general error that indicates one of the following:
- REMOTE_LOGIN_PASSWORDFILE is set to NONE
- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started
Deleting/Changing the Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you delete the Oracle password file while the instance is running you will
NOT be able to connect AS SYSDBA from remote machines, even if you re-create the
file.
You must:
- Shutdown the instance (using a local connection)
- Create the new password file
- You can now connect remotely and restart the instance
Pro*C Fails To Precompile With The Error Code 139 When The Precompiler Version Is Lower Than The Oracle Client Version
제목: Pro*C Fails To Precompile With The Error Code 139 When The Precompiler Version Is Lower Than The Oracle Client Version
문서 ID: 432913.1 유형: PROBLEM
마지막 갱신 날짜: 25-SEP-2007 상태: PUBLISHED
In this Document
Symptoms
Cause
Solution
--------------------------------------------------------------------------------
Applies to:
Precompilers - Version: 9.2.0.1 to 9.2.0.7
This problem can occur on any platform.
Symptoms
Pro*C fails to precompile with the error 139
make -f demo_proc.mk build EXE=daemon OBJS=daemon.o PROCFLAGS="sqlcheck=semantics parse=full userid=storeq/storeq123"
/usr/ccs/bin/make -f /mnt/raid1/apps/oracle/product/9.2.0.1/precomp/demo/proc/demo_proc.mk PROCFLAGS="sqlcheck=semantics parse=full userid=storeq/storeq123" PCCSRC=daemon I_SYM=include= pc1
proc sqlcheck=semantics parse=full userid=storeq/storeq123 iname=daemon include=. include=/mnt/raid1/apps/oracle/product/9.2.0.1/precomp/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/rdbms/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/rdbms/demo include=/mnt/raid1/apps/oracle/product/9.2.0.1/plsql/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/network/public
Pro*C/C++: Release 9.2.0.1.0 - Production on Thu Apr 26 14:48:48 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from: /mnt/raid1/apps/oracle/product/9.2.0.1/precomp/admin/pcscfg.cfg
*** Error code 139
make: Fatal error: Command failed for target `pc1'
Current working directory /export/home/oracle/plsql/storeq
*** Error code 1
make: Fatal error: Command failed for target `daemon.o'
Cause
This problem is faced whenever the precompiler version is not upgraded to the latest patchset applied.
e.g.
The precompiler version is 9.2.0.1 and the oracle client version is 9.2.0.8 .
Solution
Upgrade the proc precompiler version to 9.2.0.8.0
-relinking the precompiler
cd $ORACLE_HOME/precomp/lib
make -f ins_precomp.mk proc
OR
-reapplying the 9.2.0.8 patchset after the Pro*C installation
문서 ID: 432913.1 유형: PROBLEM
마지막 갱신 날짜: 25-SEP-2007 상태: PUBLISHED
In this Document
Symptoms
Cause
Solution
--------------------------------------------------------------------------------
Applies to:
Precompilers - Version: 9.2.0.1 to 9.2.0.7
This problem can occur on any platform.
Symptoms
Pro*C fails to precompile with the error 139
make -f demo_proc.mk build EXE=daemon OBJS=daemon.o PROCFLAGS="sqlcheck=semantics parse=full userid=storeq/storeq123"
/usr/ccs/bin/make -f /mnt/raid1/apps/oracle/product/9.2.0.1/precomp/demo/proc/demo_proc.mk PROCFLAGS="sqlcheck=semantics parse=full userid=storeq/storeq123" PCCSRC=daemon I_SYM=include= pc1
proc sqlcheck=semantics parse=full userid=storeq/storeq123 iname=daemon include=. include=/mnt/raid1/apps/oracle/product/9.2.0.1/precomp/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/rdbms/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/rdbms/demo include=/mnt/raid1/apps/oracle/product/9.2.0.1/plsql/public include=/mnt/raid1/apps/oracle/product/9.2.0.1/network/public
Pro*C/C++: Release 9.2.0.1.0 - Production on Thu Apr 26 14:48:48 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
System default option values taken from: /mnt/raid1/apps/oracle/product/9.2.0.1/precomp/admin/pcscfg.cfg
*** Error code 139
make: Fatal error: Command failed for target `pc1'
Current working directory /export/home/oracle/plsql/storeq
*** Error code 1
make: Fatal error: Command failed for target `daemon.o'
Cause
This problem is faced whenever the precompiler version is not upgraded to the latest patchset applied.
e.g.
The precompiler version is 9.2.0.1 and the oracle client version is 9.2.0.8 .
Solution
Upgrade the proc precompiler version to 9.2.0.8.0
-relinking the precompiler
cd $ORACLE_HOME/precomp/lib
make -f ins_precomp.mk proc
OR
-reapplying the 9.2.0.8 patchset after the Pro*C installation
2009년 3월 11일 수요일
8.1.6 DB Upgrade Script
* Target hostname: hjtour
* Target DB Name: hjmall, hjmat, hjrtc
* Oracle Login User: oracle4
* Oracle Home
- Oracle 8.1.6 Home(Source): /oracle4/app/oracle/product/816
- Oracle 8.1.6 Home(Target): /oracle4/app/oracle/product/817
* 엔진 위치
- Oracle 8.1.6 엔진 백업: /backup/DBUpgrade/oracle816_bak
- Oracle 8.1.7 엔진 임시 위치: /backup/DBUpgrade/oracle8174_temp
* Manual Upgrade Scripts
/backup/DBUpgrade/scripts/HJMALL/1.sql ~ 45.sql
/backup/DBUpgrade/scripts/HJMAT/1.sql ~ 45.sql
/backup/DBUpgrade/scripts/HJRTC/1.sql ~ 45.sql
* 준비 사항
- begin backup & end backup 스크립트 작성
Begin Backup Script: /backup/DBUpgrade/scripts/begin_backup.sh
End Backup Script: /backup/DBUpgrade/scripts/end_backup.sh
- 원복 시나리오 준비
1) 백업되는 컨트롤 파일, 데이터 파일, 리두 로그 파일, 아카이브 로그 디렉토리 결정
2) 원복 용도의 init.ora 파일 미리 작성
3) 원복 시 수행할 db file rename 스크립트 준비
< 사전 작업 #1 – 기존 엔진 백업 및 8.1.7 엔진 구축 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) 서버 로그인 [ Login 21 svr oracle4 user ] (select any instance)
2 21
(telnet) 8.1.6 엔진 백업 cd /backup/DBUpgrade
mkdir oracle816_bak
cd oracle816_bak
cp –r /oracle4 ./
2 21
(telnet) 8.1.7 엔진용 임시 저장 디렉토리 생성. 해당 디렉토리로 이동 cd /backup
mkdir DBUpgrade
cd DBUpgrade
mkdir oracle8174_temp
cd oracle8174_temp
3 21
(ftp) oracle 8.1.7 엔진을 ftp를 통해 다운로드 ftp 172.16.201.61
[ id/pw: oracle/oracledba ]
bin
cd /ims/backup/UPGRADE
get oracle8174_aix.tar.Z
quit
4 21
(telnet) 엔진 압축 해제 zcat oracle8174_aix.tar.Z | tar -xvf -
5 21
(telnet) 8.1.7 홈의 내용을 oracle 파일 시스템에 복사 cd /backup/DBUpgrade/oracle8174_temp/oracle/app/oracle/product
cp –r 817 /oracle4/app/oracle/product
6 21
(telnet) 현 OS 유저 환경 설정 파일을 8.1.7 홈에 복사
(.profile, .alias) cp $ORACLE_HOME/.profile /oracle4/app/oracle/product/817
cp $ORACLE_HOME/.alias /oracle4/app/oracle/product/817
7 21
(telnet) 현 DB 관련 설정 파일들을 8.1.7 홈에 복사
(orapwd, init) cp $ORACLE_HOME/dbs/orapwd* /oracle4/app/oracle/product/817/dbs
cp $ORACLE_HOME/dbs/init*.ora /oracle4/app/oracle/product/817/dbs
8 21
(telnet) 현 DB 관련 네트웍 관련 파일을 8.1.7 홈에 복사
(listener, tnsnames) cd /oracle/app/oracle/product/817/network/admin
cp $ORACLE_HOME/network/admin/tnsnames.ora ./
cp $ORACLE_HOME/network/admin/listener.ora ./
< 사전 작업 #2 – 파라미터 파일 변경 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) [ Login 21 svr oracle4 user ] ( select HJMALL instance )
2 21
(telnet) 8.1.7 홈의 OS 설정 파일에 대해 수정(.profile, .alias) cd /oracle4/app/oracle/product/817
vi .profile (이전 8.1.6 Oracle Home을 가리키는 경로를 8.1.7 로 변경)
#export ORACLE_HOME=$ORACLE_BASE/product/816
export ORACLE_HOME=$ORACLE_BASE/product/817
vi .alias (8.1.6 Oracle Home을 가리키는 경로를 8.1.7 로 변경)
3 21
(telnet) 8.1.7 홈의 init.ora 에 대해 복제본 생성 cd dbs
cp initHJMALL.ora initHJMALL.ora_org
cp initHJMAT.ora initHJMAT.ora_org
cp initHJRTC.ora initHJRTC.ora_org
4 Migration을 위한 init.ora 수정. [ Edit init.ora file for migration ]
vi /oracle4/app/oracle/product/817/dbs/initHJMALL.ora
vi /oracle4/app/oracle/product/817/dbs/initHJMAT.ora
vi /oracle4/app/oracle/product/817/dbs/initHJRTC.ora
## Commented out Prameters
# job_queue_processes = 0
# aq_tm_processes = 0
## Added Paramters
_system_trig_enable = false
optimizer_mode = rule
5 listener.ora 의 ORACLE_HOME 항목 변경 vi /oracle4/app/oracle/product/817/network/admin/listener.ora
(ORACLE_HOME = /oracle4/app/oracle/product/816)
(ORACLE_HOME = /oracle4/app/oracle/product/817)
< 사전 작업 #3 –DB 파일 백업 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) 서버 로그인 [ Login 21 svr oracle4 user ] ( select HJMALL instance )
3 21
(telnet) 관련 DB(HJMALL) Hot 백업 모드로 전환 cd /backup/DBUpgrade/scripts
export ORACLE_SID=HJMALL
begin_backup.sh
4 21
(telnet) 데이터 파일 백업 cd /mall02
mkdir /mall02/HJMALL_BAK
cd HJMALL_BAK
cp –r /mall01/HJMALL/indx /mall02/HJMALL_BAK
cp –r /mall01/HJMALL/sysdata /mall02/HJMALL_BAK
cp –r /mall02/HJMALL/data /mall02/HJMALL_BAK
5 21
(dbms) 컨트롤 파일 백업 sqlplus '/ as sysdba'
alter database backup controlfile to '/backup/HJMALL_BAK/control01.ctl'
exit
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl
6 21
(telnet 관련 DB(HJMALL) Normal 모드로 전환 cd /backup/DBUpgrade/scripts
end_backup.sh
< 업그레이드 작업 – 8.1.6 8.1.7 Manual Upgrade : HJMALL >
순서 DB Ver 작업 스크립트
0 8.1.6 Manual 실행 업그레이드 스크립트 다운로드 cd /backup/DBUpgrade/scripts
mkdir HJMALL
cd HJMALL
ftp 172.16.201.61
[ id/pw: oracle/oracledba ]
cd /ims/backup/UPGRADE/
get upgrade_scripts.tar
quit
tar xvf upgrade_scripts.tar
1 8.1.6 Backup 수행
(DB 및 Engine 백업)
(1) Oracle 데이터 파일 백업
(2) Oracle 엔진 백업 (위 사전 작업으로 추가 작업 필요 없음)
2 8.1.6 필수 OS Patch 검사 -
3 8.1.6 필수 Kernel 파라미터 검사 -
4 8.1.6 ORACLE_HOME, SID 확인 04.sh
5 8.1.6 DBMS Version과 Option 검토 svrmgrl
05.sql
exit
6 8.1.6 svrmgrl 수행 svrmgrl
connect internal
7 8.1.6 DB Character Set 검토 07.sql
8 8.1.6 딕셔너리 손상 체크 08.sql
9 8.1.6 Valid 하지 않는 object 확인 09.sq
10 8.1.6 - -
11 8.1.6 Snapshot refresh 완료 확인 11.sql
(존재 시 작업이 완료 때 까지 기다리거나 중지 시켜야 함)
exit
12 8.1.6 리스너 중지 lsnrctl stop
13 8.1.6 복구 파일 존재 여부 확인 svrmgrl
connect internal
13.sql
(복구 해야 하는 파일이 없어야 함. 존재한다면 복구 해야 함)
14 8.1.6 백업 모드가 아닌 파일 확인 14.sql
(백업 모드인 파일이 없어야 함. 백업 모드를 종료 해야 함)
15 8.1.6 미해결 분산 트랜잭션 확인 15.sql
(존재할 경우 해소 해야 함)
16 8.1.6 ( 해당 사항 없음 ) -
17 8.1.6 모든 Batch와 Cron 비활성화 -
18 8.1.6 SYSTEM 롤백 세그먼트 준비 18.sql
19 8.1.6 SYSTEM 테이블 스페이스 공간확인 19.sql
(50 MB 이상 필요)
20
21 8.1.6 sys, system 유저의 기본 TBS 확인 20.sql
( 모두 SYSTEM 이어야 함. 그렇지 않으면 21.sql 수행 )
22 8.1.6 AUD$ 테이블 기본 TBS 확인 22.sql
(SYSTEM 이어야 함)
23 8.1.6 v$controlfile 의 내용 기록 23.sql
24 8.1.6 v$pwfile_users 의 내용 기록 24.sql
25-1 8.1.6 DB Shutdown shutdown immediate
exit
25-2 8.1.6 archive log, redo log 파일을 백업 위치에 복사
26 init.ora 수정 (위 사전 작업으로 추가 작업 필요 없음)
27 Archive 파일 시스템 확인 df –k /arch
28 NLS_LANG 확인 echo $NLS_LANG
29 listener.ora, tnsnames.ora 복사 (위 사전 작업으로 추가 작업 필요 없음)
30 (해당 사항 없음) -
31 (해당 사항 없음) -
32 (해당 사항 없음) -
33 8.1.7 새로운 DB 홈의 .profile 수행 /oracle/app/oracle/product/817/. .profile
34 8.1.7 환경 변수 확인 34.sh 수행
35 8.1.7 업그레이드 스크립트 수행 svrmgrl
connect internal
startup restrict
35.sql
36 8.1.7 DB Shutdown SHUTDOWN IMMEIDATE
37 8.1.7 (replication 이용하지 않음) -
38 8.1.7 (replication 이용하지 않음)
39 8.1.7 utlrp 수행으로 패키지 컴파일 cd /backup/DBUpgrade/scripts/HJMALL
svrmgrl
connect internal
startup restrict
39.sql
exit
40 8.1.7 원래의 init.ora로 복원 cd $ORACLE_HOME/dbs
cp initHJMALL.ora_org initHJMALL.ora
41 8.1.7 데이터베이스 재 시작 svrmgrl
Connect internal
Shutdown
Startup
exit
42 8.1.7 listener.ora에 ORACLE_HOME 변경 (위 사전작업으로 추가 작업 필요 없음)
43 8.1.7 리스너 시작 lsnrctl start
44 8.1.7 Cron 과 Batch 잡 수행 -
* Target DB Name: hjmall, hjmat, hjrtc
* Oracle Login User: oracle4
* Oracle Home
- Oracle 8.1.6 Home(Source): /oracle4/app/oracle/product/816
- Oracle 8.1.6 Home(Target): /oracle4/app/oracle/product/817
* 엔진 위치
- Oracle 8.1.6 엔진 백업: /backup/DBUpgrade/oracle816_bak
- Oracle 8.1.7 엔진 임시 위치: /backup/DBUpgrade/oracle8174_temp
* Manual Upgrade Scripts
/backup/DBUpgrade/scripts/HJMALL/1.sql ~ 45.sql
/backup/DBUpgrade/scripts/HJMAT/1.sql ~ 45.sql
/backup/DBUpgrade/scripts/HJRTC/1.sql ~ 45.sql
* 준비 사항
- begin backup & end backup 스크립트 작성
Begin Backup Script: /backup/DBUpgrade/scripts/begin_backup.sh
End Backup Script: /backup/DBUpgrade/scripts/end_backup.sh
- 원복 시나리오 준비
1) 백업되는 컨트롤 파일, 데이터 파일, 리두 로그 파일, 아카이브 로그 디렉토리 결정
2) 원복 용도의 init.ora 파일 미리 작성
3) 원복 시 수행할 db file rename 스크립트 준비
< 사전 작업 #1 – 기존 엔진 백업 및 8.1.7 엔진 구축 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) 서버 로그인 [ Login 21 svr oracle4 user ] (select any instance)
2 21
(telnet) 8.1.6 엔진 백업 cd /backup/DBUpgrade
mkdir oracle816_bak
cd oracle816_bak
cp –r /oracle4 ./
2 21
(telnet) 8.1.7 엔진용 임시 저장 디렉토리 생성. 해당 디렉토리로 이동 cd /backup
mkdir DBUpgrade
cd DBUpgrade
mkdir oracle8174_temp
cd oracle8174_temp
3 21
(ftp) oracle 8.1.7 엔진을 ftp를 통해 다운로드 ftp 172.16.201.61
[ id/pw: oracle/oracledba ]
bin
cd /ims/backup/UPGRADE
get oracle8174_aix.tar.Z
quit
4 21
(telnet) 엔진 압축 해제 zcat oracle8174_aix.tar.Z | tar -xvf -
5 21
(telnet) 8.1.7 홈의 내용을 oracle 파일 시스템에 복사 cd /backup/DBUpgrade/oracle8174_temp/oracle/app/oracle/product
cp –r 817 /oracle4/app/oracle/product
6 21
(telnet) 현 OS 유저 환경 설정 파일을 8.1.7 홈에 복사
(.profile, .alias) cp $ORACLE_HOME/.profile /oracle4/app/oracle/product/817
cp $ORACLE_HOME/.alias /oracle4/app/oracle/product/817
7 21
(telnet) 현 DB 관련 설정 파일들을 8.1.7 홈에 복사
(orapwd, init) cp $ORACLE_HOME/dbs/orapwd* /oracle4/app/oracle/product/817/dbs
cp $ORACLE_HOME/dbs/init*.ora /oracle4/app/oracle/product/817/dbs
8 21
(telnet) 현 DB 관련 네트웍 관련 파일을 8.1.7 홈에 복사
(listener, tnsnames) cd /oracle/app/oracle/product/817/network/admin
cp $ORACLE_HOME/network/admin/tnsnames.ora ./
cp $ORACLE_HOME/network/admin/listener.ora ./
< 사전 작업 #2 – 파라미터 파일 변경 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) [ Login 21 svr oracle4 user ] ( select HJMALL instance )
2 21
(telnet) 8.1.7 홈의 OS 설정 파일에 대해 수정(.profile, .alias) cd /oracle4/app/oracle/product/817
vi .profile (이전 8.1.6 Oracle Home을 가리키는 경로를 8.1.7 로 변경)
#export ORACLE_HOME=$ORACLE_BASE/product/816
export ORACLE_HOME=$ORACLE_BASE/product/817
vi .alias (8.1.6 Oracle Home을 가리키는 경로를 8.1.7 로 변경)
3 21
(telnet) 8.1.7 홈의 init.ora 에 대해 복제본 생성 cd dbs
cp initHJMALL.ora initHJMALL.ora_org
cp initHJMAT.ora initHJMAT.ora_org
cp initHJRTC.ora initHJRTC.ora_org
4 Migration을 위한 init.ora 수정. [ Edit init.ora file for migration ]
vi /oracle4/app/oracle/product/817/dbs/initHJMALL.ora
vi /oracle4/app/oracle/product/817/dbs/initHJMAT.ora
vi /oracle4/app/oracle/product/817/dbs/initHJRTC.ora
## Commented out Prameters
# job_queue_processes = 0
# aq_tm_processes = 0
## Added Paramters
_system_trig_enable = false
optimizer_mode = rule
5 listener.ora 의 ORACLE_HOME 항목 변경 vi /oracle4/app/oracle/product/817/network/admin/listener.ora
(ORACLE_HOME = /oracle4/app/oracle/product/816)
(ORACLE_HOME = /oracle4/app/oracle/product/817)
< 사전 작업 #3 –DB 파일 백업 작업 >
순서 대상 설명 작업 스크립트
1 21
(telnet) 서버 로그인 [ Login 21 svr oracle4 user ] ( select HJMALL instance )
3 21
(telnet) 관련 DB(HJMALL) Hot 백업 모드로 전환 cd /backup/DBUpgrade/scripts
export ORACLE_SID=HJMALL
begin_backup.sh
4 21
(telnet) 데이터 파일 백업 cd /mall02
mkdir /mall02/HJMALL_BAK
cd HJMALL_BAK
cp –r /mall01/HJMALL/indx /mall02/HJMALL_BAK
cp –r /mall01/HJMALL/sysdata /mall02/HJMALL_BAK
cp –r /mall02/HJMALL/data /mall02/HJMALL_BAK
5 21
(dbms) 컨트롤 파일 백업 sqlplus '/ as sysdba'
alter database backup controlfile to '/backup/HJMALL_BAK/control01.ctl'
exit
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl
6 21
(telnet 관련 DB(HJMALL) Normal 모드로 전환 cd /backup/DBUpgrade/scripts
end_backup.sh
< 업그레이드 작업 – 8.1.6 8.1.7 Manual Upgrade : HJMALL >
순서 DB Ver 작업 스크립트
0 8.1.6 Manual 실행 업그레이드 스크립트 다운로드 cd /backup/DBUpgrade/scripts
mkdir HJMALL
cd HJMALL
ftp 172.16.201.61
[ id/pw: oracle/oracledba ]
cd /ims/backup/UPGRADE/
get upgrade_scripts.tar
quit
tar xvf upgrade_scripts.tar
1 8.1.6 Backup 수행
(DB 및 Engine 백업)
(1) Oracle 데이터 파일 백업
(2) Oracle 엔진 백업 (위 사전 작업으로 추가 작업 필요 없음)
2 8.1.6 필수 OS Patch 검사 -
3 8.1.6 필수 Kernel 파라미터 검사 -
4 8.1.6 ORACLE_HOME, SID 확인 04.sh
5 8.1.6 DBMS Version과 Option 검토 svrmgrl
05.sql
exit
6 8.1.6 svrmgrl 수행 svrmgrl
connect internal
7 8.1.6 DB Character Set 검토 07.sql
8 8.1.6 딕셔너리 손상 체크 08.sql
9 8.1.6 Valid 하지 않는 object 확인 09.sq
10 8.1.6 - -
11 8.1.6 Snapshot refresh 완료 확인 11.sql
(존재 시 작업이 완료 때 까지 기다리거나 중지 시켜야 함)
exit
12 8.1.6 리스너 중지 lsnrctl stop
13 8.1.6 복구 파일 존재 여부 확인 svrmgrl
connect internal
13.sql
(복구 해야 하는 파일이 없어야 함. 존재한다면 복구 해야 함)
14 8.1.6 백업 모드가 아닌 파일 확인 14.sql
(백업 모드인 파일이 없어야 함. 백업 모드를 종료 해야 함)
15 8.1.6 미해결 분산 트랜잭션 확인 15.sql
(존재할 경우 해소 해야 함)
16 8.1.6 ( 해당 사항 없음 ) -
17 8.1.6 모든 Batch와 Cron 비활성화 -
18 8.1.6 SYSTEM 롤백 세그먼트 준비 18.sql
19 8.1.6 SYSTEM 테이블 스페이스 공간확인 19.sql
(50 MB 이상 필요)
20
21 8.1.6 sys, system 유저의 기본 TBS 확인 20.sql
( 모두 SYSTEM 이어야 함. 그렇지 않으면 21.sql 수행 )
22 8.1.6 AUD$ 테이블 기본 TBS 확인 22.sql
(SYSTEM 이어야 함)
23 8.1.6 v$controlfile 의 내용 기록 23.sql
24 8.1.6 v$pwfile_users 의 내용 기록 24.sql
25-1 8.1.6 DB Shutdown shutdown immediate
exit
25-2 8.1.6 archive log, redo log 파일을 백업 위치에 복사
26 init.ora 수정 (위 사전 작업으로 추가 작업 필요 없음)
27 Archive 파일 시스템 확인 df –k /arch
28 NLS_LANG 확인 echo $NLS_LANG
29 listener.ora, tnsnames.ora 복사 (위 사전 작업으로 추가 작업 필요 없음)
30 (해당 사항 없음) -
31 (해당 사항 없음) -
32 (해당 사항 없음) -
33 8.1.7 새로운 DB 홈의 .profile 수행 /oracle/app/oracle/product/817/. .profile
34 8.1.7 환경 변수 확인 34.sh 수행
35 8.1.7 업그레이드 스크립트 수행 svrmgrl
connect internal
startup restrict
35.sql
36 8.1.7 DB Shutdown SHUTDOWN IMMEIDATE
37 8.1.7 (replication 이용하지 않음) -
38 8.1.7 (replication 이용하지 않음)
39 8.1.7 utlrp 수행으로 패키지 컴파일 cd /backup/DBUpgrade/scripts/HJMALL
svrmgrl
connect internal
startup restrict
39.sql
exit
40 8.1.7 원래의 init.ora로 복원 cd $ORACLE_HOME/dbs
cp initHJMALL.ora_org initHJMALL.ora
41 8.1.7 데이터베이스 재 시작 svrmgrl
Connect internal
Shutdown
Startup
exit
42 8.1.7 listener.ora에 ORACLE_HOME 변경 (위 사전작업으로 추가 작업 필요 없음)
43 8.1.7 리스너 시작 lsnrctl start
44 8.1.7 Cron 과 Batch 잡 수행 -
Complete Upgrade Checklist for Manual Upgrades from 8.x to 8.x
제목: Complete Upgrade Checklist for Manual Upgrades from 8.x to 8.x
문서 ID: 133920.1 유형: BULLETIN
마지막 갱신 날짜: 03-APR-2008 상태: PUBLISHED
Checked for relevance on 02-OCT-2006
PURPOSE
-------
This document is created for use as a guideline and checklist when
manually upgrading oracle.
SCOPE & APPLICATION
-------------------
Database adminstrators
UPGRADE CHECKLIST
-----------------
1. ----------------------------------------------------------------------------
Perform a complete online backup!!!! (or a full cold backup if you prefer)
-- root account crontab info: /usr/tivoli/tsm/backup/onbackup.sh
-- =======================================================================
-- TOTAL STATUS: SUCCESS
-- START DATE & TIME: 2009-03-12 15:10
-- END DATE & TIME: 2009-03-13 14:09
-- TOTAL SIZE: 1045 GB
-- TSM SERVER: HJSMS_TSM
-- TSM CLIENT: HDDDB
-- =======================================================================
2. ----------------------------------------------------------------------------
Verify all necessary OS patches are installed.
Example for Solaris:
$ showrev -p
--Example for AIX:
--# instfix -iak
3. ----------------------------------------------------------------------------
Verify the kernel parameters according to the installation guide of the
new version
Example for Solaris:
$ cat /etc/system
4. ----------------------------------------------------------------------------
Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME
5. ----------------------------------------------------------------------------
What version is running? What option is installed?
Select * from v$version;
Select * from v$option;
6. ----------------------------------------------------------------------------
I the procedural option installed(pl/sql)?
Start svrmgrl
7. ----------------------------------------------------------------------------
Verify characterset of the database:
$ Sqlplus SYS/
Select name, substrb(value$,1,40) value from props$;
8. ----------------------------------------------------------------------------
Check for corruption in the dictionary, use:
Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
This script (analyze.sql) should not return any errors.
9. ----------------------------------------------------------------------------
List all objects that are not VALID. After migration all objects will be
invalid, this list returns a list of fatal objects.
Select substr(owner,1,12) owner, substr(object_name,1,30) object,
Substr(object_type,1,30) type,status from dba_objects where status <>'VALID';
To create a script to compile all invalid objects run the following, this
creates a script Obj.sql.
set verify off
set space 0
Set heading off
Set feedback off
Set pages 1000
spool obj.sql
select 'set termout on' from dual;
select 'set echo on' from dual;
select 'alter trigger '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='TRIGGER';
select 'alter package '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE';
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE BODY';
select 'alter procedure '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PROCEDURE';
select 'alter function '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='FUNCTION';
select 'alter view '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='VIEW';
/
spool off
Run the script and than rerun the query to get invalid objects.
spool invalid_pre.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off
This creates a file with a list of invalid objects.
10. ---------------------------------------------------------------------------
List the grants,
If the upgrade fails and at the dictionary was already rebuild, grants are lost.
If you want to go back it is advisable to have a list of grants. Use the
following script:
#!/bin/sh
#
# struct.sh
#--:
#--: generates DDL of database
#--:
ORAENV_ASK=NO; export ORAENV_ASK
ORACLE_SID=$3; export ORACLE_SID
. oraenv
ORAENV_ASK=YES; export ORAENV_ASK
Exp userid=$1/$2 file=/tmp/struct compress=no full=y rows=n
Imp userid=$1/$2 file=/tmp/struct full=y show=y 2> /tmp/contents.lst
Rm /tmp/struct.dmp
Awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"REVOKE / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' < /tmp/contents.lst > /tmp/struct1
rm /tmp/contents.lst
sed /^$/d < /tmp/struct1 > /tmp/struct2
rm /tmp/struct1
fold -s -w75 /tmp/struct2 > $3.sql
rm /tmp/struct2
The script takes 3 arguments username, password and SID. The script SID.sql
Is generated. If only grants are needed, change the line:
' fold -s -w75 /tmp/struct2 > $3.sql '
by
' grep ^GRANT /tmp/struct2 | fold -s -w75 > $3.sql '
11. ---------------------------------------------------------------------------
Ensure that all Snapshot refreshes are succesfully completed.
And replication is stopped.
$ Sqlplus SYS/
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
12. ---------------------------------------------------------------------------
Stop the listener for the database
$ lsnrctl
Lsnrctl> stop
13. ---------------------------------------------------------------------------
Ensure no files need media recovery:
$ sqlplus SYS/
Select * from v$recover_file;
This should return no rows
14. ---------------------------------------------------------------------------
Ensure no files are in backup mode:
Select * from v$backup where status!='NOT ACTIVE';
This should return no rows.
15. ---------------------------------------------------------------------------
Resolve any outstanding unresolved distributed transaction:
Select * from dba_2pc_pending;
If this returns rows you should do the following:
Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('');
Commit;
16. ---------------------------------------------------------------------------
If you are upgrading from an 8.0 release check no users or roles are called
either MIGRATE or OUTLN.
Select * from dba_users where username in ('MIGRATE','OUTLN');
Select * from dba_roles where role in ('MIGRATE','OUTLN');
If so these users/roles will need to be dropped prior to migration.
17. ---------------------------------------------------------------------------
Disable all batch and cron jobs.
18. ---------------------------------------------------------------------------
Prepare the system rollback segment:
Alter rollback segment system storage (maxextents 121 next 1M);
19. ---------------------------------------------------------------------------
Ensure plenty of free space in the SYSTEM tablespace. A minimum of 50 Mb
free space.
Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';
20. ---------------------------------------------------------------------------
Ensure the users sys and system have 'system' as their default tablespace.
Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');
21. ---------------------------------------------------------------------------
To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;
22. ---------------------------------------------------------------------------
Ensure aud$ table is in System tablespace when auditing is enabled.
Select tablespace_name from dba_tables where table_name='AUD$';
If the aud$ table is in a non-SYSTEM Tablespace then auditing needs
to be disabled before migration. This is because auditing will try to
acquire a non-system rollback segment. However these are all taken
offline during migration.
Auditing during migration can only be achieved with the aud$ in
the System tablespace, as use will be made of the System rollback
segment.
23. ---------------------------------------------------------------------------
Note down where all control files are located.
Select * from v$controlfile;
24. ---------------------------------------------------------------------------
Note down all sysdba users.
Select * from v$pwfile_users;
If a passwordfile is used copy it to the new location. On unix the default
is $ORACLE_HOME/dbs/orapw. On windows NT this is
%ORACLE_HOME%\database\orapw
25. ---------------------------------------------------------------------------
Shutdown the database
$ svrmgrl
SVRMGR> Shutdown immediate
26. ---------------------------------------------------------------------------
Change the init.ora file:
- Make a backup of the init.ora file.
- Ensure there is a value for DB_BLOCK_SIZE
- Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- Comment out the AQ_TM_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- If archiving is enabled set LOG_ARCHIVE_START=TRUE
- Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST
are set to an explicit directory
- Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade.
Note: Only applies for upgrades/migrations to 8i/9i. See Note 149948.1 for
further information.
- Set the parameter OPTIMIZER_MODE to RULE during the upgrade. This is a
workaround for Bug 1362374.
- Comment out obsoleted parameters(list in appendix A).
- Comment out SNAPSHOT_REFRESH_? parameters
- Ensure the COMPATIBLE parameter points to the current
Version. This to ensure a more easy downgrade when something goes wrong.
We can alter this to point to the new release when everything is tested.
27. ---------------------------------------------------------------------------
Check for adequate freespace on archive log destination file systems.
28. ---------------------------------------------------------------------------
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG
29. ---------------------------------------------------------------------------
If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)
cp $ORACLE_HOME/network/admin/network/admin
30. ---------------------------------------------------------------------------
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.
C:\ORADIM80 ?DELETE ?SID ORCL
And create the ORACLE 8I service:
C:\ORADIM ?NEW ?SID ORCL ?INTPWD -MAXUSERS n
-STARTMODE AUTO ?PFILE ORACLE_HOME\DATABASE\init.ora
31. ---------------------------------------------------------------------------
If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora
OR
Ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora
Also check 'ifile' parameters in the init.ora, to be set to the correct file.
if an IFILE is used, verify the above mentioned parameter for the init.ora
and copy this to the correct location. Change the IFILE entry in the init.ora
file when this file changes from location.
32. ---------------------------------------------------------------------------
Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:
::N
33. ---------------------------------------------------------------------------
Update the enviroment variables like ORACLE_HOME and PATH
$ . oraenv
34. ---------------------------------------------------------------------------
Make sure the following enviroment variables point to the new
Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS33
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH
For HP-UX systems verify the SHLIB_PATH parameter points to the new release
directories.
$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS33
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH
HP-UX:
$ env | grep SHLIB_PATH
35. ---------------------------------------------------------------------------
Run the upgrade script:
$ cd /$ORACLE_HOME/rdbms/admin
Svrmgrl
SVRMGR> Connect internal
SVRMGR> Startup restrict
SVRMGR> Spool catoutu.log
Run the appropriate script for your version.
From To Only Script to Run
==== === ==================
8.0.3 8.0.4 or higher @u0800030.sql
8.0.4 8.0.5 or higher @u0800040.sql
8.0.5 8.0.6 or higher @u0800050.sql
8.0.6 8.1.5 or higher @u0800060.sql
8.1.3 8.1.5 or higher @u0801030.sql
8.1.4 8.1.5 or higher @u0801040.sql
8.1.5 8.1.6 or higher @u0801050.sql
8.1.6 8.1.7 @u0801060.sql
SVRMGR> spool off
Each of these scripts are a direct upgrade path from the version you are
on to 8.1.x. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.
Possible problem:
You have just installed the binaries for Oracle 8.1.6. You alreadyhave
Oracle 8.1.5 installed and a database created with 8.1.5. You want to
manually upgrade the 8.1.5 database to 8.1.6. You did not install the
Migration Utility, because you are going to upgrade manually. When you
actually run the the following script:
u0801050.sql
You receive an error 'cannot find i0801050.sql'.
Solution 1:
The i0801050.sql is not installed unless you install the Migration
Utility, eventhough it is required when upgrading the database
Manually. So, you must go back and install the Migration Utility.
Solution 2:
Using your favorite text editor, create the i0801050.sql script in
$ORACLE_HOME/rdbms/admin and add the following instruction:
alter table argument$ add pls_type varchar2(30);
-- 30 = M_IDEN
Now rerun your upgrade procedure and it should complete without errors.
The file i0801050.sql is called by the script u0801050.sql. This file is
only installed with the Migration Utility.
36. ---------------------------------------------------------------------------
Shutdown the database and startup in restricted mode:
SVRMGR> Shutdown (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SVRMGR> Startup restrict
37. ---------------------------------------------------------------------------
Run catrep script if replication is used:
$ cd $ORACLE_HOME/rdbms/admin
$ svrmgrl
SVRMGR> connect internal
SVRMGR> Startup restrict
SVRMGR> spool catoutrp.log
SVRMGR> @catrep
38. ---------------------------------------------------------------------------
Run post-catrep advanced replication upgrade script, if needed:
Run the appropriate script for your version.
From Only Script to Run
==== ==================
8.0.3 @r0800030.sql
8.0.4 @r0800040.sql
8.0.5 @r0800050.sql
8.0.6 @r0800050.sql (Same as 8.0.5)
This script do not exist for upgrades from an earlier 8.1.x version.
As it is not necessary to run this script when upgrading from an
earlier 8.1.x version.
39. ---------------------------------------------------------------------------
Run script to recompile invalid pl/sql modules:
SVRMGR> @utlrp
40. ---------------------------------------------------------------------------
Edit init.ora file:
- put back the old value for the job_queue_processes parameter
- put back the old value for the aq_tm_processes parameter
- remove the parameter _system_trig_enabled from the init.ora file. This
parameter was explicitly set to false during the upgrade.
- modify the log_archive_dest parameter specify only the path, but make sure it
ends with a '/'. (remove the format)
e.g. log_archive_dest=/path/arch into log_archive_dest=/path/
- Modify the marameter log_archive_format and add the format previously
removed from the log_archive_dest.
E.g log_archive_format=arch%t_SID_%s.log
41. ---------------------------------------------------------------------------
Shutdown the database and startup the database normal.
$ svrmgrl
SVRMGR> Connect internal
SVRMGR> Shutdown
SVRMGR> Startup
42. ---------------------------------------------------------------------------
Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
--Before: (ORACLE_HOME = /oracle2/app/oracle/product/816)
--After: (ORACLE_HOME = /oracle2/app/oracle/product/817)
43. ---------------------------------------------------------------------------
Start the listener
$ lsnrctl
LSNRCTL> start
44. ---------------------------------------------------------------------------
Enable cron and batch jobs
45. ---------------------------------------------------------------------------
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y
--Before: HDDDB:/oracle2/app/oracle/product/816:N
--After: HDDDB:/oracle2/app/oracle/product/817:N
46. ---------------------------------------------------------------------------
When everything is well tested, update the compatible parameter in the
init.ora file and restart to the new release number.
Compatible=8.1.x where x is the release number
--/oracle2/app/oracle/admin/HDDDB/pfile/initHDDDB.ora
--Before: compatible = "8.1.0"
--After: compatible = "8.1.7"
---------------------------------------------------------------------------
Appendix A: Obsolete parameter
8.1.5 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.6 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit (not obsolete in 8.1.5)
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.7 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
문서 ID: 133920.1 유형: BULLETIN
마지막 갱신 날짜: 03-APR-2008 상태: PUBLISHED
Checked for relevance on 02-OCT-2006
PURPOSE
-------
This document is created for use as a guideline and checklist when
manually upgrading oracle.
SCOPE & APPLICATION
-------------------
Database adminstrators
UPGRADE CHECKLIST
-----------------
1. ----------------------------------------------------------------------------
Perform a complete online backup!!!! (or a full cold backup if you prefer)
-- root account crontab info: /usr/tivoli/tsm/backup/onbackup.sh
-- =======================================================================
-- TOTAL STATUS: SUCCESS
-- START DATE & TIME: 2009-03-12 15:10
-- END DATE & TIME: 2009-03-13 14:09
-- TOTAL SIZE: 1045 GB
-- TSM SERVER: HJSMS_TSM
-- TSM CLIENT: HDDDB
-- =======================================================================
2. ----------------------------------------------------------------------------
Verify all necessary OS patches are installed.
Example for Solaris:
$ showrev -p
--Example for AIX:
--# instfix -iak
3. ----------------------------------------------------------------------------
Verify the kernel parameters according to the installation guide of the
new version
Example for Solaris:
$ cat /etc/system
4. ----------------------------------------------------------------------------
Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME
5. ----------------------------------------------------------------------------
What version is running? What option is installed?
Select * from v$version;
Select * from v$option;
6. ----------------------------------------------------------------------------
I the procedural option installed(pl/sql)?
Start svrmgrl
7. ----------------------------------------------------------------------------
Verify characterset of the database:
$ Sqlplus SYS/
Select name, substrb(value$,1,40) value from props$;
8. ----------------------------------------------------------------------------
Check for corruption in the dictionary, use:
Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
This script (analyze.sql) should not return any errors.
9. ----------------------------------------------------------------------------
List all objects that are not VALID. After migration all objects will be
invalid, this list returns a list of fatal objects.
Select substr(owner,1,12) owner, substr(object_name,1,30) object,
Substr(object_type,1,30) type,status from dba_objects where status <>'VALID';
To create a script to compile all invalid objects run the following, this
creates a script Obj.sql.
set verify off
set space 0
Set heading off
Set feedback off
Set pages 1000
spool obj.sql
select 'set termout on' from dual;
select 'set echo on' from dual;
select 'alter trigger '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='TRIGGER';
select 'alter package '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE';
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status <> 'VALID'
and object_type='PACKAGE BODY';
select 'alter procedure '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='PROCEDURE';
select 'alter function '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='FUNCTION';
select 'alter view '||owner||'.'||object_name||' compile;'
from dba_objects
where status <> 'VALID'
and object_type='VIEW';
/
spool off
Run the script and than rerun the query to get invalid objects.
spool invalid_pre.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off
This creates a file with a list of invalid objects.
10. ---------------------------------------------------------------------------
List the grants,
If the upgrade fails and at the dictionary was already rebuild, grants are lost.
If you want to go back it is advisable to have a list of grants. Use the
following script:
#!/bin/sh
#
# struct.sh
#--:
#--: generates DDL of database
#--:
ORAENV_ASK=NO; export ORAENV_ASK
ORACLE_SID=$3; export ORACLE_SID
. oraenv
ORAENV_ASK=YES; export ORAENV_ASK
Exp userid=$1/$2 file=/tmp/struct compress=no full=y rows=n
Imp userid=$1/$2 file=/tmp/struct full=y show=y 2> /tmp/contents.lst
Rm /tmp/struct.dmp
Awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"REVOKE / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' < /tmp/contents.lst > /tmp/struct1
rm /tmp/contents.lst
sed /^$/d < /tmp/struct1 > /tmp/struct2
rm /tmp/struct1
fold -s -w75 /tmp/struct2 > $3.sql
rm /tmp/struct2
The script takes 3 arguments username, password and SID. The script SID.sql
Is generated. If only grants are needed, change the line:
' fold -s -w75 /tmp/struct2 > $3.sql '
by
' grep ^GRANT /tmp/struct2 | fold -s -w75 > $3.sql '
11. ---------------------------------------------------------------------------
Ensure that all Snapshot refreshes are succesfully completed.
And replication is stopped.
$ Sqlplus SYS/
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
12. ---------------------------------------------------------------------------
Stop the listener for the database
$ lsnrctl
Lsnrctl> stop
13. ---------------------------------------------------------------------------
Ensure no files need media recovery:
$ sqlplus SYS/
Select * from v$recover_file;
This should return no rows
14. ---------------------------------------------------------------------------
Ensure no files are in backup mode:
Select * from v$backup where status!='NOT ACTIVE';
This should return no rows.
15. ---------------------------------------------------------------------------
Resolve any outstanding unresolved distributed transaction:
Select * from dba_2pc_pending;
If this returns rows you should do the following:
Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('
Commit;
16. ---------------------------------------------------------------------------
If you are upgrading from an 8.0 release check no users or roles are called
either MIGRATE or OUTLN.
Select * from dba_users where username in ('MIGRATE','OUTLN');
Select * from dba_roles where role in ('MIGRATE','OUTLN');
If so these users/roles will need to be dropped prior to migration.
17. ---------------------------------------------------------------------------
Disable all batch and cron jobs.
18. ---------------------------------------------------------------------------
Prepare the system rollback segment:
Alter rollback segment system storage (maxextents 121 next 1M);
19. ---------------------------------------------------------------------------
Ensure plenty of free space in the SYSTEM tablespace. A minimum of 50 Mb
free space.
Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';
20. ---------------------------------------------------------------------------
Ensure the users sys and system have 'system' as their default tablespace.
Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');
21. ---------------------------------------------------------------------------
To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;
22. ---------------------------------------------------------------------------
Ensure aud$ table is in System tablespace when auditing is enabled.
Select tablespace_name from dba_tables where table_name='AUD$';
If the aud$ table is in a non-SYSTEM Tablespace then auditing needs
to be disabled before migration. This is because auditing will try to
acquire a non-system rollback segment. However these are all taken
offline during migration.
Auditing during migration can only be achieved with the aud$ in
the System tablespace, as use will be made of the System rollback
segment.
23. ---------------------------------------------------------------------------
Note down where all control files are located.
Select * from v$controlfile;
24. ---------------------------------------------------------------------------
Note down all sysdba users.
Select * from v$pwfile_users;
If a passwordfile is used copy it to the new location. On unix the default
is $ORACLE_HOME/dbs/orapw
%ORACLE_HOME%\database\orapw
25. ---------------------------------------------------------------------------
Shutdown the database
$ svrmgrl
SVRMGR> Shutdown immediate
26. ---------------------------------------------------------------------------
Change the init.ora file:
- Make a backup of the init.ora file.
- Ensure there is a value for DB_BLOCK_SIZE
- Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- Comment out the AQ_TM_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- If archiving is enabled set LOG_ARCHIVE_START=TRUE
- Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST
are set to an explicit directory
- Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade.
Note: Only applies for upgrades/migrations to 8i/9i. See Note 149948.1 for
further information.
- Set the parameter OPTIMIZER_MODE to RULE during the upgrade. This is a
workaround for Bug 1362374.
- Comment out obsoleted parameters(list in appendix A).
- Comment out SNAPSHOT_REFRESH_? parameters
- Ensure the COMPATIBLE parameter points to the current
Version. This to ensure a more easy downgrade when something goes wrong.
We can alter this to point to the new release when everything is tested.
27. ---------------------------------------------------------------------------
Check for adequate freespace on archive log destination file systems.
28. ---------------------------------------------------------------------------
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG
29. ---------------------------------------------------------------------------
If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)
cp $ORACLE_HOME/network/admin
30. ---------------------------------------------------------------------------
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.
C:\ORADIM80 ?DELETE ?SID ORCL
And create the ORACLE 8I service:
C:\ORADIM ?NEW ?SID ORCL ?INTPWD
-STARTMODE AUTO ?PFILE ORACLE_HOME\DATABASE\init.ora
31. ---------------------------------------------------------------------------
If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora
OR
Ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora
Also check 'ifile' parameters in the init.ora, to be set to the correct file.
if an IFILE is used, verify the above mentioned parameter for the init.ora
and copy this to the correct location. Change the IFILE entry in the init.ora
file when this file changes from location.
32. ---------------------------------------------------------------------------
Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:
33. ---------------------------------------------------------------------------
Update the enviroment variables like ORACLE_HOME and PATH
$ . oraenv
34. ---------------------------------------------------------------------------
Make sure the following enviroment variables point to the new
Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS33
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH
For HP-UX systems verify the SHLIB_PATH parameter points to the new release
directories.
$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS33
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH
HP-UX:
$ env | grep SHLIB_PATH
35. ---------------------------------------------------------------------------
Run the upgrade script:
$ cd /$ORACLE_HOME/rdbms/admin
Svrmgrl
SVRMGR> Connect internal
SVRMGR> Startup restrict
SVRMGR> Spool catoutu.log
Run the appropriate script for your version.
From To Only Script to Run
==== === ==================
8.0.3 8.0.4 or higher @u0800030.sql
8.0.4 8.0.5 or higher @u0800040.sql
8.0.5 8.0.6 or higher @u0800050.sql
8.0.6 8.1.5 or higher @u0800060.sql
8.1.3 8.1.5 or higher @u0801030.sql
8.1.4 8.1.5 or higher @u0801040.sql
8.1.5 8.1.6 or higher @u0801050.sql
8.1.6 8.1.7 @u0801060.sql
SVRMGR> spool off
Each of these scripts are a direct upgrade path from the version you are
on to 8.1.x. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.
Possible problem:
You have just installed the binaries for Oracle 8.1.6. You alreadyhave
Oracle 8.1.5 installed and a database created with 8.1.5. You want to
manually upgrade the 8.1.5 database to 8.1.6. You did not install the
Migration Utility, because you are going to upgrade manually. When you
actually run the the following script:
u0801050.sql
You receive an error 'cannot find i0801050.sql'.
Solution 1:
The i0801050.sql is not installed unless you install the Migration
Utility, eventhough it is required when upgrading the database
Manually. So, you must go back and install the Migration Utility.
Solution 2:
Using your favorite text editor, create the i0801050.sql script in
$ORACLE_HOME/rdbms/admin and add the following instruction:
alter table argument$ add pls_type varchar2(30);
-- 30 = M_IDEN
Now rerun your upgrade procedure and it should complete without errors.
The file i0801050.sql is called by the script u0801050.sql. This file is
only installed with the Migration Utility.
36. ---------------------------------------------------------------------------
Shutdown the database and startup in restricted mode:
SVRMGR> Shutdown (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SVRMGR> Startup restrict
37. ---------------------------------------------------------------------------
Run catrep script if replication is used:
$ cd $ORACLE_HOME/rdbms/admin
$ svrmgrl
SVRMGR> connect internal
SVRMGR> Startup restrict
SVRMGR> spool catoutrp.log
SVRMGR> @catrep
38. ---------------------------------------------------------------------------
Run post-catrep advanced replication upgrade script, if needed:
Run the appropriate script for your version.
From Only Script to Run
==== ==================
8.0.3 @r0800030.sql
8.0.4 @r0800040.sql
8.0.5 @r0800050.sql
8.0.6 @r0800050.sql (Same as 8.0.5)
This script do not exist for upgrades from an earlier 8.1.x version.
As it is not necessary to run this script when upgrading from an
earlier 8.1.x version.
39. ---------------------------------------------------------------------------
Run script to recompile invalid pl/sql modules:
SVRMGR> @utlrp
40. ---------------------------------------------------------------------------
Edit init.ora file:
- put back the old value for the job_queue_processes parameter
- put back the old value for the aq_tm_processes parameter
- remove the parameter _system_trig_enabled from the init.ora file. This
parameter was explicitly set to false during the upgrade.
- modify the log_archive_dest parameter specify only the path, but make sure it
ends with a '/'. (remove the format)
e.g. log_archive_dest=/path/arch into log_archive_dest=/path/
- Modify the marameter log_archive_format and add the format previously
removed from the log_archive_dest.
E.g log_archive_format=arch%t_SID_%s.log
41. ---------------------------------------------------------------------------
Shutdown the database and startup the database normal.
$ svrmgrl
SVRMGR> Connect internal
SVRMGR> Shutdown
SVRMGR> Startup
42. ---------------------------------------------------------------------------
Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
--Before: (ORACLE_HOME = /oracle2/app/oracle/product/816)
--After: (ORACLE_HOME = /oracle2/app/oracle/product/817)
43. ---------------------------------------------------------------------------
Start the listener
$ lsnrctl
LSNRCTL> start
44. ---------------------------------------------------------------------------
Enable cron and batch jobs
45. ---------------------------------------------------------------------------
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y
--Before: HDDDB:/oracle2/app/oracle/product/816:N
--After: HDDDB:/oracle2/app/oracle/product/817:N
46. ---------------------------------------------------------------------------
When everything is well tested, update the compatible parameter in the
init.ora file and restart to the new release number.
Compatible=8.1.x where x is the release number
--/oracle2/app/oracle/admin/HDDDB/pfile/initHDDDB.ora
--Before: compatible = "8.1.0"
--After: compatible = "8.1.7"
---------------------------------------------------------------------------
Appendix A: Obsolete parameter
8.1.5 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.6 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit (not obsolete in 8.1.5)
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
8.1.7 Obsolete parameters:
spin_count
shared_pool_reserved_min_alloc
large_pool_min_alloc
use_ism
lock_sga_areas
lgwr_io_slaves
arch_io_slaves
backup_disk_io_slaves
ogms_home
parallel_transaction_resource_timeout
db_block_checkpoint_batch
db_block_lru_statistics
db_block_lru_extended_statistics
compatible_no_recovery
log_archive_buffers
log_archive_buffer_size
log_block_checksum
log_small_entry_max_size
log_simultaneous_copies
db_file_simultaneous_writes
log_files
gc_lck_procs
gc_latches
freeze_DB_for_fast_instance_recovery
temporary_table_locks
delayed_logging_block_cleanouts
cleanup_rollback_entries
discrete_transactions_enabled
sequence_cache_entries
sequence_cache_hash_buckets
row_cache_cursors
distributed_lock_timeout
max_transaction_branches
distributed_recovery_connection_hold_time
close_cached_open_cursors
sort_direct_writes
sort_write_buffers
sort_write_buffer_size
sort_spacemap_size
sort_read_fac
b_tree_bitmap_plans
complex_view_merging
push_join_predicate
fast_full_scan_enabled
job_queue_keep_connections
snapshot_refresh_processes
snapshot_refresh_interval
snapshot_refresh_keep_connections
optimizer_search_limit
parallel_default_max_instances
cache_size_threshold
parallel_server_idle_time
allow_partial_sn_results
ops_admin_group
parallel_min_message_pool
2009년 3월 8일 일요일
TSM Backup Script
[HDDDB|AIX]:/oracle2/app/oracle/admin/HDDDB/work> cat /backup/HDDDB/mig/bin/2008_0715_backup_wbl_sms_v2.sh
#!/usr/bin/ksh
MIG_BIN=/backup/HDDDB/mig/bin
MIG_LOG=/backup/HDDDB/mig/log
MIG_DMP=/backup/HDDDB/mig/dmp
cd /oracle2/app/oracle/product/816
. ./.profile
################################################
cd $MIG_BIN
SMS_LOG1=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_start_v2."}'`
SMS_LOG2=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_end_v2."}'`
smssend2.sh 0112682488 0221667454 $SMS_LOG1
#sqlplus -s hddsm/hddsm11 <
#exit
#EOF
smssend2.sh 0112682488 0221667454 $SMS_LOG2
DEL_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_wbl_v2.log"}'`
cp delete_wbl_v2.log $DEL_LOG
sqlplus -s hddsm/hddsm11 <
alter procedure atest_lob_in_backup_wbl_v2 compile;
exec atest_lob_in_backup_wbl_v2;
@report_wbl_v2
@export_wbl_v2
exit
EOF
EXP_FILE=`cat export_file_v2.log|head -1`
EXP_FILE_LOG=`cat export_file_v2.log | head -1 | nawk -F. '{print $1 ".log"}'`
DAY_FILE="["`date +'%Y-%m%d'`"]_"$EXP_FILE
CREATE_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "create_wbl_v2.log"}'`
REPORT_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "report_wbl_v2.log"}'`
EXPORT_FILE_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_file_v2.log"}'`
EXPORT_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_wbl_v2.log"}'`
TIVOLI_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "tivoli_wbl_v2.log"}'`
BACKUP_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "backup_wbl_v2.log"}'`
SMS_LOG3=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "bak_tbl_created_v2."}'`
SMS_LOG4=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_end_v2."}'`
smssend2.sh 0112682488 0221667454 $SMS_LOG3
exp parfile=export_wbl_v2.par file=export_wbl_v2.dmp log=export_wbl_v2.log
cp export_wbl_v2.log $MIG_DMP/$EXP_FILE_LOG
cp export_wbl_v2.dmp $MIG_DMP/$EXP_FILE
dsmc a -archmc=HDDDB_OLD_MGMT $MIG_DMP/$EXP_FILE > tivoli_wbl_v2.log
smssend2.sh 0112682488 0221667454 $SMS_LOG4
################################################
BAK_LOG=`date +'%Y-%m%d'`"_WBL_V2.log"
echo " " > $BAK_LOG
echo "==== DELETE BACKUPED DATA V2 =====================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $SMS_LOG1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $DEL_LOG >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $SMS_LOG2 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
echo " " >> $BAK_LOG
cat report_wbl_v2.log >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== EXPORT BACKUP TABLE V2 ======================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat export_wbl_v2.log >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== FILE SYSTEM USAGE STATUS ====================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
df -k /arch | head -2 ; df -k /arch | tail -1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
df -k /backup | head -2 ; df -k /backup | tail -1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
ls -ltr *.dmp | head -2 ; ls -ltr *.dmp | tail -1 >> $BAK_LOG
echo " " >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== EXPORT FILE BACKUP V2 : TSM =================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
dsmc q arch $MIG_DMP/$EXP_FILE | tail -4 >> $BAK_LOG
echo " " >> $BAK_LOG
mailx -s "HDDDB : BACKUP REPORT <> : SVC" seongeun.yang@hist.co.kr < $BAK_LOG
mailx -s "HDDDB : BACKUP REPORT <> : SVC" bdkim@hist.co.kr < $BAK_LOG
cp $BAK_LOG $MIG_BIN/backup_wbl_v2.log
mv $BAK_LOG $MIG_LOG/$BAK_LOG
cp create_wbl_v2.log $CREATE_LOG
cp report_wbl_v2.log $REPORT_LOG
cp export_file_v2.log $EXPORT_FILE_LOG
cp export_wbl_v2.log $EXPORT_LOG
cp tivoli_wbl_v2.log $TIVOLI_LOG
cp backup_wbl_v2.log $BACKUP_LOG
################################################
EOF
#!/usr/bin/ksh
MIG_BIN=/backup/HDDDB/mig/bin
MIG_LOG=/backup/HDDDB/mig/log
MIG_DMP=/backup/HDDDB/mig/dmp
cd /oracle2/app/oracle/product/816
. ./.profile
################################################
cd $MIG_BIN
SMS_LOG1=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_start_v2."}'`
SMS_LOG2=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_end_v2."}'`
smssend2.sh 0112682488 0221667454 $SMS_LOG1
#sqlplus -s hddsm/hddsm11 <
#exit
#EOF
smssend2.sh 0112682488 0221667454 $SMS_LOG2
DEL_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "delete_wbl_v2.log"}'`
cp delete_wbl_v2.log $DEL_LOG
sqlplus -s hddsm/hddsm11 <
alter procedure atest_lob_in_backup_wbl_v2 compile;
exec atest_lob_in_backup_wbl_v2;
@report_wbl_v2
@export_wbl_v2
exit
EOF
EXP_FILE=`cat export_file_v2.log|head -1`
EXP_FILE_LOG=`cat export_file_v2.log | head -1 | nawk -F. '{print $1 ".log"}'`
DAY_FILE="["`date +'%Y-%m%d'`"]_"$EXP_FILE
CREATE_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "create_wbl_v2.log"}'`
REPORT_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "report_wbl_v2.log"}'`
EXPORT_FILE_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_file_v2.log"}'`
EXPORT_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_wbl_v2.log"}'`
TIVOLI_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "tivoli_wbl_v2.log"}'`
BACKUP_LOG=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "backup_wbl_v2.log"}'`
SMS_LOG3=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "bak_tbl_created_v2."}'`
SMS_LOG4=`cat export_file_v2.log | head -1 | nawk -FW '{print $1 "export_end_v2."}'`
smssend2.sh 0112682488 0221667454 $SMS_LOG3
exp parfile=export_wbl_v2.par file=export_wbl_v2.dmp log=export_wbl_v2.log
cp export_wbl_v2.log $MIG_DMP/$EXP_FILE_LOG
cp export_wbl_v2.dmp $MIG_DMP/$EXP_FILE
dsmc a -archmc=HDDDB_OLD_MGMT $MIG_DMP/$EXP_FILE > tivoli_wbl_v2.log
smssend2.sh 0112682488 0221667454 $SMS_LOG4
################################################
BAK_LOG=`date +'%Y-%m%d'`"_WBL_V2.log"
echo " " > $BAK_LOG
echo "==== DELETE BACKUPED DATA V2 =====================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $SMS_LOG1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $DEL_LOG >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat $SMS_LOG2 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
echo " " >> $BAK_LOG
cat report_wbl_v2.log >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== EXPORT BACKUP TABLE V2 ======================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
cat export_wbl_v2.log >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== FILE SYSTEM USAGE STATUS ====================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
df -k /arch | head -2 ; df -k /arch | tail -1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
df -k /backup | head -2 ; df -k /backup | tail -1 >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
ls -ltr *.dmp | head -2 ; ls -ltr *.dmp | tail -1 >> $BAK_LOG
echo " " >> $BAK_LOG
echo " " >> $BAK_LOG
echo "==== EXPORT FILE BACKUP V2 : TSM =================================================" >> $BAK_LOG
echo ".................................................................................." >> $BAK_LOG
dsmc q arch $MIG_DMP/$EXP_FILE | tail -4 >> $BAK_LOG
echo " " >> $BAK_LOG
mailx -s "HDDDB : BACKUP REPORT <
mailx -s "HDDDB : BACKUP REPORT <
cp $BAK_LOG $MIG_BIN/backup_wbl_v2.log
mv $BAK_LOG $MIG_LOG/$BAK_LOG
cp create_wbl_v2.log $CREATE_LOG
cp report_wbl_v2.log $REPORT_LOG
cp export_file_v2.log $EXPORT_FILE_LOG
cp export_wbl_v2.log $EXPORT_LOG
cp tivoli_wbl_v2.log $TIVOLI_LOG
cp backup_wbl_v2.log $BACKUP_LOG
################################################
EOF
피드 구독하기:
글 (Atom)