Database – Memo

H2 Database – 관련정보

인메모리로 실행시켰을 때 접속할 수 있도록 하는 빈
주소 : http://localhost:8080/console

    @Bean
    fun h2ServletRegistration(): ServletRegistrationBean {
        val bean = ServletRegistrationBean(WebServlet())
        bean.addUrlMappings("/console/*")
        return bean
    }

@Bean

fun h2ServletRegistration(): ServletRegistrationBean {

val bean = ServletRegistrationBean(WebServlet())

bean.addUrlMappings("/console/*")

return bean

}

그런데 in-memory 실행 시킬때는 콘솔접속 하지말고 그냥 stand alone 실행시켜서 접속하는게 낫지 않나

in-memory connection info

jdbc:h2:mem:{디비이름 암거나지정}

url: jdbc:h2:mem:testdb
driver-class-name: org.h2.Driver
name: {name of data source}
username: sa
password: {empty}

jdbc:h2:mem:{디비이름 암거나지정}

url: jdbc:h2:mem:testdb

driver-class-name: org.h2.Driver

name: {name of data source}

username: sa

password: {empty}

CQRS와 EventSourcing 기술을 보고

CQRS와 EventSourcing제대로 이해를 했나 모르겠는데

비슷한 생각을 하고 그런 설계를 잡으려고 했었는데,
당시에는 Hibernate ORM Envers라는게 있는줄 몰라서…
그 때의 설계는
ORM을 이용한 도메인 설계를 그대로 화면에 보여줄 수 있도록 하고 히스토리는 별도로 저장하는 구조였다.

예)를 들어 회원정보 엔티티에서
level 변동, password 변경 등에 대한 히스토리 저장이 필요하다.

RDBMS에서는 다음 엔티티의 현재상태만 저장
UserEntity { username : string password : string passwordChangeDate : date email : ref EmailEntity level : type { bronze, silver, gold, platinum, diamond, challenger } lastLoginDate : date }

로그는 별도 디비 또는 파일로 관리.
user-password-change-history (password, passwordChangeDate)
user-level-change-history
user-lastLoginDate-history

이런방식으로 현재상태와 변경상태를 저장한다.
변경히스토리를 저장하는 부분을 하나하나코딩하면 안되고,
하이버네이트 인터셉터라던가 해서 데이터변경을 확인하고 로그를 남기도록 하는 추가기능이 필요하다.

이렇게 하는 경우 RDBMS의 데이터가 메인이기 때문에 변경기록이 남지 않게 되는 상황이 생길수도 있다. 중요한 데이터는 트랜잭션을 걸면 느려
계좌 밸런스를 저장하는걸 예로들걸 그랬나

지금 CQRS컨셉으로 제시하는 것을 보면 현재상태는 메모리디비같은데 저장하는 것 같은데 소규모시스템에서 생각 해 보면
RDBMS에 현재상태를 몽땅저장하는게 괜찮아보인다.

이정도로 중규모까지는 사용가능 해 보인다.

RDBMS의 업데이트를 과도하게 사용하는 부분이 문제가 되긴 할텐데…

어쨌든.. envers쓰면 쉽게 된다.
http://hibernate.org/orm/envers/
….
잘도 만들어 놨다. 이걸 왜 못찾았을까 좀만 더 찾아볼걸

DatabaseMetaData 클래스로 메타데이터 확인

코드

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;


/**
 * @author archmagece
 * @with pizzahut-parent
 * @since 2016-11-08
 */
