728x90
-- BLOB, CLOB 생성
create tablespace test datafile '/oracle/base/oradata/ORCL/test.dbf' size 100M autoextend on;
create user test identified by test default tablespace test temporary tablespace temp;
 
grant dba to test;
 
create table clob (
name varchar2(20) primary key not null,
age number(10),
character CLOB);
 
create table blob (
name varchar2(20) primary key not null,
age number(10),
character BLOB);
 
insert into clob values
('AAA', 26, 'ABCDEFGHIG1ABCDEFGHIG2ABCDEFGHIG3');
 
insert into clob values
('BBB', 27, EMPTY_CLOB());
 
insert into blob values
('AAA', 26, null);
 
-- table 구조 조회
 
desc <테이블명>
 
-- lob table 조회
select owner, table_name from dba_lobs;
 
-- LOB table count(*)
set line 300
col OWNER for a20
col OBJECT_TYPE for a5
select owner, object_type, count(*) from dba_objects
where object_type = 'LOB'
group by owner,object_type
order by owner,object_type ;
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORCLE] dbca script 생성 명령어 - silent  (0) 2022.05.11
[ORACLE] 공통계정 생성 시 c## 제거 설정  (0) 2022.05.11
[ORACLE] 특정 스키마의 PK, UK 없는 table 조회  (0) 2022.05.11
[ORACLE] PK, UK table 조회  (0) 2022.05.11
[ORACLE] SE to EE 버전업 테스트  (0) 2022.05.11
728x90
select TABLE_NAME from dba_tables
where OWNER = 'TEST' and TABLE_NAME
NOT IN (
SELECT A.TABLE_NAME
FROM USER_CONS_COLUMNS A
INNER JOIN USER_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE B.CONSTRAINT_TYPE IN ('P','U')
and COLUMN_NAME like '%%');

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] 공통계정 생성 시 c## 제거 설정  (0) 2022.05.11
[ORACLE] LOB 생성, 조회  (0) 2022.05.11
[ORACLE] PK, UK table 조회  (0) 2022.05.11
[ORACLE] SE to EE 버전업 테스트  (0) 2022.05.11
[ORACLE] Archive log format  (0) 2022.05.11
728x90
-- PK, UK table list
set line 300
col OWNER for a20
col TABLE_NAME for a30
col COLUMN_NAME for a30
SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME
FROM USER_CONS_COLUMNS A
INNER JOIN USER_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE (B.CONSTRAINT_TYPE = 'P' or B.CONSTRAINT_TYPE = 'U') and COLUMN_NAME like '%%' ;
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] LOB 생성, 조회  (0) 2022.05.11
[ORACLE] 특정 스키마의 PK, UK 없는 table 조회  (0) 2022.05.11
[ORACLE] SE to EE 버전업 테스트  (0) 2022.05.11
[ORACLE] Archive log format  (0) 2022.05.11
[ORACLE] HA start/stop Script (Linux)  (0) 2022.05.11
728x90
1.version 확인
C:\Users\Administrator>sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 18:12:02 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL>
2.oracle service 중지
 
3.backup
$ORACLE_HOME/database/
$ORACLE_HOME/network/admin/
data파일
archive파일

 

4.deinstall
5.version upgrand engine 설치
6.service 재등록
oradim -delete -sid ORCL
oradim -NEW -SID ORCL -INTPWD oracle -STARTMODE auto -PFILE 'C:\u01\base\product\19c\database\SPFILEORCL.ora'
 
7.$ORACLE_HOME 환경변수 변경
SQL> startup upgrade;
ORACLE instance started.
 
Total System Global Area 2516582224 bytes
Fixed Size                  9031504 bytes
Variable Size             536870912 bytes
Database Buffers         1962934272 bytes
Redo Buffers                7745536 bytes
Database mounted.
Database opened.
 
C:\u01\base\product\19c\rdbms\admin>sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 20:50:34 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> @catalog.sql
SQL> @catproc.sql
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 2516582224 bytes
Fixed Size                  9031504 bytes
Variable Size             536870912 bytes
Database Buffers         1962934272 bytes
Redo Buffers                7745536 bytes
Database mounted.
Database opened.
 
