2019年11月22日 星期五

Oracle DB - 設定 RMAN backup


--Oracle Catalog DB: DB11G@testdb88
--Create tablespace RMAN

CREATE TABLESPACE CATALOG_TBS DATAFILE 
  '/u01/app/oracle/oradata/DB11G/catalog_tbs01.dbf' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 --Create DB user: rman_ora11g
CREATE USER RMAN_ORA11G
  IDENTIFIED BY "1qaz2wsx"
  DEFAULT TABLESPACE CATALOG_TBS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for RMAN_ORA11G 
  GRANT CONNECT TO RMAN_ORA11G;
  GRANT RECOVERY_CATALOG_OWNER TO RMAN_ORA11G;
  ALTER USER RMAN_ORA11G DEFAULT ROLE ALL;
  -- 1 System Privilege for RMAN_ORA11G 
  GRANT UNLIMITED TABLESPACE TO RMAN_ORA11G;

--Create Catalog

 [oracle@testdb1 admin]$ rman

 Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 1 16:56:34 2019

 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 RMAN> connect catalog rman_ora11g/xxxxxxxx@DB11G

 connected to recovery catalog database

 RMAN> CREATE CATALOG TABLESPACE catalog_tbs;

 recovery catalog created

 RMAN>

--Register Database
[oracle@testdb1 admin]$ rman target / catalog rman_ora11g/xxxxxxxx@catalog

 Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 1 17:10:25 2019

 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 connected to target database: PRD (DBID=2086780556)
connected to recovery catalog database

 RMAN> register database;

 database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

 RMAN> report schema;

 Report of database schema for database with db_unique_name PRD

 List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               YES     /u02/oradata/PRD/system01.dbf
2    540      SYSAUX               NO      /u02/oradata/PRD/sysaux01.dbf
3    105      UNDOTBS1             YES     /u02/oradata/PRD/undotbs01.dbf
4    5        USERS                NO      /u02/oradata/PRD/users01.dbf
5    313      EXAMPLE              NO      /u02/oradata/PRD/example01.dbf

 List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /u02/oradata/PRD/temp01.dbf

RMAN> show all
2> ;

 RMAN configuration parameters for database with db_unique_name PRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PRD.f'; # default

 RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

 new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

 RMAN> show all
2> ;

 RMAN configuration parameters for database with db_unique_name PRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PRD.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

 old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

 RMAN>

 RUN
{ 
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/backup/%d_Full_%Y%M%D_%u.bak';
  BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/u03/backup/%d_Control_%Y%M%D_%u.bak';
  BACKUP AS BACKUPSET DATABASE; 
  RELEASE CHANNEL disk1;
} 

 released channel: ORA_DISK_1
allocated channel: disk1
channel disk1: SID=372 device type=DISK

 Starting backup at 04-NOV-19
channel disk1: starting datafile copy
copying current control file
output file name=/u03/backup/PRD_Control_20191104_0bug1589.bak tag=TAG20191104T111209 RECID=5 STAMP=1023448329
channel disk1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-NOV-19

 Starting backup at 04-NOV-19
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/PRD/system01.dbf
input datafile file number=00002 name=/u02/oradata/PRD/sysaux01.dbf
input datafile file number=00005 name=/u02/oradata/PRD/example01.dbf
input datafile file number=00003 name=/u02/oradata/PRD/undotbs01.dbf
input datafile file number=00004 name=/u02/oradata/PRD/users01.dbf
channel disk1: starting piece 1 at 04-NOV-19
channel disk1: finished piece 1 at 04-NOV-19
piece handle=/u03/backup/PRD_Full_20191104_0cug158b.bak tag=TAG20191104T111211 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:35
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel disk1: starting piece 1 at 04-NOV-19
channel disk1: finished piece 1 at 04-NOV-19
piece handle=/u03/backup/PRD_Full_20191104_0dug159e.bak tag=TAG20191104T111211 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-NOV-19

 released channel: disk1

 RMAN> list backup
