2015年8月4日火曜日

Oracle 触ってみた。

【RMANでディスクチェック】
# su - oracle
# rman target /
#  validate database check logical;

#-------------------------------------------------------
# archivelog 削除 ＆ 同期 （アーカイブログ）
#-------------------------------------------------------
# su - oracle
# rman target /
RMAN> list archivelog all;
RMAN> delete archivelog all;                    # 全削除
RMAN> crosscheck archivelog all;         ＃ 物理ディスクとの整合性（同期）
RMAN> configure channel device type disk format '/work/archivelog.backup/ar_%U';
RMAN> show all;         # 確認
RMAN configuration parameters for database with db_unique_name ARCSDBMS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/work/archivelog.backup/ar_%U';
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 '+DATA/ARCSDBMS/snapcf_arcsdbms1.f';

RMAN> backup archivelog until sequence XXX thread=1 delete input;
※ XXXは、Seq.
RMAB> backup archivelog all delete all input;               # 全バックアップ＆削除

RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ARCSDBMS
=====================================================================

Key       Thrd Seq     S Low Time     Name:
--------- ------ ------- - -------------------------
161321  1      45331   A    21-11-24 +ARCT_M/arcsdbms/archivelog/2021_11_24/thread_1_seq_45331.297.1089496643


# su - oracle
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
ORACLE_SID=XXXXXXXXX

# sqlplus / as sysdba
SQL> select * from v$version; #バージョン表示
SQL> def #ユーザ情報等確認
SQL> select username,expiry_date,profile from dba_users; #ユーザ一覧
SQL> select * from all_users; #ユーザ一覧
SQL> select * from v$instance; #インスタンス表示
SQL> select * from sys.gv_$instance; #インスタンス一覧
SQL> host #OSコマンドを実行。
SQL> select username,osuser,machine,terminal,program from v$session; #session一覧
SQL> select * from product_component_version;
SQL> select * from v$option; #オプション製品Ver.
SQL> select * from v$pwfile_users; #DBA接続可能ユーザ
SQL> select * v$database; #DBID

# sqlplus ARCSXXXX/xxxxxxx@arcsdbms1
SQL> select owner, table_name from all_tables where owner='XXX'; #テーブル一覧
SQL> select column_name,data_type, data_length from user_tab_columns where owner='XXXXXXX' and table_name='CCMXXX'; #column一覧

SQL> select * from CCMXXX;

【python36でアクセス】
# vi oracle.py
#!/usr/bin/env python
import cx_Oracle
import os

HOST = '192.168.xx.xxx'
PORT = '1521'
SID = 'arcsdbms'
os.environ["NLS_LANG"] = "JAPANESE_JAPAN.JA16SJISTILDE"

tns = cx_Oracle.makedsn(HOST, PORT, SID)
conn = cx_Oracle.connect("ARCSxxxx", "XXXXXXX", tns)

print( conn.version)

cur = conn.cursor()
cur.execute("""select column_name,data_type,data_length from all_tab_columns where table_name='XXXnnn'""")
rows = cur.fetchall()

for r in rows:
   print("%s %s %s" % (r[0],r[1],r[2]))

cur.execute("""select * from XXXnnn""")
rows = cur.fetchall()
for r in rows:
   print("%s %s %s %s" % (r[0],r[1],r[2],r[3]))


【Oracle RMANの有効期限が切れた。】
# su - oracle
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
ORACLE_SID=XXXXXXXXX

# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 4 11:58:16 2015
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, Real Application Clusters and Automatic Storage Management options

set linesize 200;

SQL>  select username,expiry_date,profile from dba_users;
USERNAME       EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
MGMT_VIEW DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP DEFAULT
SYSMAN DEFAULT
PERFSTAT DEFAULT
SGNGDBMC DEFAULT
NEORDS DEFAULT
ARCSDBPA DEFAULT
DRCSDBCB DEFAULT
ARCSDBSS DEFAULT