public class DBMetaTest {
	public static void main(String[] args)
	{
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ODRDB","username","password");
			DatabaseMetaData metadata = connection.getMetaData();

			System.out.println("allProceduresAreCallable : "+ metadata.allProceduresAreCallable());
			System.out.println("allTablesAreSelectable : "+ metadata.allTablesAreSelectable());
			System.out.println("URL : "+ metadata.getURL());
			System.out.println("UserName : "+ metadata.getUserName());
			System.out.println("isReadOnly : "+ metadata.isReadOnly());
			System.out.println("nullsAreSortedHigh : "+ metadata.nullsAreSortedHigh());
			System.out.println("nullsAreSortedLow : "+ metadata.nullsAreSortedLow());
			System.out.println("nullsAreSortedAtStart : "+ metadata.nullsAreSortedAtStart());
			System.out.println("nullsAreSortedAtEnd : "+ metadata.nullsAreSortedAtEnd());
			System.out.println("getDatabaseProductName : "+ metadata.getDatabaseProductName());
			System.out.println("getDatabaseProductVersion : "+ metadata.getDatabaseProductVersion());
			System.out.println("getDriverName : "+ metadata.getDriverName());
			System.out.println("getDriverVersion : "+ metadata.getDriverVersion());
			System.out.println("getDriverMajorVersion : "+ metadata.getDriverMajorVersion());
			System.out.println("getDriverMinorVersion : "+ metadata.getDriverMinorVersion());
			System.out.println("usesLocalFiles : "+ metadata.usesLocalFiles());
			System.out.println("usesLocalFilePerTable : "+ metadata.usesLocalFilePerTable());
			System.out.println("supportsMixedCaseIdentifiers : "+ metadata.supportsMixedCaseIdentifiers());
			System.out.println("storesUpperCaseIdentifiers : "+ metadata.storesUpperCaseIdentifiers());
			System.out.println("storesLowerCaseIdentifiers : "+ metadata.storesLowerCaseIdentifiers());
			System.out.println("storesMixedCaseIdentifiers : "+ metadata.storesMixedCaseIdentifiers());
			System.out.println("supportsMixedCaseQuotedIdentifiers : "+ metadata.supportsMixedCaseQuotedIdentifiers());
			System.out.println("storesUpperCaseQuotedIdentifiers : "+ metadata.storesUpperCaseQuotedIdentifiers());
			System.out.println("storesLowerCaseQuotedIdentifiers : "+ metadata.storesLowerCaseQuotedIdentifiers());
			System.out.println("storesMixedCaseQuotedIdentifiers : "+ metadata.storesMixedCaseQuotedIdentifiers());
			System.out.println("getIdentifierQuoteString : "+ metadata.getIdentifierQuoteString());
			System.out.println("getSQLKeywords : "+ metadata.getSQLKeywords());
			System.out.println("getNumericFunctions : "+ metadata.getNumericFunctions());
			System.out.println("getStringFunctions : "+ metadata.getStringFunctions());
			System.out.println("getSystemFunctions : "+ metadata.getSystemFunctions());
			System.out.println("getTimeDateFunctions : "+ metadata.getTimeDateFunctions());
			System.out.println("getSearchStringEscape : "+ metadata.getSearchStringEscape());
			System.out.println("getExtraNameCharacters : "+ metadata.getExtraNameCharacters());
			System.out.println("supportsAlterTableWithAddColumn : "+ metadata.supportsAlterTableWithAddColumn());
			System.out.println("supportsAlterTableWithDropColumn : "+ metadata.supportsAlterTableWithDropColumn());
			System.out.println("supportsColumnAliasing : "+ metadata.supportsColumnAliasing());
			System.out.println("nullPlusNonNullIsNull : "+ metadata.nullPlusNonNullIsNull());
			System.out.println("supportsConvert : "+ metadata.supportsConvert());
			//supportsConvert(i" : "+ nt fromType, int toType)
			System.out.println("supportsTableCorrelationNames : "+ metadata.supportsTableCorrelationNames());
			System.out.println("supportsDifferentTableCorrelationNames : "+ metadata.supportsDifferentTableCorrelationNames());
			System.out.println("supportsExpressionsInOrderBy : "+ metadata.supportsExpressionsInOrderBy());
			System.out.println("supportsOrderByUnrelated : "+ metadata.supportsOrderByUnrelated());
			System.out.println("supportsGroupBy : "+ metadata.supportsGroupBy());
			System.out.println("supportsGroupByUnrelated : "+ metadata.supportsGroupByUnrelated());
			System.out.println("supportsGroupByBeyondSelect : "+ metadata.supportsGroupByBeyondSelect());
			System.out.println("supportsLikeEscapeClause : "+ metadata.supportsLikeEscapeClause());
			System.out.println("supportsMultipleResultSets : "+ metadata.supportsMultipleResultSets());
			System.out.println("supportsMultipleTransactions : "+ metadata.supportsMultipleTransactions());
			System.out.println("supportsNonNullableColumns : "+ metadata.supportsNonNullableColumns());
			System.out.println("supportsMinimumSQLGrammar : "+ metadata.supportsMinimumSQLGrammar());
			System.out.println("supportsCoreSQLGrammar : "+ metadata.supportsCoreSQLGrammar());
			System.out.println("supportsExtendedSQLGrammar : "+ metadata.supportsExtendedSQLGrammar());
			System.out.println("supportsANSI92EntryLevelSQL : "+ metadata.supportsANSI92EntryLevelSQL());
			System.out.println("supportsANSI92IntermediateSQL : "+ metadata.supportsANSI92IntermediateSQL());
			System.out.println("supportsANSI92FullSQL : "+ metadata.supportsANSI92FullSQL());
			System.out.println("supportsIntegrityEnhancementFacility : "+ metadata.supportsIntegrityEnhancementFacility());
			System.out.println("supportsOuterJoins : "+ metadata.supportsOuterJoins());
			System.out.println("supportsFullOuterJoins: "+ metadata.supportsFullOuterJoins());
			System.out.println("supportsLimitedOuterJoins : "+ metadata.supportsLimitedOuterJoins());
			System.out.println("getSchemaTerm : "+ metadata.getSchemaTerm());
			System.out.println("getProcedureTerm : "+ metadata.getProcedureTerm());
			System.out.println("getCatalogTerm : "+ metadata.getCatalogTerm());
			System.out.println("isCatalogAtStart : "+ metadata.isCatalogAtStart());
			System.out.println("getCatalogSeparator : "+ metadata.getCatalogSeparator());
			System.out.println("supportsSchemasInDataManipulation : "+ metadata.supportsSchemasInDataManipulation());
			System.out.println("supportsSchemasInProcedureCalls : "+ metadata.supportsSchemasInProcedureCalls());
			System.out.println("supportsSchemasInTableDefinitions : "+ metadata.supportsSchemasInTableDefinitions());
			System.out.println("supportsSchemasInIndexDefinitions : "+ metadata.supportsSchemasInIndexDefinitions());
			System.out.println("supportsSchemasInPrivilegeDefinitions : "+ metadata.supportsSchemasInPrivilegeDefinitions());
			System.out.println("supportsCatalogsInDataManipulation : "+ metadata.supportsCatalogsInDataManipulation());
			System.out.println("supportsCatalogsInProcedureCalls : "+ metadata.supportsCatalogsInProcedureCalls());
			System.out.println("supportsCatalogsInTableDefinitions : "+ metadata.supportsCatalogsInTableDefinitions());
			System.out.println("supportsCatalogsInIndexDefinitions : "+ metadata.supportsCatalogsInIndexDefinitions());
			System.out.println("supportsCatalogsInPrivilegeDefinitions : "+ metadata.supportsCatalogsInPrivilegeDefinitions());
			System.out.println("supportsPositionedDelete : "+ metadata.supportsPositionedDelete());
			System.out.println("supportsPositionedUpdate : "+ metadata.supportsPositionedUpdate());
			System.out.println("supportsSelectForUpdate : "+ metadata.supportsSelectForUpdate());
			System.out.println("supportsStoredProcedures : "+ metadata.supportsStoredProcedures());
			System.out.println("supportsSubqueriesInComparisons : "+ metadata.supportsSubqueriesInComparisons());
			System.out.println("supportsSubqueriesInExists : "+ metadata.supportsSubqueriesInExists());
			System.out.println("supportsSubqueriesInIns : "+ metadata.supportsSubqueriesInIns());
			System.out.println("supportsSubqueriesInQuantifieds : "+ metadata.supportsSubqueriesInQuantifieds());
			System.out.println("supportsCorrelatedSubqueries : "+ metadata.supportsCorrelatedSubqueries());
			System.out.println("supportsUnion : "+ metadata.supportsUnion());
			System.out.println("supportsUnionAll : "+ metadata.supportsUnionAll());
			System.out.println("supportsOpenCursorsAcrossCommit : "+ metadata.supportsOpenCursorsAcrossCommit());
			System.out.println("supportsOpenCursorsAcrossRollback : "+ metadata.supportsOpenCursorsAcrossRollback());
			System.out.println("supportsOpenStatementsAcrossCommit : "+ metadata.supportsOpenStatementsAcrossCommit());
			System.out.println("supportsOpenStatementsAcrossRollback : "+ metadata.supportsOpenStatementsAcrossRollback());
			System.out.println("getMaxBinaryLiteralLength : "+ metadata.getMaxBinaryLiteralLength());
			System.out.println("getMaxCharLiteralLength : "+ metadata.getMaxCharLiteralLength());
			System.out.println("getMaxColumnNameLength : "+ metadata.getMaxColumnNameLength());
			System.out.println("getMaxColumnsInGroupBy : "+ metadata.getMaxColumnsInGroupBy());
			System.out.println("getMaxColumnsInIndex : "+ metadata.getMaxColumnsInIndex());
			System.out.println("getMaxColumnsInOrderBy : "+ metadata.getMaxColumnsInOrderBy());
			System.out.println("getMaxColumnsInSelect : "+ metadata.getMaxColumnsInSelect());
			System.out.println("getMaxColumnsInTable : "+ metadata.getMaxColumnsInTable());
			System.out.println("getMaxConnections : "+ metadata.getMaxConnections());
			System.out.println("getMaxCursorNameLength : "+ metadata.getMaxCursorNameLength());
			System.out.println("getMaxIndexLength : "+ metadata.getMaxIndexLength());
			System.out.println("getMaxSchemaNameLength : "+ metadata.getMaxSchemaNameLength());
			System.out.println("getMaxProcedureNameLength : "+ metadata.getMaxProcedureNameLength());
			System.out.println("getMaxCatalogNameLength : "+ metadata.getMaxCatalogNameLength());
			System.out.println("getMaxRowSize : "+ metadata.getMaxRowSize());
			System.out.println("doesMaxRowSizeIncludeBlobs : "+ metadata.doesMaxRowSizeIncludeBlobs());
			System.out.println("getMaxStatementLength : "+ metadata.getMaxStatementLength());
			System.out.println("getMaxStatements : "+ metadata.getMaxStatements());
			System.out.println("getMaxTableNameLength : "+ metadata.getMaxTableNameLength());
			System.out.println("getMaxTablesInSelect : "+ metadata.getMaxTablesInSelect());
			System.out.println("getMaxUserNameLength : "+ metadata.getMaxUserNameLength());
			System.out.println("getDefaultTransactionIsolation : "+ metadata.getDefaultTransactionIsolation());
			System.out.println("supportsTransactions : "+ metadata.supportsTransactions());
//			System.out.println(" : "+ metadata.supportsTransactionIsolationLevel());
			System.out.println("supportsDataDefinitionAndDataManipulationTransactions : "+ metadata.supportsDataDefinitionAndDataManipulationTransactions());
			System.out.println("supportsDataManipulationTransactionsOnly : "+ metadata.supportsDataManipulationTransactionsOnly());
			System.out.println("dataDefinitionCausesTransactionCommit : "+ metadata.dataDefinitionCausesTransactionCommit());
			System.out.println("dataDefinitionIgnoredInTransactions : "+ metadata.dataDefinitionIgnoredInTransactions());
//			System.out.println(" : "+ metadata.getProcedures());
//			System.out.println(" : "+ metadata.getProcedureColumns());
//			System.out.println(" : "+ metadata.getTables());
//			System.out.println(" : "+ metadata.getSchemas());
//			System.out.println(" : "+ metadata.getCatalogs());
//			System.out.println(" : "+ metadata.getTableTypes());
//			System.out.println(" : "+ metadata.getColumns());
//			System.out.println(" : "+ metadata.getColumnPrivileges());
//			System.out.println(" : "+ metadata.getTablePrivileges());
//			System.out.println(" : "+ metadata.getBestRowIdentifier());
//			System.out.println(" : "+ metadata.getVersionColumns());
//			System.out.println(" : "+ metadata.getPrimaryKeys());
//			System.out.println(" : "+ metadata.getImportedKeys());
//			System.out.println(" : "+ metadata.getExportedKeys());
//			System.out.println(" : "+ metadata.getCrossReference());
//			System.out.println(" : "+ metadata.getTypeInfo());
//			System.out.println(" : "+ metadata.getIndexInfo());
//			System.out.println(" : "+ metadata.supportsResultSetType());
//			System.out.println(" : "+ metadata.supportsResultSetConcurrency());
//			System.out.println(" : "+ metadata.ownUpdatesAreVisible());
//			System.out.println(" : "+ metadata.ownDeletesAreVisible());
//			System.out.println(" : "+ metadata.ownInsertsAreVisible());
//			System.out.println(" : "+ metadata.othersUpdatesAreVisible());
//			System.out.println(" : "+ metadata.othersDeletesAreVisible());
//			System.out.println(" : "+ metadata.othersInsertsAreVisible());
//			System.out.println(" : "+ metadata.updatesAreDetected());
//			System.out.println(" : "+ metadata.deletesAreDetected());
//			System.out.println(" : "+ metadata.insertsAreDetected());
			System.out.println("supportsBatchUpdates : "+ metadata.supportsBatchUpdates());
//			System.out.println(" : "+ metadata.getUDTs());
			System.out.println("getConnection : "+ metadata.getConnection());
			System.out.println("supportsSavepoints : "+ metadata.supportsSavepoints());
			System.out.println("supportsNamedParameters : "+ metadata.supportsNamedParameters());
			System.out.println("supportsMultipleOpenResults : "+ metadata.supportsMultipleOpenResults());
			System.out.println("supportsGetGeneratedKeys : "+ metadata.supportsGetGeneratedKeys());
//			System.out.println(" : "+ metadata.getSuperTypes());
//			System.out.println(" : "+ metadata.getSuperTables());
//			System.out.println(" : "+ metadata.getAttributes());
//			System.out.println(" : "+ metadata.supportsResultSetHoldability());
			System.out.println("getResultSetHoldability : "+ metadata.getResultSetHoldability());
			System.out.println("getDatabaseMajorVersion : "+ metadata.getDatabaseMajorVersion());
			System.out.println("getDatabaseMinorVersion : "+ metadata.getDatabaseMinorVersion());
			System.out.println("getJDBCMajorVersion : "+ metadata.getJDBCMajorVersion());
			System.out.println("getJDBCMinorVersion : "+ metadata.getJDBCMinorVersion());
			System.out.println("getSQLStateType : "+ metadata.getSQLStateType());
			System.out.println("locatorsUpdateCopy : "+ metadata.locatorsUpdateCopy());
			System.out.println("supportsStatementPooling : "+ metadata.supportsStatementPooling());
			System.out.println("getRowIdLifetime : "+ metadata.getRowIdLifetime());
//			System.out.println(" : "+ metadata.getSchemas(String catalog, String schemaPattern));
			System.out.println("supportsStoredFunctionsUsingCallSyntax : "+ metadata.supportsStoredFunctionsUsingCallSyntax());
			System.out.println("autoCommitFailureClosesAllResultSets : "+ metadata.autoCommitFailureClosesAllResultSets());
			System.out.println("getClientInfoProperties : "+ metadata.getClientInfoProperties());
//			System.out.println(" : "+ metadata.getFunctions());
//			System.out.println(" : "+ metadata.getFunctionColumns());
//			System.out.println(" : "+ metadata.getPseudoColumns());
//			System.out.println("generatedKeyAlwaysReturned : "+ metadata.generatedKeyAlwaysReturned());
			System.out.println("getMaxLogicalLobSize : "+ metadata.getMaxLogicalLobSize());
			System.out.println("Retrieves whether this database supports REF CURSOR : " +metadata.supportsRefCursors());
		} catch(Exception e) {
			e.printStackTrace();
			System.out.println(e);
		}
	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

/**

* @author archmagece

* @with pizzahut-parent

* @since 2016-11-08

public class DBMetaTest {

public static void main(String[] args)

{

try{

Class.forName("oracle.jdbc.OracleDriver");

Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ODRDB","username","password");

DatabaseMetaData metadata = connection.getMetaData();

System.out.println("allProceduresAreCallable : "+ metadata.allProceduresAreCallable());

System.out.println("allTablesAreSelectable : "+ metadata.allTablesAreSelectable());

System.out.println("URL : "+ metadata.getURL());

System.out.println("UserName : "+ metadata.getUserName());

System.out.println("isReadOnly : "+ metadata.isReadOnly());

System.out.println("nullsAreSortedHigh : "+ metadata.nullsAreSortedHigh());

System.out.println("nullsAreSortedLow : "+ metadata.nullsAreSortedLow());

System.out.println("nullsAreSortedAtStart : "+ metadata.nullsAreSortedAtStart());

System.out.println("nullsAreSortedAtEnd : "+ metadata.nullsAreSortedAtEnd());

System.out.println("getDatabaseProductName : "+ metadata.getDatabaseProductName());

System.out.println("getDatabaseProductVersion : "+ metadata.getDatabaseProductVersion());

System.out.println("getDriverName : "+ metadata.getDriverName());

System.out.println("getDriverVersion : "+ metadata.getDriverVersion());

System.out.println("getDriverMajorVersion : "+ metadata.getDriverMajorVersion());

System.out.println("getDriverMinorVersion : "+ metadata.getDriverMinorVersion());

System.out.println("usesLocalFiles : "+ metadata.usesLocalFiles());

System.out.println("usesLocalFilePerTable : "+ metadata.usesLocalFilePerTable());

System.out.println("supportsMixedCaseIdentifiers : "+ metadata.supportsMixedCaseIdentifiers());

System.out.println("storesUpperCaseIdentifiers : "+ metadata.storesUpperCaseIdentifiers());

System.out.println("storesLowerCaseIdentifiers : "+ metadata.storesLowerCaseIdentifiers());

System.out.println("storesMixedCaseIdentifiers : "+ metadata.storesMixedCaseIdentifiers());

System.out.println("supportsMixedCaseQuotedIdentifiers : "+ metadata.supportsMixedCaseQuotedIdentifiers());

System.out.println("storesUpperCaseQuotedIdentifiers : "+ metadata.storesUpperCaseQuotedIdentifiers());

System.out.println("storesLowerCaseQuotedIdentifiers : "+ metadata.storesLowerCaseQuotedIdentifiers());

System.out.println("storesMixedCaseQuotedIdentifiers : "+ metadata.storesMixedCaseQuotedIdentifiers());

System.out.println("getIdentifierQuoteString : "+ metadata.getIdentifierQuoteString());

System.out.println("getSQLKeywords : "+ metadata.getSQLKeywords());

System.out.println("getNumericFunctions : "+ metadata.getNumericFunctions());

System.out.println("getStringFunctions : "+ metadata.getStringFunctions());

System.out.println("getSystemFunctions : "+ metadata.getSystemFunctions());

System.out.println("getTimeDateFunctions : "+ metadata.getTimeDateFunctions());

System.out.println("getSearchStringEscape : "+ metadata.getSearchStringEscape());

System.out.println("getExtraNameCharacters : "+ metadata.getExtraNameCharacters());

System.out.println("supportsAlterTableWithAddColumn : "+ metadata.supportsAlterTableWithAddColumn());

System.out.println("supportsAlterTableWithDropColumn : "+ metadata.supportsAlterTableWithDropColumn());

System.out.println("supportsColumnAliasing : "+ metadata.supportsColumnAliasing());

System.out.println("nullPlusNonNullIsNull : "+ metadata.nullPlusNonNullIsNull());

System.out.println("supportsConvert : "+ metadata.supportsConvert());

//supportsConvert(i" : "+ nt fromType, int toType)

System.out.println("supportsTableCorrelationNames : "+ metadata.supportsTableCorrelationNames());

System.out.println("supportsDifferentTableCorrelationNames : "+ metadata.supportsDifferentTableCorrelationNames());

System.out.println("supportsExpressionsInOrderBy : "+ metadata.supportsExpressionsInOrderBy());

System.out.println("supportsOrderByUnrelated : "+ metadata.supportsOrderByUnrelated());

System.out.println("supportsGroupBy : "+ metadata.supportsGroupBy());

System.out.println("supportsGroupByUnrelated : "+ metadata.supportsGroupByUnrelated());

System.out.println("supportsGroupByBeyondSelect : "+ metadata.supportsGroupByBeyondSelect());

System.out.println("supportsLikeEscapeClause : "+ metadata.supportsLikeEscapeClause());

System.out.println("supportsMultipleResultSets : "+ metadata.supportsMultipleResultSets());

System.out.println("supportsMultipleTransactions : "+ metadata.supportsMultipleTransactions());

System.out.println("supportsNonNullableColumns : "+ metadata.supportsNonNullableColumns());

System.out.println("supportsMinimumSQLGrammar : "+ metadata.supportsMinimumSQLGrammar());

System.out.println("supportsCoreSQLGrammar : "+ metadata.supportsCoreSQLGrammar());

System.out.println("supportsExtendedSQLGrammar : "+ metadata.supportsExtendedSQLGrammar());

System.out.println("supportsANSI92EntryLevelSQL : "+ metadata.supportsANSI92EntryLevelSQL());

System.out.println("supportsANSI92IntermediateSQL : "+ metadata.supportsANSI92IntermediateSQL());

System.out.println("supportsANSI92FullSQL : "+ metadata.supportsANSI92FullSQL());

System.out.println("supportsIntegrityEnhancementFacility : "+ metadata.supportsIntegrityEnhancementFacility());

System.out.println("supportsOuterJoins : "+ metadata.supportsOuterJoins());

System.out.println("supportsFullOuterJoins: "+ metadata.supportsFullOuterJoins());

System.out.println("supportsLimitedOuterJoins : "+ metadata.supportsLimitedOuterJoins());

System.out.println("getSchemaTerm : "+ metadata.getSchemaTerm());

System.out.println("getProcedureTerm : "+ metadata.getProcedureTerm());

System.out.println("getCatalogTerm : "+ metadata.getCatalogTerm());

System.out.println("isCatalogAtStart : "+ metadata.isCatalogAtStart());

System.out.println("getCatalogSeparator : "+ metadata.getCatalogSeparator());

System.out.println("supportsSchemasInDataManipulation : "+ metadata.supportsSchemasInDataManipulation());

System.out.println("supportsSchemasInProcedureCalls : "+ metadata.supportsSchemasInProcedureCalls());

System.out.println("supportsSchemasInTableDefinitions : "+ metadata.supportsSchemasInTableDefinitions());

System.out.println("supportsSchemasInIndexDefinitions : "+ metadata.supportsSchemasInIndexDefinitions());

System.out.println("supportsSchemasInPrivilegeDefinitions : "+ metadata.supportsSchemasInPrivilegeDefinitions());

System.out.println("supportsCatalogsInDataManipulation : "+ metadata.supportsCatalogsInDataManipulation());

System.out.println("supportsCatalogsInProcedureCalls : "+ metadata.supportsCatalogsInProcedureCalls());

System.out.println("supportsCatalogsInTableDefinitions : "+ metadata.supportsCatalogsInTableDefinitions());

System.out.println("supportsCatalogsInIndexDefinitions : "+ metadata.supportsCatalogsInIndexDefinitions());

System.out.println("supportsCatalogsInPrivilegeDefinitions : "+ metadata.supportsCatalogsInPrivilegeDefinitions());

System.out.println("supportsPositionedDelete : "+ metadata.supportsPositionedDelete());

System.out.println("supportsPositionedUpdate : "+ metadata.supportsPositionedUpdate());

System.out.println("supportsSelectForUpdate : "+ metadata.supportsSelectForUpdate());

System.out.println("supportsStoredProcedures : "+ metadata.supportsStoredProcedures());

System.out.println("supportsSubqueriesInComparisons : "+ metadata.supportsSubqueriesInComparisons());

System.out.println("supportsSubqueriesInExists : "+ metadata.supportsSubqueriesInExists());

System.out.println("supportsSubqueriesInIns : "+ metadata.supportsSubqueriesInIns());

System.out.println("supportsSubqueriesInQuantifieds : "+ metadata.supportsSubqueriesInQuantifieds());

System.out.println("supportsCorrelatedSubqueries : "+ metadata.supportsCorrelatedSubqueries());

System.out.println("supportsUnion : "+ metadata.supportsUnion());

System.out.println("supportsUnionAll : "+ metadata.supportsUnionAll());

System.out.println("supportsOpenCursorsAcrossCommit : "+ metadata.supportsOpenCursorsAcrossCommit());

System.out.println("supportsOpenCursorsAcrossRollback : "+ metadata.supportsOpenCursorsAcrossRollback());

System.out.println("supportsOpenStatementsAcrossCommit : "+ metadata.supportsOpenStatementsAcrossCommit());

System.out.println("supportsOpenStatementsAcrossRollback : "+ metadata.supportsOpenStatementsAcrossRollback());

System.out.println("getMaxBinaryLiteralLength : "+ metadata.getMaxBinaryLiteralLength());

System.out.println("getMaxCharLiteralLength : "+ metadata.getMaxCharLiteralLength());

System.out.println("getMaxColumnNameLength : "+ metadata.getMaxColumnNameLength());

System.out.println("getMaxColumnsInGroupBy : "+ metadata.getMaxColumnsInGroupBy());

System.out.println("getMaxColumnsInIndex : "+ metadata.getMaxColumnsInIndex());

System.out.println("getMaxColumnsInOrderBy : "+ metadata.getMaxColumnsInOrderBy());

System.out.println("getMaxColumnsInSelect : "+ metadata.getMaxColumnsInSelect());

System.out.println("getMaxColumnsInTable : "+ metadata.getMaxColumnsInTable());

System.out.println("getMaxConnections : "+ metadata.getMaxConnections());

System.out.println("getMaxCursorNameLength : "+ metadata.getMaxCursorNameLength());

System.out.println("getMaxIndexLength : "+ metadata.getMaxIndexLength());

System.out.println("getMaxSchemaNameLength : "+ metadata.getMaxSchemaNameLength());

System.out.println("getMaxProcedureNameLength : "+ metadata.getMaxProcedureNameLength());

System.out.println("getMaxCatalogNameLength : "+ metadata.getMaxCatalogNameLength());

System.out.println("getMaxRowSize : "+ metadata.getMaxRowSize());

System.out.println("doesMaxRowSizeIncludeBlobs : "+ metadata.doesMaxRowSizeIncludeBlobs());

System.out.println("getMaxStatementLength : "+ metadata.getMaxStatementLength());

System.out.println("getMaxStatements : "+ metadata.getMaxStatements());

System.out.println("getMaxTableNameLength : "+ metadata.getMaxTableNameLength());

System.out.println("getMaxTablesInSelect : "+ metadata.getMaxTablesInSelect());

System.out.println("getMaxUserNameLength : "+ metadata.getMaxUserNameLength());

System.out.println("getDefaultTransactionIsolation : "+ metadata.getDefaultTransactionIsolation());

System.out.println("supportsTransactions : "+ metadata.supportsTransactions());

// System.out.println(" : "+ metadata.supportsTransactionIsolationLevel());

System.out.println("supportsDataDefinitionAndDataManipulationTransactions : "+ metadata.supportsDataDefinitionAndDataManipulationTransactions());

System.out.println("supportsDataManipulationTransactionsOnly : "+ metadata.supportsDataManipulationTransactionsOnly());

System.out.println("dataDefinitionCausesTransactionCommit : "+ metadata.dataDefinitionCausesTransactionCommit());

System.out.println("dataDefinitionIgnoredInTransactions : "+ metadata.dataDefinitionIgnoredInTransactions());

// System.out.println(" : "+ metadata.getProcedures());

// System.out.println(" : "+ metadata.getProcedureColumns());

// System.out.println(" : "+ metadata.getTables());

// System.out.println(" : "+ metadata.getSchemas());

// System.out.println(" : "+ metadata.getCatalogs());

// System.out.println(" : "+ metadata.getTableTypes());

// System.out.println(" : "+ metadata.getColumns());

// System.out.println(" : "+ metadata.getColumnPrivileges());

// System.out.println(" : "+ metadata.getTablePrivileges());

// System.out.println(" : "+ metadata.getBestRowIdentifier());

// System.out.println(" : "+ metadata.getVersionColumns());

// System.out.println(" : "+ metadata.getPrimaryKeys());

// System.out.println(" : "+ metadata.getImportedKeys());

// System.out.println(" : "+ metadata.getExportedKeys());

// System.out.println(" : "+ metadata.getCrossReference());

// System.out.println(" : "+ metadata.getTypeInfo());

// System.out.println(" : "+ metadata.getIndexInfo());

// System.out.println(" : "+ metadata.supportsResultSetType());

// System.out.println(" : "+ metadata.supportsResultSetConcurrency());

// System.out.println(" : "+ metadata.ownUpdatesAreVisible());

// System.out.println(" : "+ metadata.ownDeletesAreVisible());

// System.out.println(" : "+ metadata.ownInsertsAreVisible());

// System.out.println(" : "+ metadata.othersUpdatesAreVisible());

// System.out.println(" : "+ metadata.othersDeletesAreVisible());

// System.out.println(" : "+ metadata.othersInsertsAreVisible());

// System.out.println(" : "+ metadata.updatesAreDetected());

// System.out.println(" : "+ metadata.deletesAreDetected());

// System.out.println(" : "+ metadata.insertsAreDetected());

System.out.println("supportsBatchUpdates : "+ metadata.supportsBatchUpdates());

// System.out.println(" : "+ metadata.getUDTs());

System.out.println("getConnection : "+ metadata.getConnection());

System.out.println("supportsSavepoints : "+ metadata.supportsSavepoints());

System.out.println("supportsNamedParameters : "+ metadata.supportsNamedParameters());

System.out.println("supportsMultipleOpenResults : "+ metadata.supportsMultipleOpenResults());

System.out.println("supportsGetGeneratedKeys : "+ metadata.supportsGetGeneratedKeys());

// System.out.println(" : "+ metadata.getSuperTypes());

// System.out.println(" : "+ metadata.getSuperTables());

// System.out.println(" : "+ metadata.getAttributes());

// System.out.println(" : "+ metadata.supportsResultSetHoldability());

System.out.println("getResultSetHoldability : "+ metadata.getResultSetHoldability());

System.out.println("getDatabaseMajorVersion : "+ metadata.getDatabaseMajorVersion());

System.out.println("getDatabaseMinorVersion : "+ metadata.getDatabaseMinorVersion());

System.out.println("getJDBCMajorVersion : "+ metadata.getJDBCMajorVersion());

System.out.println("getJDBCMinorVersion : "+ metadata.getJDBCMinorVersion());

System.out.println("getSQLStateType : "+ metadata.getSQLStateType());

System.out.println("locatorsUpdateCopy : "+ metadata.locatorsUpdateCopy());

System.out.println("supportsStatementPooling : "+ metadata.supportsStatementPooling());

System.out.println("getRowIdLifetime : "+ metadata.getRowIdLifetime());

// System.out.println(" : "+ metadata.getSchemas(String catalog, String schemaPattern));

System.out.println("supportsStoredFunctionsUsingCallSyntax : "+ metadata.supportsStoredFunctionsUsingCallSyntax());

System.out.println("autoCommitFailureClosesAllResultSets : "+ metadata.autoCommitFailureClosesAllResultSets());

System.out.println("getClientInfoProperties : "+ metadata.getClientInfoProperties());

// System.out.println(" : "+ metadata.getFunctions());

// System.out.println(" : "+ metadata.getFunctionColumns());

// System.out.println(" : "+ metadata.getPseudoColumns());

// System.out.println("generatedKeyAlwaysReturned : "+ metadata.generatedKeyAlwaysReturned());

System.out.println("getMaxLogicalLobSize : "+ metadata.getMaxLogicalLobSize());

System.out.println("Retrieves whether this database supports REF CURSOR : " +metadata.supportsRefCursors());

} catch(Exception e) {

e.printStackTrace();

System.out.println(e);

}

오라클 12c 설치 후 재시작할 때 오류

기본설치 했는데 왜 자동실행이 안되는걸까

오라클DB 12c는 미완성품인가

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora'

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora'

init.ora 파일을 복사해서 설정파일을 만들어준다

$ cp /u01/app/oracle/product/12.1.0.2/db_1/dbs/init.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora

1	$ cp /u01/app/oracle/product/12.1.0.2/db_1/dbs/init.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora

그리고 다시 실행

SQL> startup
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist [/u01/app/oracle/product/12.1.0.2/db_1/dbs/<ORACLE_BASE>]
ORA-48187: specified directory does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
SQL>

SQL> startup

ORA-48108: invalid value given for the diagnostic_dest init.ora parameter

ORA-48140: the specified ADR Base directory does not exist [/u01/app/oracle/product/12.1.0.2/db_1/dbs/<ORACLE_BASE>]

ORA-48187: specified directory does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

SQL>

주석을 원래 잘 안읽는데 갑자기 눈에 들어와서…

init.ora에 보면 다음내용이 보인다

Change ‘<ORACLE_BASE>’ to point to the oracle base (the one you specify at install time)

기본 경로대로 설치했다면

ORACLE_BASE = /u01/app/oracle

db_name='cdb1'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

db_name='cdb1'

memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

# devices

control_files = (ora_control1, ora_control2)

compatible ='11.2.0'

12버전을 깔았는데 왜 11.2.0이 나와있는걸까… 의아하지만 오류가 나진 않을테니 넘어감

SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory

SQL> startup

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

또 오류발생

$ mkdir /u01/app/oracle/fast_recovery_area

1	$ mkdir /u01/app/oracle/fast_recovery_area

디렉토리를 수동으로 만들어줬다

그리고 실행

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             671088744 bytes
Database Buffers          394264576 bytes
Redo Buffers                5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes

Variable Size 671088744 bytes

Database Buffers 394264576 bytes

Redo Buffers 5455872 bytes

ORA-00205: error in identifying control file, check alert log for more info

실행은 되었으나..

오류가 나는거 보면 제대로 실행될리가 없다.

찾아보니

http://database.sarang.net/?inc=read&aid=23564&criteria=oracle&subcrit=&id=&limit=&keyword=&page=

컨트롤파일 오류라는 것 같다.

아까 설정 아래쪽에

control_files = (ora_control1, ora_control2)

이부분 수정이 필요해보인다.

control_files = (“/u01/app/oracle/oradata/orcl/control01.ctl”, “/u01/app/oracle/oradata/orcl/control02.ctl”)

여기는 찾아서 추가는했는데 이걸로 해도 되는지 확신이 없다

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             671088744 bytes
Database Buffers          394264576 bytes
Redo Buffers                5455872 bytes
ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version
11.2.0.0.0
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'


SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes

Variable Size 671088744 bytes

Database Buffers 394264576 bytes

Redo Buffers 5455872 bytes

ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version

11.2.0.0.0

ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'

SQL>

11.2버전? 무슨소린지 모르겠다

자동설치로 그냥 클릭클릭만 했을 뿐인데…

initcdb1.ora > compatible = 12.1.0.2.0 으로 변경해봤더니

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             671088744 bytes
Database Buffers          394264576 bytes
Redo Buffers                5455872 bytes
ORA-01103: database name 'ORCL' in control file is not 'CDB1'


SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes

Variable Size 671088744 bytes

Database Buffers 394264576 bytes

Redo Buffers 5455872 bytes

ORA-01103: database name 'ORCL' in control file is not 'CDB1'

SQL>

다음 오류

#db_name=’cdb1′
db_name=’orcl’

이거 다시 변경하고 서버 실행

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             671088744 bytes
Database Buffers          394264576 bytes
Redo Buffers                5455872 bytes
ORA-65093: multitenant container database not set up properly


SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes

Variable Size 671088744 bytes

Database Buffers 394264576 bytes

Redo Buffers 5455872 bytes

ORA-65093: multitenant container database not set up properly

SQL>

에러확인

[oracle@localhost dbs]$ oerr ora 65093
65093, 00000, "multitenant container database not set up properly"
// *Cause:  An attempt was made to open a multitenant container database without
//          the correct parameter set for a multitenant container database in
//          the initialization parameter file.
// *Action: Set the 'enable_pluggable_database=true' parameter for the
//          multitenant container database in the initialization parameter file
//          and restart the database.
//
[oracle@localhost dbs]$

[oracle@localhost dbs]$ oerr ora 65093

65093, 00000, "multitenant container database not set up properly"

// *Cause: An attempt was made to open a multitenant container database without

// the correct parameter set for a multitenant container database in

// the initialization parameter file.

// *Action: Set the 'enable_pluggable_database=true' parameter for the

// multitenant container database in the initialization parameter file

// and restart the database.

[oracle@localhost dbs]$

진짜 이것만 하면 되는건가

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             671088744 bytes
Database Buffers          394264576 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes

Variable Size 671088744 bytes

Database Buffers 394264576 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

되네

오라클은 엔지니어 쓰게 만들라고 어렵게 해놓나보다

오라클 안 쓰는 회사나 SE있는 회사로 이직 해야겠다

오라클 11g 삭제

개발하다보면 이것저것 깔고 지우고 많이 하게 되는데 오라클은 자동으로 삭제가 안된다

지우면서 삭제순서 기록

1. 삭제 프로그램 실행 (프로그램-Oracle)

deinstall 버튼이 있으니 잘 찾아보면 된다.

바로 삭제되는건 아니고 bat파일 위치를 알려준다.
(윈도우 버전에 따라 그냥 삭제가 되는건 아니겠지? 확실히 윈10에서는 안된다)

<oracle_home>/deinstall/deinstall.bat

대충 설치한 경우 이 위치에 잡힌다
/product/11.2.0/dbhome_1/deinstall/deinsta.bat

이걸 관리자 권한으로 실행

[ENTER][ENTER][ENTER][ENTER][ENTER][ENTER][ENTER]

그 후

2. 서비스는 알아서 정지를

cmd관리자 권한으로 실행 후 아래 명령어 실행

sc delete OracleVssWriterORCL
sc delete OracleDBConsoleorcl
sc delete OracleJobSchedulerORCL
sc delete OracleOraDb11g_home2TNSListener
sc delete OracleServiceORCL

sc delete OracleVssWriterORCL

sc delete OracleDBConsoleorcl

sc delete OracleJobSchedulerORCL

sc delete OracleOraDb11g_home2TNSListener

sc delete OracleServiceORCL

글쓴이와 설치를 다르게 했다면 다를 수 있다.

3. 레지스트리 정리

regedit

Ora, ORACLE를 다 삭제하면 자바나 버추어박스 오류가 날 수 있으므로 찾아서 지워야한다

HKEY_CLASSES_ROOT\OracleConfig*
HKEY_CLASSES_ROOT\OracleDatabase*
HKEY_CLASSES_ROOT\OracleHome*
HKEY_CLASSES_ROOT\OracleDatabaseHome*
HKEY_CLASSES_ROOT\ORAMM*
HKEY_CLASSES_ROOT\Orcl*

VirtualBox붙은거 빼고 다 지우면 된다
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY*
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP*
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\SYS*

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Oracle11

HKEY_CLASSES_ROOT\OracleConfig*

HKEY_CLASSES_ROOT\OracleDatabase*

HKEY_CLASSES_ROOT\OracleHome*

HKEY_CLASSES_ROOT\OracleDatabaseHome*

HKEY_CLASSES_ROOT\ORAMM*

HKEY_CLASSES_ROOT\Orcl*

VirtualBox붙은거 빼고 다 지우면 된다

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY*

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP*

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\SYS*

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Oracle11

4. 파일삭제

삭제삭제

CentOS6 Postgresql 9.3 replication[현재안됨]

서버두개 Master Slave인 경우

대략적인 설정을 하고 확인한 것은. pg의 레플리케이션은 파일을 Master서버에서 파일을 퍼와서 Slave서버에 바로 배치입력시키는 과정을 자동화해놓은 정도의 수준인 것처럼 보인다.

Master

ip : 192.168.0.101
username : testuser
password : testpass1234
권한 : replication

아이디 비밀번호가 같을 필요는 없지만 편의상 같ㄱ ㅔ한다

Slave
ip : 192.168.0.102
username : testuser
password : testpass1234

Master서버 설정

데이터 경로

자동설치의 경우 /etc/init.d/postgresql-0.3 스크립트에서 수정해야된다

# Set defaults for configuration variables
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
#PGDATA=/var/lib/pgsql/9.3/data
PGDATA=/home/postgres/data
#PGLOG=/var/lib/pgsql/9.3/pgstartup.log
PGLOG=/home/postgres/data/pg_log/pgstartup.log
# Log file for pg_upgrade
#PGUPLOG=/var/lib/pgsql/data/$PGMAJORVERSION/pgupgrade.log
PGUPLOG=/home/postgres/data/$PGMAJORVERSION/pgupgrade.log

# Set defaults for configuration variables

PGENGINE=/usr/pgsql-9.3/bin

PGPORT=5432

#PGDATA=/var/lib/pgsql/9.3/data

PGDATA=/home/postgres/data

#PGLOG=/var/lib/pgsql/9.3/pgstartup.log

PGLOG=/home/postgres/data/pg_log/pgstartup.log

# Log file for pg_upgrade

#PGUPLOG=/var/lib/pgsql/data/$PGMAJORVERSION/pgupgrade.log

PGUPLOG=/home/postgres/data/$PGMAJORVERSION/pgupgrade.log

pg_hba.conf

local trust, 외부접속설정. replication설정을 주의

# "local" is for Unix domain socket connections only
###trust로하자 편하게
local   all             all                                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
## 아무데서나 접속하게해준다는 뜻. 하나하나 설정해주는게 보안에 좋다. 내 경우는 방화벽도 있고 암호도 있으니까 그냥 ip는 풀어놨다
host    all             all             0.0.0.0/0               md5

# IPv6 local connections:
host    all             all             ::1/128                 ident

# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
## 리플레케이션 유저를 설정. trust 또는 md5로 보통 쓴다
host    replication     testuser            192.168.0.102/0         trust

# "local" is for Unix domain socket connections only

###trust로하자 편하게

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 ident

## 아무데서나 접속하게해준다는 뜻. 하나하나 설정해주는게 보안에 좋다. 내 경우는 방화벽도 있고 암호도 있으니까 그냥 ip는 풀어놨다

host all all 0.0.0.0/0 md5

# IPv6 local connections:

host all all ::1/128 ident

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local replication postgres peer

#host replication postgres 127.0.0.1/32 ident

#host replication postgres ::1/128 ident

## 리플레케이션 유저를 설정. trust 또는 md5로 보통 쓴다

host replication testuser 192.168.0.102/0 trust

postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /home/postgres/data/pg_archive/%f'
max_wal_senders = 3
wal_keep_segments = 8

listen_addresses = '*'

port = 5432

wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /home/postgres/data/pg_archive/%f'

max_wal_senders = 3

wal_keep_segments = 8

pg인증방식

pg_hba.conf
host all all 127.0.0.1/32 trust
host all all 127.0.0.1/32 md5

local all postgres peer
local all postgres md5

Slave서버

# pg_basebackup -h 192.168.0.101 -p 5432 -U testuser -D /home/postgres/data --xlog --checkpoint=fast --progress
# chown postgres:postgres /home/postgres -R
# mkdir /home/postgres/data/pg_archive

# pg_basebackup -h 192.168.0.101 -p 5432 -U testuser -D /home/postgres/data --xlog --checkpoint=fast --progress

# chown postgres:postgres /home/postgres -R

# mkdir /home/postgres/data/pg_archive

pg_hba.conf

노예는 이것만 설정한다. 사용만 하면되니까… 마스터에서 접근은 안한다.

host    all             all             0.0.0.0/0               md5

1	host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = hot_standby 
archive_mode = on
archive_command = 'cp %p /home/postgres/data/pg_archive/%f'
max_wal_senders = 2
wal_keep_segments = 8

listen_addresses = '*'

port = 5432

wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /home/postgres/data/pg_archive/%f'

max_wal_senders = 2

wal_keep_segments = 8

recoverty.conf

다른쪽들은 복제할 때 다 됐을테니 따로 안해도 된다… 이것만 새로 생성

restore_command = 'cp /home/postgres/data/pg_archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /home/postgres/data/pg_archive %r'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.101 port=5432 user=testuser password=testpass1234'
#trigger_file = '/tmp/pgsql.trigger'

restore_command = 'cp /home/postgres/data/pg_archive/%f %p'

archive_cleanup_command = 'pg_archivecleanup /home/postgres/data/pg_archive %r'

recovery_target_timeline = 'latest'

standby_mode = on

primary_conninfo = 'host=192.168.0.101 port=5432 user=testuser password=testpass1234'

#trigger_file = '/tmp/pgsql.trigger'

주의할점

로그확인을 잘 하자.

참고한 페이지

http://mixellaneous.tistory.com/1007
https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
http://histlinux.egloos.com/1227710
http://mixellaneous.tistory.com/1007
http://dba.stackexchange.com/questions/16781/postgresql-9-1-hot-backup-error-the-database-system-is-starting-up

Hibernate 복합키 사용하기

pk여러개 달아놓고 쓰는 방법

pk 순서를 정하는건 잘 안된다.

이 부분을 수정하려면 하이버네이트 코드 커스터마이징을 하던가 해야할듯하다.

@Data
@Embeddable
public class GameId
    implements Serializable {

	/**
     * 
     */
	private static final long serialVersionUID = -229826876863703005L;