2> ;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
463     Full    1.16G      DISK        00:00:29     04-NOV-19
        BP Key: 467   Status: AVAILABLE  Compressed: NO  Tag: TAG20191104T105948
        Piece Name: /u03/backup/PRD_Full_20191104_09ug14h4.bak
  List of Datafiles in backup set 463
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1393036    04-NOV-19 /u02/oradata/PRD/system01.dbf
  2       Full 1393036    04-NOV-19 /u02/oradata/PRD/sysaux01.dbf
  3       Full 1393036    04-NOV-19 /u02/oradata/PRD/undotbs01.dbf
  4       Full 1393036    04-NOV-19 /u02/oradata/PRD/users01.dbf
  5       Full 1393036    04-NOV-19 /u02/oradata/PRD/example01.dbf

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
464     Full    9.36M      DISK        00:00:01     04-NOV-19
        BP Key: 468   Status: AVAILABLE  Compressed: NO  Tag: TAG20191104T105948
        Piece Name: /u03/backup/PRD_Full_20191104_0aug14i7.bak
  SPFILE Included: Modification time: 03-NOV-19
  SPFILE db_unique_name: PRD
  Control File Included: Ckp SCN: 1393229      Ckp time: 04-NOV-19

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
593     Full    1.16G      DISK        00:00:30     04-NOV-19
        BP Key: 597   Status: AVAILABLE  Compressed: NO  Tag: TAG20191104T111211
        Piece Name: /u03/backup/PRD_Full_20191104_0cug158b.bak
  List of Datafiles in backup set 593
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1394382    04-NOV-19 /u02/oradata/PRD/system01.dbf
  2       Full 1394382    04-NOV-19 /u02/oradata/PRD/sysaux01.dbf
  3       Full 1394382    04-NOV-19 /u02/oradata/PRD/undotbs01.dbf
  4       Full 1394382    04-NOV-19 /u02/oradata/PRD/users01.dbf
  5       Full 1394382    04-NOV-19 /u02/oradata/PRD/example01.dbf

 BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
594     Full    9.36M      DISK        00:00:01     04-NOV-19
        BP Key: 598   Status: AVAILABLE  Compressed: NO  Tag: TAG20191104T111211
        Piece Name: /u03/backup/PRD_Full_20191104_0dug159e.bak
  SPFILE Included: Modification time: 03-NOV-19
  SPFILE db_unique_name: PRD
  Control File Included: Ckp SCN: 1394398      Ckp time: 04-NOV-19

 RMAN>

 RMAN> report obsolete;

 RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           463    04-NOV-19
  Backup Piece       467    04-NOV-19          /u03/backup/PRD_Full_20191104_09ug14h4.bak
Archive Log          547    04-NOV-19          /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_25_gvz5o5ko_.arc
Backup Set           464    04-NOV-19
  Backup Piece       468    04-NOV-19          /u03/backup/PRD_Full_20191104_0aug14i7.bak
Archive Log          548    04-NOV-19          /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_26_gvz5o6py_.arc
Archive Log          549    04-NOV-19          /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_27_gvz5o851_.arc
Control File Copy     576    04-NOV-19          /u03/backup/PRD_Control_20191104_0bug1589.bak


張貼者： 於 沒有留言:
標籤： , ,

2019年11月21日 星期四

Oracle DB - 11g 使用 DBNEWID utility (nid) 指定 new DBNAME db_id

舊 db_name: PRD
新 db_name: QAS

STEP 1: Backup the database.

STEP 2: Mount the database after a clean shutdown:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1056967720 bytes
Database Buffers          587202560 bytes
Redo Buffers                7094272 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 STEP 3: 使用 DBNEWID utility (nid) 指定 new DBNAME from the command line using a user with SYSDBA privilege:

[oracle@testdb2 ~]$ nid TARGET=sys/xxxxxxxxx DBNAME=QAS

 DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 21 16:05:19 2019

 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 Connected to database PRD (DBID=2086780556)

 Connected to server version 11.2.0

 Control Files in database:
    /u02/oradata/PRD/control01.ctl
    /u01/app/oracle/fast_recovery_area/PRD/control02.ctl

 Change database ID and database name PRD to QAS? (Y/[N]) => Y

 Proceeding with operation