USERNAME       EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
ARCSDBCO DEFAULT
ARCSDBCB DEFAULT
SGNGDBMS DEFAULT
ARCSDBCS DEFAULT
TRCSDBCB DEFAULT
XRCSDBCB DEFAULT
OUTLN       15-01-20 DEFAULT
WMSYS       15-01-20 DEFAULT
APPQOSSYS       15-01-20 DEFAULT
DIP       15-01-20 DEFAULT
GG_USER       15-04-07 DEFAULT

USERNAME       EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
ORACLE_OCM       15-01-20 DEFAULT

23 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


[oracle@dbsv1 ~]$ sqlplus sys/パスワード@rmanrepo1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 4 12:01:34 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select username,expiry_date,profile from dba_users;

USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW     15-07-23
DEFAULT

SYS     15-07-23
DEFAULT

SYSTEM     15-07-23
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN     16-01-30
DEFAULT

DBSNMP     15-07-30
DEFAULT

SYSMAN     15-07-30
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN     15-01-24
DEFAULT

ANONYMOUS     15-01-24
DEFAULT

WMSYS     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB     15-01-24
DEFAULT

APPQOSSYS     15-01-24
DEFAULT

DIP     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM     15-01-24
DEFAULT

XS$NULL     15-01-24
DEFAULT


14 rows selected.

SQL> select username,expiry_date,profile from dba_users;

USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW     15-07-23
DEFAULT

SYS     15-07-23
DEFAULT

SYSTEM     15-07-23
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN     16-01-30
DEFAULT

DBSNMP     15-07-30
DEFAULT

SYSMAN     15-07-30
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN     15-01-24
DEFAULT

ANONYMOUS     15-01-24
DEFAULT

WMSYS     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB     15-01-24
DEFAULT

APPQOSSYS     15-01-24
DEFAULT

DIP     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM     15-01-24
DEFAULT

XS$NULL     15-01-24
DEFAULT


14 rows selected.

SQL> select *  from dba_profiles where PROFILE = 'DEFAULT'and RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

PROFILE
------------------------------------------------------------
RESOURCE_NAME
----------------------------------------------------------------
RESOURCE_TYPE
----------------
LIMIT
--------------------------------------------------------------------------------
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
180


SQL> alter profile DEFAULT limit password_life_time unlimited;

Profile altered.

SQL> select *  from dba_profiles where PROFILE = 'DEFAULT'and RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

PROFILE
------------------------------------------------------------
RESOURCE_NAME
----------------------------------------------------------------
RESOURCE_TYPE
----------------
LIMIT
--------------------------------------------------------------------------------
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
UNLIMITED


SQL> select username,expiry_date,profile from dba_users;

USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW
DEFAULT

SYS
DEFAULT

SYSTEM
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN
DEFAULT

DBSNMP     15-07-30
DEFAULT

SYSMAN     15-07-30
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN     15-01-24
DEFAULT

ANONYMOUS     15-01-24
DEFAULT

WMSYS     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB     15-01-24
DEFAULT

APPQOSSYS     15-01-24
DEFAULT

DIP     15-01-24
DEFAULT


USERNAME     EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM     15-01-24
DEFAULT

XS$NULL     15-01-24
DEFAULT


14 rows selected.

【パスワード変更】
SQL> alter USER DBSNMP IDENTIFIED BY "パスワード";

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@dbsv1 ~]$ logout
[root@dbsv1 ~]# logout
時刻:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)

zabbix7 amazon linux2023 インストール postgres15

【postgres】 dnf -y install postgresql15-server postgresql15-server-devel postgresql-setup initdb passwd postgres vi `find / -name pg_hba.con...

  • LVM ファイルシステムのマウント
    # mount /dev/nvme0n1p3 /mnt/m2 mount: /mnt/m2: 未知のファイルシステムタイプ 'LVM2_member' です. #  fdisk -l /dev/nvme0n1 ディスク /dev/nvme0n1: 953....
  • ZABBIX SNMP Trap 設定
    【snmp/snmptrap】 # yum -y install net-snmp # yum -y install net-snmp-utils # yum -y install snmptt # yum install perl-Sys-Syslog # fi...
  • CentOS8 mariaDBインストール
     ※Status code: 404 for https://dlm.mariadb.com/repo/mariadb-server/10.7....  → Ver.10 サポート切れ！！ # dnf remove MareaDB-client # curl -sS https:...