	@Type(type = "org.beansugar.tools.hibernate.JodaDateTimeUserType")
//	@Column(name="GameTime", columnDefinition="CURRENT_TIMESTAMP")
	private DateTime          gameTime;
	private String            username;
	private String            gameCode;
}

@Data
@Entity
@Table(name = "GAME_INFO")
public class GameInfo
    implements Serializable {

	/**
     * 
     */
	private static final long serialVersionUID = -8733838789326651901L;

	@EmbeddedId
	private GameId            id;

	private boolean           callPut;
	private float             price;
}

@Data

@Embeddable

public class GameId

implements Serializable {

/**

private static final long serialVersionUID = -229826876863703005L;

@Type(type = "org.beansugar.tools.hibernate.JodaDateTimeUserType")

// @Column(name="GameTime", columnDefinition="CURRENT_TIMESTAMP")

private DateTime gameTime;

private String username;

private String gameCode;

}

@Data

@Entity

@Table(name = "GAME_INFO")

public class GameInfo

implements Serializable {

/**

private static final long serialVersionUID = -8733838789326651901L;

@EmbeddedId

private GameId id;

private boolean callPut;

private float price;

}

위 코드로 생성을 하면 아래의 쿼리가 나온다.

CREATE TABLE `GAME_INFO` (
  `gameCode` varchar(255) NOT NULL,
  `gameTime` datetime NOT NULL,
  `username` varchar(255) NOT NULL,
  `callPut` tinyint(1) NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`gameCode`,`gameTime`,`username`)
)

CREATE TABLE `GAME_INFO` (

`gameCode` varchar(255) NOT NULL,

`gameTime` datetime NOT NULL,

`username` varchar(255) NOT NULL,

`callPut` tinyint(1) NOT NULL,

`price` float NOT NULL,

PRIMARY KEY (`gameCode`,`gameTime`,`username`)

)

pk순서가 time, code, name이면 좋겠는데… 이 부분이 조절이 잘 안된다. abc순서로 순서가 잡히는거라면 이름을 바꿔서 짓는것도 고려해봐야겠다.

특별히 제공하는 방법은 없는 것 같으니… 하이버네이트에서 어노테이션을 스캔하는 코드를 찾아서 상속받아서 수정하고 다시 쑤셔넣어주는게 좋을듯하다.

소스코드를 찾아서 재컴파일해서 lib폴더나 개인용 mavenrepo에 넣는것보다는… 낫지않을까 하는 생각이다.

MySql 백업Backup & 복구|복원Restore

백업의 대상

데이터베이스 파일
오류 로그 파일, 슬로쿼리 로그 파일
바이너리 로그 파일
설정파일 my.cnf
실행 파일
기타 공유 라이브러리, 지원 스크립트, 테스트 툴

콜드 백업

1. 디비 정지
$ mysqladmin shutdown -uroot
2. 디렉토리 백업
$ tar cvf /var/lib/mysql ~/mysqlbak.tar
3. 디비 재기동
$myqld_safe &amp;

1. 디비 정지

$ mysqladmin shutdown -uroot

2. 디렉토리 백업

$ tar cvf /var/lib/mysql ~/mysqlbak.tar

3. 디비 재기동

$myqld_safe &

바이너리 로그 이용

바이너리 로그 활성화
my.cnf
log-bin을 추가

호스트이름.0000001 이라는 바이너리 파일 생성

mysqlbinlog사용
$ mysqlbinlog hostname.000011 &gt; recover.sql
$ mysql -uroot &lt; recover.sql
(mysqlbinlog는 쿼리문 마지막에 rollback를 넣는다. 문제의 소지가 있으니 참고)

바이너리 로그 활성화

my.cnf

log-bin을 추가

호스트이름.0000001 이라는 바이너리 파일 생성

mysqlbinlog사용

$ mysqlbinlog hostname.000011 > recover.sql

$ mysql -uroot < recover.sql

(mysqlbinlog는 쿼리문 마지막에 rollback를 넣는다. 문제의 소지가 있으니 참고)

온라인 백업 mysqldump

잠금걸고 온라인 백업
$ mysqldump -uroot --lock-all-tables -master-data=2 &gt; dump.sql
내용을 보고 바이너리 로그의 위치정보 찾기
$ more dump.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='hostname.00042', MASTER_LOG_POS=98;

백업파일 복구
$ mysql -uroot &lt; dump.sql
바이너리로그적용
$ mysqlbinlog --position--98 hostname.000042 &gt; binlog.sql
$ mysql -uroot &lt; binlog.sql

잠금걸고 온라인 백업

$ mysqldump -uroot --lock-all-tables -master-data=2 > dump.sql

내용을 보고 바이너리 로그의 위치정보 찾기

$ more dump.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='hostname.00042', MASTER_LOG_POS=98;

백업파일 복구

$ mysql -uroot < dump.sql

바이너리로그적용

$ mysqlbinlog --position--98 hostname.000042 > binlog.sql

$ mysql -uroot < binlog.sql

새로운 백업후 바이너리로그 삭제

mysql&gt; PURGE MASTER LOGS TO 'hostname.000042'
(42번 이전의 바이너리 로그를 모두 삭제하는 명령)

1 2	mysql> PURGE MASTER LOGS TO 'hostname.000042' (42번 이전의 바이너리 로그를 모두 삭제하는 명령)

기타 백업

lvm스냅샷

전체 디비 공유잠금
mysql&gt; FLUSH TABLES WITH READ LOCK;

바이너리 로그의 위치 정보를 알아냄
mysql&gt; SHOW MASTER STSTUS;

디스크에 기록되지 않은 정보를 기록
$ sync

스냅샷을 취득
LVM이라면 lvcreate커맨드

공유 잠금을 해제
mysql&gt; UNLOCK TABLES;

취득한 스냅샷을 테이프 등에 복사
cp..tar...등

전체 디비 공유잠금

mysql> FLUSH TABLES WITH READ LOCK;

바이너리 로그의 위치 정보를 알아냄

mysql> SHOW MASTER STSTUS;

디스크에 기록되지 않은 정보를 기록

$ sync

스냅샷을 취득

LVM이라면 lvcreate커맨드

공유 잠금을 해제

mysql> UNLOCK TABLES;

취득한 스냅샷을 테이프 등에 복사

cp..tar...등

innodb의 경우 Xtrabackup

Mysql settings for Remote Accessing

mysql -u root mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST" > = PASSWORD('new_password');

Now while we’re still here, we’ll create a new HOST for root and allow root to login from anywhere.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' > IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> exit

Hibernate 구현상의 문제점 정리

1. 설계상의 문제

Object구조를 갖추기 위해서인지는 모르겠지만.. Long id를 거의 강제하는 (커스터마이징이 가능하지만 조금 불편…) 도메인 구조때문에 설계에 제약이 생기고 디비의 성능을 제대로 발휘하지 못한다.

2. Bulk Insert 동작시

postgresql의 경우 sequence를 가져오기 위해 select nextval(‘{sequencename}’)를 연속으로 호출한다. oracle에서도 마찬가지가 아닐까 싶다. transaction처리를 위해서인것같지만… 문제가 있어보인다. 왜 이렇게 구현되어있는지 찾아봐야할 것 같다.

3. SaveOrUpdate 구현상의 문제점

Save And Update 형태로 동작한다.

upsert 기능을 지원하는 디비에서 굳이 저런식으로 동작하는 것은 비효율적이다.