Changing database ID from 2086780556 to 2414354175
Changing database name from PRD to QAS
    Control File /u02/oradata/PRD/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - modified
    Datafile /u02/oradata/PRD/system01.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/sysaux01.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/undotbs01.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/users01.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/example01.db - dbid changed, wrote new name
    Datafile /u02/oradata/QAS/test01.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/test2/test2.db - dbid changed, wrote new name
    Datafile /u02/oradata/PRD/temp01.db - dbid changed, wrote new name
    Control File /u02/oradata/PRD/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - dbid changed, wrote new name
    Instance shut down

 Database name changed to QAS.
Modify parameter file and generate a new password file before restarting.
Database ID for database QAS changed to 2414354175.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

 STEP 5: Modify the DB_NAME parameter. The startup will result in an error but proceed anyway.

[oracle@testdb2 dbs]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:11:10 2019

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup mount
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1056967720 bytes
Database Buffers          587202560 bytes
Redo Buffers                7094272 bytes
ORA-01103: database name 'QAS' in control file is not 'PRD'

 --這是因為spfile仍然記錄舊的db_name

 SQL> alter system set db_name=QAS scope=spfile;

 System altered.

 SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
  • STEP 6: Create a new password file: (選用)
    orapwd file=$ORACLE_HOME/dbs/orapwQAS password=password entries=10
  • STEP 7: Rename the SPFILE to match the new DBNAME. (選用)
  • STEP 8:ORACLE_SID=QAS; export ORACLE_SID (選用)
  •  STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
[oracle@testdb2 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@testdb2 admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@testdb2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

 ADR_BASE_LISTENER = /u01/app/oracle

 [oracle@testdb2 admin]$
[oracle@testdb2 admin]$ more tnsnames.ora

 QAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = QAS)
    )
  )
  
PRD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRD)
    )
  )
DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G)
    )
  )

 CATALOG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G)
    )
  )

 lsnrctl reload

  • STEP 10: Open the database with RESETLOGS:

 [oracle@testdb2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@testdb2 dbs]$ ls
hc_PRD.dat  init.ora  lkPRD  orapwPRD  spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:16:36 2019

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1056967720 bytes
Database Buffers          587202560 bytes
Redo Buffers                7094272 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select open_mode from v$database;

 OPEN_MODE
--------------------
MOUNTED

 SQL> ALTER DATABASE OPEN RESETLOGS;

 Database altered.

 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1056967720 bytes
Database Buffers          587202560 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.

SQL>
  • STEP 11: Backup the database.

 --一些觀察
-- DB Name 改為 QAS
SQL> select name from v$database;

 NAME
---------
QAS
-- Instance Name 維持PRD
SQL> select instance from v$thread ;

 INSTANCE
--------------------------------------------------------------------------------
PRD

 SQL> select instance_name from v$instance;

 INSTANCE_NAME
----------------
PRD

[oracle@testdb2 dbs]$ ps -ef|grep ora_
oracle    3424     1  0 16:16 ?        00:00:00 ora_pmon_PRD
oracle    3448     1  0 16:16 ?        00:00:00 ora_smon_PRD