SQL> @utlrp.sql
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] 특정 스키마의 PK, UK 없는 table 조회  (0) 2022.05.11
[ORACLE] PK, UK table 조회  (0) 2022.05.11
[ORACLE] Archive log format  (0) 2022.05.11
[ORACLE] HA start/stop Script (Linux)  (0) 2022.05.11
[ORACLE] DB_NAME 변경  (0) 2022.05.11
728x90
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] PK, UK table 조회  (0) 2022.05.11
[ORACLE] SE to EE 버전업 테스트  (0) 2022.05.11
[ORACLE] HA start/stop Script (Linux)  (0) 2022.05.11
[ORACLE] DB_NAME 변경  (0) 2022.05.11
[ORACLE] RMAN-03009, ORA-00245  (0) 2022.03.07
728x90
## START script
vi ora_start.sh
#!/bin/ksh
su - oracle << EOF
lsnrctl start listener
exit
EOF
su - oracle << EOF
export ORACLE_SID=PHARMS
sqlplus "/as sysdba" << EOF
startup
exit
EOF
 
## STOP  script
vi ora_stop.sh
#!/bin/ksh
su - oracle << EOF
lsnrctl stop listener
EOF
su - oracle << EOF
export ORACLE_SID=PHARMS
sqlplus "/as sysdba" << EOF
shutdown immediate
exit
EOF
-- 확인
# ps -ef | grep pmon
oracle    5094     1  0 01:34 ?        00:00:00 ora_pmon_ABCD
root      5165  2722  0 01:34 pts/1    00:00:00 grep pmon
 
# ps -ef | grep tns
root        29     2  0 Nov11 ?        00:00:00 [netns]
oracle    5068     1  0 01:34 ?        00:00:00 /u01/base/product/11.2.0.4/bin/tnslsnr listener -inherit
root      5167  2722  0 01:34 pts/1    00:00:00 grep tns

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] SE to EE 버전업 테스트  (0) 2022.05.11
[ORACLE] Archive log format  (0) 2022.05.11
[ORACLE] DB_NAME 변경  (0) 2022.05.11
[ORACLE] RMAN-03009, ORA-00245  (0) 2022.03.07
[ORACLE] INCREMENTAL BACKUP (Crontab)  (0) 2022.03.07
728x90
<NID 사용>
1. pfile 생성
SQL> create pfile from spfile;
File created.
 
2. DB 중지
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
3. DB mount (pfile 사용)
SQL> startup mount pfile='$ORACLE_HOME/dbs/initORCL.ora';
 
ORACLE instance started.
Total System Global Area 2405122048 bytes
Fixed Size            2255592 bytes
Variable Size          603981080 bytes
Database Buffers     1778384896 bytes
Redo Buffers           20500480 bytes
Database mounted.
 
4. listener, tns 상태 확인
[oracle@centos7-:~]$lsnrctl start listener
[oracle@centos7-:~]$tnsping ORCL
 
5. NID utility - dbname 변경
[oracle@centos7-:~]$nid target=sys/oracle@ORCL dbname=TEST logfile=/nidlog
 
DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 5 21:29:07 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL (DBID=1583333315)
Connected to server version 11.2.0
Control Files in database:
    /oradata/ORCL/control01.ctl
    /oradata/ORCL/control02.ctl
Changing database ID from 1583333315 to 2353826467
Changing database name from ORCL to TEST
    Control File /oradata/ORCL/control01.ctl - modified
    Control File /oradata/ORCL/control02.ctl - modified
    Datafile /oradata/ORCL/system01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/sysaux01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/undotbs01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/users01.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/test.db - dbid changed, wrote new name
    Datafile /oradata/ORCL/temp01.db - dbid changed, wrote new name
    Control File /oradata/ORCL/control01.ctl - dbid changed, wrote new name
    Control File /oradata/ORCL/control02.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2353826467.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
 
6. pfile 이름, 내용 변경
[oracle@centos7-:~]$vi initORCL.ora
*.db_name='TEST'
 
[oracle@centos7-:~]$cp initORCL.ora initTEST.ora
 
7. password 파일 생성
[oracle@centos7-:~]$orapwd file=orapwTEST password=oracle entries=5
 
8. 환경변수 수정
vi ~/.bash_profile
export ORACLE_SID=TEST
. ~/.bash_profile
 
9. DB mount (변경된 pfile)
SQL> startup mount pfile='$ORACLE_HOME/dbs/initTEST.ora';
ORACLE instance started.
Total System Global Area 2405122048 bytes
Fixed Size            2255592 bytes
Variable Size          603981080 bytes
Database Buffers     1778384896 bytes
Redo Buffers           20500480 bytes
Database mounted.
 
10. DB open
SQL> alter database open resetlogs;
Database altered.
 
--DB name 조회
SQL> select name from v$database;
SQL> select instance_name from v$instance;
<controlfile 사용>
 