-- $ORACLE_HOME/dbs  多一個 lkQAS
[oracle@testdb2 admin]$ cd $ORACLE_HOME
[oracle@testdb2 db_1]$ cd dbs
[oracle@testdb2 dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Nov 21 16:17 hc_PRD.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Nov 18 16:29 lkPRD
-rw-r-----. 1 oracle oinstall   24 Nov 21 16:16 lkQAS
-rw-r-----. 1 oracle oinstall 1536 Nov 18 15:55 orapwPRD
-rw-r-----. 1 oracle oinstall 2560 Nov 21 16:18 spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:24:42 2019

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> show parameter spfile

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfilePRD.ora
SQL> show parameter control

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u02/oradata/PRD/control01.ctl
                                                 , /u01/app/oracle/fast_recover
                                                 y_area/PRD/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> select name from v$tempfile;

 NAME
--------------------------------------------------------------------------------
/u02/oradata/PRD/temp01.dbf

 SQL> select DB_UNIQUE_NAME from v$database;

 DB_UNIQUE_NAME
------------------------------
QAS
[oracle@testdb2 dbs]$ lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-NOV-2019 16:52:39

 Copyright (c) 1991, 2013, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                21-NOV-2019 16:51:28
Uptime                    0 days 0 hr. 1 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/testdb2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PRDXDB" has 1 instance(s).
  Instance "PRD", status READY, has 1 handler(s) for this service...
Service "QAS" has 1 instance(s).
  Instance "PRD", status READY, has 1 handler(s) for this service...
The command completed successfully


[root@testdb2 ~]# cd /u01/app/oracle

 [root@testdb2 oracle]# ll
total 20
drwxr-xr-x.  3 oracle oinstall 4096 Nov 18 16:22 admin
drwxr-xr-x.  2 oracle oinstall 4096 Oct 30 16:41 checkpoints
drwxrwxr-x. 11 oracle oinstall 4096 Oct 30 16:39 diag
drwxr-xr-x.  4 oracle oinstall 4096 Nov 21 16:17 fast_recovery_area
drwxrwxr-x.  3 oracle oinstall 4096 Oct 30 15:43 product

[root@testdb2 oracle]# ll /u01/app/oracle/admin/PRD/
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:24 adump


 [oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:58 PRD
drwxr-x---. 3 oracle oinstall 4096 Nov 21 16:17 QAS

 [oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 21 16:17 onlinelog
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/onlinelog/
[root@testdb2 oracle]# su - oracle
[oracle@testdb2 ~]$ env|grep ORA
ORACLE_UNQNAME=PRD
ORACLE_SID=PRD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

 [oracle@testdb2 ~]$ cd $ORACLE_BASE
[oracle@testdb2 oracle]$ ls
admin  checkpoints  diag  fast_recovery_area  product
[oracle@testdb2 oracle]$ cd diag/rdbms/

[oracle@testdb2 rdbms]$ ll
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:14 prd
drwxr-xr-x. 3 oracle oinstall 4096 Nov 21 16:16 qas

 [oracle@testdb2 rdbms]$ cd qas
[oracle@testdb2 qas]$ ll
total 4
-rw-r-----.  1 oracle oinstall    0 Nov 21 16:16 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Nov 21 16:16 PRD


[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 alert
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 hm
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incident
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 ir
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 lck
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 stage
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:23 trace

 [oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_*.log
-rw-r-----. 1 oracle oinstall 6758 Nov 21 16:22 /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_PRD.log
[oracle@testdb2 trace]$




張貼者： 於 1 則留言:
標籤： ,

2019年11月15日 星期五

Oracle DB - linux based ORACLE_SID 大小寫區分 case sensitivity



[root@testdb88 build]# su - oracle
[oracle@testdb88 ~]$ env|grep ORA
ORACLE_UNQNAME=DB11G
ORACLE_SID=DB11G
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb88
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb88 ~]$ export ORACLE_SID=db11g
[oracle@testdb88 ~]$ env|grep ORA
ORACLE_UNQNAME=DB11G
ORACLE_SID=db11g
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb88
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb88 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 13:05:01 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@testdb88 ~]$ export ORACLE_SID=DB11G
[oracle@testdb88 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 13:05:26 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production



張貼者： 於 沒有留言:
標籤：

Perl script 錯誤訊息 Can't locate Mail/Sendmail.pm 解決辦法

在新的Linux Server（Oracle Enterprise Linux Server 6.8)）上配置tablespace空間告警的perl script後，測試時報錯如下

[oracle@testdb88 ~]$ ~/perl/mailTableSpaceSize.pl bypass
Can't locate Mail/Sendmail.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /home/oracle/perl/mailTableSpaceSize.pl line 6.
BEGIN failed--compilation aborted at /home/oracle/perl/mailTableSpaceSize.pl line 6.


Perl腳本mailTableSpaceSize.pl如下所示，其中使用了Mail::Sendmail郵件模塊。
#!/usr/bin/perl

use DBI;
use Mail::Sendmail;
use MIME::QuotedPrint;
use MIME::Base64;
use Encode;
use Number::Format qw(:subs);
use DateTime qw();



出現這個錯誤是因為Perl沒有安裝Mail::Sendmail模組，安裝此模組問題即可解決，可以使用下面幾種方式安裝。 (使用cpan安裝，Server要能夠連上網路，否則無法下載相關package。

[Best]

CPAN shell

                     perl -MCPAN -e shell
                    install Mail::Sendmail

 cpanm

                     cpanm Mail::Sendmail

[Traditional]

perl Makefile.PL
make
make test
make install

[Manual]

Copy Sendmail.pm to Mail/ in your Perl lib directory.



參考資訊:

Neil Bowers  /   Mail-Sendmail-0.80  ++  / Mail::Sendmail
https://metacpan.org/pod/Mail::Sendmail

How to install CPAN modules
https://www.cpan.org/modules/INSTALL.html

Perl 教程
https://www.runoob.com/perl/perl-tutorial.html
張貼者： 於 沒有留言:
標籤： ,

2019年11月12日 星期二

Oracle Database Offerings 版本與縮寫 Oracle Multitenant - # of PDBs 多租戶


Oracle數據庫19c | 每個CDB最多3個PDB，無需許可多租戶

  1. 對於所有產品，如果您沒有獲得Oracle Multitenant的許可，則給定容器數據庫中隨時可以有多達3個用戶創建的PDB。
  2. EE：額外費用選項； 如果您已獲得Oracle Multitenant的許可，則最多可以創建252個PDB。
  3. EE-ES：額外費用選項； 如果您已獲得Oracle Multitenant的許可，則最多可以創建4096個PDB
  4. DBCS EE-HP，DBCS EE-EP和ExaCS：包含選項； 您最多可以創建4096個PDB。


Oracle Database Offerings
Offering提供型態縮寫說明
Oracle Database
Standard Edition 2		On-PremisesSE2Oracle數據庫標準版2包含開發工作組，部門級和Web應用程序所必需的功能。
Oracle Database Enterprise EditionOn-PremisesEEOracle數據庫企業版為開發應用程序提供了性能，可用性，可擴展性和安全性，例如大容量在線事務處理（OLTP）應用程序，查詢密集型數據倉庫和要求苛刻的Internet應用程序。
可以通過購買Oracle數據庫選件和Oracle管理包來增強Oracle數據庫企業版。
Oracle Database
Enterprise Edition on
Engineered Systems		On-PremisesEE-ES在本地工程系統（Oracle數據庫雲服務器或Oracle數據庫機）上安裝的Oracle數據庫企業版軟件。
包括Oracle數據庫的所有組件。您可以通過購買Oracle數據庫選件和Oracle管理包來進一步增強此功能。
EE-ES的許可策略取決於它是安裝在Oracle數據庫雲服務器還是Oracle數據庫機上。請務必注意這些差異，這些差異將在本指南的後續部分中進行記錄。
Oracle Database Personal
Edition		On-PremisesPEOracle數據庫個人版支持單用戶開發和部署環境，這些環境要求與Oracle數據庫標準版2和Oracle數據庫企業版完全兼容。
包括Enterprise Edition隨附的所有組件以及所有Oracle Database選項，但Oracle RAC One Node和Oracle Real Application Clusters選項無法與Personal Edition一起使用。 Personal Edition僅在Linux和Windows平台上可用。 Oracle管理包不能與個人版一起使用。



Offering提供型態縮寫說明
Oracle Database Cloud
Service Standard Edition		CloudDBCS SE包括Oracle數據庫標準版2軟件。
Oracle Database Cloud
Service Enterprise Edition		CloudDBCS EE包括Oracle數據庫企業版軟件。
Oracle Database Cloud
Service Enterprise Edition -
High Performance		CloudDBCS EE-HP包括Oracle數據庫企業版軟件以及許多Oracle數據庫選件和Oracle管理包。
Oracle Database Cloud
Service Enterprise Edition -
Extreme Performance		CloudDBCS EE-EP包括Oracle數據庫企業版軟件以及適用於Oracle數據庫雲服務的所有Oracle數據庫選件和Oracle管理包。
Oracle Database Exadata
Cloud Service		CloudExaCS包括Oracle數據庫企業版軟件以及適用於Oracle數據庫數據庫雲服務器的所有Oracle數據庫選件和Oracle管理包。 ExaCS的許可政策也適用於客戶的Oracle Database Exadata Cloud。





張貼者： 於 沒有留言:
標籤： , ,
訂閱： 文章 (Atom)