1. trace file 생성
SQL> alter database backup controlfile to trace as '/backup/con.sql';
Database altered.
 
2. pfile 생성
SQL> create pfile from spfile;
File created.
 
3. DB 중지
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
4. pfile 이름, 내용 변경
[oracle@centos7-:~]$vi initORCL.ora
*.control_files='/oradata/ORCL/controlab.ctl','/oradata/ORCL/controlcd.ctl'
*.db_name='ABCD'
 
[oracle@centos7-:~]$cp initORCL.ora initABCD.ora
 
5. trace file 수정
[oracle@centos7-:~]$vi con.sql
 
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "ABCD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/ORCL/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/oradata/ORCL/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/oradata/ORCL/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/oradata/ORCL/system01.dbf',
  '/oradata/ORCL/sysaux01.dbf',
  '/oradata/ORCL/undotbs01.dbf',
  '/oradata/ORCL/users01.dbf',
  '/oradata/ORCL/test.dbf'
CHARACTER SET WE8MSWIN1252
;
 
6. 환경변수 수정
vi ~/.bash_profile
export ORACLE_SID=ABCD
. ~/.bash_profile
 
7. script 실행
SQL> @con.sql
ORACLE instance started.
Total System Global Area 2405122048 bytes
Fixed Size            2255592 bytes
Variable Size          603981080 bytes
Database Buffers     1778384896 bytes
Redo Buffers           20500480 bytes
Control file created.
 
8. recover 수행
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 905470 generated at 11/05/2020 23:56:03 needed for thread 1
ORA-00289: suggestion : /arch/1_96_1055552579.arc
ORA-00280: change 905470 for thread 1 is in sequence #96
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
 
9. DB open
SQL> alter database open resetlogs;
Database altered.
 
--DB name 조회
SQL> select name from v$database;
SQL> select instance_name from v$instance;
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Archive log format  (0) 2022.05.11
[ORACLE] HA start/stop Script (Linux)  (0) 2022.05.11
[ORACLE] RMAN-03009, ORA-00245  (0) 2022.03.07
[ORACLE] INCREMENTAL BACKUP (Crontab)  (0) 2022.03.07
[ORACLE] RMAN FULLBACKUP (Crontab)  (1) 2022.03.07
728x90
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
 
 
RMAN-03009: failure of backup command on ch00 channel at 03/06/2022 21:05:01
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

 

RMAN> show all;     
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/base/product/19/dbs/snapcf_orcl1.f'; # default
 
 
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/ORCL/snapcf_orcl.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/ORCL/snapcf_orcl.f';
new RMAN configuration parameters are successfully stored
 
 
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/ORCL/snapcf_orcl.f';
 
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] HA start/stop Script (Linux)  (0) 2022.05.11
[ORACLE] DB_NAME 변경  (0) 2022.05.11
[ORACLE] INCREMENTAL BACKUP (Crontab)  (0) 2022.03.07
[ORACLE] RMAN FULLBACKUP (Crontab)  (1) 2022.03.07
[ORACLE] RAC interconnect MTU 값이 다를경우 log  (0) 2022.01.28
728x90
-- full backup
#!/bin/sh
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
 
#RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=/RMAN/log/fullbackup.`/bin/date +%Y%m%d_%H%M%S`.log
find /RMAN/log/* -mtime +40 -exec rm -f {} \;
 
if [ -f "$RMAN_LOG_FILE" ]
then
    rm -f "$RMAN_LOG_FILE"
fi
 
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
ORACLE_HOME=/oracle/base/product/19
export ORACLE_HOME
ORACLE_SID=orcl1
export ORACLE_SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=sys/oracle
RMAN=$ORACLE_HOME/bin/rman
 
echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo "Full backup requested" >> $RMAN_LOG_FILE
 
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR nocatalog msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    SKIP INACCESSIBLE
    TAG full_level0
    FILESPERSET 5
    FORMAT '/RMAN/backup/bk_%s_%p_%t'
    INCREMENTAL LEVEL 0
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
#CONFIGURE CHANNEL 1 DE
BACKUP
    filesperset 20
    FORMAT '/RMAN/backup/al_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    FORMAT '/RMAN/backup/control_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
exit
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
    su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
else
    /usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
fi
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
-- cumulative backup
#!/bin/sh
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
 
#RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=/RMAN/log/cumu_incre.`/bin/date +%Y%m%d_%H%M%S`.log
find /RMAN/log/* -mtime +40 -exec rm -f {} \;
 
if [ -f "$RMAN_LOG_FILE" ]
then
    rm -f "$RMAN_LOG_FILE"
fi
 
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
ORACLE_HOME=/oracle/base/product/19
export ORACLE_HOME
ORACLE_SID=orcl1
export ORACLE_SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=sys/oracle
RMAN=$ORACLE_HOME/bin/rman
 
echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo "Full backup requested" >> $RMAN_LOG_FILE
 
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR nocatalog msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    SKIP INACCESSIBLE
    TAG cumu_incre_1
    FILESPERSET 5
    FORMAT '/RMAN/backup/bk_%s_%p_%t'
    INCREMENTAL LEVEL 1 CUMULATIVE
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    filesperset 20
    FORMAT '/RMAN/backup/al_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    FORMAT '/RMAN/backup/ctl_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
exit
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
    su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
else
    /usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
fi
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
-- incremental backup
#!/bin/sh
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
 
#RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=/RMAN/log/incre.`/bin/date +%Y%m%d_%H%M%S`.log
find /RMAN/log/* -mtime +40 -exec rm -f {} \;
 
if [ -f "$RMAN_LOG_FILE" ]
then
    rm -f "$RMAN_LOG_FILE"
fi
 
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
ORACLE_HOME=/oracle/base/product/19
export ORACLE_HOME
ORACLE_SID=orcl1
export ORACLE_SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=sys/oracle
RMAN=$ORACLE_HOME/bin/rman
 
echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo "Full backup requested" >> $RMAN_LOG_FILE
 
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR nocatalog msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    SKIP INACCESSIBLE
    TAG incre_2
    FILESPERSET 5
    FORMAT '/RMAN/backup/bk_%s_%p_%t'
    INCREMENTAL LEVEL 2
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    filesperset 20
    FORMAT '/RMAN/backup/al_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    FORMAT '/RMAN/backup/ctl_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
exit
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
    su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
else
    /usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
fi
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
 

 

 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] DB_NAME 변경  (0) 2022.05.11
[ORACLE] RMAN-03009, ORA-00245  (0) 2022.03.07
[ORACLE] RMAN FULLBACKUP (Crontab)  (1) 2022.03.07
[ORACLE] RAC interconnect MTU 값이 다를경우 log  (0) 2022.01.28
[ORACLE] solaris 11.4 - Oracle Database 12c RAC install  (0) 2022.01.17
728x90
-- full backup
#!/bin/sh
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
 
#RMAN_LOG_FILE=${0}.out
RMAN_LOG_FILE=/RMAN/log/fullbackup.`/bin/date +%Y%m%d_%H%M%S`.log
find /RMAN/log/* -mtime +40 -exec rm -f {} \;
 
if [ -f "$RMAN_LOG_FILE" ]
then
    rm -f "$RMAN_LOG_FILE"
fi
 
echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
ORACLE_HOME=/oracle/base/product/19
export ORACLE_HOME
ORACLE_SID=orcl1
export ORACLE_SID
ORACLE_USER=oracle
TARGET_CONNECT_STR=sys/oracle
RMAN=$ORACLE_HOME/bin/rman
 
echo >> $RMAN_LOG_FILE
echo   "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo   "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo   "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo   "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo "Full backup requested" >> $RMAN_LOG_FILE
 
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target $TARGET_CONNECT_STR nocatalog msglog $RMAN_LOG_FILE append << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    SKIP INACCESSIBLE
    TAG full_level0
    FILESPERSET 5
    FORMAT '/RMAN/backup/bk_%s_%p_%t'
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
#CONFIGURE CHANNEL 1 DE
BACKUP
    filesperset 20
    FORMAT '/RMAN/backup/al_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
 
 
ALLOCATE CHANNEL ch00 TYPE DISK CONNECT='sys/oracle@orcl1';
ALLOCATE CHANNEL ch01 TYPE DISK CONNECT='sys/oracle@orcl2';
BACKUP
    FORMAT '/RMAN/backup/control_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
exit
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
    su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
else
    /usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
fi
if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RMAN-03009, ORA-00245  (0) 2022.03.07
[ORACLE] INCREMENTAL BACKUP (Crontab)  (0) 2022.03.07
[ORACLE] RAC interconnect MTU 값이 다를경우 log  (0) 2022.01.28
[ORACLE] solaris 11.4 - Oracle Database 12c RAC install  (0) 2022.01.17
[ORACLE] Recyclebin  (0) 2022.01.14

+ Recent posts

티스토리툴바